konnoscheck-tables-anon-authenticated-access.ts25 lines · main
| 1 | import { literal, safeSql, type SafeSqlFragment } from '../../../pg-format' |
| 2 | |
| 3 | /** |
| 4 | * Given a schema name, list all the tables in that schema which have access |
| 5 | * granted to either the "anon" or "authenticated" role |
| 6 | */ |
| 7 | export const getTablesWithAnonAuthenticatedAccessSQL = ({ |
| 8 | schema, |
| 9 | }: { |
| 10 | schema: string |
| 11 | }): SafeSqlFragment => |
| 12 | safeSql` |
| 13 | SELECT c.relname AS table_name |
| 14 | FROM pg_catalog.pg_class AS c |
| 15 | JOIN pg_catalog.pg_namespace AS n ON n.oid = c.relnamespace |
| 16 | WHERE n.nspname = ${literal(schema)} |
| 17 | AND c.relkind IN ('r','p') -- table, partitioned table |
| 18 | AND EXISTS ( |
| 19 | SELECT 1 |
| 20 | FROM pg_catalog.aclexplode(COALESCE(c.relacl, '{}'::aclitem[])) AS a |
| 21 | JOIN pg_catalog.pg_roles r ON r.oid = a.grantee |
| 22 | WHERE r.rolname IN ('anon','authenticated') |
| 23 | ) |
| 24 | ; |
| 25 | ` |