konnosDashboard
pg-meta-roles.ts252 lines · main
1import { z } from 'zod'
2
3import { ident, joinSqlFragments, literal, safeSql, type SafeSqlFragment } from './pg-format'
4import { ROLES_SQL } from './sql/roles'
5
6const pgRoleZod = z.object({
7 id: z.number(),
8 name: z.string(),
9 isSuperuser: z.boolean(),
10 canCreateDb: z.boolean(),
11 canCreateRole: z.boolean(),
12 inheritRole: z.boolean(),
13 canLogin: z.boolean(),
14 isReplicationRole: z.boolean(),
15 canBypassRls: z.boolean(),
16 activeConnections: z.number(),
17 connectionLimit: z.number(),
18 validUntil: z.union([z.string(), z.null()]),
19 config: z.record(z.string(), z.string()),
20})
21const pgRoleArrayZod = z.array(pgRoleZod)
22const pgRoleOptionalZod = z.optional(pgRoleZod)
23
24export type PGRole = z.infer<typeof pgRoleZod>
25
26function list({
27 includeDefaultRoles: includeDefaultRoles = false,
28 limit,
29 offset,
30}: {
31 includeDefaultRoles?: boolean
32 limit?: number
33 offset?: number
34} = {}): {
35 sql: SafeSqlFragment
36 zod: typeof pgRoleArrayZod
37} {
38 let sql = safeSql`
39with
40 roles as (${ROLES_SQL})
41select
42 *
43from
44 roles
45where
46 true
47`
48 if (!includeDefaultRoles) {
49 // All default/predefined roles start with pg_: https://www.postgresql.org/docs/15/predefined-roles.html
50 // The pg_ prefix is also reserved:
51 //
52 // ```
53 // postgres=# create role pg_myrole;
54 // ERROR: role name "pg_myrole" is reserved
55 // DETAIL: Role names starting with "pg_" are reserved.
56 // ```
57 sql = safeSql`${sql} and not pg_catalog.starts_with(name, 'pg_')`
58 }
59 if (limit) {
60 sql = safeSql`${sql} limit ${literal(limit)}`
61 }
62 if (offset) {
63 sql = safeSql`${sql} offset ${literal(offset)}`
64 }
65 return {
66 sql,
67 zod: pgRoleArrayZod,
68 }
69}
70
71type RoleIdentifier = Pick<PGRole, 'id'> | Pick<PGRole, 'name'>
72
73function getIdentifierWhereClause(identifier: RoleIdentifier): SafeSqlFragment {
74 if ('id' in identifier && identifier.id) {
75 return safeSql`${ident('id')} = ${literal(identifier.id)}`
76 } else if ('name' in identifier && identifier.name) {
77 return safeSql`${ident('name')} = ${literal(identifier.name)}`
78 }
79 throw new Error('Must provide either id or name')
80}
81
82function retrieve(identifier: RoleIdentifier): {
83 sql: SafeSqlFragment
84 zod: typeof pgRoleOptionalZod
85} {
86 const sql = safeSql`with roles as (${ROLES_SQL}) select * from roles where ${getIdentifierWhereClause(identifier)};`
87 return {
88 sql,
89 zod: pgRoleOptionalZod,
90 }
91}
92
93type RoleCreateParams = {
94 name: string
95 isSuperuser?: boolean
96 canCreateDb?: boolean
97 canCreateRole?: boolean
98 inheritRole?: boolean
99 canLogin?: boolean
100 isReplicationRole?: boolean
101 canBypassRls?: boolean
102 connectionLimit?: number
103 password?: string
104 validUntil?: string
105 memberOf?: Array<string>
106 members?: Array<string>
107 admins?: Array<string>
108 config?: Record<string, string>
109}
110function create({
111 name,
112 isSuperuser = false,
113 canCreateDb = false,
114 canCreateRole = false,
115 inheritRole = true,
116 canLogin = false,
117 isReplicationRole = false,
118 canBypassRls = false,
119 connectionLimit = -1,
120 password,
121 validUntil,
122 memberOf = [],
123 members = [],
124 admins = [],
125 config = {},
126}: RoleCreateParams): { sql: SafeSqlFragment } {
127 const sql = safeSql`
128create role ${ident(name)}
129 ${isSuperuser ? safeSql`superuser` : safeSql``}
130 ${canCreateDb ? safeSql`createdb` : safeSql``}
131 ${canCreateRole ? safeSql`createrole` : safeSql``}
132 ${inheritRole ? safeSql`` : safeSql`noinherit`}
133 ${canLogin ? safeSql`login` : safeSql``}
134 ${isReplicationRole ? safeSql`replication` : safeSql``}
135 ${canBypassRls ? safeSql`bypassrls` : safeSql``}
136 connection limit ${literal(connectionLimit)}
137 ${password === undefined ? safeSql`` : safeSql`password ${literal(password)}`}
138 ${validUntil === undefined ? safeSql`` : safeSql`valid until ${literal(validUntil)}`}
139 ${memberOf.length === 0 ? safeSql`` : safeSql`in role ${joinSqlFragments(memberOf.map(ident), ',')}`}
140 ${members.length === 0 ? safeSql`` : safeSql`role ${joinSqlFragments(members.map(ident), ',')}`}
141 ${admins.length === 0 ? safeSql`` : safeSql`admin ${joinSqlFragments(admins.map(ident), ',')}`}
142 ;
143${joinSqlFragments(
144 Object.entries(config).map(
145 ([param, value]) => safeSql`alter role ${ident(name)} set ${ident(param)} = ${literal(value)};`
146 ),
147 '\n'
148)}
149`
150 return { sql }
151}
152
153type RoleUpdateParams = {
154 name?: string
155 isSuperuser?: boolean
156 canCreateDb?: boolean
157 canCreateRole?: boolean
158 inheritRole?: boolean
159 canLogin?: boolean
160 isReplicationRole?: boolean
161 canBypassRls?: boolean
162 connectionLimit?: number
163 password?: string
164 validUntil?: string
165}
166function update(identifier: RoleIdentifier, params: RoleUpdateParams): { sql: SafeSqlFragment } {
167 const {
168 name: newName,
169 isSuperuser,
170 canCreateDb,
171 canCreateRole,
172 inheritRole,
173 canLogin,
174 isReplicationRole,
175 canBypassRls,
176 connectionLimit,
177 password,
178 validUntil,
179 } = params
180 const sql = safeSql`
181do $$
182declare
183 old record;
184begin
185 with roles as (${ROLES_SQL})
186 select * into old from roles where ${getIdentifierWhereClause(identifier)};
187 if old is null then
188 raise exception 'Cannot find role with id %', id;
189 end if;
190
191 execute(format('alter role %I
192 ${isSuperuser === undefined ? safeSql`` : isSuperuser ? safeSql`superuser` : safeSql`nosuperuser`}
193 ${canCreateDb === undefined ? safeSql`` : canCreateDb ? safeSql`createdb` : safeSql`nocreatedb`}
194 ${canCreateRole === undefined ? safeSql`` : canCreateRole ? safeSql`createrole` : safeSql`nocreaterole`}
195 ${inheritRole === undefined ? safeSql`` : inheritRole ? safeSql`inherit` : safeSql`noinherit`}
196 ${canLogin === undefined ? safeSql`` : canLogin ? safeSql`login` : safeSql`nologin`}
197 ${isReplicationRole === undefined ? safeSql`` : isReplicationRole ? safeSql`replication` : safeSql`noreplication`}
198 ${canBypassRls === undefined ? safeSql`` : canBypassRls ? safeSql`bypassrls` : safeSql`nobypassrls`}
199 ${connectionLimit === undefined ? safeSql`` : safeSql`connection limit ${literal(connectionLimit)}`}
200 ${password === undefined ? safeSql`` : safeSql`password ${literal(password)}`}
201 ${validUntil === undefined ? safeSql`` : safeSql`valid until %L`}
202 ', old.name${validUntil === undefined ? safeSql`` : safeSql`, ${literal(validUntil)}`}));
203
204 ${
205 newName === undefined
206 ? safeSql``
207 : safeSql`
208 -- Using the same name in the rename clause gives an error, so only do it if the new name is different.
209 if ${literal(newName)} != old.name then
210 execute(format('alter role %I rename to %I;', old.name, ${literal(newName)}));
211 end if;
212 `
213 }
214end
215$$;
216`
217 return { sql }
218}
219
220type RoleRemoveParams = {
221 ifExists?: boolean
222}
223function remove(
224 identifier: RoleIdentifier,
225 { ifExists = false }: RoleRemoveParams = {}
226): { sql: SafeSqlFragment } {
227 const sql = safeSql`
228do $$
229declare
230 old record;
231begin
232 with roles as (${ROLES_SQL})
233 select * into old from roles where ${getIdentifierWhereClause(identifier)};
234 if old is null then
235 raise exception 'Cannot find role with id %', id;
236 end if;
237
238 execute(format('drop role ${ifExists ? safeSql`if exists` : safeSql``} %I;', old.name));
239end
240$$;
241`
242 return { sql }
243}
244
245export default {
246 list,
247 retrieve,
248 create,
249 update,
250 remove,
251 zod: pgRoleZod,
252}