diff options
author | Zuul <zuul@review.opendev.org> | 2022-02-09 21:13:14 +0000 |
---|---|---|
committer | Gerrit Code Review <review@openstack.org> | 2022-02-09 21:13:14 +0000 |
commit | f55fc977270a8a845217c7b4fdb08705c213a139 (patch) | |
tree | e2236b47d01c178abd155eb928d61de96f2610b7 | |
parent | a1d0694d5c15f2f1542ee85107067b0af5daa1b8 (diff) | |
parent | afce7ca8eb70a8be7dfa58b206d7054f1f5df2a4 (diff) | |
download | keystone-f55fc977270a8a845217c7b4fdb08705c213a139.tar.gz |
Merge "sql: Populate initial alembic migration"
3 files changed, 1109 insertions, 30 deletions
diff --git a/keystone/common/sql/migrations/env.py b/keystone/common/sql/migrations/env.py index 49efd90f3..2d116f1bd 100644 --- a/keystone/common/sql/migrations/env.py +++ b/keystone/common/sql/migrations/env.py @@ -20,9 +20,10 @@ from sqlalchemy import pool # access to the values within the .ini file in use. config = context.config -# Interpret the config file for Python logging. +# Interpret the config file for Python logging unless we're told not to. # This line sets up loggers basically. -fileConfig(config.config_file_name) +if config.attributes.get('configure_logger', True): + fileConfig(config.config_file_name) # add your model's MetaData object here # for 'autogenerate' support diff --git a/keystone/common/sql/migrations/versions/27e647c0fad4_initial_version.py b/keystone/common/sql/migrations/versions/27e647c0fad4_initial_version.py new file mode 100644 index 000000000..fb40343d6 --- /dev/null +++ b/keystone/common/sql/migrations/versions/27e647c0fad4_initial_version.py @@ -0,0 +1,1106 @@ +# 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. + +"""Initial version + +Revision ID: 27e647c0fad4 +Revises: +Create Date: 2021-12-23 11:13:26.305412 +""" + +import datetime +import textwrap + +from alembic import op +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 + +# revision identifiers, used by Alembic. +revision = '27e647c0fad4' +down_revision = None +depends_on = None + +CONF = keystone.conf.CONF +LOG = log.getLogger(__name__) + +NULL_DOMAIN_ID = '<<keystone.domain.root>>' + +# 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 +# alembic 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(): + bind = op.get_bind() + + if bind.engine.name == 'mysql': + # Set default DB charset to UTF8. + op.execute( + 'ALTER DATABASE %s DEFAULT CHARACTER SET utf8' + % bind.engine.url.database + ) + + op.create_table( + 'application_credential', + 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', + ) + + op.create_table( + 'assignment', + 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', + ) + + op.create_table( + 'access_rule', + 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', + ) + + op.create_table( + 'config_register', + sql.Column('type', sql.String(64), primary_key=True), + sql.Column('domain_id', sql.String(64), nullable=False), + mysql_engine='InnoDB', + mysql_charset='utf8', + ) + + op.create_table( + 'consumer', + 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), + ) + + op.create_table( + 'credential', + 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', + ) + + op.create_table( + 'group', + 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), + sql.UniqueConstraint( + 'domain_id', + 'name', + name='ixu_group_name_domain_id', + ), + mysql_engine='InnoDB', + mysql_charset='utf8', + ) + + op.create_table( + 'id_mapping', + 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, + ), + sql.UniqueConstraint( + 'domain_id', + 'local_id', + 'entity_type', + name='domain_id', + ), + mysql_engine='InnoDB', + mysql_charset='utf8', + ) + + op.create_table( + 'identity_provider', + 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', + ) + + op.create_table( + 'idp_remote_ids', + 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', + ) + + op.create_table( + 'mapping', + sql.Column('id', sql.String(64), primary_key=True), + sql.Column('rules', sql.Text(), nullable=False), + mysql_engine='InnoDB', + mysql_charset='utf8', + ) + + op.create_table( + 'policy', + 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', + ) + + op.create_table( + 'policy_association', + 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', + ) + + op.create_table( + 'project', + 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), + sql.ForeignKey( + 'project.id', + name='project_domain_id_fkey', + ), + nullable=False, + ), + sql.Column( + 'parent_id', + sql.String(64), + sql.ForeignKey( + 'project.id', + name='project_parent_id_fkey', + ), + nullable=True, + ), + sql.Column( + 'is_domain', + sql.Boolean, + nullable=False, + server_default='0', + default=False, + ), + sql.UniqueConstraint( + 'domain_id', + 'name', + name='ixu_project_name_domain_id', + ), + mysql_engine='InnoDB', + mysql_charset='utf8', + ) + + op.create_table( + 'project_endpoint', + sql.Column( + 'endpoint_id', sql.String(64), primary_key=True, nullable=False + ), + sql.Column( + 'project_id', sql.String(64), primary_key=True, nullable=False + ), + ) + + op.create_table( + 'project_option', + sql.Column( + 'project_id', + sql.String(64), + sql.ForeignKey('project.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 + op.create_table( + 'project_tag', + sql.Column( + 'project_id', + sql.String(64), + sql.ForeignKey('project.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', + ) + + op.create_table( + 'region', + 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', + ) + + op.create_table( + 'registered_limit', + 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', + ) + + op.create_table( + 'request_token', + 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), + ) + + op.create_table( + 'revocation_event', + 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', + ), + ) + + op.create_table( + 'role', + 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='<<null>>', + ), + sql.Column('description', sql.String(255), nullable=True), + sql.UniqueConstraint( + 'name', + 'domain_id', + name='ixu_role_name_domain_id', + ), + mysql_engine='InnoDB', + mysql_charset='utf8', + ) + + op.create_table( + 'role_option', + sql.Column( + 'role_id', + sql.String(64), + sql.ForeignKey('role.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', + ) + + op.create_table( + 'sensitive_config', + 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', + ) + + op.create_table( + 'service', + 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', + ) + + op.create_table( + 'service_provider', + 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', + ) + + op.create_table( + 'system_assignment', + 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', + ) + + op.create_table( + 'token', + 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', + ) + + op.create_table( + 'trust', + 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', + ) + + op.create_table( + 'trust_role', + 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', + ) + + op.create_table( + 'user', + 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', + ) + + op.create_table( + 'user_group_membership', + sql.Column( + 'user_id', + sql.String(length=64), + sql.ForeignKey( + 'user.id', + name='fk_user_group_membership_user_id', + ), + primary_key=True, + ), + sql.Column( + 'group_id', + sql.String(length=64), + sql.ForeignKey( + 'group.id', + name='fk_user_group_membership_group_id', + ), + 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', + ) + + op.create_table( + 'user_option', + sql.Column( + 'user_id', + sql.String(64), + sql.ForeignKey('user.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', + ) + + op.create_table( + 'whitelisted_config', + 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', + ) + + op.create_table( + 'access_token', + 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), + ) + + op.create_table( + 'application_credential_role', + sql.Column( + 'application_credential_id', + sql.Integer, + sql.ForeignKey( + 'application_credential.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', + ) + + op.create_table( + 'application_credential_access_rule', + sql.Column( + 'application_credential_id', + sql.Integer, + sql.ForeignKey( + 'application_credential.internal_id', ondelete='CASCADE' + ), + primary_key=True, + nullable=False, + ), + sql.Column( + 'access_rule_id', + sql.Integer, + sql.ForeignKey('access_rule.id', ondelete='CASCADE'), + primary_key=True, + nullable=False, + ), + mysql_engine='InnoDB', + mysql_charset='utf8', + ) + + op.create_table( + 'endpoint', + 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), + sql.ForeignKey( + 'service.id', + name='endpoint_service_id_fkey', + ), + 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), + sql.ForeignKey( + 'region.id', + name='fk_endpoint_region_id', + ), + 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', + ) + + op.create_table( + 'endpoint_group', + 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), + ) + + op.create_table( + 'expiring_user_group_membership', + sql.Column( + 'user_id', + sql.String(64), + sql.ForeignKey('user.id'), + primary_key=True, + ), + sql.Column( + 'group_id', + sql.String(64), + sql.ForeignKey('group.id'), + primary_key=True, + ), + sql.Column( + 'idp_id', + sql.String(64), + sql.ForeignKey('identity_provider.id', ondelete='CASCADE'), + primary_key=True, + ), + sql.Column('last_verified', sql.DateTime(), nullable=False), + mysql_engine='InnoDB', + mysql_charset='utf8', + ) + + op.create_table( + 'federation_protocol', + 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', + ) + + op.create_table( + 'implied_role', + sql.Column( + 'prior_role_id', + sql.String(length=64), + sql.ForeignKey( + 'role.id', + name='implied_role_prior_role_id_fkey', + ondelete='CASCADE', + ), + primary_key=True, + ), + sql.Column( + 'implied_role_id', + sql.String(length=64), + sql.ForeignKey( + 'role.id', + name='implied_role_implied_role_id_fkey', + ondelete='CASCADE', + ), + primary_key=True, + ), + mysql_engine='InnoDB', + mysql_charset='utf8', + ) + + op.create_table( + 'limit', + 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', + ) + + op.create_table( + 'local_user', + 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.ForeignKeyConstraint( + ['user_id', 'domain_id'], + ['user.id', 'user.domain_id'], + name='local_user_user_id_fkey', + onupdate='CASCADE', + ondelete='CASCADE', + ), + sql.UniqueConstraint('domain_id', 'name'), + ) + + op.create_table( + 'nonlocal_user', + 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.ForeignKeyConstraint( + ['user_id', 'domain_id'], + ['user.id', 'user.domain_id'], + name='nonlocal_user_user_id_fkey', + onupdate='CASCADE', + ondelete='CASCADE', + ), + sql.UniqueConstraint('user_id', name='ixu_nonlocal_user_user_id'), + mysql_engine='InnoDB', + mysql_charset='utf8', + ) + + op.create_table( + 'password', + sql.Column('id', sql.Integer, primary_key=True, nullable=False), + sql.Column( + 'local_user_id', + sql.Integer, + sql.ForeignKey('local_user.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, + ), + ) + + op.create_table( + 'project_endpoint_group', + 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'), + ) + + op.create_table( + 'federated_user', + 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.ForeignKeyConstraint( + ['protocol_id', 'idp_id'], + ['federation_protocol.id', 'federation_protocol.idp_id'], + name='federated_user_protocol_id_fkey', + ondelete='CASCADE', + ), + sql.UniqueConstraint('idp_id', 'protocol_id', 'unique_id'), + mysql_engine='InnoDB', + mysql_charset='utf8', + ) + + if bind.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 + with op.batch_alter_table('assignment') as batch_op: + batch_op.create_foreign_key( + 'fk_assignment_role_id', + 'role', + ['role_id'], + ['id'], + ) + + # TODO(stephenfin): Remove these procedures in a future contract migration + + if bind.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; + """) + op.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; + """) + op.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; + """) + op.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; + """) + op.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 bind.engine.name == 'mysql': + op.create_index('region_id', 'registered_limit', ['region_id']) + + # FIXME(stephenfin): This should be dropped when we add the FK + # constraint to this column + op.create_index( + 'registered_limit_id', + 'limit', + ['registered_limit_id'], + ) + + # FIXME(stephenfin): These are leftover from when we removed a FK + # constraint and should probable be dropped + op.create_index('domain_id', 'identity_provider', ['domain_id']) + op.create_index('domain_id', 'user', ['domain_id']) + + # data migration + + 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 + + bind = op.get_bind() + meta = sql.MetaData() + project = sql.Table('project', meta, autoload_with=bind.engine) + + root_domain_project = _generate_root_domain_project() + op.execute(project.insert().values(**root_domain_project)) diff --git a/keystone/common/sql/migrations/versions/27e647c0fad4_initial_version_expand.py b/keystone/common/sql/migrations/versions/27e647c0fad4_initial_version_expand.py deleted file mode 100644 index 53a90f9b9..000000000 --- a/keystone/common/sql/migrations/versions/27e647c0fad4_initial_version_expand.py +++ /dev/null @@ -1,28 +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. - -"""Initial version - -Revision ID: 27e647c0fad4 -Revises: -Create Date: 2021-12-23 11:13:26.305412 -""" - -# revision identifiers, used by Alembic. -revision = '27e647c0fad4' -down_revision = None -branch_labels = None -depends_on = None - - -def upgrade(): - pass |