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')