def check_db_exists(db_name, db_prefix=None):
admin_connection_string = get_external_admin_connection_string(
db_prefix=db_prefix)
with postgres_driver.connect(admin_connection_string) as admin_conn:
return len(
list(postgres_driver.list_roles(admin_conn,
role_name=db_name))) > 0
def set_datastore_readonly_permissions(self):
assert self.db_type == 'datastore'
db_name = self.db_spec['name']
ro_user = self.instance.annotations.get_secret('datastoreReadonlyUser')
print(f'setting datastore permissions: {db_name} ({ro_user})')
with postgres_driver.connect(
db_manager.get_external_admin_connection_string(
db_name=db_name)) as conn:
with conn.cursor() as cur:
for line in [
f"GRANT CONNECT ON DATABASE \"{db_name}\" TO \"{ro_user}\";",
f"GRANT USAGE ON SCHEMA public TO \"{ro_user}\";",
f"GRANT SELECT ON ALL TABLES IN SCHEMA public TO \"{ro_user}\";",
f"ALTER DEFAULT PRIVILEGES FOR USER \"{db_name}\" IN SCHEMA public GRANT SELECT ON TABLES TO \"{ro_user}\";",
]:
cur.execute(line)
# site_user = self.instance.spec.db['name']
site_user = db_name
datastore_permissions = DATASTORE_PERMISSIONS_SQL_TEMPLATE.replace(
'{{SITE_USER}}',
site_user).replace('{{DS_RO_USER}}', ro_user)
try:
cur.execute(datastore_permissions)
except Exception:
traceback.print_exc()
print(
'Failed to set datastore sql template, continuing anyway'
)
def _create_base_dbs_and_roles(migration_name, db_name, datastore_name,
recreate_dbs, datastore_ro_name):
logs.info('Creating base DBS')
admin_connection_string = db_manager.get_external_admin_connection_string()
with postgres_driver.connect(admin_connection_string) as admin_conn:
if recreate_dbs:
_delete_dbs(admin_conn, db_name, datastore_name, datastore_ro_name)
if not datastore_name:
skip_keys = ['database-password']
elif not db_name:
skip_keys = ['datastore-password', 'datastore-readonly-password']
else:
skip_keys = None
password_errors, db_password, datastore_password, datastore_ro_password = _get_or_create_migration_db_passwords(
migration_name, skip_keys=skip_keys)
yield {'step': 'get-create-passwords', 'msg': 'Created Passwords'}
admin_user = db_manager.get_admin_db_user()
if db_name:
db_errors = postgres_driver.create_base_db(
admin_conn, db_name, db_password, grant_to_user=admin_user)
else:
db_errors = []
if datastore_name:
datastore_errors = postgres_driver.create_base_db(
admin_conn,
datastore_name,
datastore_password,
grant_to_user=admin_user)
else:
datastore_errors = []
if datastore_name and db_name:
assert (len(datastore_errors) == 0 and len(db_errors) == 0) or len(password_errors) == 3, \
'some passwords were not created, but DB / roles need to be created, we cannot know the right passwords'
yield {
'step': 'create-base-dbs',
'msg': f'Created Base DB and roles: {db_name}, {datastore_name}'
}
if datastore_name:
postgres_driver.create_role_if_not_exists(admin_conn,
datastore_ro_name,
datastore_ro_password)
yield {
'step': 'created-datastore-ro-role',
'msg': f'Created Datastore read-only user: {datastore_ro_name}'
}
yield {
'step':
'created-base-dbs-and-roles',
f'msg':
f'Created base dbs and roles: {db_name}, {datastore_name}, {datastore_ro_name}'
}
yield from _update_db_proxy(db_name, datastore_name, datastore_ro_name,
db_password, datastore_password,
datastore_ro_password)
def delete(name, delete_dbs=False):
migration = crds_manager.get(CRD_SINGULAR, name=name, required=False) or {}
if delete_dbs:
admin_connection_string = db_manager.get_external_admin_connection_string(
)
db_name = migration.get('spec', {}).get('datastore-name')
datastore_name = migration.get('spec', {}).get('db-name')
datastore_ro_name = crds_manager.config_get(
CRD_SINGULAR,
name,
key='datastore-readonly-user-name',
is_secret=True,
required=False)
if db_name or datastore_name or datastore_ro_name:
with postgres_driver.connect(
admin_connection_string) as admin_conn:
_delete_dbs(admin_conn, db_name, datastore_name,
datastore_ro_name)
crds_manager.delete(CRD_SINGULAR, name)
def _initialize_postgis_extensions(db_name):
logs.info('initializing postgis extensions for main db')
connection_string = db_manager.get_external_admin_connection_string(
db_name)
with postgres_driver.connect(connection_string) as conn:
postgres_driver.initialize_extensions(conn, [
'postgis', 'postgis_topology', 'fuzzystrmatch',
'postgis_tiger_geocoder'
])
with conn.cursor() as cur:
try:
cur.execute(
f'ALTER TABLE spatial_ref_sys OWNER TO "{db_name}"')
except Exception:
traceback.print_exc()
yield {
'step': 'initialize-postgis',
'msg': f'Initialized postgis for db: {db_name}'
}
def check_connection_string(connection_string):
with postgres_driver.connect(connection_string) as conn:
with conn.cursor() as cur:
cur.execute('select 1')
