From 3e2b01d5f66c542491513d9c0db6c77f271e1d20 Mon Sep 17 00:00:00 2001 From: Stephen Finucane Date: Mon, 10 Jan 2022 16:59:56 +0000 Subject: sql: Squash ussuri migrations Make the following changes to the new "initial" migrations. - Drop foreign key constraints on 'domain_id' columns of 'user' and 'identity_provider' tables (072) - Add 'authorization_ttl' column to 'identity_provider' table (073) - Add 'expiring_user_group_membership' table (073) This concludes our squashing. We won't squash the 079 migrations, added in Xena, since this would break fast forward upgrades. Future changes will instead (finally) focus on the switch to alembic. Change-Id: Ia1495cd4683d6631be2691e816734d01b03037a3 Signed-off-by: Stephen Finucane --- .../versions/066_contract_initial_migration.py | 18 - .../sql/contract_repo/versions/067_placeholder.py | 18 - .../sql/contract_repo/versions/068_placeholder.py | 18 - .../sql/contract_repo/versions/069_placeholder.py | 18 - .../sql/contract_repo/versions/070_placeholder.py | 18 - .../sql/contract_repo/versions/071_placeholder.py | 18 - .../versions/072_contract_drop_domain_id_fk.py | 47 - .../073_contract_expiring_group_membership.py | 15 - .../versions/073_contract_initial_migration.py | 18 + .../versions/066_migrate_initial_migration.py | 56 - .../versions/067_placeholder.py | 18 - .../versions/068_placeholder.py | 18 - .../versions/069_placeholder.py | 18 - .../versions/070_placeholder.py | 18 - .../versions/071_placeholder.py | 18 - .../versions/072_migrate_drop_domain_id_fk.py | 20 - .../073_migrate_expiring_group_membership.py | 15 - .../versions/073_migrate_initial_migration.py | 56 + .../versions/066_expand_initial_migration.py | 1134 ------------------- .../sql/expand_repo/versions/067_placeholder.py | 18 - .../sql/expand_repo/versions/068_placeholder.py | 18 - .../sql/expand_repo/versions/069_placeholder.py | 18 - .../sql/expand_repo/versions/070_placeholder.py | 18 - .../sql/expand_repo/versions/071_placeholder.py | 18 - .../versions/072_expand_drop_domain_id_fk.py | 20 - .../073_expand_expiring_group_membership.py | 47 - .../versions/073_expand_initial_migration.py | 1157 ++++++++++++++++++++ keystone/common/sql/upgrades.py | 2 +- keystone/tests/unit/test_sql_upgrade.py | 45 +- 29 files changed, 1236 insertions(+), 1684 deletions(-) delete mode 100644 keystone/common/sql/contract_repo/versions/066_contract_initial_migration.py delete mode 100644 keystone/common/sql/contract_repo/versions/067_placeholder.py delete mode 100644 keystone/common/sql/contract_repo/versions/068_placeholder.py delete mode 100644 keystone/common/sql/contract_repo/versions/069_placeholder.py delete mode 100644 keystone/common/sql/contract_repo/versions/070_placeholder.py delete mode 100644 keystone/common/sql/contract_repo/versions/071_placeholder.py delete mode 100644 keystone/common/sql/contract_repo/versions/072_contract_drop_domain_id_fk.py delete mode 100644 keystone/common/sql/contract_repo/versions/073_contract_expiring_group_membership.py create mode 100644 keystone/common/sql/contract_repo/versions/073_contract_initial_migration.py delete mode 100644 keystone/common/sql/data_migration_repo/versions/066_migrate_initial_migration.py delete mode 100644 keystone/common/sql/data_migration_repo/versions/067_placeholder.py delete mode 100644 keystone/common/sql/data_migration_repo/versions/068_placeholder.py delete mode 100644 keystone/common/sql/data_migration_repo/versions/069_placeholder.py delete mode 100644 keystone/common/sql/data_migration_repo/versions/070_placeholder.py delete mode 100644 keystone/common/sql/data_migration_repo/versions/071_placeholder.py delete mode 100644 keystone/common/sql/data_migration_repo/versions/072_migrate_drop_domain_id_fk.py delete mode 100644 keystone/common/sql/data_migration_repo/versions/073_migrate_expiring_group_membership.py create mode 100644 keystone/common/sql/data_migration_repo/versions/073_migrate_initial_migration.py delete mode 100644 keystone/common/sql/expand_repo/versions/066_expand_initial_migration.py delete mode 100644 keystone/common/sql/expand_repo/versions/067_placeholder.py delete mode 100644 keystone/common/sql/expand_repo/versions/068_placeholder.py delete mode 100644 keystone/common/sql/expand_repo/versions/069_placeholder.py delete mode 100644 keystone/common/sql/expand_repo/versions/070_placeholder.py delete mode 100644 keystone/common/sql/expand_repo/versions/071_placeholder.py delete mode 100644 keystone/common/sql/expand_repo/versions/072_expand_drop_domain_id_fk.py delete mode 100644 keystone/common/sql/expand_repo/versions/073_expand_expiring_group_membership.py create mode 100644 keystone/common/sql/expand_repo/versions/073_expand_initial_migration.py diff --git a/keystone/common/sql/contract_repo/versions/066_contract_initial_migration.py b/keystone/common/sql/contract_repo/versions/066_contract_initial_migration.py deleted file mode 100644 index 1cd34e617..000000000 --- a/keystone/common/sql/contract_repo/versions/066_contract_initial_migration.py +++ /dev/null @@ -1,18 +0,0 @@ -# Licensed under the Apache License, Version 2.0 (the "License"); you may -# not use this file except in compliance with the License. You may obtain -# a copy of the License at -# -# http://www.apache.org/licenses/LICENSE-2.0 -# -# Unless required by applicable law or agreed to in writing, software -# distributed under the License is distributed on an "AS IS" BASIS, WITHOUT -# WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the -# License for the specific language governing permissions and limitations -# under the License. - -# A null initial migration to open this repo. Do not re-use replace this with -# a real migration, add additional ones in subsequent version scripts. - - -def upgrade(migrate_engine): - pass diff --git a/keystone/common/sql/contract_repo/versions/067_placeholder.py b/keystone/common/sql/contract_repo/versions/067_placeholder.py deleted file mode 100644 index 8522ef3ce..000000000 --- a/keystone/common/sql/contract_repo/versions/067_placeholder.py +++ /dev/null @@ -1,18 +0,0 @@ -# Licensed under the Apache License, Version 2.0 (the "License"); you may -# not use this file except in compliance with the License. You may obtain -# a copy of the License at -# -# http://www.apache.org/licenses/LICENSE-2.0 -# -# Unless required by applicable law or agreed to in writing, software -# distributed under the License is distributed on an "AS IS" BASIS, WITHOUT -# WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the -# License for the specific language governing permissions and limitations -# under the License. - -# This is a placeholder for Train backports. Do not use this number for new -# Ussuri work. New Ussuri work starts after all the placeholders. - - -def upgrade(migrate_engine): - pass diff --git a/keystone/common/sql/contract_repo/versions/068_placeholder.py b/keystone/common/sql/contract_repo/versions/068_placeholder.py deleted file mode 100644 index 8522ef3ce..000000000 --- a/keystone/common/sql/contract_repo/versions/068_placeholder.py +++ /dev/null @@ -1,18 +0,0 @@ -# Licensed under the Apache License, Version 2.0 (the "License"); you may -# not use this file except in compliance with the License. You may obtain -# a copy of the License at -# -# http://www.apache.org/licenses/LICENSE-2.0 -# -# Unless required by applicable law or agreed to in writing, software -# distributed under the License is distributed on an "AS IS" BASIS, WITHOUT -# WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the -# License for the specific language governing permissions and limitations -# under the License. - -# This is a placeholder for Train backports. Do not use this number for new -# Ussuri work. New Ussuri work starts after all the placeholders. - - -def upgrade(migrate_engine): - pass diff --git a/keystone/common/sql/contract_repo/versions/069_placeholder.py b/keystone/common/sql/contract_repo/versions/069_placeholder.py deleted file mode 100644 index 8522ef3ce..000000000 --- a/keystone/common/sql/contract_repo/versions/069_placeholder.py +++ /dev/null @@ -1,18 +0,0 @@ -# Licensed under the Apache License, Version 2.0 (the "License"); you may -# not use this file except in compliance with the License. You may obtain -# a copy of the License at -# -# http://www.apache.org/licenses/LICENSE-2.0 -# -# Unless required by applicable law or agreed to in writing, software -# distributed under the License is distributed on an "AS IS" BASIS, WITHOUT -# WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the -# License for the specific language governing permissions and limitations -# under the License. - -# This is a placeholder for Train backports. Do not use this number for new -# Ussuri work. New Ussuri work starts after all the placeholders. - - -def upgrade(migrate_engine): - pass diff --git a/keystone/common/sql/contract_repo/versions/070_placeholder.py b/keystone/common/sql/contract_repo/versions/070_placeholder.py deleted file mode 100644 index 8522ef3ce..000000000 --- a/keystone/common/sql/contract_repo/versions/070_placeholder.py +++ /dev/null @@ -1,18 +0,0 @@ -# Licensed under the Apache License, Version 2.0 (the "License"); you may -# not use this file except in compliance with the License. You may obtain -# a copy of the License at -# -# http://www.apache.org/licenses/LICENSE-2.0 -# -# Unless required by applicable law or agreed to in writing, software -# distributed under the License is distributed on an "AS IS" BASIS, WITHOUT -# WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the -# License for the specific language governing permissions and limitations -# under the License. - -# This is a placeholder for Train backports. Do not use this number for new -# Ussuri work. New Ussuri work starts after all the placeholders. - - -def upgrade(migrate_engine): - pass diff --git a/keystone/common/sql/contract_repo/versions/071_placeholder.py b/keystone/common/sql/contract_repo/versions/071_placeholder.py deleted file mode 100644 index 8522ef3ce..000000000 --- a/keystone/common/sql/contract_repo/versions/071_placeholder.py +++ /dev/null @@ -1,18 +0,0 @@ -# Licensed under the Apache License, Version 2.0 (the "License"); you may -# not use this file except in compliance with the License. You may obtain -# a copy of the License at -# -# http://www.apache.org/licenses/LICENSE-2.0 -# -# Unless required by applicable law or agreed to in writing, software -# distributed under the License is distributed on an "AS IS" BASIS, WITHOUT -# WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the -# License for the specific language governing permissions and limitations -# under the License. - -# This is a placeholder for Train backports. Do not use this number for new -# Ussuri work. New Ussuri work starts after all the placeholders. - - -def upgrade(migrate_engine): - pass diff --git a/keystone/common/sql/contract_repo/versions/072_contract_drop_domain_id_fk.py b/keystone/common/sql/contract_repo/versions/072_contract_drop_domain_id_fk.py deleted file mode 100644 index 7e00c1e9f..000000000 --- a/keystone/common/sql/contract_repo/versions/072_contract_drop_domain_id_fk.py +++ /dev/null @@ -1,47 +0,0 @@ -# Copyright 2019 SUSE LLC -# -# Licensed under the Apache License, Version 2.0 (the "License"); you may -# not use this file except in compliance with the License. You may obtain -# a copy of the License at -# -# http://www.apache.org/licenses/LICENSE-2.0 -# -# Unless required by applicable law or agreed to in writing, software -# distributed under the License is distributed on an "AS IS" BASIS, WITHOUT -# WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the -# License for the specific language governing permissions and limitations -# under the License. - -# This is a placeholder for Train backports. Do not use this number for new -# Ussuri work. New Ussuri work starts after all the placeholders. - -import migrate -import sqlalchemy as sql - - -def upgrade(migrate_engine): - meta = sql.MetaData() - meta.bind = migrate_engine - user = sql.Table('user', meta, autoload=True) - project = sql.Table('project', meta, autoload=True) - - fk_name = [ - c for c in user.constraints - if isinstance(c, sql.ForeignKeyConstraint) - and c.column_keys == ['domain_id'] - ][0].name - fk_constraint = migrate.ForeignKeyConstraint( - columns=[user.c.domain_id], refcolumns=[project.c.id]) - fk_constraint.name = fk_name - fk_constraint.drop() - - identity_provider = sql.Table('identity_provider', meta, autoload=True) - fk_name = [ - c for c in identity_provider.constraints - if isinstance(c, sql.ForeignKeyConstraint) - and c.column_keys == ['domain_id'] - ][0].name - fk_constraint = migrate.ForeignKeyConstraint( - columns=[identity_provider.c.domain_id], refcolumns=[project.c.id]) - fk_constraint.name = fk_name - fk_constraint.drop() diff --git a/keystone/common/sql/contract_repo/versions/073_contract_expiring_group_membership.py b/keystone/common/sql/contract_repo/versions/073_contract_expiring_group_membership.py deleted file mode 100644 index 8aa15c1ef..000000000 --- a/keystone/common/sql/contract_repo/versions/073_contract_expiring_group_membership.py +++ /dev/null @@ -1,15 +0,0 @@ -# Licensed under the Apache License, Version 2.0 (the "License"); you may -# not use this file except in compliance with the License. You may obtain -# a copy of the License at -# -# http://www.apache.org/licenses/LICENSE-2.0 -# -# Unless required by applicable law or agreed to in writing, software -# distributed under the License is distributed on an "AS IS" BASIS, WITHOUT -# WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the -# License for the specific language governing permissions and limitations -# under the License. - - -def upgrade(migrate_engine): - pass diff --git a/keystone/common/sql/contract_repo/versions/073_contract_initial_migration.py b/keystone/common/sql/contract_repo/versions/073_contract_initial_migration.py new file mode 100644 index 000000000..1cd34e617 --- /dev/null +++ b/keystone/common/sql/contract_repo/versions/073_contract_initial_migration.py @@ -0,0 +1,18 @@ +# Licensed under the Apache License, Version 2.0 (the "License"); you may +# not use this file except in compliance with the License. You may obtain +# a copy of the License at +# +# http://www.apache.org/licenses/LICENSE-2.0 +# +# Unless required by applicable law or agreed to in writing, software +# distributed under the License is distributed on an "AS IS" BASIS, WITHOUT +# WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the +# License for the specific language governing permissions and limitations +# under the License. + +# A null initial migration to open this repo. Do not re-use replace this with +# a real migration, add additional ones in subsequent version scripts. + + +def upgrade(migrate_engine): + pass diff --git a/keystone/common/sql/data_migration_repo/versions/066_migrate_initial_migration.py b/keystone/common/sql/data_migration_repo/versions/066_migrate_initial_migration.py deleted file mode 100644 index d05b151b8..000000000 --- a/keystone/common/sql/data_migration_repo/versions/066_migrate_initial_migration.py +++ /dev/null @@ -1,56 +0,0 @@ -# Licensed under the Apache License, Version 2.0 (the "License"); you may -# not use this file except in compliance with the License. You may obtain -# a copy of the License at -# -# http://www.apache.org/licenses/LICENSE-2.0 -# -# Unless required by applicable law or agreed to in writing, software -# distributed under the License is distributed on an "AS IS" BASIS, WITHOUT -# WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the -# License for the specific language governing permissions and limitations -# under the License. - -# A null initial migration to open this repo. Do not re-use replace this with -# a real migration, add additional ones in subsequent version scripts. - -import sqlalchemy as sql -import sqlalchemy.orm - -NULL_DOMAIN_ID = '<>' - - -def upgrade(migrate_engine): - - def _generate_root_domain_project(): - # Generate a project that will act as a root for all domains, in order - # for use to be able to use a FK constraint on domain_id. Projects - # acting as a domain will not reference this as their parent_id, just - # as domain_id. - # - # This special project is filtered out by the driver, so is never - # visible to the manager or API. - - project_ref = { - 'id': NULL_DOMAIN_ID, - 'name': NULL_DOMAIN_ID, - 'enabled': False, - 'description': '', - 'domain_id': NULL_DOMAIN_ID, - 'is_domain': True, - 'parent_id': None, - 'extra': '{}', - } - return project_ref - - meta = sql.MetaData() - meta.bind = migrate_engine - session = sql.orm.sessionmaker(bind=migrate_engine)() - - project = sql.Table('project', meta, autoload=True) - - root_domain_project = _generate_root_domain_project() - new_entry = project.insert().values(**root_domain_project) - session.execute(new_entry) - session.commit() - - session.close() diff --git a/keystone/common/sql/data_migration_repo/versions/067_placeholder.py b/keystone/common/sql/data_migration_repo/versions/067_placeholder.py deleted file mode 100644 index 8522ef3ce..000000000 --- a/keystone/common/sql/data_migration_repo/versions/067_placeholder.py +++ /dev/null @@ -1,18 +0,0 @@ -# Licensed under the Apache License, Version 2.0 (the "License"); you may -# not use this file except in compliance with the License. You may obtain -# a copy of the License at -# -# http://www.apache.org/licenses/LICENSE-2.0 -# -# Unless required by applicable law or agreed to in writing, software -# distributed under the License is distributed on an "AS IS" BASIS, WITHOUT -# WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the -# License for the specific language governing permissions and limitations -# under the License. - -# This is a placeholder for Train backports. Do not use this number for new -# Ussuri work. New Ussuri work starts after all the placeholders. - - -def upgrade(migrate_engine): - pass diff --git a/keystone/common/sql/data_migration_repo/versions/068_placeholder.py b/keystone/common/sql/data_migration_repo/versions/068_placeholder.py deleted file mode 100644 index 8522ef3ce..000000000 --- a/keystone/common/sql/data_migration_repo/versions/068_placeholder.py +++ /dev/null @@ -1,18 +0,0 @@ -# Licensed under the Apache License, Version 2.0 (the "License"); you may -# not use this file except in compliance with the License. You may obtain -# a copy of the License at -# -# http://www.apache.org/licenses/LICENSE-2.0 -# -# Unless required by applicable law or agreed to in writing, software -# distributed under the License is distributed on an "AS IS" BASIS, WITHOUT -# WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the -# License for the specific language governing permissions and limitations -# under the License. - -# This is a placeholder for Train backports. Do not use this number for new -# Ussuri work. New Ussuri work starts after all the placeholders. - - -def upgrade(migrate_engine): - pass diff --git a/keystone/common/sql/data_migration_repo/versions/069_placeholder.py b/keystone/common/sql/data_migration_repo/versions/069_placeholder.py deleted file mode 100644 index 8522ef3ce..000000000 --- a/keystone/common/sql/data_migration_repo/versions/069_placeholder.py +++ /dev/null @@ -1,18 +0,0 @@ -# Licensed under the Apache License, Version 2.0 (the "License"); you may -# not use this file except in compliance with the License. You may obtain -# a copy of the License at -# -# http://www.apache.org/licenses/LICENSE-2.0 -# -# Unless required by applicable law or agreed to in writing, software -# distributed under the License is distributed on an "AS IS" BASIS, WITHOUT -# WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the -# License for the specific language governing permissions and limitations -# under the License. - -# This is a placeholder for Train backports. Do not use this number for new -# Ussuri work. New Ussuri work starts after all the placeholders. - - -def upgrade(migrate_engine): - pass diff --git a/keystone/common/sql/data_migration_repo/versions/070_placeholder.py b/keystone/common/sql/data_migration_repo/versions/070_placeholder.py deleted file mode 100644 index 8522ef3ce..000000000 --- a/keystone/common/sql/data_migration_repo/versions/070_placeholder.py +++ /dev/null @@ -1,18 +0,0 @@ -# Licensed under the Apache License, Version 2.0 (the "License"); you may -# not use this file except in compliance with the License. You may obtain -# a copy of the License at -# -# http://www.apache.org/licenses/LICENSE-2.0 -# -# Unless required by applicable law or agreed to in writing, software -# distributed under the License is distributed on an "AS IS" BASIS, WITHOUT -# WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the -# License for the specific language governing permissions and limitations -# under the License. - -# This is a placeholder for Train backports. Do not use this number for new -# Ussuri work. New Ussuri work starts after all the placeholders. - - -def upgrade(migrate_engine): - pass diff --git a/keystone/common/sql/data_migration_repo/versions/071_placeholder.py b/keystone/common/sql/data_migration_repo/versions/071_placeholder.py deleted file mode 100644 index 8522ef3ce..000000000 --- a/keystone/common/sql/data_migration_repo/versions/071_placeholder.py +++ /dev/null @@ -1,18 +0,0 @@ -# Licensed under the Apache License, Version 2.0 (the "License"); you may -# not use this file except in compliance with the License. You may obtain -# a copy of the License at -# -# http://www.apache.org/licenses/LICENSE-2.0 -# -# Unless required by applicable law or agreed to in writing, software -# distributed under the License is distributed on an "AS IS" BASIS, WITHOUT -# WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the -# License for the specific language governing permissions and limitations -# under the License. - -# This is a placeholder for Train backports. Do not use this number for new -# Ussuri work. New Ussuri work starts after all the placeholders. - - -def upgrade(migrate_engine): - pass diff --git a/keystone/common/sql/data_migration_repo/versions/072_migrate_drop_domain_id_fk.py b/keystone/common/sql/data_migration_repo/versions/072_migrate_drop_domain_id_fk.py deleted file mode 100644 index bb90c3de3..000000000 --- a/keystone/common/sql/data_migration_repo/versions/072_migrate_drop_domain_id_fk.py +++ /dev/null @@ -1,20 +0,0 @@ -# Copyright 2019 SUSE LLC -# -# Licensed under the Apache License, Version 2.0 (the "License"); you may -# not use this file except in compliance with the License. You may obtain -# a copy of the License at -# -# http://www.apache.org/licenses/LICENSE-2.0 -# -# Unless required by applicable law or agreed to in writing, software -# distributed under the License is distributed on an "AS IS" BASIS, WITHOUT -# WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the -# License for the specific language governing permissions and limitations -# under the License. - -# This is a placeholder for Train backports. Do not use this number for new -# Ussuri work. New Ussuri work starts after all the placeholders. - - -def upgrade(migrate_engine): - pass diff --git a/keystone/common/sql/data_migration_repo/versions/073_migrate_expiring_group_membership.py b/keystone/common/sql/data_migration_repo/versions/073_migrate_expiring_group_membership.py deleted file mode 100644 index 8aa15c1ef..000000000 --- a/keystone/common/sql/data_migration_repo/versions/073_migrate_expiring_group_membership.py +++ /dev/null @@ -1,15 +0,0 @@ -# Licensed under the Apache License, Version 2.0 (the "License"); you may -# not use this file except in compliance with the License. You may obtain -# a copy of the License at -# -# http://www.apache.org/licenses/LICENSE-2.0 -# -# Unless required by applicable law or agreed to in writing, software -# distributed under the License is distributed on an "AS IS" BASIS, WITHOUT -# WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the -# License for the specific language governing permissions and limitations -# under the License. - - -def upgrade(migrate_engine): - pass diff --git a/keystone/common/sql/data_migration_repo/versions/073_migrate_initial_migration.py b/keystone/common/sql/data_migration_repo/versions/073_migrate_initial_migration.py new file mode 100644 index 000000000..d05b151b8 --- /dev/null +++ b/keystone/common/sql/data_migration_repo/versions/073_migrate_initial_migration.py @@ -0,0 +1,56 @@ +# Licensed under the Apache License, Version 2.0 (the "License"); you may +# not use this file except in compliance with the License. You may obtain +# a copy of the License at +# +# http://www.apache.org/licenses/LICENSE-2.0 +# +# Unless required by applicable law or agreed to in writing, software +# distributed under the License is distributed on an "AS IS" BASIS, WITHOUT +# WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the +# License for the specific language governing permissions and limitations +# under the License. + +# A null initial migration to open this repo. Do not re-use replace this with +# a real migration, add additional ones in subsequent version scripts. + +import sqlalchemy as sql +import sqlalchemy.orm + +NULL_DOMAIN_ID = '<>' + + +def upgrade(migrate_engine): + + def _generate_root_domain_project(): + # Generate a project that will act as a root for all domains, in order + # for use to be able to use a FK constraint on domain_id. Projects + # acting as a domain will not reference this as their parent_id, just + # as domain_id. + # + # This special project is filtered out by the driver, so is never + # visible to the manager or API. + + project_ref = { + 'id': NULL_DOMAIN_ID, + 'name': NULL_DOMAIN_ID, + 'enabled': False, + 'description': '', + 'domain_id': NULL_DOMAIN_ID, + 'is_domain': True, + 'parent_id': None, + 'extra': '{}', + } + return project_ref + + meta = sql.MetaData() + meta.bind = migrate_engine + session = sql.orm.sessionmaker(bind=migrate_engine)() + + project = sql.Table('project', meta, autoload=True) + + root_domain_project = _generate_root_domain_project() + new_entry = project.insert().values(**root_domain_project) + session.execute(new_entry) + session.commit() + + session.close() diff --git a/keystone/common/sql/expand_repo/versions/066_expand_initial_migration.py b/keystone/common/sql/expand_repo/versions/066_expand_initial_migration.py deleted file mode 100644 index 506ba7fad..000000000 --- a/keystone/common/sql/expand_repo/versions/066_expand_initial_migration.py +++ /dev/null @@ -1,1134 +0,0 @@ -# Licensed under the Apache License, Version 2.0 (the "License"); you may -# not use this file except in compliance with the License. You may obtain -# a copy of the License at -# -# http://www.apache.org/licenses/LICENSE-2.0 -# -# Unless required by applicable law or agreed to in writing, software -# distributed under the License is distributed on an "AS IS" BASIS, WITHOUT -# WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the -# License for the specific language governing permissions and limitations -# under the License. - -import datetime -import textwrap - -import migrate -from oslo_log import log -import sqlalchemy as sql - -from keystone.assignment.backends import sql as assignment_sql -from keystone.common import sql as ks_sql -import keystone.conf -from keystone.identity.mapping_backends import mapping as mapping_backend - -CONF = keystone.conf.CONF -LOG = log.getLogger(__name__) - -# FIXME(stephenfin): Remove this as soon as we're done reworking the -# migrations. Until then, this is necessary to allow us to use the native -# sqlalchemy-migrate tooling (which won't register opts). Alternatively, maybe -# the server default *shouldn't* rely on a (changeable) config option value? -try: - service_provider_relay_state_prefix_default = CONF.saml.relay_state_prefix -except Exception: - service_provider_relay_state_prefix_default = 'ss:mem:' - - -def upgrade(migrate_engine): - meta = sql.MetaData() - meta.bind = migrate_engine - - if migrate_engine.name == 'mysql': - # In Folsom we explicitly converted migrate_version to UTF8. - migrate_engine.execute( - 'ALTER TABLE migrate_version CONVERT TO CHARACTER SET utf8' - ) - # Set default DB charset to UTF8. - migrate_engine.execute( - 'ALTER DATABASE %s DEFAULT CHARACTER SET utf8' - % migrate_engine.url.database - ) - - access_token = sql.Table( - 'access_token', - meta, - sql.Column('id', sql.String(64), primary_key=True, nullable=False), - sql.Column('access_secret', sql.String(64), nullable=False), - sql.Column( - 'authorizing_user_id', sql.String(64), nullable=False, index=True - ), - sql.Column('project_id', sql.String(64), nullable=False), - sql.Column('role_ids', sql.Text(), nullable=False), - sql.Column( - 'consumer_id', - sql.String(64), - sql.ForeignKey('consumer.id'), - nullable=False, - index=True, - ), - sql.Column('expires_at', sql.String(64), nullable=True), - ) - - consumer = sql.Table( - 'consumer', - meta, - sql.Column('id', sql.String(64), primary_key=True, nullable=False), - sql.Column('description', sql.String(64), nullable=True), - sql.Column('secret', sql.String(64), nullable=False), - sql.Column('extra', sql.Text(), nullable=False), - ) - - credential = sql.Table( - 'credential', - meta, - sql.Column('id', sql.String(length=64), primary_key=True), - sql.Column('user_id', sql.String(length=64), nullable=False), - sql.Column('project_id', sql.String(length=64)), - sql.Column('type', sql.String(length=255), nullable=False), - sql.Column('extra', ks_sql.JsonBlob.impl), - sql.Column('key_hash', sql.String(64), nullable=False), - sql.Column( - 'encrypted_blob', - ks_sql.Text, - nullable=False, - ), - mysql_engine='InnoDB', - mysql_charset='utf8', - ) - - endpoint = sql.Table( - 'endpoint', - meta, - sql.Column('id', sql.String(length=64), primary_key=True), - sql.Column('legacy_endpoint_id', sql.String(length=64)), - sql.Column('interface', sql.String(length=8), nullable=False), - sql.Column('service_id', sql.String(length=64), nullable=False), - sql.Column('url', sql.Text, nullable=False), - sql.Column('extra', ks_sql.JsonBlob.impl), - sql.Column( - 'enabled', - sql.Boolean, - nullable=False, - default=True, - server_default='1', - ), - sql.Column('region_id', sql.String(length=255), nullable=True), - # NOTE(stevemar): The index was named 'service_id' in - # 050_fk_consistent_indexes.py and needs to be preserved - sql.Index('service_id', 'service_id'), - mysql_engine='InnoDB', - mysql_charset='utf8', - ) - - endpoint_group = sql.Table( - 'endpoint_group', - meta, - sql.Column('id', sql.String(64), primary_key=True), - sql.Column('name', sql.String(255), nullable=False), - sql.Column('description', sql.Text, nullable=True), - sql.Column('filters', sql.Text(), nullable=False), - ) - - federated_user = sql.Table( - 'federated_user', - meta, - sql.Column('id', sql.Integer, primary_key=True, nullable=False), - sql.Column( - 'user_id', - sql.String(64), - sql.ForeignKey('user.id', ondelete='CASCADE'), - nullable=False, - ), - sql.Column( - 'idp_id', - sql.String(64), - sql.ForeignKey('identity_provider.id', ondelete='CASCADE'), - nullable=False, - ), - sql.Column('protocol_id', sql.String(64), nullable=False), - sql.Column('unique_id', sql.String(255), nullable=False), - sql.Column('display_name', sql.String(255), nullable=True), - sql.UniqueConstraint('idp_id', 'protocol_id', 'unique_id'), - mysql_engine='InnoDB', - mysql_charset='utf8', - ) - - federation_protocol = sql.Table( - 'federation_protocol', - meta, - sql.Column('id', sql.String(64), primary_key=True), - sql.Column( - 'idp_id', - sql.String(64), - sql.ForeignKey('identity_provider.id', ondelete='CASCADE'), - primary_key=True, - ), - sql.Column('mapping_id', sql.String(64), nullable=False), - sql.Column('remote_id_attribute', sql.String(64)), - mysql_engine='InnoDB', - mysql_charset='utf8', - ) - - group = sql.Table( - 'group', - meta, - sql.Column('id', sql.String(length=64), primary_key=True), - sql.Column('domain_id', sql.String(length=64), nullable=False), - sql.Column('name', sql.String(length=64), nullable=False), - sql.Column('description', sql.Text), - sql.Column('extra', ks_sql.JsonBlob.impl), - migrate.UniqueConstraint( - 'domain_id', - 'name', - name='ixu_group_name_domain_id', - ), - mysql_engine='InnoDB', - mysql_charset='utf8', - ) - - identity_provider = sql.Table( - 'identity_provider', - meta, - sql.Column('id', sql.String(64), primary_key=True), - sql.Column('enabled', sql.Boolean, nullable=False), - sql.Column('description', sql.Text(), nullable=True), - sql.Column('domain_id', sql.String(64), nullable=False), - mysql_engine='InnoDB', - mysql_charset='utf8', - ) - - idp_remote_ids = sql.Table( - 'idp_remote_ids', - meta, - sql.Column( - 'idp_id', - sql.String(64), - sql.ForeignKey('identity_provider.id', ondelete='CASCADE'), - ), - sql.Column('remote_id', sql.String(255), primary_key=True), - mysql_engine='InnoDB', - mysql_charset='utf8', - ) - - implied_role = sql.Table( - 'implied_role', - meta, - sql.Column('prior_role_id', sql.String(length=64), primary_key=True), - sql.Column('implied_role_id', sql.String(length=64), primary_key=True), - mysql_engine='InnoDB', - mysql_charset='utf8', - ) - - local_user = sql.Table( - 'local_user', - meta, - sql.Column('id', sql.Integer, primary_key=True, nullable=False), - sql.Column( - 'user_id', - sql.String(64), - nullable=False, - unique=True, - ), - sql.Column('domain_id', sql.String(64), nullable=False), - sql.Column('name', sql.String(255), nullable=False), - sql.Column('failed_auth_count', sql.Integer, nullable=True), - sql.Column('failed_auth_at', sql.DateTime(), nullable=True), - sql.UniqueConstraint('domain_id', 'name'), - ) - - mapping = sql.Table( - 'mapping', - meta, - sql.Column('id', sql.String(64), primary_key=True), - sql.Column('rules', sql.Text(), nullable=False), - mysql_engine='InnoDB', - mysql_charset='utf8', - ) - - password = sql.Table( - 'password', - meta, - sql.Column('id', sql.Integer, primary_key=True, nullable=False), - sql.Column( - 'local_user_id', - sql.Integer, - sql.ForeignKey(local_user.c.id, ondelete='CASCADE'), - nullable=False, - ), - sql.Column('expires_at', sql.DateTime(), nullable=True), - sql.Column( - 'self_service', - sql.Boolean, - nullable=False, - server_default='0', - default=False, - ), - # NOTE(notmorgan): To support the full range of scrypt and pbkfd - # password hash lengths, this should be closer to varchar(1500) instead - # of varchar(255). - sql.Column('password_hash', sql.String(255), nullable=True), - sql.Column( - 'created_at_int', - ks_sql.DateTimeInt(), - nullable=False, - default=0, - server_default='0', - ), - sql.Column('expires_at_int', ks_sql.DateTimeInt(), nullable=True), - sql.Column( - 'created_at', - sql.DateTime(), - nullable=False, - default=datetime.datetime.utcnow, - ), - ) - - policy = sql.Table( - 'policy', - meta, - sql.Column('id', sql.String(length=64), primary_key=True), - sql.Column('type', sql.String(length=255), nullable=False), - sql.Column('blob', ks_sql.JsonBlob, nullable=False), - sql.Column('extra', ks_sql.JsonBlob.impl), - mysql_engine='InnoDB', - mysql_charset='utf8', - ) - - policy_association = sql.Table( - 'policy_association', - meta, - sql.Column('id', sql.String(64), primary_key=True), - sql.Column('policy_id', sql.String(64), nullable=False), - sql.Column('endpoint_id', sql.String(64), nullable=True), - sql.Column('service_id', sql.String(64), nullable=True), - sql.Column('region_id', sql.String(64), nullable=True), - sql.UniqueConstraint('endpoint_id', 'service_id', 'region_id'), - mysql_engine='InnoDB', - mysql_charset='utf8', - ) - - project = sql.Table( - 'project', - meta, - sql.Column('id', sql.String(length=64), primary_key=True), - sql.Column('name', sql.String(length=64), nullable=False), - sql.Column('extra', ks_sql.JsonBlob.impl), - sql.Column('description', sql.Text), - sql.Column('enabled', sql.Boolean), - sql.Column('domain_id', sql.String(length=64), nullable=False), - sql.Column('parent_id', sql.String(64), nullable=True), - sql.Column( - 'is_domain', - sql.Boolean, - nullable=False, - server_default='0', - default=False, - ), - migrate.UniqueConstraint( - 'domain_id', - 'name', - name='ixu_project_name_domain_id', - ), - mysql_engine='InnoDB', - mysql_charset='utf8', - ) - - project_option = sql.Table( - 'project_option', - meta, - sql.Column( - 'project_id', - sql.String(64), - sql.ForeignKey(project.c.id, ondelete='CASCADE'), - nullable=False, - primary_key=True, - ), - sql.Column( - 'option_id', sql.String(4), nullable=False, primary_key=True - ), - sql.Column('option_value', ks_sql.JsonBlob, nullable=True), - mysql_engine='InnoDB', - mysql_charset='utf8', - ) - - # NOTE(lamt) To allow tag name to be case sensitive for MySQL, the 'name' - # column needs to use collation, which is incompatible with Postgresql. - # Using unicode to mirror nova's server tag: - # https://github.com/openstack/nova/blob/master/nova/db/sqlalchemy/models.py - project_tag = sql.Table( - 'project_tag', - meta, - sql.Column( - 'project_id', - sql.String(64), - sql.ForeignKey(project.c.id, ondelete='CASCADE'), - nullable=False, - primary_key=True, - ), - sql.Column('name', sql.Unicode(255), nullable=False, primary_key=True), - sql.UniqueConstraint('project_id', 'name'), - mysql_engine='InnoDB', - mysql_charset='utf8', - ) - - project_endpoint = sql.Table( - 'project_endpoint', - meta, - sql.Column( - 'endpoint_id', sql.String(64), primary_key=True, nullable=False - ), - sql.Column( - 'project_id', sql.String(64), primary_key=True, nullable=False - ), - ) - - project_endpoint_group = sql.Table( - 'project_endpoint_group', - meta, - sql.Column( - 'endpoint_group_id', - sql.String(64), - sql.ForeignKey('endpoint_group.id'), - nullable=False, - ), - sql.Column('project_id', sql.String(64), nullable=False), - sql.PrimaryKeyConstraint('endpoint_group_id', 'project_id'), - ) - - config_register = sql.Table( - 'config_register', - meta, - sql.Column('type', sql.String(64), primary_key=True), - sql.Column('domain_id', sql.String(64), nullable=False), - mysql_engine='InnoDB', - mysql_charset='utf8', - ) - - request_token = sql.Table( - 'request_token', - meta, - sql.Column('id', sql.String(64), primary_key=True, nullable=False), - sql.Column('request_secret', sql.String(64), nullable=False), - sql.Column('verifier', sql.String(64), nullable=True), - sql.Column('authorizing_user_id', sql.String(64), nullable=True), - sql.Column('requested_project_id', sql.String(64), nullable=False), - sql.Column('role_ids', sql.Text(), nullable=True), - sql.Column( - 'consumer_id', - sql.String(64), - sql.ForeignKey('consumer.id'), - nullable=False, - index=True, - ), - sql.Column('expires_at', sql.String(64), nullable=True), - ) - - revocation_event = sql.Table( - 'revocation_event', - meta, - sql.Column('id', sql.Integer, primary_key=True), - sql.Column('domain_id', sql.String(64)), - sql.Column('project_id', sql.String(64)), - sql.Column('user_id', sql.String(64)), - sql.Column('role_id', sql.String(64)), - sql.Column('trust_id', sql.String(64)), - sql.Column('consumer_id', sql.String(64)), - sql.Column('access_token_id', sql.String(64)), - sql.Column('issued_before', sql.DateTime(), nullable=False), - sql.Column('expires_at', sql.DateTime()), - sql.Column('revoked_at', sql.DateTime(), nullable=False), - sql.Column('audit_id', sql.String(32), nullable=True), - sql.Column('audit_chain_id', sql.String(32), nullable=True), - # NOTE(stephenfin): The '_new' suffix here is due to migration 095, - # which changed the 'id' column from String(64) to Integer. It did this - # by creating a 'revocation_event_new' table and populating it with - # data from the 'revocation_event' table before deleting the - # 'revocation_event' table and renaming the 'revocation_event_new' - # table to 'revocation_event'. Because the 'revoked_at' column had - # 'index=True', sqlalchemy automatically generated the index name as - # 'ix_{table}_{column}'. However, when intitially created, '{table}' - # was 'revocation_event_new' so the index got that name. We may wish to - # rename this eventually. - sql.Index('ix_revocation_event_new_revoked_at', 'revoked_at'), - sql.Index('ix_revocation_event_issued_before', 'issued_before'), - sql.Index( - 'ix_revocation_event_project_id_issued_before', - 'project_id', - 'issued_before', - ), - sql.Index( - 'ix_revocation_event_user_id_issued_before', - 'user_id', - 'issued_before', - ), - sql.Index( - 'ix_revocation_event_audit_id_issued_before', - 'audit_id', - 'issued_before', - ), - ) - - role = sql.Table( - 'role', - meta, - sql.Column('id', sql.String(length=64), primary_key=True), - sql.Column('name', sql.String(length=255), nullable=False), - sql.Column('extra', ks_sql.JsonBlob.impl), - sql.Column( - 'domain_id', - sql.String(64), - nullable=False, - server_default='<>', - ), - sql.Column('description', sql.String(255), nullable=True), - migrate.UniqueConstraint( - 'name', - 'domain_id', - name='ixu_role_name_domain_id', - ), - mysql_engine='InnoDB', - mysql_charset='utf8', - ) - - role_option = sql.Table( - 'role_option', - meta, - sql.Column( - 'role_id', - sql.String(64), - sql.ForeignKey(role.c.id, ondelete='CASCADE'), - nullable=False, - primary_key=True, - ), - sql.Column( - 'option_id', sql.String(4), nullable=False, primary_key=True - ), - sql.Column('option_value', ks_sql.JsonBlob, nullable=True), - mysql_engine='InnoDB', - mysql_charset='utf8', - ) - - service = sql.Table( - 'service', - meta, - sql.Column('id', sql.String(length=64), primary_key=True), - sql.Column('type', sql.String(length=255)), - sql.Column( - 'enabled', - sql.Boolean, - nullable=False, - default=True, - server_default='1', - ), - sql.Column('extra', ks_sql.JsonBlob.impl), - mysql_engine='InnoDB', - mysql_charset='utf8', - ) - - service_provider = sql.Table( - 'service_provider', - meta, - sql.Column('auth_url', sql.String(256), nullable=False), - sql.Column('id', sql.String(64), primary_key=True), - sql.Column('enabled', sql.Boolean, nullable=False), - sql.Column('description', sql.Text(), nullable=True), - sql.Column('sp_url', sql.String(256), nullable=False), - sql.Column( - 'relay_state_prefix', - sql.String(256), - nullable=False, - server_default=service_provider_relay_state_prefix_default, - ), - mysql_engine='InnoDB', - mysql_charset='utf8', - ) - - token = sql.Table( - 'token', - meta, - sql.Column('id', sql.String(length=64), primary_key=True), - sql.Column('expires', sql.DateTime, default=None), - sql.Column('extra', ks_sql.JsonBlob.impl), - sql.Column('valid', sql.Boolean, default=True, nullable=False), - sql.Column('trust_id', sql.String(length=64)), - sql.Column('user_id', sql.String(length=64)), - sql.Index('ix_token_expires', 'expires'), - sql.Index('ix_token_expires_valid', 'expires', 'valid'), - sql.Index('ix_token_user_id', 'user_id'), - sql.Index('ix_token_trust_id', 'trust_id'), - mysql_engine='InnoDB', - mysql_charset='utf8', - ) - - trust = sql.Table( - 'trust', - meta, - sql.Column('id', sql.String(length=64), primary_key=True), - sql.Column('trustor_user_id', sql.String(length=64), nullable=False), - sql.Column('trustee_user_id', sql.String(length=64), nullable=False), - sql.Column('project_id', sql.String(length=64)), - sql.Column('impersonation', sql.Boolean, nullable=False), - sql.Column('deleted_at', sql.DateTime), - sql.Column('expires_at', sql.DateTime), - sql.Column('remaining_uses', sql.Integer, nullable=True), - sql.Column('extra', ks_sql.JsonBlob.impl), - sql.Column('expires_at_int', ks_sql.DateTimeInt()), - sql.UniqueConstraint( - 'trustor_user_id', - 'trustee_user_id', - 'project_id', - 'impersonation', - 'expires_at', - 'expires_at_int', - name='duplicate_trust_constraint_expanded', - ), - sql.Column( - 'redelegated_trust_id', - sql.String(64), - nullable=True, - ), - sql.Column( - 'redelegation_count', - sql.Integer, - nullable=True, - ), - mysql_engine='InnoDB', - mysql_charset='utf8', - ) - - trust_role = sql.Table( - 'trust_role', - meta, - sql.Column( - 'trust_id', sql.String(length=64), primary_key=True, nullable=False - ), - sql.Column( - 'role_id', sql.String(length=64), primary_key=True, nullable=False - ), - mysql_engine='InnoDB', - mysql_charset='utf8', - ) - - user = sql.Table( - 'user', - meta, - sql.Column('id', sql.String(length=64), primary_key=True), - sql.Column('extra', ks_sql.JsonBlob.impl), - sql.Column('enabled', sql.Boolean), - sql.Column('default_project_id', sql.String(length=64)), - sql.Column('created_at', sql.DateTime(), nullable=True), - sql.Column('last_active_at', sql.Date(), nullable=True), - sql.Column( - 'domain_id', - sql.String(64), - sql.ForeignKey(project.c.id), - nullable=False, - ), - sql.UniqueConstraint('id', 'domain_id', name='ixu_user_id_domain_id'), - sql.Index('ix_default_project_id', 'default_project_id'), - mysql_engine='InnoDB', - mysql_charset='utf8', - ) - - user_option = sql.Table( - 'user_option', - meta, - sql.Column( - 'user_id', - sql.String(64), - sql.ForeignKey(user.c.id, ondelete='CASCADE'), - nullable=False, - primary_key=True, - ), - sql.Column( - 'option_id', sql.String(4), nullable=False, primary_key=True - ), - sql.Column('option_value', ks_sql.JsonBlob, nullable=True), - mysql_engine='InnoDB', - mysql_charset='utf8', - ) - - nonlocal_user = sql.Table( - 'nonlocal_user', - meta, - sql.Column('domain_id', sql.String(64), primary_key=True), - sql.Column('name', sql.String(255), primary_key=True), - sql.Column( - 'user_id', - sql.String(64), - nullable=False, - ), - sql.UniqueConstraint('user_id', name='ixu_nonlocal_user_user_id'), - mysql_engine='InnoDB', - mysql_charset='utf8', - ) - - user_group_membership = sql.Table( - 'user_group_membership', - meta, - sql.Column('user_id', sql.String(length=64), primary_key=True), - sql.Column('group_id', sql.String(length=64), primary_key=True), - # NOTE(stevemar): The index was named 'group_id' in - # 050_fk_consistent_indexes.py and needs to be preserved - sql.Index('group_id', 'group_id'), - mysql_engine='InnoDB', - mysql_charset='utf8', - ) - - region = sql.Table( - 'region', - meta, - sql.Column('id', sql.String(255), primary_key=True), - sql.Column('description', sql.String(255), nullable=False), - sql.Column('parent_region_id', sql.String(255), nullable=True), - sql.Column('extra', sql.Text()), - mysql_engine='InnoDB', - mysql_charset='utf8', - ) - - assignment = sql.Table( - 'assignment', - meta, - sql.Column( - 'type', - sql.Enum( - assignment_sql.AssignmentType.USER_PROJECT, - assignment_sql.AssignmentType.GROUP_PROJECT, - assignment_sql.AssignmentType.USER_DOMAIN, - assignment_sql.AssignmentType.GROUP_DOMAIN, - name='type', - ), - nullable=False, - ), - sql.Column('actor_id', sql.String(64), nullable=False), - sql.Column('target_id', sql.String(64), nullable=False), - sql.Column('role_id', sql.String(64), nullable=False), - sql.Column('inherited', sql.Boolean, default=False, nullable=False), - sql.PrimaryKeyConstraint( - 'type', - 'actor_id', - 'target_id', - 'role_id', - 'inherited', - ), - sql.Index('ix_actor_id', 'actor_id'), - mysql_engine='InnoDB', - mysql_charset='utf8', - ) - - id_mapping = sql.Table( - 'id_mapping', - meta, - sql.Column('public_id', sql.String(64), primary_key=True), - sql.Column('domain_id', sql.String(64), nullable=False), - sql.Column('local_id', sql.String(64), nullable=False), - sql.Column( - 'entity_type', - sql.Enum( - mapping_backend.EntityType.USER, - mapping_backend.EntityType.GROUP, - name='entity_type', - ), - nullable=False, - ), - migrate.UniqueConstraint( - 'domain_id', - 'local_id', - 'entity_type', - name='domain_id', - ), - mysql_engine='InnoDB', - mysql_charset='utf8', - ) - - whitelisted_config = sql.Table( - 'whitelisted_config', - meta, - sql.Column('domain_id', sql.String(64), primary_key=True), - sql.Column('group', sql.String(255), primary_key=True), - sql.Column('option', sql.String(255), primary_key=True), - sql.Column('value', ks_sql.JsonBlob.impl, nullable=False), - mysql_engine='InnoDB', - mysql_charset='utf8', - ) - - sensitive_config = sql.Table( - 'sensitive_config', - meta, - sql.Column('domain_id', sql.String(64), primary_key=True), - sql.Column('group', sql.String(255), primary_key=True), - sql.Column('option', sql.String(255), primary_key=True), - sql.Column('value', ks_sql.JsonBlob.impl, nullable=False), - mysql_engine='InnoDB', - mysql_charset='utf8', - ) - - system_assignment = sql.Table( - 'system_assignment', - meta, - sql.Column('type', sql.String(64), nullable=False), - sql.Column('actor_id', sql.String(64), nullable=False), - sql.Column('target_id', sql.String(64), nullable=False), - sql.Column('role_id', sql.String(64), nullable=False), - sql.Column('inherited', sql.Boolean, default=False, nullable=False), - sql.PrimaryKeyConstraint( - 'type', 'actor_id', 'target_id', 'role_id', 'inherited' - ), - mysql_engine='InnoDB', - mysql_charset='utf8', - ) - - registered_limit = sql.Table( - 'registered_limit', - meta, - sql.Column('id', sql.String(length=64), nullable=False), - sql.Column('service_id', sql.String(255)), - sql.Column('region_id', sql.String(64), nullable=True), - sql.Column('resource_name', sql.String(255)), - sql.Column('default_limit', sql.Integer, nullable=False), - sql.Column('description', sql.Text), - sql.Column('internal_id', sql.Integer, primary_key=True), - # NOTE(stephenfin): Name chosen to preserve backwards compatibility - # with names used for primary key unique constraints - sql.UniqueConstraint('id', name='registered_limit_id_key'), - mysql_engine='InnoDB', - mysql_charset='utf8', - ) - - limit = sql.Table( - 'limit', - meta, - sql.Column('id', sql.String(length=64), nullable=False), - sql.Column('project_id', sql.String(64), nullable=True), - sql.Column('resource_limit', sql.Integer, nullable=False), - sql.Column('description', sql.Text), - sql.Column('internal_id', sql.Integer, primary_key=True), - # FIXME(stephenfin): This should have a foreign key constraint on - # registered_limit.id, but sqlalchemy-migrate clearly didn't handle - # creating a column with embedded FK info as was attempted in 048 - sql.Column( - 'registered_limit_id', - sql.String(64), - ), - sql.Column('domain_id', sql.String(64), nullable=True), - # NOTE(stephenfin): Name chosen to preserve backwards compatibility - # with names used for primary key unique constraints - sql.UniqueConstraint('id', name='limit_id_key'), - mysql_engine='InnoDB', - mysql_charset='utf8', - ) - - application_credential = sql.Table( - 'application_credential', - meta, - sql.Column( - 'internal_id', sql.Integer, primary_key=True, nullable=False - ), - sql.Column('id', sql.String(length=64), nullable=False), - sql.Column('name', sql.String(length=255), nullable=False), - sql.Column('secret_hash', sql.String(length=255), nullable=False), - sql.Column('description', sql.Text), - sql.Column('user_id', sql.String(length=64), nullable=False), - sql.Column('project_id', sql.String(64), nullable=True), - sql.Column('expires_at', ks_sql.DateTimeInt()), - sql.Column('system', sql.String(64), nullable=True), - sql.Column('unrestricted', sql.Boolean), - sql.UniqueConstraint( - 'user_id', 'name', name='duplicate_app_cred_constraint' - ), - mysql_engine='InnoDB', - mysql_charset='utf8', - ) - - application_credential_role = sql.Table( - 'application_credential_role', - meta, - sql.Column( - 'application_credential_id', - sql.Integer, - sql.ForeignKey( - application_credential.c.internal_id, ondelete='CASCADE' - ), - primary_key=True, - nullable=False, - ), - sql.Column( - 'role_id', sql.String(length=64), primary_key=True, nullable=False - ), - mysql_engine='InnoDB', - mysql_charset='utf8', - ) - - access_rule = sql.Table( - 'access_rule', - meta, - sql.Column('id', sql.Integer, primary_key=True, nullable=False), - sql.Column('service', sql.String(64)), - sql.Column('path', sql.String(128)), - sql.Column('method', sql.String(16)), - sql.Column('external_id', sql.String(64)), - sql.Column('user_id', sql.String(64)), - sql.UniqueConstraint( - 'external_id', - name='access_rule_external_id_key', - ), - sql.UniqueConstraint( - 'user_id', - 'service', - 'path', - 'method', - name='duplicate_access_rule_for_user_constraint', - ), - sql.Index('user_id', 'user_id'), - sql.Index('external_id', 'external_id'), - mysql_engine='InnoDB', - mysql_charset='utf8', - ) - - app_cred_access_rule = sql.Table( - 'application_credential_access_rule', - meta, - sql.Column( - 'application_credential_id', - sql.Integer, - sql.ForeignKey( - application_credential.c.internal_id, ondelete='CASCADE' - ), - primary_key=True, - nullable=False, - ), - sql.Column( - 'access_rule_id', - sql.Integer, - sql.ForeignKey(access_rule.c.id, ondelete='CASCADE'), - primary_key=True, - nullable=False, - ), - mysql_engine='InnoDB', - mysql_charset='utf8', - ) - - # create all tables - tables = [ - credential, - endpoint, - group, - policy, - project, - project_option, - project_tag, - role, - role_option, - service, - token, - trust, - trust_role, - user, - user_option, - user_group_membership, - region, - assignment, - id_mapping, - whitelisted_config, - sensitive_config, - config_register, - policy_association, - identity_provider, - federation_protocol, - mapping, - service_provider, - idp_remote_ids, - consumer, - request_token, - access_token, - revocation_event, - project_endpoint, - endpoint_group, - project_endpoint_group, - implied_role, - local_user, - password, - federated_user, - nonlocal_user, - system_assignment, - registered_limit, - limit, - application_credential, - application_credential_role, - access_rule, - app_cred_access_rule, - ] - - for table in tables: - try: - table.create() - except Exception: - LOG.exception('Exception while creating table: %r', table) - raise - - fkeys = [ - { - 'columns': [endpoint.c.service_id], - 'references': [service.c.id], - }, - { - 'columns': [user_group_membership.c.group_id], - 'references': [group.c.id], - 'name': 'fk_user_group_membership_group_id', - }, - { - 'columns': [user_group_membership.c.user_id], - 'references': [user.c.id], - 'name': 'fk_user_group_membership_user_id', - }, - { - 'columns': [project.c.domain_id], - 'references': [project.c.id], - }, - { - 'columns': [endpoint.c.region_id], - 'references': [region.c.id], - 'name': 'fk_endpoint_region_id', - }, - { - 'columns': [project.c.parent_id], - 'references': [project.c.id], - 'name': 'project_parent_id_fkey', - }, - { - 'columns': [implied_role.c.prior_role_id], - 'references': [role.c.id], - 'ondelete': 'CASCADE', - }, - { - 'columns': [implied_role.c.implied_role_id], - 'references': [role.c.id], - 'ondelete': 'CASCADE', - }, - { - 'columns': [ - federated_user.c.protocol_id, - federated_user.c.idp_id, - ], - 'references': [ - federation_protocol.c.id, - federation_protocol.c.idp_id, - ], - 'ondelete': 'CASCADE', - }, - { - 'columns': [identity_provider.c.domain_id], - 'references': [project.c.id], - 'name': 'domain_id', - }, - { - 'columns': [local_user.c.user_id, local_user.c.domain_id], - 'references': [user.c.id, user.c.domain_id], - 'onupdate': 'CASCADE', - 'ondelete': 'CASCADE', - }, - { - 'columns': [nonlocal_user.c.user_id, nonlocal_user.c.domain_id], - 'references': [user.c.id, user.c.domain_id], - 'onupdate': 'CASCADE', - 'ondelete': 'CASCADE', - }, - ] - - if migrate_engine.name == 'sqlite': - # NOTE(stevemar): We need to keep this FK constraint due to 073, but - # only for sqlite, once we collapse 073 we can remove this constraint - fkeys.append( - { - 'columns': [assignment.c.role_id], - 'references': [role.c.id], - 'name': 'fk_assignment_role_id', - }, - ) - - for fkey in fkeys: - migrate.ForeignKeyConstraint( - columns=fkey['columns'], - refcolumns=fkey['references'], - name=fkey.get('name'), - ondelete=fkey.get('ondelete'), - onupdate=fkey.get('onupdate'), - ).create() - - # TODO(stephenfin): Remove these procedures in a future contract migration - - if migrate_engine.name == 'postgresql': - error_message = ( - 'Credential migration in progress. Cannot perform ' - 'writes to credential table.' - ) - credential_update_trigger = textwrap.dedent(f""" - CREATE OR REPLACE FUNCTION keystone_read_only_update() - RETURNS trigger AS - $BODY$ - BEGIN - IF NEW.encrypted_blob IS NULL THEN - RAISE EXCEPTION '{error_message}'; - END IF; - IF NEW.encrypted_blob IS NOT NULL AND OLD.blob IS NULL THEN - RAISE EXCEPTION '{error_message}'; - END IF; - RETURN NEW; - END - $BODY$ LANGUAGE plpgsql; - """) - migrate_engine.execute(credential_update_trigger) - - error_message = ( - 'Identity provider migration in progress. Cannot ' - 'insert new rows into the identity_provider table at ' - 'this time.' - ) - identity_provider_insert_trigger = textwrap.dedent(f""" - CREATE OR REPLACE FUNCTION keystone_read_only_insert() - RETURNS trigger AS - $BODY$ - BEGIN - RAISE EXCEPTION '{error_message}'; - END - $BODY$ LANGUAGE plpgsql; - """) - migrate_engine.execute(identity_provider_insert_trigger) - - federated_user_insert_trigger = textwrap.dedent(""" - CREATE OR REPLACE FUNCTION update_federated_user_domain_id() - RETURNS trigger AS - $BODY$ - BEGIN - UPDATE "user" SET domain_id = ( - SELECT domain_id FROM identity_provider WHERE id = NEW.idp_id) - WHERE id = NEW.user_id and domain_id IS NULL; - RETURN NULL; - END - $BODY$ LANGUAGE plpgsql; - """) - migrate_engine.execute(federated_user_insert_trigger) - - local_user_insert_trigger = textwrap.dedent(""" - CREATE OR REPLACE FUNCTION update_user_domain_id() - RETURNS trigger AS - $BODY$ - BEGIN - UPDATE "user" SET domain_id = NEW.domain_id - WHERE id = NEW.user_id; - RETURN NULL; - END - $BODY$ LANGUAGE plpgsql; - """) - migrate_engine.execute(local_user_insert_trigger) - - # FIXME(stephenfin): Remove these indexes. They're left over from attempts - # to remove foreign key constraints in past migrations. Apparently - # sqlalchemy-migrate didn't do the job fully and left behind indexes - if migrate_engine.name == 'mysql': - sql.Index('region_id', registered_limit.c.region_id).create() - - # FIXME(stephenfin): This should be dropped when we add the FK - # constraint to this column - sql.Index('registered_limit_id', limit.c.registered_limit_id).create() diff --git a/keystone/common/sql/expand_repo/versions/067_placeholder.py b/keystone/common/sql/expand_repo/versions/067_placeholder.py deleted file mode 100644 index 8522ef3ce..000000000 --- a/keystone/common/sql/expand_repo/versions/067_placeholder.py +++ /dev/null @@ -1,18 +0,0 @@ -# Licensed under the Apache License, Version 2.0 (the "License"); you may -# not use this file except in compliance with the License. You may obtain -# a copy of the License at -# -# http://www.apache.org/licenses/LICENSE-2.0 -# -# Unless required by applicable law or agreed to in writing, software -# distributed under the License is distributed on an "AS IS" BASIS, WITHOUT -# WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the -# License for the specific language governing permissions and limitations -# under the License. - -# This is a placeholder for Train backports. Do not use this number for new -# Ussuri work. New Ussuri work starts after all the placeholders. - - -def upgrade(migrate_engine): - pass diff --git a/keystone/common/sql/expand_repo/versions/068_placeholder.py b/keystone/common/sql/expand_repo/versions/068_placeholder.py deleted file mode 100644 index 8522ef3ce..000000000 --- a/keystone/common/sql/expand_repo/versions/068_placeholder.py +++ /dev/null @@ -1,18 +0,0 @@ -# Licensed under the Apache License, Version 2.0 (the "License"); you may -# not use this file except in compliance with the License. You may obtain -# a copy of the License at -# -# http://www.apache.org/licenses/LICENSE-2.0 -# -# Unless required by applicable law or agreed to in writing, software -# distributed under the License is distributed on an "AS IS" BASIS, WITHOUT -# WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the -# License for the specific language governing permissions and limitations -# under the License. - -# This is a placeholder for Train backports. Do not use this number for new -# Ussuri work. New Ussuri work starts after all the placeholders. - - -def upgrade(migrate_engine): - pass diff --git a/keystone/common/sql/expand_repo/versions/069_placeholder.py b/keystone/common/sql/expand_repo/versions/069_placeholder.py deleted file mode 100644 index 8522ef3ce..000000000 --- a/keystone/common/sql/expand_repo/versions/069_placeholder.py +++ /dev/null @@ -1,18 +0,0 @@ -# Licensed under the Apache License, Version 2.0 (the "License"); you may -# not use this file except in compliance with the License. You may obtain -# a copy of the License at -# -# http://www.apache.org/licenses/LICENSE-2.0 -# -# Unless required by applicable law or agreed to in writing, software -# distributed under the License is distributed on an "AS IS" BASIS, WITHOUT -# WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the -# License for the specific language governing permissions and limitations -# under the License. - -# This is a placeholder for Train backports. Do not use this number for new -# Ussuri work. New Ussuri work starts after all the placeholders. - - -def upgrade(migrate_engine): - pass diff --git a/keystone/common/sql/expand_repo/versions/070_placeholder.py b/keystone/common/sql/expand_repo/versions/070_placeholder.py deleted file mode 100644 index 8522ef3ce..000000000 --- a/keystone/common/sql/expand_repo/versions/070_placeholder.py +++ /dev/null @@ -1,18 +0,0 @@ -# Licensed under the Apache License, Version 2.0 (the "License"); you may -# not use this file except in compliance with the License. You may obtain -# a copy of the License at -# -# http://www.apache.org/licenses/LICENSE-2.0 -# -# Unless required by applicable law or agreed to in writing, software -# distributed under the License is distributed on an "AS IS" BASIS, WITHOUT -# WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the -# License for the specific language governing permissions and limitations -# under the License. - -# This is a placeholder for Train backports. Do not use this number for new -# Ussuri work. New Ussuri work starts after all the placeholders. - - -def upgrade(migrate_engine): - pass diff --git a/keystone/common/sql/expand_repo/versions/071_placeholder.py b/keystone/common/sql/expand_repo/versions/071_placeholder.py deleted file mode 100644 index 8522ef3ce..000000000 --- a/keystone/common/sql/expand_repo/versions/071_placeholder.py +++ /dev/null @@ -1,18 +0,0 @@ -# Licensed under the Apache License, Version 2.0 (the "License"); you may -# not use this file except in compliance with the License. You may obtain -# a copy of the License at -# -# http://www.apache.org/licenses/LICENSE-2.0 -# -# Unless required by applicable law or agreed to in writing, software -# distributed under the License is distributed on an "AS IS" BASIS, WITHOUT -# WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the -# License for the specific language governing permissions and limitations -# under the License. - -# This is a placeholder for Train backports. Do not use this number for new -# Ussuri work. New Ussuri work starts after all the placeholders. - - -def upgrade(migrate_engine): - pass diff --git a/keystone/common/sql/expand_repo/versions/072_expand_drop_domain_id_fk.py b/keystone/common/sql/expand_repo/versions/072_expand_drop_domain_id_fk.py deleted file mode 100644 index bb90c3de3..000000000 --- a/keystone/common/sql/expand_repo/versions/072_expand_drop_domain_id_fk.py +++ /dev/null @@ -1,20 +0,0 @@ -# Copyright 2019 SUSE LLC -# -# Licensed under the Apache License, Version 2.0 (the "License"); you may -# not use this file except in compliance with the License. You may obtain -# a copy of the License at -# -# http://www.apache.org/licenses/LICENSE-2.0 -# -# Unless required by applicable law or agreed to in writing, software -# distributed under the License is distributed on an "AS IS" BASIS, WITHOUT -# WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the -# License for the specific language governing permissions and limitations -# under the License. - -# This is a placeholder for Train backports. Do not use this number for new -# Ussuri work. New Ussuri work starts after all the placeholders. - - -def upgrade(migrate_engine): - pass diff --git a/keystone/common/sql/expand_repo/versions/073_expand_expiring_group_membership.py b/keystone/common/sql/expand_repo/versions/073_expand_expiring_group_membership.py deleted file mode 100644 index 8577ee052..000000000 --- a/keystone/common/sql/expand_repo/versions/073_expand_expiring_group_membership.py +++ /dev/null @@ -1,47 +0,0 @@ -# Licensed under the Apache License, Version 2.0 (the "License"); you may -# not use this file except in compliance with the License. You may obtain -# a copy of the License at -# -# http://www.apache.org/licenses/LICENSE-2.0 -# -# Unless required by applicable law or agreed to in writing, software -# distributed under the License is distributed on an "AS IS" BASIS, WITHOUT -# WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the -# License for the specific language governing permissions and limitations -# under the License. - -import sqlalchemy as sql - - -def upgrade(migrate_engine): - meta = sql.MetaData() - meta.bind = migrate_engine - - identity_provider = sql.Table('identity_provider', meta, autoload=True) - authorization_ttl = sql.Column('authorization_ttl', sql.Integer, - nullable=True) - identity_provider.create_column(authorization_ttl) - - user_table = sql.Table('user', meta, autoload=True) - group_table = sql.Table('group', meta, autoload=True) - idp_table = sql.Table('identity_provider', meta, autoload=True) - - expiring_user_group_membership = sql.Table( - 'expiring_user_group_membership', meta, - - sql.Column('user_id', sql.String(64), - sql.ForeignKey(user_table.c.id), primary_key=True), - sql.Column('group_id', sql.String(64), - sql.ForeignKey(group_table.c.id), primary_key=True), - sql.Column('idp_id', - sql.String(64), - sql.ForeignKey(idp_table.c.id, - ondelete='CASCADE'), - primary_key=True), - sql.Column('last_verified', sql.DateTime(), nullable=False), - - mysql_engine='InnoDB', - mysql_charset='utf8' - ) - - expiring_user_group_membership.create(migrate_engine, checkfirst=True) diff --git a/keystone/common/sql/expand_repo/versions/073_expand_initial_migration.py b/keystone/common/sql/expand_repo/versions/073_expand_initial_migration.py new file mode 100644 index 000000000..a68bb4c0e --- /dev/null +++ b/keystone/common/sql/expand_repo/versions/073_expand_initial_migration.py @@ -0,0 +1,1157 @@ +# Licensed under the Apache License, Version 2.0 (the "License"); you may +# not use this file except in compliance with the License. You may obtain +# a copy of the License at +# +# http://www.apache.org/licenses/LICENSE-2.0 +# +# Unless required by applicable law or agreed to in writing, software +# distributed under the License is distributed on an "AS IS" BASIS, WITHOUT +# WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the +# License for the specific language governing permissions and limitations +# under the License. + +import datetime +import textwrap + +import migrate +from oslo_log import log +import sqlalchemy as sql + +from keystone.assignment.backends import sql as assignment_sql +from keystone.common import sql as ks_sql +import keystone.conf +from keystone.identity.mapping_backends import mapping as mapping_backend + +CONF = keystone.conf.CONF +LOG = log.getLogger(__name__) + +# FIXME(stephenfin): Remove this as soon as we're done reworking the +# migrations. Until then, this is necessary to allow us to use the native +# sqlalchemy-migrate tooling (which won't register opts). Alternatively, maybe +# the server default *shouldn't* rely on a (changeable) config option value? +try: + service_provider_relay_state_prefix_default = CONF.saml.relay_state_prefix +except Exception: + service_provider_relay_state_prefix_default = 'ss:mem:' + + +def upgrade(migrate_engine): + meta = sql.MetaData() + meta.bind = migrate_engine + + if migrate_engine.name == 'mysql': + # In Folsom we explicitly converted migrate_version to UTF8. + migrate_engine.execute( + 'ALTER TABLE migrate_version CONVERT TO CHARACTER SET utf8' + ) + # Set default DB charset to UTF8. + migrate_engine.execute( + 'ALTER DATABASE %s DEFAULT CHARACTER SET utf8' + % migrate_engine.url.database + ) + + access_token = sql.Table( + 'access_token', + meta, + sql.Column('id', sql.String(64), primary_key=True, nullable=False), + sql.Column('access_secret', sql.String(64), nullable=False), + sql.Column( + 'authorizing_user_id', sql.String(64), nullable=False, index=True + ), + sql.Column('project_id', sql.String(64), nullable=False), + sql.Column('role_ids', sql.Text(), nullable=False), + sql.Column( + 'consumer_id', + sql.String(64), + sql.ForeignKey('consumer.id'), + nullable=False, + index=True, + ), + sql.Column('expires_at', sql.String(64), nullable=True), + ) + + consumer = sql.Table( + 'consumer', + meta, + sql.Column('id', sql.String(64), primary_key=True, nullable=False), + sql.Column('description', sql.String(64), nullable=True), + sql.Column('secret', sql.String(64), nullable=False), + sql.Column('extra', sql.Text(), nullable=False), + ) + + credential = sql.Table( + 'credential', + meta, + sql.Column('id', sql.String(length=64), primary_key=True), + sql.Column('user_id', sql.String(length=64), nullable=False), + sql.Column('project_id', sql.String(length=64)), + sql.Column('type', sql.String(length=255), nullable=False), + sql.Column('extra', ks_sql.JsonBlob.impl), + sql.Column('key_hash', sql.String(64), nullable=False), + sql.Column( + 'encrypted_blob', + ks_sql.Text, + nullable=False, + ), + mysql_engine='InnoDB', + mysql_charset='utf8', + ) + + endpoint = sql.Table( + 'endpoint', + meta, + sql.Column('id', sql.String(length=64), primary_key=True), + sql.Column('legacy_endpoint_id', sql.String(length=64)), + sql.Column('interface', sql.String(length=8), nullable=False), + sql.Column('service_id', sql.String(length=64), nullable=False), + sql.Column('url', sql.Text, nullable=False), + sql.Column('extra', ks_sql.JsonBlob.impl), + sql.Column( + 'enabled', + sql.Boolean, + nullable=False, + default=True, + server_default='1', + ), + sql.Column('region_id', sql.String(length=255), nullable=True), + # NOTE(stevemar): The index was named 'service_id' in + # 050_fk_consistent_indexes.py and needs to be preserved + sql.Index('service_id', 'service_id'), + mysql_engine='InnoDB', + mysql_charset='utf8', + ) + + endpoint_group = sql.Table( + 'endpoint_group', + meta, + sql.Column('id', sql.String(64), primary_key=True), + sql.Column('name', sql.String(255), nullable=False), + sql.Column('description', sql.Text, nullable=True), + sql.Column('filters', sql.Text(), nullable=False), + ) + + federated_user = sql.Table( + 'federated_user', + meta, + sql.Column('id', sql.Integer, primary_key=True, nullable=False), + sql.Column( + 'user_id', + sql.String(64), + sql.ForeignKey('user.id', ondelete='CASCADE'), + nullable=False, + ), + sql.Column( + 'idp_id', + sql.String(64), + sql.ForeignKey('identity_provider.id', ondelete='CASCADE'), + nullable=False, + ), + sql.Column('protocol_id', sql.String(64), nullable=False), + sql.Column('unique_id', sql.String(255), nullable=False), + sql.Column('display_name', sql.String(255), nullable=True), + sql.UniqueConstraint('idp_id', 'protocol_id', 'unique_id'), + mysql_engine='InnoDB', + mysql_charset='utf8', + ) + + federation_protocol = sql.Table( + 'federation_protocol', + meta, + sql.Column('id', sql.String(64), primary_key=True), + sql.Column( + 'idp_id', + sql.String(64), + sql.ForeignKey('identity_provider.id', ondelete='CASCADE'), + primary_key=True, + ), + sql.Column('mapping_id', sql.String(64), nullable=False), + sql.Column('remote_id_attribute', sql.String(64)), + mysql_engine='InnoDB', + mysql_charset='utf8', + ) + + group = sql.Table( + 'group', + meta, + sql.Column('id', sql.String(length=64), primary_key=True), + sql.Column('domain_id', sql.String(length=64), nullable=False), + sql.Column('name', sql.String(length=64), nullable=False), + sql.Column('description', sql.Text), + sql.Column('extra', ks_sql.JsonBlob.impl), + migrate.UniqueConstraint( + 'domain_id', + 'name', + name='ixu_group_name_domain_id', + ), + mysql_engine='InnoDB', + mysql_charset='utf8', + ) + + identity_provider = sql.Table( + 'identity_provider', + meta, + sql.Column('id', sql.String(64), primary_key=True), + sql.Column('enabled', sql.Boolean, nullable=False), + sql.Column('description', sql.Text(), nullable=True), + sql.Column('domain_id', sql.String(64), nullable=False), + sql.Column('authorization_ttl', sql.Integer, nullable=True), + mysql_engine='InnoDB', + mysql_charset='utf8', + ) + + idp_remote_ids = sql.Table( + 'idp_remote_ids', + meta, + sql.Column( + 'idp_id', + sql.String(64), + sql.ForeignKey('identity_provider.id', ondelete='CASCADE'), + ), + sql.Column('remote_id', sql.String(255), primary_key=True), + mysql_engine='InnoDB', + mysql_charset='utf8', + ) + + implied_role = sql.Table( + 'implied_role', + meta, + sql.Column('prior_role_id', sql.String(length=64), primary_key=True), + sql.Column('implied_role_id', sql.String(length=64), primary_key=True), + mysql_engine='InnoDB', + mysql_charset='utf8', + ) + + local_user = sql.Table( + 'local_user', + meta, + sql.Column('id', sql.Integer, primary_key=True, nullable=False), + sql.Column( + 'user_id', + sql.String(64), + nullable=False, + unique=True, + ), + sql.Column('domain_id', sql.String(64), nullable=False), + sql.Column('name', sql.String(255), nullable=False), + sql.Column('failed_auth_count', sql.Integer, nullable=True), + sql.Column('failed_auth_at', sql.DateTime(), nullable=True), + sql.UniqueConstraint('domain_id', 'name'), + ) + + mapping = sql.Table( + 'mapping', + meta, + sql.Column('id', sql.String(64), primary_key=True), + sql.Column('rules', sql.Text(), nullable=False), + mysql_engine='InnoDB', + mysql_charset='utf8', + ) + + password = sql.Table( + 'password', + meta, + sql.Column('id', sql.Integer, primary_key=True, nullable=False), + sql.Column( + 'local_user_id', + sql.Integer, + sql.ForeignKey(local_user.c.id, ondelete='CASCADE'), + nullable=False, + ), + sql.Column('expires_at', sql.DateTime(), nullable=True), + sql.Column( + 'self_service', + sql.Boolean, + nullable=False, + server_default='0', + default=False, + ), + # NOTE(notmorgan): To support the full range of scrypt and pbkfd + # password hash lengths, this should be closer to varchar(1500) instead + # of varchar(255). + sql.Column('password_hash', sql.String(255), nullable=True), + sql.Column( + 'created_at_int', + ks_sql.DateTimeInt(), + nullable=False, + default=0, + server_default='0', + ), + sql.Column('expires_at_int', ks_sql.DateTimeInt(), nullable=True), + sql.Column( + 'created_at', + sql.DateTime(), + nullable=False, + default=datetime.datetime.utcnow, + ), + ) + + policy = sql.Table( + 'policy', + meta, + sql.Column('id', sql.String(length=64), primary_key=True), + sql.Column('type', sql.String(length=255), nullable=False), + sql.Column('blob', ks_sql.JsonBlob, nullable=False), + sql.Column('extra', ks_sql.JsonBlob.impl), + mysql_engine='InnoDB', + mysql_charset='utf8', + ) + + policy_association = sql.Table( + 'policy_association', + meta, + sql.Column('id', sql.String(64), primary_key=True), + sql.Column('policy_id', sql.String(64), nullable=False), + sql.Column('endpoint_id', sql.String(64), nullable=True), + sql.Column('service_id', sql.String(64), nullable=True), + sql.Column('region_id', sql.String(64), nullable=True), + sql.UniqueConstraint('endpoint_id', 'service_id', 'region_id'), + mysql_engine='InnoDB', + mysql_charset='utf8', + ) + + project = sql.Table( + 'project', + meta, + sql.Column('id', sql.String(length=64), primary_key=True), + sql.Column('name', sql.String(length=64), nullable=False), + sql.Column('extra', ks_sql.JsonBlob.impl), + sql.Column('description', sql.Text), + sql.Column('enabled', sql.Boolean), + sql.Column('domain_id', sql.String(length=64), nullable=False), + sql.Column('parent_id', sql.String(64), nullable=True), + sql.Column( + 'is_domain', + sql.Boolean, + nullable=False, + server_default='0', + default=False, + ), + migrate.UniqueConstraint( + 'domain_id', + 'name', + name='ixu_project_name_domain_id', + ), + mysql_engine='InnoDB', + mysql_charset='utf8', + ) + + project_option = sql.Table( + 'project_option', + meta, + sql.Column( + 'project_id', + sql.String(64), + sql.ForeignKey(project.c.id, ondelete='CASCADE'), + nullable=False, + primary_key=True, + ), + sql.Column( + 'option_id', sql.String(4), nullable=False, primary_key=True + ), + sql.Column('option_value', ks_sql.JsonBlob, nullable=True), + mysql_engine='InnoDB', + mysql_charset='utf8', + ) + + # NOTE(lamt) To allow tag name to be case sensitive for MySQL, the 'name' + # column needs to use collation, which is incompatible with Postgresql. + # Using unicode to mirror nova's server tag: + # https://github.com/openstack/nova/blob/master/nova/db/sqlalchemy/models.py + project_tag = sql.Table( + 'project_tag', + meta, + sql.Column( + 'project_id', + sql.String(64), + sql.ForeignKey(project.c.id, ondelete='CASCADE'), + nullable=False, + primary_key=True, + ), + sql.Column('name', sql.Unicode(255), nullable=False, primary_key=True), + sql.UniqueConstraint('project_id', 'name'), + mysql_engine='InnoDB', + mysql_charset='utf8', + ) + + project_endpoint = sql.Table( + 'project_endpoint', + meta, + sql.Column( + 'endpoint_id', sql.String(64), primary_key=True, nullable=False + ), + sql.Column( + 'project_id', sql.String(64), primary_key=True, nullable=False + ), + ) + + project_endpoint_group = sql.Table( + 'project_endpoint_group', + meta, + sql.Column( + 'endpoint_group_id', + sql.String(64), + sql.ForeignKey('endpoint_group.id'), + nullable=False, + ), + sql.Column('project_id', sql.String(64), nullable=False), + sql.PrimaryKeyConstraint('endpoint_group_id', 'project_id'), + ) + + config_register = sql.Table( + 'config_register', + meta, + sql.Column('type', sql.String(64), primary_key=True), + sql.Column('domain_id', sql.String(64), nullable=False), + mysql_engine='InnoDB', + mysql_charset='utf8', + ) + + request_token = sql.Table( + 'request_token', + meta, + sql.Column('id', sql.String(64), primary_key=True, nullable=False), + sql.Column('request_secret', sql.String(64), nullable=False), + sql.Column('verifier', sql.String(64), nullable=True), + sql.Column('authorizing_user_id', sql.String(64), nullable=True), + sql.Column('requested_project_id', sql.String(64), nullable=False), + sql.Column('role_ids', sql.Text(), nullable=True), + sql.Column( + 'consumer_id', + sql.String(64), + sql.ForeignKey('consumer.id'), + nullable=False, + index=True, + ), + sql.Column('expires_at', sql.String(64), nullable=True), + ) + + revocation_event = sql.Table( + 'revocation_event', + meta, + sql.Column('id', sql.Integer, primary_key=True), + sql.Column('domain_id', sql.String(64)), + sql.Column('project_id', sql.String(64)), + sql.Column('user_id', sql.String(64)), + sql.Column('role_id', sql.String(64)), + sql.Column('trust_id', sql.String(64)), + sql.Column('consumer_id', sql.String(64)), + sql.Column('access_token_id', sql.String(64)), + sql.Column('issued_before', sql.DateTime(), nullable=False), + sql.Column('expires_at', sql.DateTime()), + sql.Column('revoked_at', sql.DateTime(), nullable=False), + sql.Column('audit_id', sql.String(32), nullable=True), + sql.Column('audit_chain_id', sql.String(32), nullable=True), + # NOTE(stephenfin): The '_new' suffix here is due to migration 095, + # which changed the 'id' column from String(64) to Integer. It did this + # by creating a 'revocation_event_new' table and populating it with + # data from the 'revocation_event' table before deleting the + # 'revocation_event' table and renaming the 'revocation_event_new' + # table to 'revocation_event'. Because the 'revoked_at' column had + # 'index=True', sqlalchemy automatically generated the index name as + # 'ix_{table}_{column}'. However, when intitially created, '{table}' + # was 'revocation_event_new' so the index got that name. We may wish to + # rename this eventually. + sql.Index('ix_revocation_event_new_revoked_at', 'revoked_at'), + sql.Index('ix_revocation_event_issued_before', 'issued_before'), + sql.Index( + 'ix_revocation_event_project_id_issued_before', + 'project_id', + 'issued_before', + ), + sql.Index( + 'ix_revocation_event_user_id_issued_before', + 'user_id', + 'issued_before', + ), + sql.Index( + 'ix_revocation_event_audit_id_issued_before', + 'audit_id', + 'issued_before', + ), + ) + + role = sql.Table( + 'role', + meta, + sql.Column('id', sql.String(length=64), primary_key=True), + sql.Column('name', sql.String(length=255), nullable=False), + sql.Column('extra', ks_sql.JsonBlob.impl), + sql.Column( + 'domain_id', + sql.String(64), + nullable=False, + server_default='<>', + ), + sql.Column('description', sql.String(255), nullable=True), + migrate.UniqueConstraint( + 'name', + 'domain_id', + name='ixu_role_name_domain_id', + ), + mysql_engine='InnoDB', + mysql_charset='utf8', + ) + + role_option = sql.Table( + 'role_option', + meta, + sql.Column( + 'role_id', + sql.String(64), + sql.ForeignKey(role.c.id, ondelete='CASCADE'), + nullable=False, + primary_key=True, + ), + sql.Column( + 'option_id', sql.String(4), nullable=False, primary_key=True + ), + sql.Column('option_value', ks_sql.JsonBlob, nullable=True), + mysql_engine='InnoDB', + mysql_charset='utf8', + ) + + service = sql.Table( + 'service', + meta, + sql.Column('id', sql.String(length=64), primary_key=True), + sql.Column('type', sql.String(length=255)), + sql.Column( + 'enabled', + sql.Boolean, + nullable=False, + default=True, + server_default='1', + ), + sql.Column('extra', ks_sql.JsonBlob.impl), + mysql_engine='InnoDB', + mysql_charset='utf8', + ) + + service_provider = sql.Table( + 'service_provider', + meta, + sql.Column('auth_url', sql.String(256), nullable=False), + sql.Column('id', sql.String(64), primary_key=True), + sql.Column('enabled', sql.Boolean, nullable=False), + sql.Column('description', sql.Text(), nullable=True), + sql.Column('sp_url', sql.String(256), nullable=False), + sql.Column( + 'relay_state_prefix', + sql.String(256), + nullable=False, + server_default=service_provider_relay_state_prefix_default, + ), + mysql_engine='InnoDB', + mysql_charset='utf8', + ) + + token = sql.Table( + 'token', + meta, + sql.Column('id', sql.String(length=64), primary_key=True), + sql.Column('expires', sql.DateTime, default=None), + sql.Column('extra', ks_sql.JsonBlob.impl), + sql.Column('valid', sql.Boolean, default=True, nullable=False), + sql.Column('trust_id', sql.String(length=64)), + sql.Column('user_id', sql.String(length=64)), + sql.Index('ix_token_expires', 'expires'), + sql.Index('ix_token_expires_valid', 'expires', 'valid'), + sql.Index('ix_token_user_id', 'user_id'), + sql.Index('ix_token_trust_id', 'trust_id'), + mysql_engine='InnoDB', + mysql_charset='utf8', + ) + + trust = sql.Table( + 'trust', + meta, + sql.Column('id', sql.String(length=64), primary_key=True), + sql.Column('trustor_user_id', sql.String(length=64), nullable=False), + sql.Column('trustee_user_id', sql.String(length=64), nullable=False), + sql.Column('project_id', sql.String(length=64)), + sql.Column('impersonation', sql.Boolean, nullable=False), + sql.Column('deleted_at', sql.DateTime), + sql.Column('expires_at', sql.DateTime), + sql.Column('remaining_uses', sql.Integer, nullable=True), + sql.Column('extra', ks_sql.JsonBlob.impl), + sql.Column('expires_at_int', ks_sql.DateTimeInt()), + sql.UniqueConstraint( + 'trustor_user_id', + 'trustee_user_id', + 'project_id', + 'impersonation', + 'expires_at', + 'expires_at_int', + name='duplicate_trust_constraint_expanded', + ), + sql.Column( + 'redelegated_trust_id', + sql.String(64), + nullable=True, + ), + sql.Column( + 'redelegation_count', + sql.Integer, + nullable=True, + ), + mysql_engine='InnoDB', + mysql_charset='utf8', + ) + + trust_role = sql.Table( + 'trust_role', + meta, + sql.Column( + 'trust_id', sql.String(length=64), primary_key=True, nullable=False + ), + sql.Column( + 'role_id', sql.String(length=64), primary_key=True, nullable=False + ), + mysql_engine='InnoDB', + mysql_charset='utf8', + ) + + user = sql.Table( + 'user', + meta, + sql.Column('id', sql.String(length=64), primary_key=True), + sql.Column('extra', ks_sql.JsonBlob.impl), + sql.Column('enabled', sql.Boolean), + sql.Column('default_project_id', sql.String(length=64)), + sql.Column('created_at', sql.DateTime(), nullable=True), + sql.Column('last_active_at', sql.Date(), nullable=True), + sql.Column('domain_id', sql.String(64), nullable=False), + sql.UniqueConstraint('id', 'domain_id', name='ixu_user_id_domain_id'), + sql.Index('ix_default_project_id', 'default_project_id'), + mysql_engine='InnoDB', + mysql_charset='utf8', + ) + + user_option = sql.Table( + 'user_option', + meta, + sql.Column( + 'user_id', + sql.String(64), + sql.ForeignKey(user.c.id, ondelete='CASCADE'), + nullable=False, + primary_key=True, + ), + sql.Column( + 'option_id', sql.String(4), nullable=False, primary_key=True + ), + sql.Column('option_value', ks_sql.JsonBlob, nullable=True), + mysql_engine='InnoDB', + mysql_charset='utf8', + ) + + nonlocal_user = sql.Table( + 'nonlocal_user', + meta, + sql.Column('domain_id', sql.String(64), primary_key=True), + sql.Column('name', sql.String(255), primary_key=True), + sql.Column( + 'user_id', + sql.String(64), + nullable=False, + ), + sql.UniqueConstraint('user_id', name='ixu_nonlocal_user_user_id'), + mysql_engine='InnoDB', + mysql_charset='utf8', + ) + + user_group_membership = sql.Table( + 'user_group_membership', + meta, + sql.Column('user_id', sql.String(length=64), primary_key=True), + sql.Column('group_id', sql.String(length=64), primary_key=True), + # NOTE(stevemar): The index was named 'group_id' in + # 050_fk_consistent_indexes.py and needs to be preserved + sql.Index('group_id', 'group_id'), + mysql_engine='InnoDB', + mysql_charset='utf8', + ) + + region = sql.Table( + 'region', + meta, + sql.Column('id', sql.String(255), primary_key=True), + sql.Column('description', sql.String(255), nullable=False), + sql.Column('parent_region_id', sql.String(255), nullable=True), + sql.Column('extra', sql.Text()), + mysql_engine='InnoDB', + mysql_charset='utf8', + ) + + assignment = sql.Table( + 'assignment', + meta, + sql.Column( + 'type', + sql.Enum( + assignment_sql.AssignmentType.USER_PROJECT, + assignment_sql.AssignmentType.GROUP_PROJECT, + assignment_sql.AssignmentType.USER_DOMAIN, + assignment_sql.AssignmentType.GROUP_DOMAIN, + name='type', + ), + nullable=False, + ), + sql.Column('actor_id', sql.String(64), nullable=False), + sql.Column('target_id', sql.String(64), nullable=False), + sql.Column('role_id', sql.String(64), nullable=False), + sql.Column('inherited', sql.Boolean, default=False, nullable=False), + sql.PrimaryKeyConstraint( + 'type', + 'actor_id', + 'target_id', + 'role_id', + 'inherited', + ), + sql.Index('ix_actor_id', 'actor_id'), + mysql_engine='InnoDB', + mysql_charset='utf8', + ) + + id_mapping = sql.Table( + 'id_mapping', + meta, + sql.Column('public_id', sql.String(64), primary_key=True), + sql.Column('domain_id', sql.String(64), nullable=False), + sql.Column('local_id', sql.String(64), nullable=False), + sql.Column( + 'entity_type', + sql.Enum( + mapping_backend.EntityType.USER, + mapping_backend.EntityType.GROUP, + name='entity_type', + ), + nullable=False, + ), + migrate.UniqueConstraint( + 'domain_id', + 'local_id', + 'entity_type', + name='domain_id', + ), + mysql_engine='InnoDB', + mysql_charset='utf8', + ) + + whitelisted_config = sql.Table( + 'whitelisted_config', + meta, + sql.Column('domain_id', sql.String(64), primary_key=True), + sql.Column('group', sql.String(255), primary_key=True), + sql.Column('option', sql.String(255), primary_key=True), + sql.Column('value', ks_sql.JsonBlob.impl, nullable=False), + mysql_engine='InnoDB', + mysql_charset='utf8', + ) + + sensitive_config = sql.Table( + 'sensitive_config', + meta, + sql.Column('domain_id', sql.String(64), primary_key=True), + sql.Column('group', sql.String(255), primary_key=True), + sql.Column('option', sql.String(255), primary_key=True), + sql.Column('value', ks_sql.JsonBlob.impl, nullable=False), + mysql_engine='InnoDB', + mysql_charset='utf8', + ) + + system_assignment = sql.Table( + 'system_assignment', + meta, + sql.Column('type', sql.String(64), nullable=False), + sql.Column('actor_id', sql.String(64), nullable=False), + sql.Column('target_id', sql.String(64), nullable=False), + sql.Column('role_id', sql.String(64), nullable=False), + sql.Column('inherited', sql.Boolean, default=False, nullable=False), + sql.PrimaryKeyConstraint( + 'type', 'actor_id', 'target_id', 'role_id', 'inherited' + ), + mysql_engine='InnoDB', + mysql_charset='utf8', + ) + + registered_limit = sql.Table( + 'registered_limit', + meta, + sql.Column('id', sql.String(length=64), nullable=False), + sql.Column('service_id', sql.String(255)), + sql.Column('region_id', sql.String(64), nullable=True), + sql.Column('resource_name', sql.String(255)), + sql.Column('default_limit', sql.Integer, nullable=False), + sql.Column('description', sql.Text), + sql.Column('internal_id', sql.Integer, primary_key=True), + # NOTE(stephenfin): Name chosen to preserve backwards compatibility + # with names used for primary key unique constraints + sql.UniqueConstraint('id', name='registered_limit_id_key'), + mysql_engine='InnoDB', + mysql_charset='utf8', + ) + + limit = sql.Table( + 'limit', + meta, + sql.Column('id', sql.String(length=64), nullable=False), + sql.Column('project_id', sql.String(64), nullable=True), + sql.Column('resource_limit', sql.Integer, nullable=False), + sql.Column('description', sql.Text), + sql.Column('internal_id', sql.Integer, primary_key=True), + # FIXME(stephenfin): This should have a foreign key constraint on + # registered_limit.id, but sqlalchemy-migrate clearly didn't handle + # creating a column with embedded FK info as was attempted in 048 + sql.Column( + 'registered_limit_id', + sql.String(64), + ), + sql.Column('domain_id', sql.String(64), nullable=True), + # NOTE(stephenfin): Name chosen to preserve backwards compatibility + # with names used for primary key unique constraints + sql.UniqueConstraint('id', name='limit_id_key'), + mysql_engine='InnoDB', + mysql_charset='utf8', + ) + + application_credential = sql.Table( + 'application_credential', + meta, + sql.Column( + 'internal_id', sql.Integer, primary_key=True, nullable=False + ), + sql.Column('id', sql.String(length=64), nullable=False), + sql.Column('name', sql.String(length=255), nullable=False), + sql.Column('secret_hash', sql.String(length=255), nullable=False), + sql.Column('description', sql.Text), + sql.Column('user_id', sql.String(length=64), nullable=False), + sql.Column('project_id', sql.String(64), nullable=True), + sql.Column('expires_at', ks_sql.DateTimeInt()), + sql.Column('system', sql.String(64), nullable=True), + sql.Column('unrestricted', sql.Boolean), + sql.UniqueConstraint( + 'user_id', 'name', name='duplicate_app_cred_constraint' + ), + mysql_engine='InnoDB', + mysql_charset='utf8', + ) + + application_credential_role = sql.Table( + 'application_credential_role', + meta, + sql.Column( + 'application_credential_id', + sql.Integer, + sql.ForeignKey( + application_credential.c.internal_id, ondelete='CASCADE' + ), + primary_key=True, + nullable=False, + ), + sql.Column( + 'role_id', sql.String(length=64), primary_key=True, nullable=False + ), + mysql_engine='InnoDB', + mysql_charset='utf8', + ) + + access_rule = sql.Table( + 'access_rule', + meta, + sql.Column('id', sql.Integer, primary_key=True, nullable=False), + sql.Column('service', sql.String(64)), + sql.Column('path', sql.String(128)), + sql.Column('method', sql.String(16)), + sql.Column('external_id', sql.String(64)), + sql.Column('user_id', sql.String(64)), + sql.UniqueConstraint( + 'external_id', + name='access_rule_external_id_key', + ), + sql.UniqueConstraint( + 'user_id', + 'service', + 'path', + 'method', + name='duplicate_access_rule_for_user_constraint', + ), + sql.Index('user_id', 'user_id'), + sql.Index('external_id', 'external_id'), + mysql_engine='InnoDB', + mysql_charset='utf8', + ) + + app_cred_access_rule = sql.Table( + 'application_credential_access_rule', + meta, + sql.Column( + 'application_credential_id', + sql.Integer, + sql.ForeignKey( + application_credential.c.internal_id, ondelete='CASCADE' + ), + primary_key=True, + nullable=False, + ), + sql.Column( + 'access_rule_id', + sql.Integer, + sql.ForeignKey(access_rule.c.id, ondelete='CASCADE'), + primary_key=True, + nullable=False, + ), + mysql_engine='InnoDB', + mysql_charset='utf8', + ) + + expiring_user_group_membership = sql.Table( + 'expiring_user_group_membership', + meta, + sql.Column( + 'user_id', + sql.String(64), + sql.ForeignKey(user.c.id), + primary_key=True, + ), + sql.Column( + 'group_id', + sql.String(64), + sql.ForeignKey(group.c.id), + primary_key=True, + ), + sql.Column( + 'idp_id', + sql.String(64), + sql.ForeignKey(identity_provider.c.id, ondelete='CASCADE'), + primary_key=True, + ), + sql.Column('last_verified', sql.DateTime(), nullable=False), + mysql_engine='InnoDB', + mysql_charset='utf8', + ) + + # create all tables + tables = [ + credential, + endpoint, + group, + policy, + project, + project_option, + project_tag, + role, + role_option, + service, + token, + trust, + trust_role, + user, + user_option, + user_group_membership, + region, + assignment, + id_mapping, + whitelisted_config, + sensitive_config, + config_register, + policy_association, + identity_provider, + federation_protocol, + mapping, + service_provider, + idp_remote_ids, + consumer, + request_token, + access_token, + revocation_event, + project_endpoint, + endpoint_group, + project_endpoint_group, + implied_role, + local_user, + password, + federated_user, + nonlocal_user, + system_assignment, + registered_limit, + limit, + application_credential, + application_credential_role, + access_rule, + app_cred_access_rule, + expiring_user_group_membership, + ] + + for table in tables: + try: + table.create() + except Exception: + LOG.exception('Exception while creating table: %r', table) + raise + + fkeys = [ + { + 'columns': [endpoint.c.service_id], + 'references': [service.c.id], + }, + { + 'columns': [user_group_membership.c.group_id], + 'references': [group.c.id], + 'name': 'fk_user_group_membership_group_id', + }, + { + 'columns': [user_group_membership.c.user_id], + 'references': [user.c.id], + 'name': 'fk_user_group_membership_user_id', + }, + { + 'columns': [project.c.domain_id], + 'references': [project.c.id], + }, + { + 'columns': [endpoint.c.region_id], + 'references': [region.c.id], + 'name': 'fk_endpoint_region_id', + }, + { + 'columns': [project.c.parent_id], + 'references': [project.c.id], + 'name': 'project_parent_id_fkey', + }, + { + 'columns': [implied_role.c.prior_role_id], + 'references': [role.c.id], + 'ondelete': 'CASCADE', + }, + { + 'columns': [implied_role.c.implied_role_id], + 'references': [role.c.id], + 'ondelete': 'CASCADE', + }, + { + 'columns': [ + federated_user.c.protocol_id, + federated_user.c.idp_id, + ], + 'references': [ + federation_protocol.c.id, + federation_protocol.c.idp_id, + ], + 'ondelete': 'CASCADE', + }, + { + 'columns': [local_user.c.user_id, local_user.c.domain_id], + 'references': [user.c.id, user.c.domain_id], + 'onupdate': 'CASCADE', + 'ondelete': 'CASCADE', + }, + { + 'columns': [nonlocal_user.c.user_id, nonlocal_user.c.domain_id], + 'references': [user.c.id, user.c.domain_id], + 'onupdate': 'CASCADE', + 'ondelete': 'CASCADE', + }, + ] + + if migrate_engine.name == 'sqlite': + # NOTE(stevemar): We need to keep this FK constraint due to 073, but + # only for sqlite, once we collapse 073 we can remove this constraint + fkeys.append( + { + 'columns': [assignment.c.role_id], + 'references': [role.c.id], + 'name': 'fk_assignment_role_id', + }, + ) + + for fkey in fkeys: + migrate.ForeignKeyConstraint( + columns=fkey['columns'], + refcolumns=fkey['references'], + name=fkey.get('name'), + ondelete=fkey.get('ondelete'), + onupdate=fkey.get('onupdate'), + ).create() + + # TODO(stephenfin): Remove these procedures in a future contract migration + + if migrate_engine.name == 'postgresql': + error_message = ( + 'Credential migration in progress. Cannot perform ' + 'writes to credential table.' + ) + credential_update_trigger = textwrap.dedent(f""" + CREATE OR REPLACE FUNCTION keystone_read_only_update() + RETURNS trigger AS + $BODY$ + BEGIN + IF NEW.encrypted_blob IS NULL THEN + RAISE EXCEPTION '{error_message}'; + END IF; + IF NEW.encrypted_blob IS NOT NULL AND OLD.blob IS NULL THEN + RAISE EXCEPTION '{error_message}'; + END IF; + RETURN NEW; + END + $BODY$ LANGUAGE plpgsql; + """) + migrate_engine.execute(credential_update_trigger) + + error_message = ( + 'Identity provider migration in progress. Cannot ' + 'insert new rows into the identity_provider table at ' + 'this time.' + ) + identity_provider_insert_trigger = textwrap.dedent(f""" + CREATE OR REPLACE FUNCTION keystone_read_only_insert() + RETURNS trigger AS + $BODY$ + BEGIN + RAISE EXCEPTION '{error_message}'; + END + $BODY$ LANGUAGE plpgsql; + """) + migrate_engine.execute(identity_provider_insert_trigger) + + federated_user_insert_trigger = textwrap.dedent(""" + CREATE OR REPLACE FUNCTION update_federated_user_domain_id() + RETURNS trigger AS + $BODY$ + BEGIN + UPDATE "user" SET domain_id = ( + SELECT domain_id FROM identity_provider WHERE id = NEW.idp_id) + WHERE id = NEW.user_id and domain_id IS NULL; + RETURN NULL; + END + $BODY$ LANGUAGE plpgsql; + """) + migrate_engine.execute(federated_user_insert_trigger) + + local_user_insert_trigger = textwrap.dedent(""" + CREATE OR REPLACE FUNCTION update_user_domain_id() + RETURNS trigger AS + $BODY$ + BEGIN + UPDATE "user" SET domain_id = NEW.domain_id + WHERE id = NEW.user_id; + RETURN NULL; + END + $BODY$ LANGUAGE plpgsql; + """) + migrate_engine.execute(local_user_insert_trigger) + + # FIXME(stephenfin): Remove these indexes. They're left over from attempts + # to remove foreign key constraints in past migrations. Apparently + # sqlalchemy-migrate didn't do the job fully and left behind indexes + if migrate_engine.name == 'mysql': + sql.Index('region_id', registered_limit.c.region_id).create() + + # FIXME(stephenfin): This should be dropped when we add the FK + # constraint to this column + sql.Index('registered_limit_id', limit.c.registered_limit_id).create() + + # FIXME(stephenfin): These are leftover from when we removed a FK + # constraint and should probable be dropped + sql.Index('domain_id', identity_provider.c.domain_id).create() + sql.Index('domain_id', user.c.domain_id).create() diff --git a/keystone/common/sql/upgrades.py b/keystone/common/sql/upgrades.py index b5d9092cb..49649cc61 100644 --- a/keystone/common/sql/upgrades.py +++ b/keystone/common/sql/upgrades.py @@ -29,7 +29,7 @@ from keystone.i18n import _ USE_TRIGGERS = True -INITIAL_VERSION = 65 +INITIAL_VERSION = 72 EXPAND_REPO = 'expand_repo' DATA_MIGRATION_REPO = 'data_migration_repo' CONTRACT_REPO = 'contract_repo' diff --git a/keystone/tests/unit/test_sql_upgrade.py b/keystone/tests/unit/test_sql_upgrade.py index 4494ffa44..bb5b19208 100644 --- a/keystone/tests/unit/test_sql_upgrade.py +++ b/keystone/tests/unit/test_sql_upgrade.py @@ -142,7 +142,7 @@ INITIAL_TABLE_STRUCTURE = { 'id', 'policy_id', 'endpoint_id', 'service_id', 'region_id', ], 'identity_provider': [ - 'id', 'enabled', 'description', 'domain_id', + 'id', 'enabled', 'description', 'domain_id', 'authorization_ttl', ], 'federation_protocol': [ 'id', 'idp_id', 'mapping_id', 'remote_id_attribute', @@ -223,6 +223,9 @@ INITIAL_TABLE_STRUCTURE = { 'application_credential_access_rule': [ 'application_credential_id', 'access_rule_id', ], + 'expiring_user_group_membership': [ + 'user_id', 'group_id', 'idp_id', 'last_verified', + ], } @@ -633,46 +636,6 @@ class FullMigration(MigrateBase, unit.TestCase): upgrades.INITIAL_VERSION + 2, ) - def test_migration_072_drop_domain_id_fk(self): - self.expand(71) - self.migrate(71) - self.contract(71) - - self.assertTrue(self.does_fk_exist('user', 'domain_id')) - self.assertTrue(self.does_fk_exist('identity_provider', 'domain_id')) - - self.expand(72) - self.migrate(72) - self.contract(72) - - self.assertFalse(self.does_fk_exist('user', 'domain_id')) - self.assertFalse(self.does_fk_exist('identity_provider', 'domain_id')) - - def test_migration_073_contract_expiring_group_membership(self): - self.expand(72) - self.migrate(72) - self.contract(72) - - membership_table = 'expiring_user_group_membership' - self.assertTableDoesNotExist(membership_table) - - idp_table = 'identity_provider' - self.assertTableColumns( - idp_table, - ['id', 'domain_id', 'enabled', 'description']) - - self.expand(73) - self.migrate(73) - self.contract(73) - - self.assertTableColumns( - membership_table, - ['user_id', 'group_id', 'idp_id', 'last_verified']) - self.assertTableColumns( - idp_table, - ['id', 'domain_id', 'enabled', 'description', - 'authorization_ttl']) - def test_migration_079_expand_update_local_id_limit(self): self.expand(78) self.migrate(78) -- cgit v1.2.1