konnospolicies.ts37 lines · main
| 1 | import { safeSql } from '../pg-format' |
| 2 | |
| 3 | export const POLICIES_SQL = /* SQL */ safeSql` |
| 4 | select |
| 5 | pol.oid :: int8 as id, |
| 6 | n.nspname as schema, |
| 7 | c.relname as table, |
| 8 | c.oid :: int8 as table_id, |
| 9 | pol.polname as name, |
| 10 | case |
| 11 | when pol.polpermissive then 'PERMISSIVE'::text |
| 12 | else 'RESTRICTIVE'::text |
| 13 | end as action, |
| 14 | case |
| 15 | when pol.polroles = '{0}'::oid[] then array_to_json(string_to_array('public'::text, ''::text)::name[]) |
| 16 | else array_to_json(array( |
| 17 | select pg_roles.rolname |
| 18 | from pg_roles |
| 19 | where pg_roles.oid = any(pol.polroles) |
| 20 | order by pg_roles.rolname |
| 21 | )) |
| 22 | end as roles, |
| 23 | case pol.polcmd |
| 24 | when 'r'::"char" then 'SELECT'::text |
| 25 | when 'a'::"char" then 'INSERT'::text |
| 26 | when 'w'::"char" then 'UPDATE'::text |
| 27 | when 'd'::"char" then 'DELETE'::text |
| 28 | when '*'::"char" then 'ALL'::text |
| 29 | else null::text |
| 30 | end as command, |
| 31 | pg_get_expr(pol.polqual, pol.polrelid) as definition, |
| 32 | pg_get_expr(pol.polwithcheck, pol.polrelid) as check |
| 33 | from |
| 34 | pg_policy pol |
| 35 | join pg_class c on c.oid = pol.polrelid |
| 36 | left join pg_namespace n on n.oid = c.relnamespace |
| 37 | ` |