konnosDashboard
column-privileges.test.ts357 lines · main
1import { afterAll, beforeAll, expect, test } from 'vitest'
2
3import pgMeta from '../src/index'
4import { cleanupRoot, createTestDatabase } from './db/utils'
5
6beforeAll(async () => {
7 // Any global setup if needed
8})
9
10afterAll(async () => {
11 await cleanupRoot()
12})
13
14const withTestDatabase = (
15 name: string,
16 fn: (db: Awaited<ReturnType<typeof createTestDatabase>>) => Promise<void>
17) => {
18 test(name, async () => {
19 const db = await createTestDatabase()
20 try {
21 await fn(db)
22 } finally {
23 await db.cleanup()
24 }
25 })
26}
27
28withTestDatabase('list column privileges', async ({ executeQuery }) => {
29 const { sql, zod } = await pgMeta.columnPrivileges.list()
30 const res = zod.parse(await executeQuery(sql))
31 const column = res.find(
32 ({ relation_schema, relation_name, column_name }) =>
33 relation_schema === 'public' && relation_name === 'todos' && column_name === 'id'
34 )!
35
36 // We don't guarantee order of privileges, but we want to keep the snapshots consistent.
37 column.privileges.sort((a, b) => JSON.stringify(a).localeCompare(JSON.stringify(b)))
38 expect(column).toMatchInlineSnapshot(
39 { column_id: expect.stringMatching(/^\d+\.\d+$/) },
40 `
41 {
42 "column_id": StringMatching /\\^\\\\d\\+\\\\\\.\\\\d\\+\\$/,
43 "column_name": "id",
44 "privileges": [
45 {
46 "grantee": "postgres",
47 "grantor": "postgres",
48 "is_grantable": false,
49 "privilege_type": "INSERT",
50 },
51 {
52 "grantee": "postgres",
53 "grantor": "postgres",
54 "is_grantable": false,
55 "privilege_type": "REFERENCES",
56 },
57 {
58 "grantee": "postgres",
59 "grantor": "postgres",
60 "is_grantable": false,
61 "privilege_type": "SELECT",
62 },
63 {
64 "grantee": "postgres",
65 "grantor": "postgres",
66 "is_grantable": false,
67 "privilege_type": "UPDATE",
68 },
69 ],
70 "relation_name": "todos",
71 "relation_schema": "public",
72 }
73 `
74 )
75})
76
77withTestDatabase('revoke & grant column privileges', async ({ executeQuery }) => {
78 const testRole = `test_role_${Date.now()}`
79 // Create test role
80 await executeQuery(`create role ${testRole};`)
81
82 // Get initial column privileges
83 const { sql: listSql, zod: listZod } = await pgMeta.columnPrivileges.list()
84 const listRes = listZod.parse(await executeQuery(listSql))
85 const { column_id } = listRes.find(
86 ({ relation_schema, relation_name, column_name }) =>
87 relation_schema === 'public' && relation_name === 'todos' && column_name === 'id'
88 )!
89 const { sql: listSqlTodos } = await pgMeta.columnPrivileges.list({ columnIds: [column_id] })
90
91 // Grant all privileges
92 const { sql: grantSql } = pgMeta.columnPrivileges.grant([
93 {
94 columnId: column_id,
95 grantee: testRole,
96 privilegeType: 'ALL',
97 },
98 ])
99 await executeQuery(grantSql)
100
101 let privs = listZod.parse(await executeQuery(listSqlTodos))
102 expect(privs.length).toBe(1)
103 expect(privs[0]).toMatchInlineSnapshot(
104 { column_id: expect.stringMatching(/^\d+\.\d+$/) },
105 `
106 {
107 "column_id": StringMatching /\\^\\\\d\\+\\\\\\.\\\\d\\+\\$/,
108 "column_name": "id",
109 "privileges": [
110 {
111 "grantee": "${testRole}",
112 "grantor": "postgres",
113 "is_grantable": false,
114 "privilege_type": "UPDATE",
115 },
116 {
117 "grantee": "${testRole}",
118 "grantor": "postgres",
119 "is_grantable": false,
120 "privilege_type": "SELECT",
121 },
122 {
123 "grantee": "${testRole}",
124 "grantor": "postgres",
125 "is_grantable": false,
126 "privilege_type": "REFERENCES",
127 },
128 {
129 "grantee": "${testRole}",
130 "grantor": "postgres",
131 "is_grantable": false,
132 "privilege_type": "INSERT",
133 },
134 {
135 "grantee": "postgres",
136 "grantor": "postgres",
137 "is_grantable": false,
138 "privilege_type": "UPDATE",
139 },
140 {
141 "grantee": "postgres",
142 "grantor": "postgres",
143 "is_grantable": false,
144 "privilege_type": "SELECT",
145 },
146 {
147 "grantee": "postgres",
148 "grantor": "postgres",
149 "is_grantable": false,
150 "privilege_type": "REFERENCES",
151 },
152 {
153 "grantee": "postgres",
154 "grantor": "postgres",
155 "is_grantable": false,
156 "privilege_type": "INSERT",
157 },
158 ],
159 "relation_name": "todos",
160 "relation_schema": "public",
161 }
162 `
163 )
164
165 // Revoke all privileges
166 const { sql: revokeSql } = pgMeta.columnPrivileges.revoke([
167 {
168 columnId: column_id,
169 grantee: testRole,
170 privilegeType: 'ALL',
171 },
172 ])
173 await executeQuery(revokeSql)
174
175 // Verify privileges were revoked
176 privs = listZod.parse(await executeQuery(listSqlTodos))
177 expect(privs.length).toBe(1)
178 expect(privs[0]).toMatchInlineSnapshot(
179 { column_id: expect.stringMatching(/^\d+\.\d+$/) },
180 `
181 {
182 "column_id": StringMatching /\\^\\\\d\\+\\\\\\.\\\\d\\+\\$/,
183 "column_name": "id",
184 "privileges": [
185 {
186 "grantee": "postgres",
187 "grantor": "postgres",
188 "is_grantable": false,
189 "privilege_type": "UPDATE",
190 },
191 {
192 "grantee": "postgres",
193 "grantor": "postgres",
194 "is_grantable": false,
195 "privilege_type": "SELECT",
196 },
197 {
198 "grantee": "postgres",
199 "grantor": "postgres",
200 "is_grantable": false,
201 "privilege_type": "REFERENCES",
202 },
203 {
204 "grantee": "postgres",
205 "grantor": "postgres",
206 "is_grantable": false,
207 "privilege_type": "INSERT",
208 },
209 ],
210 "relation_name": "todos",
211 "relation_schema": "public",
212 }
213 `
214 )
215})
216
217withTestDatabase(
218 'revoke & grant column privileges w/ quoted column name',
219 async ({ executeQuery }) => {
220 const testRole = `test_role_${Date.now()}`
221 // Create test role and table with quoted names
222 await executeQuery(`create role ${testRole}; create table "t 1"("c 1" int8);`)
223
224 // Get column privileges
225 const { sql: listSql, zod: listZod } = await pgMeta.columnPrivileges.list()
226 const listRes = listZod.parse(await executeQuery(listSql))
227 const { column_id } = listRes.find(
228 ({ relation_name, column_name }) => relation_name === 't 1' && column_name === 'c 1'
229 )!
230 const { sql: listSqlT1 } = await pgMeta.columnPrivileges.list({ columnIds: [column_id] })
231
232 // Grant all privileges
233 const { sql: grantSql } = pgMeta.columnPrivileges.grant([
234 {
235 columnId: column_id,
236 grantee: testRole,
237 privilegeType: 'ALL',
238 },
239 ])
240 await executeQuery(grantSql)
241
242 // Verify privileges were granted
243 let privs = listZod.parse(await executeQuery(listSqlT1))
244 expect(privs.length).toBe(1)
245 expect(privs[0]).toMatchInlineSnapshot(
246 { column_id: expect.stringMatching(/^\d+\.\d+$/) },
247 `
248 {
249 "column_id": StringMatching /\\^\\\\d\\+\\\\\\.\\\\d\\+\\$/,
250 "column_name": "c 1",
251 "privileges": [
252 {
253 "grantee": "${testRole}",
254 "grantor": "postgres",
255 "is_grantable": false,
256 "privilege_type": "UPDATE",
257 },
258 {
259 "grantee": "${testRole}",
260 "grantor": "postgres",
261 "is_grantable": false,
262 "privilege_type": "SELECT",
263 },
264 {
265 "grantee": "${testRole}",
266 "grantor": "postgres",
267 "is_grantable": false,
268 "privilege_type": "REFERENCES",
269 },
270 {
271 "grantee": "${testRole}",
272 "grantor": "postgres",
273 "is_grantable": false,
274 "privilege_type": "INSERT",
275 },
276 {
277 "grantee": "postgres",
278 "grantor": "postgres",
279 "is_grantable": false,
280 "privilege_type": "UPDATE",
281 },
282 {
283 "grantee": "postgres",
284 "grantor": "postgres",
285 "is_grantable": false,
286 "privilege_type": "SELECT",
287 },
288 {
289 "grantee": "postgres",
290 "grantor": "postgres",
291 "is_grantable": false,
292 "privilege_type": "REFERENCES",
293 },
294 {
295 "grantee": "postgres",
296 "grantor": "postgres",
297 "is_grantable": false,
298 "privilege_type": "INSERT",
299 },
300 ],
301 "relation_name": "t 1",
302 "relation_schema": "public",
303 }
304 `
305 )
306 // Revoke all privileges
307 const { sql: revokeSql } = pgMeta.columnPrivileges.revoke([
308 {
309 columnId: column_id,
310 grantee: testRole,
311 privilegeType: 'ALL',
312 },
313 ])
314 await executeQuery(revokeSql)
315
316 // Verify privileges were revoked
317 privs = listZod.parse(await executeQuery(listSqlT1))
318 expect(privs.length).toBe(1)
319 expect(privs[0]).toMatchInlineSnapshot(
320 { column_id: expect.stringMatching(/^\d+\.\d+$/) },
321 `
322 {
323 "column_id": StringMatching /\\^\\\\d\\+\\\\\\.\\\\d\\+\\$/,
324 "column_name": "c 1",
325 "privileges": [
326 {
327 "grantee": "postgres",
328 "grantor": "postgres",
329 "is_grantable": false,
330 "privilege_type": "UPDATE",
331 },
332 {
333 "grantee": "postgres",
334 "grantor": "postgres",
335 "is_grantable": false,
336 "privilege_type": "SELECT",
337 },
338 {
339 "grantee": "postgres",
340 "grantor": "postgres",
341 "is_grantable": false,
342 "privilege_type": "REFERENCES",
343 },
344 {
345 "grantee": "postgres",
346 "grantor": "postgres",
347 "is_grantable": false,
348 "privilege_type": "INSERT",
349 },
350 ],
351 "relation_name": "t 1",
352 "relation_schema": "public",
353 }
354 `
355 )
356 }
357)