konnostable-privileges.ts80 lines · main
| 1 | import { safeSql } from '../pg-format' |
| 2 | |
| 3 | export const TABLE_PRIVILEGES_SQL = /* SQL */ safeSql` |
| 4 | -- Despite the name \`table_privileges\`, this includes other kinds of relations: |
| 5 | -- views, matviews, etc. "Relation privileges" just doesn't roll off the tongue. |
| 6 | -- |
| 7 | -- For each relation, get its relacl in a jsonb format, |
| 8 | -- e.g. |
| 9 | -- |
| 10 | -- '{postgres=arwdDxt/postgres}' |
| 11 | -- |
| 12 | -- becomes |
| 13 | -- |
| 14 | -- [ |
| 15 | -- { |
| 16 | -- "grantee": "postgres", |
| 17 | -- "grantor": "postgres", |
| 18 | -- "is_grantable": false, |
| 19 | -- "privilege_type": "INSERT" |
| 20 | -- }, |
| 21 | -- ... |
| 22 | -- ] |
| 23 | select |
| 24 | c.oid as relation_id, |
| 25 | nc.nspname as schema, |
| 26 | c.relname as name, |
| 27 | case |
| 28 | when c.relkind = 'r' then 'table' |
| 29 | when c.relkind = 'v' then 'view' |
| 30 | when c.relkind = 'm' then 'materialized_view' |
| 31 | when c.relkind = 'f' then 'foreign_table' |
| 32 | when c.relkind = 'p' then 'partitioned_table' |
| 33 | end as kind, |
| 34 | coalesce( |
| 35 | jsonb_agg( |
| 36 | jsonb_build_object( |
| 37 | 'grantor', grantor.rolname, |
| 38 | 'grantee', grantee.rolname, |
| 39 | 'privilege_type', _priv.privilege_type, |
| 40 | 'is_grantable', _priv.is_grantable |
| 41 | ) |
| 42 | ) filter (where _priv is not null), |
| 43 | '[]' |
| 44 | ) as privileges |
| 45 | from pg_class c |
| 46 | join pg_namespace as nc |
| 47 | on nc.oid = c.relnamespace |
| 48 | left join lateral ( |
| 49 | select grantor, grantee, privilege_type, is_grantable |
| 50 | from aclexplode(coalesce(c.relacl, acldefault('r', c.relowner))) |
| 51 | ) as _priv on true |
| 52 | left join pg_roles as grantor |
| 53 | on grantor.oid = _priv.grantor |
| 54 | left join ( |
| 55 | select |
| 56 | pg_roles.oid, |
| 57 | pg_roles.rolname |
| 58 | from pg_roles |
| 59 | union all |
| 60 | select |
| 61 | (0)::oid as oid, 'PUBLIC' |
| 62 | ) as grantee (oid, rolname) |
| 63 | on grantee.oid = _priv.grantee |
| 64 | where c.relkind in ('r', 'v', 'm', 'f', 'p') |
| 65 | and not pg_is_other_temp_schema(c.relnamespace) |
| 66 | and ( |
| 67 | pg_has_role(c.relowner, 'USAGE') |
| 68 | or has_table_privilege( |
| 69 | c.oid, |
| 70 | 'SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER' |
| 71 | || case when current_setting('server_version_num')::int4 >= 170000 then ', MAINTAIN' else '' end |
| 72 | ) |
| 73 | or has_any_column_privilege(c.oid, 'SELECT, INSERT, UPDATE, REFERENCES') |
| 74 | ) |
| 75 | group by |
| 76 | c.oid, |
| 77 | nc.nspname, |
| 78 | c.relname, |
| 79 | c.relkind |
| 80 | ` |