diff options
-rw-r--r-- | keystone/common/sql/expand_repo/versions/073_expand_initial_migration.py | 892 |
1 files changed, 459 insertions, 433 deletions
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 index a68bb4c0e..00efa6ee1 100644 --- a/keystone/common/sql/expand_repo/versions/073_expand_initial_migration.py +++ b/keystone/common/sql/expand_repo/versions/073_expand_initial_migration.py @@ -50,24 +50,91 @@ def upgrade(migrate_engine): % migrate_engine.url.database ) - access_token = sql.Table( - 'access_token', + application_credential = sql.Table( + 'application_credential', 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 + 'internal_id', sql.Integer, primary_key=True, nullable=False ), - sql.Column('project_id', sql.String(64), nullable=False), - sql.Column('role_ids', sql.Text(), 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', + ) + + assignment = sql.Table( + 'assignment', + meta, sql.Column( - 'consumer_id', - sql.String(64), - sql.ForeignKey('consumer.id'), + '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, - index=True, ), - sql.Column('expires_at', sql.String(64), nullable=True), + 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', + ) + + 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', + ) + + 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', ) consumer = sql.Table( @@ -97,91 +164,43 @@ def upgrade(migrate_engine): mysql_charset='utf8', ) - endpoint = sql.Table( - 'endpoint', + group = sql.Table( + 'group', 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('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.Column( - 'enabled', - sql.Boolean, - nullable=False, - default=True, - server_default='1', + migrate.UniqueConstraint( + 'domain_id', + 'name', + name='ixu_group_name_domain_id', ), - 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', + id_mapping = sql.Table( + 'id_mapping', 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('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( - 'idp_id', - sql.String(64), - sql.ForeignKey('identity_provider.id', ondelete='CASCADE'), + 'entity_type', + sql.Enum( + mapping_backend.EntityType.USER, + mapping_backend.EntityType.GROUP, + name='entity_type', + ), 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', + 'local_id', + 'entity_type', + name='domain_id', ), mysql_engine='InnoDB', mysql_charset='utf8', @@ -205,39 +224,13 @@ def upgrade(migrate_engine): sql.Column( 'idp_id', sql.String(64), - sql.ForeignKey('identity_provider.id', ondelete='CASCADE'), + sql.ForeignKey(identity_provider.c.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, @@ -247,44 +240,6 @@ def upgrade(migrate_engine): 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, @@ -317,8 +272,24 @@ def upgrade(migrate_engine): 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( + '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, @@ -335,6 +306,17 @@ def upgrade(migrate_engine): 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_option = sql.Table( 'project_option', meta, @@ -373,35 +355,30 @@ def upgrade(migrate_engine): 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', + region = sql.Table( + 'region', 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'), + 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', ) - config_register = sql.Table( - 'config_register', + registered_limit = sql.Table( + 'registered_limit', meta, - sql.Column('type', sql.String(64), primary_key=True), - sql.Column('domain_id', sql.String(64), nullable=False), + 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', ) @@ -418,7 +395,7 @@ def upgrade(migrate_engine): sql.Column( 'consumer_id', sql.String(64), - sql.ForeignKey('consumer.id'), + sql.ForeignKey(consumer.c.id), nullable=False, index=True, ), @@ -510,6 +487,17 @@ def upgrade(migrate_engine): 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', + ) + service = sql.Table( 'service', meta, @@ -545,6 +533,21 @@ def upgrade(migrate_engine): 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', + ) + token = sql.Table( 'token', meta, @@ -627,6 +630,34 @@ def upgrade(migrate_engine): mysql_charset='utf8', ) + user_group_membership = sql.Table( + 'user_group_membership', + meta, + sql.Column( + 'user_id', + sql.String(length=64), + sql.ForeignKey( + user.c.id, + name='fk_user_group_membership_user_id', + ), + primary_key=True, + ), + sql.Column( + 'group_id', + sql.String(length=64), + sql.ForeignKey( + group.c.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', + ) + user_option = sql.Table( 'user_option', meta, @@ -645,149 +676,193 @@ def upgrade(migrate_engine): mysql_charset='utf8', ) - nonlocal_user = sql.Table( - 'nonlocal_user', + whitelisted_config = sql.Table( + 'whitelisted_config', 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'), + 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', ) - user_group_membership = sql.Table( - 'user_group_membership', + access_token = sql.Table( + 'access_token', 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', + 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.c.id), + nullable=False, + index=True, + ), + sql.Column('expires_at', sql.String(64), nullable=True), ) - region = sql.Table( - 'region', + application_credential_role = sql.Table( + 'application_credential_role', 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()), + 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', ) - assignment = sql.Table( - 'assignment', + application_credential_access_rule = sql.Table( + 'application_credential_access_rule', 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', + 'application_credential_id', + sql.Integer, + sql.ForeignKey( + application_credential.c.internal_id, ondelete='CASCADE' ), + primary_key=True, 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.Column( + 'access_rule_id', + sql.Integer, + sql.ForeignKey(access_rule.c.id, ondelete='CASCADE'), + primary_key=True, + nullable=False, ), - sql.Index('ix_actor_id', 'actor_id'), mysql_engine='InnoDB', mysql_charset='utf8', ) - id_mapping = sql.Table( - 'id_mapping', + endpoint = sql.Table( + 'endpoint', 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('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( - 'entity_type', - sql.Enum( - mapping_backend.EntityType.USER, - mapping_backend.EntityType.GROUP, - name='entity_type', + 'service_id', + sql.String(length=64), + sql.ForeignKey( + service.c.id, + name='endpoint_service_id_fkey', ), nullable=False, ), - migrate.UniqueConstraint( - 'domain_id', - 'local_id', - 'entity_type', - name='domain_id', + 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.c.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', ) - whitelisted_config = sql.Table( - 'whitelisted_config', + endpoint_group = sql.Table( + 'endpoint_group', 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', + 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), ) - sensitive_config = sql.Table( - 'sensitive_config', + expiring_user_group_membership = sql.Table( + 'expiring_user_group_membership', 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), + 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', ) - system_assignment = sql.Table( - 'system_assignment', + federation_protocol = sql.Table( + 'federation_protocol', 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' + sql.Column('id', sql.String(64), primary_key=True), + sql.Column( + 'idp_id', + sql.String(64), + sql.ForeignKey(identity_provider.c.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', ) - registered_limit = sql.Table( - 'registered_limit', + implied_role = sql.Table( + 'implied_role', 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'), + sql.Column( + 'prior_role_id', + sql.String(length=64), + sql.ForeignKey( + role.c.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.c.id, + name='implied_role_implied_role_id_fkey', + ondelete='CASCADE', + ), + primary_key=True, + ), mysql_engine='InnoDB', mysql_charset='utf8', ) @@ -815,172 +890,185 @@ def upgrade(migrate_engine): mysql_charset='utf8', ) - application_credential = sql.Table( - 'application_credential', + local_user = sql.Table( + 'local_user', meta, + sql.Column('id', sql.Integer, primary_key=True, nullable=False), sql.Column( - 'internal_id', sql.Integer, primary_key=True, nullable=False + 'user_id', + sql.String(64), + nullable=False, + unique=True, ), - 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' + 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.c.id, user.c.domain_id], + name='local_user_user_id_fkey', + onupdate='CASCADE', + ondelete='CASCADE', ), - mysql_engine='InnoDB', - mysql_charset='utf8', + sql.UniqueConstraint('domain_id', 'name'), ) - application_credential_role = sql.Table( - 'application_credential_role', + 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( - 'application_credential_id', - sql.Integer, - sql.ForeignKey( - application_credential.c.internal_id, ondelete='CASCADE' - ), - primary_key=True, + 'user_id', + sql.String(64), nullable=False, ), - sql.Column( - 'role_id', sql.String(length=64), primary_key=True, nullable=False + sql.ForeignKeyConstraint( + ['user_id', 'domain_id'], + [user.c.id, user.c.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', ) - access_rule = sql.Table( - 'access_rule', + password = sql.Table( + 'password', 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.Column( + 'local_user_id', + sql.Integer, + sql.ForeignKey(local_user.c.id, ondelete='CASCADE'), + nullable=False, ), - sql.UniqueConstraint( - 'user_id', - 'service', - 'path', - 'method', - name='duplicate_access_rule_for_user_constraint', + sql.Column('expires_at', sql.DateTime(), nullable=True), + sql.Column( + 'self_service', + sql.Boolean, + nullable=False, + server_default='0', + default=False, ), - 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, + # 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( - 'application_credential_id', - sql.Integer, - sql.ForeignKey( - application_credential.c.internal_id, ondelete='CASCADE' - ), - primary_key=True, + '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( - 'access_rule_id', - sql.Integer, - sql.ForeignKey(access_rule.c.id, ondelete='CASCADE'), - primary_key=True, + 'created_at', + sql.DateTime(), nullable=False, + default=datetime.datetime.utcnow, ), - mysql_engine='InnoDB', - mysql_charset='utf8', ) - expiring_user_group_membership = sql.Table( - 'expiring_user_group_membership', + project_endpoint_group = sql.Table( + 'project_endpoint_group', meta, sql.Column( - 'user_id', + 'endpoint_group_id', sql.String(64), - sql.ForeignKey(user.c.id), - primary_key=True, + sql.ForeignKey(endpoint_group.c.id), + nullable=False, ), + sql.Column('project_id', sql.String(64), nullable=False), + sql.PrimaryKeyConstraint('endpoint_group_id', 'project_id'), + ) + + federated_user = sql.Table( + 'federated_user', + meta, + sql.Column('id', sql.Integer, primary_key=True, nullable=False), sql.Column( - 'group_id', + 'user_id', sql.String(64), - sql.ForeignKey(group.c.id), - primary_key=True, + sql.ForeignKey(user.c.id, ondelete='CASCADE'), + nullable=False, ), sql.Column( 'idp_id', sql.String(64), sql.ForeignKey(identity_provider.c.id, ondelete='CASCADE'), - primary_key=True, + nullable=False, ), - sql.Column('last_verified', sql.DateTime(), 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.c.id, federation_protocol.c.idp_id], + name='federated_user_protocol_id_fkey', + ondelete='CASCADE', + ), + sql.UniqueConstraint('idp_id', 'protocol_id', 'unique_id'), mysql_engine='InnoDB', mysql_charset='utf8', ) # create all tables tables = [ + access_rule, + application_credential, + assignment, + config_register, + consumer, credential, - endpoint, group, + id_mapping, + identity_provider, + idp_remote_ids, + mapping, policy, + policy_association, project, + project_endpoint, project_option, project_tag, + region, + registered_limit, + request_token, + revocation_event, role, role_option, + sensitive_config, service, + service_provider, + system_assignment, token, trust, trust_role, user, - user_option, user_group_membership, - region, - assignment, - id_mapping, + user_option, 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, + application_credential_access_rule, + application_credential_role, + endpoint, endpoint_group, - project_endpoint_group, + expiring_user_group_membership, + federation_protocol, implied_role, + limit, local_user, + nonlocal_user, password, + project_endpoint_group, + 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: @@ -990,69 +1078,7 @@ def upgrade(migrate_engine): 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', - }, - ] + fkeys = [] if migrate_engine.name == 'sqlite': # NOTE(stevemar): We need to keep this FK constraint due to 073, but |