konnosDashboard
sandbox.utils.ts210 lines · main
1import { ident, literal, type PGPolicy } from '@supabase/pg-meta'
2
3import { DatabaseSchemaDDL } from '@/data/rls-tester/get-schema-ddl'
4import { TableSeedData } from '@/data/rls-tester/get-seed-data'
5import { getErrorMessage } from '@/lib/get-error-message'
6
7interface Executor {
8 execSql(sql: string): Promise<void>
9}
10
11function buildPolicySQL(policy: PGPolicy): string {
12 const name = ident(policy.name)
13 const target = `${ident(policy.schema)}.${ident(policy.table)}`
14 const permissiveness = policy.action === 'RESTRICTIVE' ? 'AS RESTRICTIVE' : ''
15 const command = policy.command === 'ALL' ? '' : `FOR ${policy.command}`
16 const roles = policy.roles?.length ? `TO ${policy.roles.map(ident).join(', ')}` : ''
17 const using = policy.definition ? `USING (${policy.definition})` : ''
18 const withCheck = policy.check ? `WITH CHECK (${policy.check})` : ''
19
20 const drop = `DROP POLICY IF EXISTS ${name} ON ${target}`
21 const create = [
22 `CREATE POLICY ${name}`,
23 `ON ${target}`,
24 permissiveness,
25 command,
26 roles,
27 using,
28 withCheck,
29 ]
30 .filter(Boolean)
31 .join(' ')
32 return `${drop}; ${create}`
33}
34
35async function tryExec(sandbox: Executor, sql: string, label: string): Promise<void> {
36 try {
37 await sandbox.execSql(sql)
38 } catch (err) {
39 console.warn(`[rls-sandbox] skipped ${label}:`, getErrorMessage(err) ?? err)
40 }
41}
42
43// Retry items until no further progress can be made — handles ordering
44// dependencies (e.g. table A references type B that hasn't been created yet).
45// Each pass attempts every pending item; survivors carry forward. When a full
46// pass makes zero progress, surviving items are reported as unresolved.
47async function runUntilFixpoint<T>(
48 items: T[],
49 attempt: (item: T) => Promise<void>,
50 onUnresolved: (item: T, error: unknown) => void
51): Promise<void> {
52 let pending = items.slice()
53 while (pending.length > 0) {
54 const failed: Array<{ item: T; error: unknown }> = []
55 for (const item of pending) {
56 try {
57 await attempt(item)
58 } catch (error) {
59 failed.push({ item, error })
60 }
61 }
62 if (failed.length === pending.length) {
63 for (const { item, error } of failed) onUnresolved(item, error)
64 break
65 }
66 pending = failed.map((f) => f.item)
67 }
68}
69
70async function applyDDLWithRetries(sandbox: Executor, ddlStatements: string[]): Promise<void> {
71 await runUntilFixpoint(
72 ddlStatements,
73 (ddl) => sandbox.execSql(ddl),
74 (ddl, error) =>
75 console.warn(
76 `[rls-sandbox] skipped DDL: ${ddl.slice(0, 80).replace(/\s+/g, ' ')} — ${getErrorMessage(error) ?? String(error)}`
77 )
78 )
79}
80
81export async function applySchema(
82 sandbox: Executor,
83 {
84 schemas,
85 typeDefinitions,
86 entityDefinitions,
87 functionDefinitions,
88 policies,
89 rlsStatuses,
90 customRoles,
91 }: DatabaseSchemaDDL
92): Promise<void> {
93 // Reset each user schema so re-syncs pick up renames/drops/column changes and
94 // CREATE statements don't collide with the previous run's objects.
95 for (const schema of schemas) {
96 const schemaId = ident(schema)
97 await tryExec(sandbox, `DROP SCHEMA IF EXISTS ${schemaId} CASCADE`, `drop schema ${schema}`)
98 await tryExec(sandbox, `CREATE SCHEMA ${schemaId}`, `create schema ${schema}`)
99 await tryExec(
100 sandbox,
101 `GRANT USAGE ON SCHEMA ${schemaId} TO anon, authenticated, service_role`,
102 `grant schema ${schema}`
103 )
104 }
105
106 if (customRoles.length > 0) {
107 const checks = customRoles
108 .map(
109 ({ name }) =>
110 `IF NOT EXISTS (SELECT FROM pg_roles WHERE rolname = ${literal(name)}) THEN CREATE ROLE ${ident(name)} NOLOGIN; END IF;`
111 )
112 .join('\n')
113 await tryExec(sandbox, `DO $$ BEGIN\n${checks}\nEND $$`, 'custom roles')
114 }
115
116 await applyDDLWithRetries(sandbox, typeDefinitions)
117 await applyDDLWithRetries(sandbox, entityDefinitions)
118
119 for (const schema of [...new Set(rlsStatuses.map((t) => t.schema))]) {
120 await tryExec(
121 sandbox,
122 `GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA ${ident(schema)} TO anon, authenticated, service_role`,
123 `grant tables in schema ${schema}`
124 )
125 }
126
127 for (const { schema, table, rls_enabled, rls_forced } of rlsStatuses) {
128 const actions: string[] = []
129 if (rls_enabled) actions.push('ENABLE ROW LEVEL SECURITY')
130 if (rls_forced) actions.push('FORCE ROW LEVEL SECURITY')
131 if (actions.length === 0) continue
132 await tryExec(
133 sandbox,
134 `ALTER TABLE ${ident(schema)}.${ident(table)} ${actions.join(', ')}`,
135 `RLS on ${schema}.${table}`
136 )
137 }
138
139 // Disable check_function_bodies so functions referencing not-yet-created objects don't abort.
140 // Postgres resolves policy→function references at query time, not at CREATE POLICY time.
141 await tryExec(sandbox, `SET check_function_bodies = off`, 'set check_function_bodies')
142 for (const fn of functionDefinitions) {
143 await tryExec(sandbox, fn, `function ${fn.slice(0, 60).replace(/\s+/g, ' ')}`)
144 }
145 await tryExec(sandbox, `RESET check_function_bodies`, 'reset check_function_bodies')
146
147 for (const policy of policies) {
148 await tryExec(
149 sandbox,
150 buildPolicySQL(policy),
151 `policy ${policy.schema}.${policy.table} "${policy.name}"`
152 )
153 }
154}
155
156function serializeValue(val: unknown): string {
157 if (val === null || val === undefined) return 'NULL'
158 if (typeof val === 'boolean') return val ? 'TRUE' : 'FALSE'
159 if (typeof val === 'number') return String(val)
160 if (val instanceof Date) return `'${val.toISOString()}'`
161 if (Array.isArray(val)) return `ARRAY[${val.map(serializeValue).join(', ')}]`
162 if (typeof val === 'object') return `'${JSON.stringify(val).replace(/'/g, "''")}'::jsonb`
163 return `'${String(val).replace(/'/g, "''")}'`
164}
165
166function buildInsertSQL(schema: string, table: string, rows: Record<string, unknown>[]): string {
167 if (rows.length === 0) throw new Error(`buildInsertSQL requires at least one row`)
168 const columns = Object.keys(rows[0])
169 const colList = columns.map((c) => ident(c)).join(', ')
170 const valuesList = rows
171 .map((row) => `(${columns.map((c) => serializeValue(row[c])).join(', ')})`)
172 .join(',\n ')
173 return `INSERT INTO ${ident(schema)}.${ident(table)} (${colList}) VALUES\n ${valuesList};`
174}
175
176export async function applySeed(sandbox: Executor, tables: TableSeedData[]): Promise<void> {
177 // Disable FK triggers so we can delete and re-insert in any order.
178 // Requires superuser (ALTER ROLE postgres SUPERUSER in SANDBOX_SETUP_STATEMENTS).
179 // Falls back gracefully if the privilege is not available.
180 let triggersDisabled = false
181 try {
182 await sandbox.execSql(`SET session_replication_role = replica`)
183 triggersDisabled = true
184 } catch {
185 // postgres not yet a superuser in this PGlite build — proceed without it
186 }
187
188 try {
189 // Always clear before inserting so re-seed reflects the latest data.
190 for (const { schema, table } of tables) {
191 try {
192 await sandbox.execSql(`DELETE FROM ${ident(schema)}.${ident(table)}`)
193 } catch {
194 // table may not exist yet — ignore
195 }
196 }
197
198 // Retry loop handles any remaining FK ordering constraints.
199 await runUntilFixpoint(
200 tables.filter((t) => t.rows.length > 0),
201 (entry) => sandbox.execSql(buildInsertSQL(entry.schema, entry.table, entry.rows)),
202 (entry) =>
203 console.warn(`[rls-sandbox] seed skipped ${entry.schema}.${entry.table}: unresolved FK`)
204 )
205 } finally {
206 if (triggersDisabled) {
207 await sandbox.execSql(`SET session_replication_role = DEFAULT`)
208 }
209 }
210}