def from_database(cls, connection, schema):
"""Get a list of all policies defined in the db"""
sql = sql_text(
f"""
select
schemaname,
tablename,
policyname,
permissive,
roles,
cmd,
qual,
with_check
from
pg_policies
where
schemaname = '{schema}'
"""
)
rows = connection.execute(sql).fetchall()
def get_definition(permissive, roles, cmd, qual, with_check):
definition = ""
if permissive is not None:
definition += f"as {permissive} "
if cmd is not None:
definition += f"for {cmd} "
if roles is not None:
definition += f"to {', '.join(roles)} "
if qual is not None:
if qual[0] != "(":
qual = f"({qual})"
definition += f"using {qual} "
if with_check is not None:
if with_check[0] != "(":
with_check = f"({with_check})"
definition += f"with check {with_check} "
return definition
db_policies = []
for schema, table, policy_name, permissive, roles, cmd, qual, with_check in rows:
definition = get_definition(permissive, roles, cmd, qual, with_check)
schema = coerce_to_quoted(schema)
table = coerce_to_quoted(table)
policy_name = coerce_to_quoted(policy_name)
policy = PGPolicy.from_sql(
f"create policy {policy_name} on {schema}.{table} {definition}"
)
db_policies.append(policy)
for policy in db_policies:
assert policy is not None
return db_policies
def literal_schema(self) -> str:
"""Wrap a schema name in literal quotes
Useful for emitting SQL statements
"""
return coerce_to_quoted(self.schema)
def test_coerce_to_quoted() -> None:
assert coerce_to_quoted('"public"') == '"public"'
assert coerce_to_quoted("public") == '"public"'
assert coerce_to_quoted("public.table") == '"public"."table"'
assert coerce_to_quoted('"public".table') == '"public"."table"'
assert coerce_to_quoted('public."table"') == '"public"."table"'