konnosDashboard
Policies.utils.ts477 lines · main
1import {
2 acceptUntrustedSql,
3 ident,
4 safeSql,
5 untrustedSql,
6 type DisplayableSqlFragment,
7 type SafeSqlFragment,
8} from '@supabase/pg-meta'
9import type { PGPolicy } from '@supabase/pg-meta'
10import { has, isEmpty, isEqual } from 'lodash'
11
12import {
13 DraftPostgresPolicyCreatePayload,
14 DraftPostgresPolicyUpdatePayload,
15 PolicyFormField,
16 PolicyForReview,
17} from './Policies.types'
18import { generateSqlPolicy } from '@/data/ai/sql-policy-mutation'
19import type { CreatePolicyBody } from '@/data/database-policies/database-policy-create-mutation'
20import type { ForeignKeyConstraint } from '@/data/database/foreign-key-constraints-query'
21
22/**
23 * Returns an array of SQL statements that will preview in the review step of the policy editor
24 * @param {*} policyFormFields { name, using, check, command }
25 */
26
27export const createSQLPolicy = (
28 policyFormFields: PolicyFormField,
29 originalPolicyFormFields?: PGPolicy
30) => {
31 const { definition, check } = policyFormFields
32 const formattedPolicyFormFields = {
33 ...policyFormFields,
34 definition: definition
35 ? definition.replace(/\s+/g, ' ').trim()
36 : definition === undefined
37 ? null
38 : definition,
39 check: check ? check.replace(/\s+/g, ' ').trim() : check === undefined ? null : check,
40 }
41
42 if (!originalPolicyFormFields || isEmpty(originalPolicyFormFields)) {
43 return createSQLStatementForCreatePolicy(formattedPolicyFormFields)
44 }
45
46 // If there are no changes, return an empty object
47 if (isEqual(policyFormFields, originalPolicyFormFields)) {
48 return {}
49 }
50
51 // Extract out all the fields that updated
52 const fieldsToUpdate: any = {}
53 if (!isEqual(formattedPolicyFormFields.name, originalPolicyFormFields.name)) {
54 fieldsToUpdate.name = formattedPolicyFormFields.name
55 }
56 if (!isEqual(formattedPolicyFormFields.definition, originalPolicyFormFields.definition)) {
57 fieldsToUpdate.definition = formattedPolicyFormFields.definition
58 }
59 if (!isEqual(formattedPolicyFormFields.check, originalPolicyFormFields.check)) {
60 fieldsToUpdate.check = formattedPolicyFormFields.check
61 }
62 if (!isEqual(formattedPolicyFormFields.roles, originalPolicyFormFields.roles)) {
63 fieldsToUpdate.roles = formattedPolicyFormFields.roles
64 }
65
66 if (!isEmpty(fieldsToUpdate)) {
67 return createSQLStatementForUpdatePolicy(formattedPolicyFormFields, fieldsToUpdate)
68 }
69
70 return {}
71}
72
73const createSQLStatementForCreatePolicy = (policyFormFields: PolicyFormField): PolicyForReview => {
74 const { name, definition, check, command, schema, table } = policyFormFields
75 const roles = policyFormFields.roles.length === 0 ? ['public'] : policyFormFields.roles
76 const description = `Add policy for the ${command} operation under the policy "${name}"`
77 const statement = [
78 `CREATE POLICY "${name}" ON "${schema}"."${table}"`,
79 `AS PERMISSIVE FOR ${command}`,
80 `TO ${roles.join(', ')}`,
81 `${definition ? `USING (${definition})` : ''}`,
82 `${check ? `WITH CHECK (${check})` : ''}`,
83 ].join('\n')
84
85 return { description, statement }
86}
87
88const createSQLStatementForUpdatePolicy = (
89 policyFormFields: PolicyFormField,
90 fieldsToUpdate: Partial<PolicyFormField>
91): PolicyForReview => {
92 const { name, schema, table } = policyFormFields
93
94 const definitionChanged = has(fieldsToUpdate, ['definition'])
95 const checkChanged = has(fieldsToUpdate, ['check'])
96 const nameChanged = has(fieldsToUpdate, ['name'])
97 const rolesChanged = has(fieldsToUpdate, ['roles'])
98
99 const parameters = Object.keys(fieldsToUpdate)
100 const description = `Update policy's ${
101 parameters.length === 1
102 ? parameters[0]
103 : `${parameters.slice(0, parameters.length - 1).join(', ')} and ${
104 parameters[parameters.length - 1]
105 }`
106 } `
107 const roles =
108 (fieldsToUpdate?.roles ?? []).length === 0 ? ['public'] : (fieldsToUpdate.roles as string[])
109
110 const alterStatement = `ALTER POLICY "${name}" ON "${schema}"."${table}"`
111 const statement = [
112 'BEGIN;',
113 ...(definitionChanged ? [` ${alterStatement} USING (${fieldsToUpdate.definition});`] : []),
114 ...(checkChanged ? [` ${alterStatement} WITH CHECK (${fieldsToUpdate.check});`] : []),
115 ...(rolesChanged ? [` ${alterStatement} TO ${roles.join(', ')};`] : []),
116 ...(nameChanged ? [` ${alterStatement} RENAME TO "${fieldsToUpdate.name}";`] : []),
117 'COMMIT;',
118 ].join('\n')
119
120 return { description, statement }
121}
122
123// These constructors return DRAFT payloads — `definition`/`check` are still
124// `DisplayableSqlFragment`. Promotion to `SafeSqlFragment` must happen at the user gesture
125// (the Save click in `PolicyEditorModal`), not here, since this module has no guarantee that
126// it was reached via a deliberate user action.
127export const createPayloadForCreatePolicy = (
128 policyFormFields: PolicyFormField
129): DraftPostgresPolicyCreatePayload => {
130 const { name, schema, table, command, definition, check, roles } = policyFormFields
131 return {
132 name,
133 schema,
134 table,
135 action: 'PERMISSIVE',
136 command: command || undefined,
137 definition: !definition ? undefined : untrustedSql(definition),
138 check: !check ? undefined : untrustedSql(check),
139 roles: roles.length > 0 ? roles : undefined,
140 }
141}
142
143export const createPayloadForUpdatePolicy = (
144 policyFormFields: PolicyFormField,
145 originalPolicyFormFields: PGPolicy
146): DraftPostgresPolicyUpdatePayload => {
147 const { definition, check } = policyFormFields
148 const formattedDefinition = definition ? definition.replace(/\s+/g, ' ').trim() : definition
149 const formattedCheck = check ? check.replace(/\s+/g, ' ').trim() : check
150
151 const payload: DraftPostgresPolicyUpdatePayload = { id: originalPolicyFormFields.id }
152
153 if (!isEqual(policyFormFields.name, originalPolicyFormFields.name)) {
154 payload.name = policyFormFields.name
155 }
156 if (!isEqual(formattedDefinition, originalPolicyFormFields.definition)) {
157 payload.definition = !formattedDefinition ? undefined : untrustedSql(formattedDefinition)
158 }
159 if (!isEqual(formattedCheck, originalPolicyFormFields.check)) {
160 payload.check = !formattedCheck ? undefined : untrustedSql(formattedCheck)
161 }
162 if (!isEqual(policyFormFields.roles, originalPolicyFormFields.roles)) {
163 if (policyFormFields.roles.length === 0) payload.roles = ['public']
164 else payload.roles = policyFormFields.roles || undefined
165 }
166
167 return payload
168}
169
170// --- Policy Generation ---
171
172/**
173 * A policy generated for display/staging in the table editor.
174 * `definition`/`check` are `DisplayableSqlFragment` because generators have different provenance:
175 * programmatic generation produces `SafeSqlFragment` (composed via `safeSql`), AI generation
176 * produces `UntrustedSqlFragment` (third-party output). Consumers must promote via
177 * `acceptUntrustedSql` at a user gesture before executing.
178 */
179export type GeneratedPolicy = Required<
180 Pick<CreatePolicyBody, 'name' | 'table' | 'schema' | 'action' | 'roles'>
181> &
182 Pick<CreatePolicyBody, 'command'> & {
183 definition?: DisplayableSqlFragment
184 check?: DisplayableSqlFragment
185 sql: string
186 }
187
188/**
189 * A {@link GeneratedPolicy} whose `definition`/`check` have already been promoted to
190 * `SafeSqlFragment`. Producing one of these is the contract that says: the user gesture
191 * required to execute this SQL has already happened.
192 */
193export type AcceptedGeneratedPolicy = Omit<GeneratedPolicy, 'definition' | 'check'> & {
194 definition?: SafeSqlFragment
195 check?: SafeSqlFragment
196}
197
198/**
199 * Promotes a {@link GeneratedPolicy} to an {@link AcceptedGeneratedPolicy}.
200 * ONLY call from an event handler tied to a deliberate user action (e.g. the Save click
201 * on the table editor). Never call from useEffect, render, or any path that runs without
202 * a user gesture.
203 */
204export const acceptGeneratedPolicy = (policy: GeneratedPolicy): AcceptedGeneratedPolicy => ({
205 ...policy,
206 definition: policy.definition === undefined ? undefined : acceptUntrustedSql(policy.definition),
207 check: policy.check === undefined ? undefined : acceptUntrustedSql(policy.check),
208})
209
210type Relationship = {
211 source_schema: string
212 source_table_name: string
213 source_column_name: string
214 target_table_schema: string
215 target_table_name: string
216 target_column_name: string
217}
218
219/**
220 * Gets relationships for a specific table from FK constraints.
221 * Returns relationships where the table is the source.
222 */
223const getRelationshipsForTable = ({
224 schema,
225 table,
226 fkConstraints,
227}: {
228 schema: string
229 table: string
230 fkConstraints: ForeignKeyConstraint[]
231}): Relationship[] => {
232 return fkConstraints
233 .filter((fk) => fk.source_schema === schema && fk.source_table === table)
234 .flatMap((fk) =>
235 fk.source_columns.map((sourceCol, i) => ({
236 source_schema: fk.source_schema,
237 source_table_name: fk.source_table,
238 source_column_name: sourceCol,
239 target_table_schema: fk.target_schema,
240 target_table_name: fk.target_table,
241 target_column_name: fk.target_columns[i],
242 }))
243 )
244}
245
246/**
247 * BFS to find shortest path from table to auth.users via foreign key relationships.
248 * Returns null if no path exists within maxDepth.
249 */
250const findPathToAuthUsers = (
251 startTable: { schema: string; name: string },
252 allForeignKeyConstraints: ForeignKeyConstraint[],
253 maxDepth = 3
254): Relationship[] | null => {
255 const startRelationships = getRelationshipsForTable({
256 schema: startTable.schema,
257 table: startTable.name,
258 fkConstraints: allForeignKeyConstraints,
259 })
260
261 const queue: { table: { schema: string; name: string }; path: Relationship[] }[] = [
262 { table: startTable, path: [] },
263 ]
264 const visited = new Set<string>()
265 visited.add(`${startTable.schema}.${startTable.name}`)
266
267 while (queue.length > 0) {
268 const queueItem = queue.shift()
269 if (!queueItem) continue
270
271 const { table, path } = queueItem
272 if (path.length >= maxDepth) continue
273
274 const relationships =
275 path.length === 0
276 ? startRelationships
277 : getRelationshipsForTable({
278 schema: table.schema,
279 table: table.name,
280 fkConstraints: allForeignKeyConstraints,
281 })
282
283 for (const rel of relationships) {
284 // Found path to auth.users
285 if (
286 rel.target_table_schema === 'auth' &&
287 rel.target_table_name === 'users' &&
288 rel.target_column_name === 'id'
289 ) {
290 return [...path, rel]
291 }
292
293 const targetId = `${rel.target_table_schema}.${rel.target_table_name}`
294 if (visited.has(targetId)) continue
295
296 // Add target table to queue for further exploration
297 queue.push({
298 table: { schema: rel.target_table_schema, name: rel.target_table_name },
299 path: [...path, rel],
300 })
301 visited.add(targetId)
302 }
303 }
304
305 return null
306}
307
308/** Generates SQL expression for RLS policy based on FK path to auth.users */
309const buildPolicyExpression = (path: Relationship[]): SafeSqlFragment => {
310 if (path.length === 0) return safeSql``
311
312 // Direct FK to auth.users
313 if (path.length === 1) {
314 return safeSql`(select auth.uid()) = ${ident(path[0].source_column_name)}`
315 }
316
317 // Indirect path - build EXISTS with JOINs
318 const [first, ...rest] = path
319 const firstTarget = safeSql`${ident(first.target_table_schema)}.${ident(first.target_table_name)}`
320 const source = safeSql`${ident(first.source_schema)}.${ident(first.source_table_name)}`
321 const last = path[path.length - 1]
322
323 const joins = rest.slice(0, -1).reduce<SafeSqlFragment>(
324 (acc, r) => {
325 const targetSchema = ident(r.target_table_schema)
326 const targetTable = ident(r.target_table_name)
327 const targetColumn = ident(r.target_column_name)
328
329 const sourceSchema = ident(r.source_schema)
330 const sourceTable = ident(r.source_table_name)
331 const sourceColumn = ident(r.source_column_name)
332 const join = safeSql`join ${targetSchema}.${targetTable} on ${targetSchema}.${targetTable}.${targetColumn} = ${sourceSchema}.${sourceTable}.${sourceColumn}`
333 return acc.length === 0 ? join : safeSql`${acc}\n ${join}`
334 },
335 safeSql``
336 )
337
338 return safeSql`exists (
339 select 1 from ${firstTarget}
340 ${joins}
341 where ${firstTarget}.${ident(first.target_column_name)} = ${source}.${ident(first.source_column_name)}
342 and ${ident(last.source_schema)}.${ident(last.source_table_name)}.${ident(last.source_column_name)} = (select auth.uid())
343)`
344}
345
346/** Builds policy SQL for all CRUD operations */
347const buildPoliciesForPath = (
348 table: { name: string; schema: string },
349 path: Relationship[]
350): GeneratedPolicy[] => {
351 const expression = buildPolicyExpression(path)
352 const targetCol = path[0].source_column_name
353
354 return (['SELECT', 'INSERT', 'UPDATE', 'DELETE'] as const).map((command) => {
355 const name = `Enable ${command.toLowerCase()} access for users based on ${ident(targetCol)}`
356 const base = `CREATE POLICY "${name}" ON ${ident(table.schema)}.${ident(table.name)} AS PERMISSIVE FOR ${command} TO authenticated`
357
358 const sql =
359 command === 'INSERT'
360 ? `${base} WITH CHECK (${expression});`
361 : command === 'UPDATE'
362 ? `${base} USING (${expression}) WITH CHECK (${expression});`
363 : `${base} USING (${expression});`
364
365 // Structured data for mutation API
366 const definition = command === 'INSERT' ? undefined : expression
367 const check = command === 'SELECT' || command === 'DELETE' ? undefined : expression
368
369 return {
370 name,
371 sql,
372 command,
373 table: table.name,
374 schema: table.schema,
375 definition,
376 check,
377 action: 'PERMISSIVE' as const,
378 roles: ['authenticated'],
379 }
380 })
381}
382
383/**
384 * Generates RLS policies programmatically based on FK relationships to auth.users.
385 */
386export const generateProgrammaticPoliciesForTable = ({
387 table,
388 foreignKeyConstraints,
389}: {
390 table: { name: string; schema: string }
391 foreignKeyConstraints: ForeignKeyConstraint[]
392}): GeneratedPolicy[] => {
393 try {
394 const path = findPathToAuthUsers(table, foreignKeyConstraints)
395
396 if (path?.length) {
397 return buildPoliciesForPath(table, path)
398 }
399 } catch (error) {
400 // Silently fail - caller will handle empty result
401 }
402
403 return []
404}
405
406/**
407 * Generates RLS policies using AI.
408 */
409export const generateAiPoliciesForTable = async ({
410 table,
411 columns,
412 projectRef,
413 connectionString,
414}: {
415 table: { name: string; schema: string }
416 columns: { name: string }[]
417 projectRef: string
418 connectionString?: string | null
419}): Promise<GeneratedPolicy[]> => {
420 if (!connectionString) return []
421
422 try {
423 return await generateSqlPolicy({
424 tableName: table.name,
425 schema: table.schema,
426 columns: columns.map((col) => col.name.trim()),
427 projectRef,
428 connectionString: connectionString ?? '',
429 })
430 } catch (error) {
431 console.log('AI policy generation failed:', error)
432 return []
433 }
434}
435
436/**
437 * Generates RLS policies for a table.
438 * First tries programmatic generation based on FK relationships to auth.users.
439 * Falls back to AI generation if no path exists.
440 */
441export const generateStartingPoliciesForTable = async ({
442 table,
443 foreignKeyConstraints,
444 columns,
445 projectRef,
446 connectionString,
447 enableAi,
448}: {
449 table: { name: string; schema: string }
450 foreignKeyConstraints: ForeignKeyConstraint[]
451 columns: { name: string }[]
452 projectRef: string
453 connectionString?: string | null
454 enableAi: boolean
455}): Promise<GeneratedPolicy[]> => {
456 // Try programmatic generation first
457 const programmaticPolicies = generateProgrammaticPoliciesForTable({
458 table,
459 foreignKeyConstraints,
460 })
461
462 if (programmaticPolicies.length > 0) {
463 return programmaticPolicies
464 }
465
466 // Fall back to AI generation
467 if (enableAi) {
468 return await generateAiPoliciesForTable({
469 table,
470 columns,
471 projectRef,
472 connectionString,
473 })
474 }
475
476 return []
477}