konnosDashboard
pg-meta-policies.ts178 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 { POLICIES_SQL } from './sql/policies'
14
15const pgPolicyZod = z.object({
16 id: z.number(),
17 schema: z.string(),
18 table: z.string(),
19 table_id: z.number(),
20 name: z.string(),
21 action: z.union([z.literal('PERMISSIVE'), z.literal('RESTRICTIVE')]),
22 roles: z.array(z.string()),
23 command: z.union([
24 z.literal('SELECT'),
25 z.literal('INSERT'),
26 z.literal('UPDATE'),
27 z.literal('DELETE'),
28 z.literal('ALL'),
29 ]),
30 definition: z.union([z.string(), z.null()]),
31 check: z.union([z.string(), z.null()]),
32})
33const pgPolicyArrayZod = z.array(pgPolicyZod)
34const pgPolicyOptionalZod = z.optional(pgPolicyZod)
35
36export type PGPolicy = z.infer<typeof pgPolicyZod>
37
38type PolicyIdentifier = Pick<PGPolicy, 'id'> | Pick<PGPolicy, 'name' | 'schema' | 'table'>
39
40function getIdentifierWhereClause(identifier: PolicyIdentifier): SafeSqlFragment {
41 if ('id' in identifier && identifier.id) {
42 return safeSql`id = ${literal(identifier.id)}`
43 } else if ('name' in identifier && identifier.name && identifier.schema && identifier.table) {
44 return safeSql`name = ${literal(identifier.name)} AND schema = ${literal(identifier.schema)} AND table = ${literal(identifier.table)}`
45 }
46 throw new Error('Must provide either id or name, schema and table')
47}
48
49function list({
50 includeSystemSchemas = false,
51 includedSchemas,
52 excludedSchemas,
53 limit,
54 offset,
55}: {
56 includeSystemSchemas?: boolean
57 includedSchemas?: string[]
58 excludedSchemas?: string[]
59 limit?: number
60 offset?: number
61} = {}): {
62 sql: SafeSqlFragment
63 zod: typeof pgPolicyArrayZod
64} {
65 let sql = safeSql`
66 with policies as (${POLICIES_SQL})
67 select *
68 from policies
69 `
70 const filter = filterByList(
71 includedSchemas,
72 excludedSchemas,
73 !includeSystemSchemas ? DEFAULT_SYSTEM_SCHEMAS : undefined
74 )
75 if (filter) {
76 sql = safeSql`${sql}where schema ${filter}`
77 }
78 if (limit) {
79 sql = safeSql`${sql} limit ${literal(limit)}`
80 }
81 if (offset) {
82 sql = safeSql`${sql} offset ${literal(offset)}`
83 }
84 return {
85 sql,
86 zod: pgPolicyArrayZod,
87 }
88}
89
90function retrieve(identifier: PolicyIdentifier): {
91 sql: SafeSqlFragment
92 zod: typeof pgPolicyOptionalZod
93} {
94 const sql = safeSql`with policies as (${POLICIES_SQL}) select * from policies where ${getIdentifierWhereClause(identifier)};`
95 return {
96 sql,
97 zod: pgPolicyOptionalZod,
98 }
99}
100
101type PolicyCreateParams = {
102 name: string
103 schema?: string
104 table: string
105 definition?: SafeSqlFragment
106 check?: SafeSqlFragment
107 action?: 'PERMISSIVE' | 'RESTRICTIVE'
108 command?: 'ALL' | 'SELECT' | 'INSERT' | 'UPDATE' | 'DELETE'
109 roles?: string[]
110}
111
112function create({
113 name,
114 schema = 'public',
115 table,
116 definition,
117 check,
118 action = 'PERMISSIVE',
119 command = 'ALL',
120 roles = ['public'],
121}: PolicyCreateParams): { sql: SafeSqlFragment } {
122 const rolesFragment = joinSqlFragments(roles.map(ident), ', ')
123 const definitionSql = definition ? safeSql`using (${definition})` : safeSql``
124 const checkSql = check ? safeSql`with check (${check})` : safeSql``
125 const sql = safeSql`
126create policy ${ident(name)} on ${ident(schema)}.${ident(table)}
127 as ${keyword(action)}
128 for ${keyword(command)}
129 to ${rolesFragment}
130 ${definitionSql}
131 ${checkSql};`
132 return { sql }
133}
134
135type PolicyUpdateParams = {
136 name?: string
137 definition?: SafeSqlFragment
138 check?: SafeSqlFragment
139 roles?: string[]
140}
141
142function update(
143 identifier: Pick<PGPolicy, 'name' | 'schema' | 'table'>,
144 params: PolicyUpdateParams
145): { sql: SafeSqlFragment } {
146 const { name, definition, check, roles } = params
147
148 const alter = safeSql`ALTER POLICY ${ident(identifier.name)} ON ${ident(identifier.schema)}.${ident(identifier.table)}`
149 const nameSql: SafeSqlFragment =
150 name === undefined ? safeSql`` : safeSql`${alter} RENAME TO ${ident(name)};`
151 const definitionSql: SafeSqlFragment =
152 definition === undefined ? safeSql`` : safeSql`${alter} USING (${definition});`
153 const checkSql: SafeSqlFragment =
154 check === undefined ? safeSql`` : safeSql`${alter} WITH CHECK (${check});`
155 const rolesSql: SafeSqlFragment =
156 roles === undefined
157 ? safeSql``
158 : safeSql`${alter} TO ${joinSqlFragments(roles.map(ident), ', ')};`
159
160 // nameSql must be last
161 const sql = safeSql`BEGIN; ${definitionSql} ${checkSql} ${rolesSql} ${nameSql} COMMIT;`
162
163 return { sql }
164}
165
166function remove(identifier: Pick<PGPolicy, 'name' | 'schema' | 'table'>): { sql: SafeSqlFragment } {
167 const sql = safeSql`DROP POLICY ${ident(identifier.name)} ON ${ident(identifier.schema)}.${ident(identifier.table)};`
168 return { sql }
169}
170
171export default {
172 list,
173 retrieve,
174 create,
175 update,
176 remove,
177 zod: pgPolicyZod,
178}