password created_at does not honor timezones

Bug #1621200 reported by Boris Bobrov on 2016-09-07
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
OpenStack Identity (keystone)
High
Ron De Rose
Newton
High
Ron De Rose

Bug Description

This was initially discovered when running the unit tests for migration 002 in a timezone that is UTC+3.

Migration 002 sets the password created_at column to a TIMESTAMP type
with a server_default=sql.func.now(). There are a couple problems
that have been uncovered with this change:
* We cannot guarantee that func.now() will generate a UTC timestamp.
* For some older versions of MySQL, the TIMESTAMP column will
automatically be updated when other columns are updated:
https://dev.mysql.com/doc/refman/5.5/en/timestamp-initialization.html

Steps to reproduce:
1. dpkg-reconfigure tzdata and select there Europe/Moscow (UTC+3).
2. Restart mysql
3. Configure opportunistic tests with the following command in mysql:
GRANT ALL PRIVILEGES ON *.* TO 'openstack_citest' @'%' identified by 'openstack_citest' WITH GRANT OPTION;
4. Run keystone.tests.unit.identity.backends.test_sql.MySQLOpportunisticIdentityDriverTestCase.test_change_password

Expected result: test pass

Actual result:
    Traceback (most recent call last):
      File "keystone/tests/unit/identity/backends/test_base.py", line 255, in test_change_password
        self.driver.authenticate(user['id'], new_password)
      File "keystone/identity/backends/sql.py", line 65, in authenticate
        raise AssertionError(_('Invalid user / password'))
    AssertionError: Invalid user / password

Aside from the test issue, we should be saving all time related data in DateTime format instead of TIMESTAMP.

Boris Bobrov (bbobrov) on 2016-09-07
description: updated
Changed in keystone:
assignee: nobody → Ron De Rose (ronald-de-rose)
Changed in keystone:
status: New → In Progress

Fix proposed to branch: master
Review: https://review.openstack.org/367374

Changed in keystone:
assignee: Ron De Rose (ronald-de-rose) → Boris Bobrov (bbobrov)
Changed in keystone:
assignee: Boris Bobrov (bbobrov) → Ron De Rose (ronald-de-rose)
tags: added: rc-potential

Can we get a new bug or update this bug to better showcase the error that is happening? We mentioned this in today's keystone meeting.

Changed in keystone:
importance: Undecided → High
Ron De Rose (ronald-de-rose) wrote :

Updated the description. The steps to reproduce will showcase the errors.

description: updated
description: updated
Changed in keystone:
milestone: none → ocata-1
Steve Martinelli (stevemar) wrote :

Due to the fact that the created_at column was created in the Newton milestone 3 release it is best to fix this time sensitive attribute in the same release. Let's create an rc2 milestone and backport the fix.

summary: - MySQLOpportunisticIdentityDriverTestCase.test_change_password fails in
- UTC+N timezone
+ password created_at does not honor timezones
description: updated

Reviewed: https://review.openstack.org/367025
Committed: https://git.openstack.org/cgit/openstack/keystone/commit/?id=32328de6e37e1f9f55d563f8a55087dc7d6f46e1
Submitter: Jenkins
Branch: master

commit 32328de6e37e1f9f55d563f8a55087dc7d6f46e1
Author: Ronald De Rose <email address hidden>
Date: Wed Sep 7 23:51:09 2016 +0000

    Fixes password created_at errors due to the server_default

    Migration 002 sets the password created_at column to a TIMESTAMP type
    with a server_default=sql.func.now(). There are a couple problems
    that have been uncovered with this change:
    * We cannot guarantee that func.now() will generate a UTC timestamp.
    * For some older versions of MySQL, the default TIMESTAMP column will
    automatically be updated when other columns are updated:
    https://dev.mysql.com/doc/refman/5.5/en/timestamp-initialization.html

    This patch fixes the problem by recreating the password created_at
    column back to a DateTime type without a server_default:
    1) Drop and recreate the created_at column
    2) Update the created_at value
    3) Set the created_at column as not nullable

    Closes-Bug: #1621200
    Change-Id: Id5c607a777afb6565d66a336028eba796e3846b2

Changed in keystone:
status: In Progress → Fix Released

Reviewed: https://review.openstack.org/371839
Committed: https://git.openstack.org/cgit/openstack/keystone/commit/?id=b3fae7724db797c911bedec1e9ab1ded982691c5
Submitter: Jenkins
Branch: stable/newton

commit b3fae7724db797c911bedec1e9ab1ded982691c5
Author: Ronald De Rose <email address hidden>
Date: Wed Sep 7 23:51:09 2016 +0000

    Fixes password created_at errors due to the server_default

    Migration 002 sets the password created_at column to a TIMESTAMP type
    with a server_default=sql.func.now(). There are a couple problems
    that have been uncovered with this change:
    * We cannot guarantee that func.now() will generate a UTC timestamp.
    * For some older versions of MySQL, the default TIMESTAMP column will
    automatically be updated when other columns are updated:
    https://dev.mysql.com/doc/refman/5.5/en/timestamp-initialization.html

    This patch fixes the problem by recreating the password created_at
    column back to a DateTime type without a server_default:
    1) Drop and recreate the created_at column
    2) Update the created_at value
    3) Set the created_at column as not nullable

    Closes-Bug: #1621200
    Change-Id: Id5c607a777afb6565d66a336028eba796e3846b2
    (cherry picked from commit 32328de6e37e1f9f55d563f8a55087dc7d6f46e1)

Change abandoned by Boris Bobrov (<email address hidden>) on branch: master
Review: https://review.openstack.org/367374
Reason: done in another patch

This issue was fixed in the openstack/keystone 11.0.0.0b1 development milestone.

To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers