konnosDashboard
pg-meta-table-privileges.ts204 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 { TABLE_PRIVILEGES_SQL } from './sql/table-privileges'
14
15const pgTablePrivilegesZod = z.object({
16 relation_id: z.number(),
17 schema: z.string(),
18 name: z.string(),
19 kind: z.union([
20 z.literal('table'),
21 z.literal('view'),
22 z.literal('materialized_view'),
23 z.literal('foreign_table'),
24 z.literal('partitioned_table'),
25 ]),
26 privileges: z.array(
27 z.object({
28 grantor: z.string(),
29 grantee: z.string(),
30 privilege_type: z.union([
31 z.literal('SELECT'),
32 z.literal('INSERT'),
33 z.literal('UPDATE'),
34 z.literal('DELETE'),
35 z.literal('TRUNCATE'),
36 z.literal('REFERENCES'),
37 z.literal('TRIGGER'),
38 z.literal('MAINTAIN'),
39 ]),
40 is_grantable: z.boolean(),
41 })
42 ),
43})
44const pgTablePrivilegesArrayZod = z.array(pgTablePrivilegesZod)
45const pgTablePrivilegesOptionalZod = z.optional(pgTablePrivilegesZod)
46
47function list({
48 includeSystemSchemas = false,
49 includedSchemas,
50 excludedSchemas,
51 limit,
52 offset,
53}: {
54 includeSystemSchemas?: boolean
55 includedSchemas?: string[]
56 excludedSchemas?: string[]
57 limit?: number
58 offset?: number
59} = {}): {
60 sql: SafeSqlFragment
61 zod: typeof pgTablePrivilegesArrayZod
62} {
63 let sql = safeSql`
64with table_privileges as (${TABLE_PRIVILEGES_SQL})
65select *
66from table_privileges
67`
68 const filter = filterByList(
69 includedSchemas,
70 excludedSchemas,
71 !includeSystemSchemas ? DEFAULT_SYSTEM_SCHEMAS : undefined
72 )
73 if (filter) {
74 sql = safeSql`${sql} where schema ${filter}`
75 }
76 if (limit) {
77 sql = safeSql`${sql} limit ${literal(limit)}`
78 }
79 if (offset) {
80 sql = safeSql`${sql} offset ${literal(offset)}`
81 }
82 return {
83 sql,
84 zod: pgTablePrivilegesArrayZod,
85 }
86}
87
88function retrieve({ id }: { id: number }): {
89 sql: SafeSqlFragment
90 zod: typeof pgTablePrivilegesOptionalZod
91}
92function retrieve({ name, schema }: { name: string; schema?: string }): {
93 sql: SafeSqlFragment
94 zod: typeof pgTablePrivilegesOptionalZod
95}
96function retrieve({
97 id,
98 name,
99 schema = 'public',
100}: {
101 id?: number
102 name?: string
103 schema?: string
104}): {
105 sql: SafeSqlFragment
106 zod: typeof pgTablePrivilegesOptionalZod
107} {
108 if (id) {
109 const sql = /* SQL */ safeSql`
110with table_privileges as (${TABLE_PRIVILEGES_SQL})
111select *
112from table_privileges
113where table_privileges.relation_id = ${literal(id)};`
114 return {
115 sql,
116 zod: pgTablePrivilegesOptionalZod,
117 }
118 } else {
119 const sql = /* SQL */ safeSql`
120with table_privileges as (${TABLE_PRIVILEGES_SQL})
121select *
122from table_privileges
123where table_privileges.schema = ${literal(schema)}
124 and table_privileges.name = ${literal(name)}
125`
126 return {
127 sql,
128 zod: pgTablePrivilegesOptionalZod,
129 }
130 }
131}
132
133type TablePrivilegesGrant = {
134 relationId: number
135 grantee: string
136 privilegeType:
137 | 'ALL'
138 | 'SELECT'
139 | 'INSERT'
140 | 'UPDATE'
141 | 'DELETE'
142 | 'TRUNCATE'
143 | 'REFERENCES'
144 | 'TRIGGER'
145 | 'MAINTAIN'
146 isGrantable?: boolean
147}
148function grant(grants: TablePrivilegesGrant[]): { sql: SafeSqlFragment } {
149 const sql = safeSql`
150do $$
151begin
152${joinSqlFragments(
153 grants.map(
154 ({ privilegeType, relationId, grantee, isGrantable }) =>
155 safeSql`execute format('grant ${keyword(privilegeType)} on table %s to ${
156 grantee.toLowerCase() === 'public' ? safeSql`public` : ident(grantee)
157 } ${isGrantable ? safeSql`with grant option` : safeSql``}', ${literal(relationId)}::regclass);`
158 ),
159 '\n'
160)}
161end $$;
162`
163 return { sql }
164}
165
166type TablePrivilegesRevoke = {
167 relationId: number
168 grantee: string
169 privilegeType:
170 | 'ALL'
171 | 'SELECT'
172 | 'INSERT'
173 | 'UPDATE'
174 | 'DELETE'
175 | 'TRUNCATE'
176 | 'REFERENCES'
177 | 'TRIGGER'
178 | 'MAINTAIN'
179}
180function revoke(revokes: TablePrivilegesRevoke[]): { sql: SafeSqlFragment } {
181 const sql = safeSql`
182do $$
183begin
184${joinSqlFragments(
185 revokes.map(
186 ({ privilegeType, relationId, grantee }) =>
187 safeSql`execute format('revoke ${keyword(privilegeType)} on table %s from ${
188 grantee.toLowerCase() === 'public' ? safeSql`public` : ident(grantee)
189 }', ${literal(relationId)}::regclass);`
190 ),
191 '\n'
192)}
193end $$;
194`
195 return { sql }
196}
197
198export default {
199 list,
200 retrieve,
201 grant,
202 revoke,
203 zod: pgTablePrivilegesZod,
204}