konnosDashboard
pg-meta-column-privileges.ts170 lines · main
1import { z } from 'zod'
2
3import { DEFAULT_SYSTEM_SCHEMAS } from './constants'
4import { filterByList } from './helpers'
5import {
6 ident,
7 joinSqlFragments,
8 keyword,
9 literal,
10 safeSql,
11 type SafeSqlFragment,
12} from './pg-format'
13import { COLUMN_PRIVILEGES_SQL } from './sql/column-privileges'
14
15const pgColumnPrivilegeGrant = z.object({
16 grantor: z.string(),
17 grantee: z.string(),
18 privilege_type: z.union([
19 z.literal('SELECT'),
20 z.literal('INSERT'),
21 z.literal('UPDATE'),
22 z.literal('REFERENCES'),
23 ]),
24 is_grantable: z.boolean(),
25})
26const pgColumnPrivilegesZod = z.object({
27 column_id: z.string(),
28 relation_schema: z.string(),
29 relation_name: z.string(),
30 column_name: z.string(),
31 privileges: z.array(pgColumnPrivilegeGrant),
32})
33const pgColumnPrivilegesArrayZod = z.array(pgColumnPrivilegesZod)
34
35const privilegeGrant = z.object({
36 columnId: z.string(),
37 grantee: z.string(),
38 privilegeType: z.union([
39 z.literal('ALL'),
40 z.literal('SELECT'),
41 z.literal('INSERT'),
42 z.literal('UPDATE'),
43 z.literal('REFERENCES'),
44 ]),
45 isGrantable: z.boolean().optional(),
46})
47
48function list({
49 includeSystemSchemas = false,
50 includedSchemas,
51 excludedSchemas,
52 columnIds,
53 limit,
54 offset,
55}: {
56 includeSystemSchemas?: boolean
57 includedSchemas?: string[]
58 excludedSchemas?: string[]
59 columnIds?: string[]
60 limit?: number
61 offset?: number
62} = {}): {
63 sql: SafeSqlFragment
64 zod: typeof pgColumnPrivilegesArrayZod
65} {
66 let sql = safeSql`
67 with column_privileges as (${COLUMN_PRIVILEGES_SQL})
68 select *
69 from column_privileges
70 `
71
72 const conditions: SafeSqlFragment[] = []
73
74 const filter = filterByList(
75 includedSchemas,
76 excludedSchemas,
77 !includeSystemSchemas ? DEFAULT_SYSTEM_SCHEMAS : undefined
78 )
79 if (filter) {
80 conditions.push(safeSql`relation_schema ${filter}`)
81 }
82
83 if (columnIds?.length) {
84 conditions.push(safeSql`column_id in (${joinSqlFragments(columnIds.map(literal), ',')})`)
85 }
86
87 if (conditions.length > 0) {
88 sql = safeSql`${sql} where ${joinSqlFragments(conditions, ' and ')}`
89 }
90
91 if (limit) {
92 sql = safeSql`${sql} limit ${literal(limit)}`
93 }
94 if (offset) {
95 sql = safeSql`${sql} offset ${literal(offset)}`
96 }
97 return {
98 sql,
99 zod: pgColumnPrivilegesArrayZod,
100 }
101}
102
103type ColumnPrivilegesGrant = z.infer<typeof privilegeGrant>
104function grant(grants: ColumnPrivilegesGrant[]): { sql: SafeSqlFragment } {
105 const sql = safeSql`
106do $$
107declare
108 col record;
109begin
110${joinSqlFragments(
111 grants.map(({ privilegeType, columnId, grantee, isGrantable }) => {
112 const [relationId, columnNumber] = columnId.split('.')
113 return safeSql`
114select *
115from pg_attribute a
116where a.attrelid = ${literal(relationId)}
117 and a.attnum = ${literal(columnNumber)}
118into col;
119execute format(
120 'grant ${keyword(privilegeType)} (%I) on %s to ${
121 grantee.toLowerCase() === 'public' ? safeSql`public` : ident(grantee)
122 } ${isGrantable ? safeSql`with grant option` : safeSql``}',
123 col.attname,
124 col.attrelid::regclass
125);`
126 }),
127 '\n'
128)}
129end $$;
130`
131 return { sql }
132}
133
134type ColumnPrivilegesRevoke = Omit<ColumnPrivilegesGrant, 'isGrantable'>
135function revoke(revokes: ColumnPrivilegesRevoke[]): { sql: SafeSqlFragment } {
136 const sql = safeSql`
137do $$
138declare
139 col record;
140begin
141${joinSqlFragments(
142 revokes.map(({ privilegeType, columnId, grantee }) => {
143 const [relationId, columnNumber] = columnId.split('.')
144 return safeSql`
145select *
146from pg_attribute a
147where a.attrelid = ${literal(relationId)}
148 and a.attnum = ${literal(columnNumber)}
149into col;
150execute format(
151 'revoke ${keyword(privilegeType)} (%I) on %s from ${
152 grantee.toLowerCase() === 'public' ? safeSql`public` : ident(grantee)
153 }',
154 col.attname,
155 col.attrelid::regclass
156);`
157 }),
158 '\n'
159)}
160end $$;
161`
162 return { sql }
163}
164
165export default {
166 list,
167 grant,
168 revoke,
169 zod: pgColumnPrivilegesZod,
170}