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