password created_at does not honor timezones

[Boris Bobrov]

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.

[OpenStack Infra (hudson-openstack)]

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

[Steve Martinelli (stevemar)]

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.

[Ron De Rose (ronald-de-rose)]

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

[Steve Martinelli (stevemar)]

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.

[OpenStack Infra (hudson-openstack)]

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

[OpenStack Infra (hudson-openstack)]

Fix proposed to branch: stable/newton
Review: https://review.openstack.org/371839

[OpenStack Infra (hudson-openstack)]

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)

[OpenStack Infra (hudson-openstack)]

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

[OpenStack Infra (hudson-openstack)]

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