konnosDashboard
column-privileges.ts149 lines · main
1import { safeSql } from '../pg-format'
2
3export const COLUMN_PRIVILEGES_SQL = /* SQL */ safeSql`
4-- Lists each column's privileges in the form of:
5--
6-- [
7-- {
8-- "column_id": "12345.1",
9-- "relation_schema": "public",
10-- "relation_name": "mytable",
11-- "column_name": "mycolumn",
12-- "privileges": [
13-- {
14-- "grantor": "postgres",
15-- "grantee": "myrole",
16-- "privilege_type": "SELECT",
17-- "is_grantable": false
18-- },
19-- ...
20-- ]
21-- },
22-- ...
23-- ]
24--
25-- Modified from information_schema.column_privileges. We try to be as close as
26-- possible to the view definition, obtained from:
27--
28-- select pg_get_viewdef('information_schema.column_privileges');
29--
30-- The main differences are:
31-- - we include column privileges for materialized views
32-- (reason for exclusion in information_schema.column_privileges:
33-- https://www.postgresql.org/message-id/9136.1502740844%40sss.pgh.pa.us)
34-- - we query a.attrelid and a.attnum to generate column_id
35-- - table_catalog is omitted
36-- - table_schema -> relation_schema, table_name -> relation_name
37--
38-- Column privileges are intertwined with table privileges in that table
39-- privileges override column privileges. E.g. if we do:
40--
41-- grant all on mytable to myrole;
42--
43-- Then myrole is granted privileges for ALL columns. Likewise, if we do:
44--
45-- grant all (id) on mytable to myrole;
46-- revoke all on mytable from myrole;
47--
48-- Then the grant on the id column is revoked.
49--
50-- This is unlike how grants for schemas and tables interact, where you need
51-- privileges for BOTH the schema the table is in AND the table itself in order
52-- to access the table.
53
54select (x.attrelid || '.' || x.attnum) as column_id,
55 nc.nspname as relation_schema,
56 x.relname as relation_name,
57 x.attname as column_name,
58 coalesce(
59 jsonb_agg(
60 jsonb_build_object(
61 'grantor', u_grantor.rolname,
62 'grantee', grantee.rolname,
63 'privilege_type', x.prtype,
64 'is_grantable', x.grantable
65 )
66 ),
67 '[]'
68 ) as privileges
69from
70 (select pr_c.grantor,
71 pr_c.grantee,
72 a.attrelid,
73 a.attnum,
74 a.attname,
75 pr_c.relname,
76 pr_c.relnamespace,
77 pr_c.prtype,
78 pr_c.grantable,
79 pr_c.relowner
80 from
81 (select pg_class.oid,
82 pg_class.relname,
83 pg_class.relnamespace,
84 pg_class.relowner,
85 (aclexplode(coalesce(pg_class.relacl, acldefault('r', pg_class.relowner)))).grantor as grantor,
86 (aclexplode(coalesce(pg_class.relacl, acldefault('r', pg_class.relowner)))).grantee as grantee,
87 (aclexplode(coalesce(pg_class.relacl, acldefault('r', pg_class.relowner)))).privilege_type as privilege_type,
88 (aclexplode(coalesce(pg_class.relacl, acldefault('r', pg_class.relowner)))).is_grantable as is_grantable
89 from pg_class
90 where (pg_class.relkind = any (array['r',
91 'v',
92 'm',
93 'f',
94 'p'])) ) pr_c(oid, relname, relnamespace, relowner, grantor, grantee, prtype, grantable),
95 pg_attribute a
96 where ((a.attrelid = pr_c.oid)
97 and (a.attnum > 0)
98 and (not a.attisdropped))
99 union select pr_a.grantor,
100 pr_a.grantee,
101 pr_a.attrelid,
102 pr_a.attnum,
103 pr_a.attname,
104 c.relname,
105 c.relnamespace,
106 pr_a.prtype,
107 pr_a.grantable,
108 c.relowner
109 from
110 (select a.attrelid,
111 a.attnum,
112 a.attname,
113 (aclexplode(coalesce(a.attacl, acldefault('c', cc.relowner)))).grantor as grantor,
114 (aclexplode(coalesce(a.attacl, acldefault('c', cc.relowner)))).grantee as grantee,
115 (aclexplode(coalesce(a.attacl, acldefault('c', cc.relowner)))).privilege_type as privilege_type,
116 (aclexplode(coalesce(a.attacl, acldefault('c', cc.relowner)))).is_grantable as is_grantable
117 from (pg_attribute a
118 join pg_class cc on ((a.attrelid = cc.oid)))
119 where ((a.attnum > 0)
120 and (not a.attisdropped))) pr_a(attrelid, attnum, attname, grantor, grantee, prtype, grantable),
121 pg_class c
122 where ((pr_a.attrelid = c.oid)
123 and (c.relkind = any (ARRAY['r',
124 'v',
125 'm',
126 'f',
127 'p'])))) x,
128 pg_namespace nc,
129 pg_authid u_grantor,
130 (select pg_authid.oid,
131 pg_authid.rolname
132 from pg_authid
133 union all select (0)::oid as oid,
134 'PUBLIC') grantee(oid, rolname)
135where ((x.relnamespace = nc.oid)
136 and (x.grantee = grantee.oid)
137 and (x.grantor = u_grantor.oid)
138 and (x.prtype = any (ARRAY['INSERT',
139 'SELECT',
140 'UPDATE',
141 'REFERENCES']))
142 and (pg_has_role(u_grantor.oid, 'USAGE')
143 or pg_has_role(grantee.oid, 'USAGE')
144 or (grantee.rolname = 'PUBLIC')))
145group by column_id,
146 nc.nspname,
147 x.relname,
148 x.attname
149`