def save_and_reapply_permissions(conn, tables: List[TableInfo], schema: str): """ Holy hell this is annoying. See this issue for details: https://github.com/JustFixNYC/nycdb-k8s-loader/issues/5 """ # Create blank placeholder tables if they don't already exist, # so that any users with default privileges in our schema # have expected permissions on them. create_blank_tables = ";".join( [f"CREATE TABLE IF NOT EXISTS {schema}.{table.name} ()" for table in tables] ) with conn.cursor() as cur: cur.execute(create_blank_tables) conn.commit() # Now remember the permissions on the tables. grants = "".join( db_perms.get_grant_sql(conn, table.name, schema) for table in tables ) # Let the code inside our "with" clause run. It will likely # drop the tables and replace them with new ones that have # the same name. yield # Now grant the same permissions to the new tables. db_perms.exec_grant_sql(conn, grants)
def test_get_grant_sql_works(conn): with conn.cursor() as cur: cur.execute('DROP USER IF EXISTS boop') cur.execute("CREATE TABLE mytable (code char(5) PRIMARY KEY)") assert db_perms.get_grant_sql(conn, 'mytable') == '' db_perms.create_readonly_user(conn, 'boop', 'pass') grant_sql = 'GRANT SELECT ON TABLE public.mytable TO boop;' assert db_perms.get_grant_sql(conn, 'mytable') == grant_sql with load_dataset.create_and_enter_temporary_schema(conn, 'blarf'): with conn.cursor() as cur: cur.execute("CREATE TABLE mytable (code char(5) PRIMARY KEY)") assert db_perms.get_grant_sql(conn, 'mytable', 'blarf') == '' with conn.cursor() as cur: cur.execute(f"DROP TABLE public.mytable") cur.execute(f"ALTER TABLE mytable SET SCHEMA public") assert db_perms.get_grant_sql(conn, 'mytable') == '' db_perms.exec_grant_sql(conn, grant_sql) assert db_perms.get_grant_sql(conn, 'mytable') == grant_sql
def test_get_grant_sql_works(conn): with conn.cursor() as cur: cur.execute("DROP USER IF EXISTS boop") cur.execute("CREATE TABLE mytable (code char(5) PRIMARY KEY)") assert db_perms.get_grant_sql(conn, "mytable") == "" db_perms.create_readonly_user(conn, "boop", "pass") grant_sql = "GRANT SELECT ON TABLE public.mytable TO boop;" assert db_perms.get_grant_sql(conn, "mytable") == grant_sql with load_dataset.create_and_enter_temporary_schema(conn, "blarf"): with conn.cursor() as cur: cur.execute("CREATE TABLE mytable (code char(5) PRIMARY KEY)") assert db_perms.get_grant_sql(conn, "mytable", "blarf") == "" with conn.cursor() as cur: cur.execute(f"DROP TABLE public.mytable") cur.execute(f"ALTER TABLE mytable SET SCHEMA public") assert db_perms.get_grant_sql(conn, "mytable") == "" db_perms.exec_grant_sql(conn, grant_sql) assert db_perms.get_grant_sql(conn, "mytable") == grant_sql
def test_get_grant_sql_raises_error_on_invalid_table(conn): with pytest.raises(ValueError, match="Table public.blarg does not exist"): db_perms.get_grant_sql(conn, 'blarg')