class Password(sql.ModelBase, sql.ModelDictMixin):
__tablename__ = 'password'
attributes = [
'id', 'local_user_id', 'password', 'password_hash', 'created_at',
'expires_at'
]
id = sql.Column(sql.Integer, primary_key=True)
local_user_id = sql.Column(
sql.Integer, sql.ForeignKey('local_user.id', ondelete='CASCADE'))
# TODO(notmorgan): in the Q release the "password" field can be dropped as
# long as data migration exists to move the hashes over to the
# password_hash column if no value is in the password_hash column.
password = sql.Column(sql.String(128), nullable=True)
password_hash = sql.Column(sql.String(255), nullable=True)
# TODO(lbragstad): Once Rocky opens for development, the _created_at and
# _expires_at attributes/columns can be removed from the schema. The
# migration ensures all passwords are converted from datetime objects to
# big integers. The old datetime columns and their corresponding attributes
# in the model are no longer required.
# created_at default set here to safe guard in case it gets missed
_created_at = sql.Column('created_at',
sql.DateTime,
nullable=False,
default=datetime.datetime.utcnow)
_expires_at = sql.Column('expires_at', sql.DateTime, nullable=True)
# set the default to 0, a 0 indicates it is unset.
created_at_int = sql.Column(sql.DateTimeInt(),
nullable=False,
default=datetime.datetime.utcnow)
expires_at_int = sql.Column(sql.DateTimeInt(), nullable=True)
self_service = sql.Column(sql.Boolean,
default=False,
nullable=False,
server_default='0')
@hybrid_property
def created_at(self):
return self.created_at_int or self._created_at
@created_at.setter
def created_at(self, value):
self._created_at = value
self.created_at_int = value
@hybrid_property
def expires_at(self):
return self.expires_at_int or self._expires_at
@expires_at.setter
def expires_at(self, value):
self._expires_at = value
self.expires_at_int = value
class ApplicationCredentialModel(sql.ModelBase, sql.ModelDictMixin):
__tablename__ = 'application_credential'
attributes = [
'internal_id', 'id', 'name', 'secret_hash', 'description', 'user_id',
'project_id', 'system', 'expires_at', 'unrestricted'
]
internal_id = sql.Column(sql.Integer, primary_key=True, nullable=False)
id = sql.Column(sql.String(64), nullable=False)
name = sql.Column(sql.String(255), nullable=False)
secret_hash = sql.Column(sql.String(255), nullable=False)
description = sql.Column(sql.Text())
user_id = sql.Column(sql.String(64), nullable=False)
project_id = sql.Column(sql.String(64), nullable=True)
system = sql.Column(sql.String(64), nullable=True)
expires_at = sql.Column(sql.DateTimeInt())
unrestricted = sql.Column(sql.Boolean)
__table_args__ = (sql.UniqueConstraint(
'name', 'user_id', name='duplicate_app_cred_constraint'), )
roles = sqlalchemy.orm.relationship(
'ApplicationCredentialRoleModel',
backref=sqlalchemy.orm.backref('application_credential'),
cascade='all, delete-orphan')
access_rules = sqlalchemy.orm.relationship(
'ApplicationCredentialAccessRuleModel',
backref=sqlalchemy.orm.backref('application_credential'),
cascade='all, delete-orphan')
class TrustModel(sql.ModelBase, sql.ModelDictMixinWithExtras):
__tablename__ = 'trust'
attributes = ['id', 'trustor_user_id', 'trustee_user_id',
'project_id', 'impersonation', 'expires_at',
'remaining_uses', 'deleted_at']
id = sql.Column(sql.String(64), primary_key=True)
# user id of owner
trustor_user_id = sql.Column(sql.String(64), nullable=False,)
# user_id of user allowed to consume this preauth
trustee_user_id = sql.Column(sql.String(64), nullable=False)
project_id = sql.Column(sql.String(64))
impersonation = sql.Column(sql.Boolean, nullable=False)
deleted_at = sql.Column(sql.DateTime)
_expires_at = sql.Column('expires_at', sql.DateTime)
expires_at_int = sql.Column(sql.DateTimeInt(), nullable=True)
remaining_uses = sql.Column(sql.Integer, nullable=True)
extra = sql.Column(sql.JsonBlob())
__table_args__ = (sql.UniqueConstraint(
'trustor_user_id', 'trustee_user_id', 'project_id',
'impersonation', 'expires_at',
name='duplicate_trust_constraint'),)
@hybrid_property
def expires_at(self):
return self.expires_at_int or self._expires_at
@expires_at.setter
def expires_at(self, value):
self._expires_at = value
self.expires_at_int = value
def upgrade(migrate_engine):
meta = sql.MetaData()
meta.bind = migrate_engine
# NOTE(morgan): column is nullable here for migration purposes
# it is set to not-nullable in the contract phase to ensure we can handle
# rolling upgrades in a sane way. This differs from the model in
# keystone.identity.backends.sql_model by design.
created_at = sql.Column('created_at_int',
ks_sql.DateTimeInt(),
nullable=True)
expires_at = sql.Column('expires_at_int',
ks_sql.DateTimeInt(),
nullable=True)
password_table = sql.Table('password', meta, autoload=True)
password_table.create_column(created_at)
password_table.create_column(expires_at)
def upgrade(migrate_engine):
meta = sql.MetaData()
meta.bind = migrate_engine
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=False),
sql.Column('expires_at', ks_sql.DateTimeInt()),
sql.Column('allow_application_credential_creation', 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')
application_credential.create(migrate_engine, checkfirst=True)
application_credential_role.create(migrate_engine, checkfirst=True)
def upgrade(migrate_engine):
meta = sql.MetaData()
meta.bind = migrate_engine
# NOTE(morgan): column is nullable here for migration purposes
# it is set to not-nullable in the contract phase to ensure we can handle
# rolling upgrades in a sane way. This differs from the model in
# keystone.identity.backends.sql_model by design.
expires_at = sql.Column('expires_at_int', ks_sql.DateTimeInt())
trust_table = sql.Table('trust', meta, autoload=True)
trust_table.create_column(expires_at)
UniqueConstraint('trustor_user_id',
'trustee_user_id',
'project_id',
'impersonation',
'expires_at',
'expires_at_int',
table=trust_table,
name='duplicate_trust_constraint_expanded').create()
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(255), 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))
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)
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',
)
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',
)
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(
'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',
)
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',
)
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',
)
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.c.id, ondelete='CASCADE'),
),
sql.Column('remote_id', sql.String(255), primary_key=True),
mysql_engine='InnoDB',
mysql_charset='utf8',
)
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',
)
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),
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,
),
migrate.UniqueConstraint(
'domain_id',
'name',
name='ixu_project_name_domain_id',
),
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_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',
)
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',
)
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',
)
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.c.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='<<null>>',
),
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',
)
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,
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',
)
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,
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_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,
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',
)
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',
)
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.c.id),
nullable=False,
index=True,
),
sql.Column('expires_at', sql.String(64), nullable=True),
)
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',
)
application_credential_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',
)
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),
sql.ForeignKey(
service.c.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.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',
)
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),
)
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',
)
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.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',
)
implied_role = sql.Table(
'implied_role',
meta,
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',
)
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',
)
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.ForeignKeyConstraint(
['user_id', 'domain_id'],
[user.c.id, user.c.domain_id],
name='local_user_user_id_fkey',
onupdate='CASCADE',
ondelete='CASCADE',
),
sql.UniqueConstraint('domain_id', 'name'),
)
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.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',
)
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,
),
)
project_endpoint_group = sql.Table(
'project_endpoint_group',
meta,
sql.Column(
'endpoint_group_id',
sql.String(64),
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(
'user_id',
sql.String(64),
sql.ForeignKey(user.c.id, ondelete='CASCADE'),
nullable=False,
),
sql.Column(
'idp_id',
sql.String(64),
sql.ForeignKey(identity_provider.c.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.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,
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_group_membership,
user_option,
whitelisted_config,
access_token,
application_credential_access_rule,
application_credential_role,
endpoint,
endpoint_group,
expiring_user_group_membership,
federation_protocol,
implied_role,
limit,
local_user,
nonlocal_user,
password,
project_endpoint_group,
federated_user,
]
for table in tables:
try:
table.create()
except Exception:
LOG.exception('Exception while creating table: %r', table)
raise
fkeys = []
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()
