konnosDashboard
Policies.utils.test.ts450 lines · main
1import { safeSql } from '@supabase/pg-meta'
2import { beforeEach, describe, expect, it, vi } from 'vitest'
3
4import {
5 generateAiPoliciesForTable,
6 generateProgrammaticPoliciesForTable,
7 generateStartingPoliciesForTable,
8 type GeneratedPolicy,
9} from './Policies.utils'
10import type { ForeignKeyConstraint } from '@/data/database/foreign-key-constraints-query'
11
12// Mock generateSqlPolicy for AI tests
13const mockGenerateSqlPolicy = vi.fn()
14vi.mock('@/data/ai/sql-policy-mutation', () => ({
15 generateSqlPolicy: (...args: unknown[]) => mockGenerateSqlPolicy(...args),
16}))
17
18// Helper to create a foreign key constraint
19const createForeignKey = (overrides: Partial<ForeignKeyConstraint> = {}): ForeignKeyConstraint => ({
20 id: 1,
21 constraint_name: 'fk_constraint',
22 source_id: 100,
23 source_schema: 'public',
24 source_table: 'posts',
25 source_columns: ['user_id'],
26 target_id: 200,
27 target_schema: 'auth',
28 target_table: 'users',
29 target_columns: ['id'],
30 deletion_action: 'NO ACTION',
31 update_action: 'NO ACTION',
32 ...overrides,
33})
34
35describe('Policies.utils - Policy Generation', () => {
36 beforeEach(() => {
37 vi.clearAllMocks()
38 })
39
40 describe('generateProgrammaticPoliciesForTable', () => {
41 it('should generate 4 CRUD policies for direct FK to auth.users', () => {
42 const foreignKeyConstraints: ForeignKeyConstraint[] = [
43 createForeignKey({
44 source_schema: 'public',
45 source_table: 'posts',
46 source_columns: ['user_id'],
47 target_schema: 'auth',
48 target_table: 'users',
49 target_columns: ['id'],
50 }),
51 ]
52
53 const policies = generateProgrammaticPoliciesForTable({
54 table: { name: 'posts', schema: 'public' },
55 foreignKeyConstraints,
56 })
57
58 expect(policies).toHaveLength(4)
59
60 const commands = policies.map((p) => p.command)
61 expect(commands).toContain('SELECT')
62 expect(commands).toContain('INSERT')
63 expect(commands).toContain('UPDATE')
64 expect(commands).toContain('DELETE')
65 })
66
67 it('should return empty array when no FK path to auth.users exists', () => {
68 const foreignKeyConstraints: ForeignKeyConstraint[] = [
69 createForeignKey({
70 source_schema: 'public',
71 source_table: 'posts',
72 source_columns: ['category_id'],
73 target_schema: 'public',
74 target_table: 'categories',
75 target_columns: ['id'],
76 }),
77 ]
78
79 const policies = generateProgrammaticPoliciesForTable({
80 table: { name: 'posts', schema: 'public' },
81 foreignKeyConstraints,
82 })
83
84 expect(policies).toHaveLength(0)
85 })
86
87 it('should return empty array when foreignKeyConstraints is empty', () => {
88 const policies = generateProgrammaticPoliciesForTable({
89 table: { name: 'posts', schema: 'public' },
90 foreignKeyConstraints: [],
91 })
92
93 expect(policies).toHaveLength(0)
94 })
95
96 it('should generate policies with EXISTS clause for indirect FK path (2 hops)', () => {
97 // posts -> profiles -> auth.users
98 const foreignKeyConstraints: ForeignKeyConstraint[] = [
99 createForeignKey({
100 id: 1,
101 source_schema: 'public',
102 source_table: 'posts',
103 source_columns: ['profile_id'],
104 target_schema: 'public',
105 target_table: 'profiles',
106 target_columns: ['id'],
107 }),
108 createForeignKey({
109 id: 2,
110 source_schema: 'public',
111 source_table: 'profiles',
112 source_columns: ['user_id'],
113 target_schema: 'auth',
114 target_table: 'users',
115 target_columns: ['id'],
116 }),
117 ]
118
119 const policies = generateProgrammaticPoliciesForTable({
120 table: { name: 'posts', schema: 'public' },
121 foreignKeyConstraints,
122 })
123
124 expect(policies).toHaveLength(4)
125
126 // Check that the expression contains EXISTS for indirect path
127 const selectPolicy = policies.find((p) => p.command === 'SELECT')
128 expect(selectPolicy?.definition).toContain('exists')
129 expect(selectPolicy?.sql).toContain('exists')
130 })
131
132 describe('policy structure validation', () => {
133 const foreignKeyConstraints: ForeignKeyConstraint[] = [
134 createForeignKey({
135 source_schema: 'public',
136 source_table: 'posts',
137 source_columns: ['user_id'],
138 target_schema: 'auth',
139 target_table: 'users',
140 target_columns: ['id'],
141 }),
142 ]
143
144 it('should include all required fields in generated policies', () => {
145 const policies = generateProgrammaticPoliciesForTable({
146 table: { name: 'posts', schema: 'public' },
147 foreignKeyConstraints,
148 })
149
150 for (const policy of policies) {
151 expect(policy).toHaveProperty('name')
152 expect(policy).toHaveProperty('sql')
153 expect(policy).toHaveProperty('command')
154 expect(policy).toHaveProperty('table', 'posts')
155 expect(policy).toHaveProperty('schema', 'public')
156 expect(policy).toHaveProperty('action', 'PERMISSIVE')
157 expect(policy).toHaveProperty('roles')
158 expect(policy.roles).toContain('authenticated')
159 }
160 })
161
162 it('SELECT policy should have definition but no check', () => {
163 const policies = generateProgrammaticPoliciesForTable({
164 table: { name: 'posts', schema: 'public' },
165 foreignKeyConstraints,
166 })
167
168 const selectPolicy = policies.find((p) => p.command === 'SELECT')
169 expect(selectPolicy?.definition).toBeDefined()
170 expect(selectPolicy?.check).toBeUndefined()
171 })
172
173 it('DELETE policy should have definition but no check', () => {
174 const policies = generateProgrammaticPoliciesForTable({
175 table: { name: 'posts', schema: 'public' },
176 foreignKeyConstraints,
177 })
178
179 const deletePolicy = policies.find((p) => p.command === 'DELETE')
180 expect(deletePolicy?.definition).toBeDefined()
181 expect(deletePolicy?.check).toBeUndefined()
182 })
183
184 it('INSERT policy should have check but no definition', () => {
185 const policies = generateProgrammaticPoliciesForTable({
186 table: { name: 'posts', schema: 'public' },
187 foreignKeyConstraints,
188 })
189
190 const insertPolicy = policies.find((p) => p.command === 'INSERT')
191 expect(insertPolicy?.definition).toBeUndefined()
192 expect(insertPolicy?.check).toBeDefined()
193 })
194
195 it('UPDATE policy should have both definition and check', () => {
196 const policies = generateProgrammaticPoliciesForTable({
197 table: { name: 'posts', schema: 'public' },
198 foreignKeyConstraints,
199 })
200
201 const updatePolicy = policies.find((p) => p.command === 'UPDATE')
202 expect(updatePolicy?.definition).toBeDefined()
203 expect(updatePolicy?.check).toBeDefined()
204 })
205
206 it('should generate correct SQL syntax for direct FK', () => {
207 const policies = generateProgrammaticPoliciesForTable({
208 table: { name: 'posts', schema: 'public' },
209 foreignKeyConstraints,
210 })
211
212 const selectPolicy = policies.find((p) => p.command === 'SELECT')
213 expect(selectPolicy?.sql).toContain('CREATE POLICY')
214 expect(selectPolicy?.sql).toContain('public.posts')
215 expect(selectPolicy?.sql).toContain('AS PERMISSIVE FOR SELECT')
216 expect(selectPolicy?.sql).toContain('TO authenticated')
217 expect(selectPolicy?.sql).toContain('USING')
218 expect(selectPolicy?.sql).toContain('auth.uid()')
219 })
220 })
221
222 it('should handle non-public schema', () => {
223 const foreignKeyConstraints: ForeignKeyConstraint[] = [
224 createForeignKey({
225 source_schema: 'private',
226 source_table: 'documents',
227 source_columns: ['owner_id'],
228 target_schema: 'auth',
229 target_table: 'users',
230 target_columns: ['id'],
231 }),
232 ]
233
234 const policies = generateProgrammaticPoliciesForTable({
235 table: { name: 'documents', schema: 'private' },
236 foreignKeyConstraints,
237 })
238
239 expect(policies).toHaveLength(4)
240 expect(policies[0].schema).toBe('private')
241 expect(policies[0].sql).toContain('private.documents')
242 })
243 })
244
245 describe('generateAiPoliciesForTable', () => {
246 const mockAiPolicies: GeneratedPolicy[] = [
247 {
248 name: 'ai_select_policy',
249 sql: 'CREATE POLICY "ai_select_policy" ON public.posts FOR SELECT USING (true);',
250 command: 'SELECT',
251 table: 'posts',
252 schema: 'public',
253 definition: safeSql`true`,
254 action: 'PERMISSIVE',
255 roles: ['public'],
256 },
257 ]
258
259 it('should return policies from AI when called with valid inputs', async () => {
260 mockGenerateSqlPolicy.mockResolvedValue(mockAiPolicies)
261
262 const policies = await generateAiPoliciesForTable({
263 table: { name: 'posts', schema: 'public' },
264 columns: [{ name: 'id' }, { name: 'title' }],
265 projectRef: 'test-project',
266 connectionString: 'postgresql://localhost:5432/test',
267 })
268
269 expect(mockGenerateSqlPolicy).toHaveBeenCalledWith({
270 tableName: 'posts',
271 schema: 'public',
272 columns: ['id', 'title'],
273 projectRef: 'test-project',
274 connectionString: 'postgresql://localhost:5432/test',
275 })
276 expect(policies).toEqual(mockAiPolicies)
277 })
278
279 it('should return empty array when connectionString is null', async () => {
280 const policies = await generateAiPoliciesForTable({
281 table: { name: 'posts', schema: 'public' },
282 columns: [{ name: 'id' }],
283 projectRef: 'test-project',
284 connectionString: null,
285 })
286
287 expect(mockGenerateSqlPolicy).not.toHaveBeenCalled()
288 expect(policies).toEqual([])
289 })
290
291 it('should return empty array when connectionString is undefined', async () => {
292 const policies = await generateAiPoliciesForTable({
293 table: { name: 'posts', schema: 'public' },
294 columns: [{ name: 'id' }],
295 projectRef: 'test-project',
296 connectionString: undefined,
297 })
298
299 expect(mockGenerateSqlPolicy).not.toHaveBeenCalled()
300 expect(policies).toEqual([])
301 })
302
303 it('should handle API errors gracefully and return empty array', async () => {
304 const consoleLogSpy = vi.spyOn(console, 'log').mockImplementation(() => {})
305 mockGenerateSqlPolicy.mockRejectedValue(new Error('API error'))
306
307 const policies = await generateAiPoliciesForTable({
308 table: { name: 'posts', schema: 'public' },
309 columns: [{ name: 'id' }],
310 projectRef: 'test-project',
311 connectionString: 'postgresql://localhost:5432/test',
312 })
313
314 expect(policies).toEqual([])
315 expect(consoleLogSpy).toHaveBeenCalledWith('AI policy generation failed:', expect.any(Error))
316
317 consoleLogSpy.mockRestore()
318 })
319
320 it('should trim column names before sending to API', async () => {
321 mockGenerateSqlPolicy.mockResolvedValue([])
322
323 await generateAiPoliciesForTable({
324 table: { name: 'posts', schema: 'public' },
325 columns: [{ name: ' id ' }, { name: ' title ' }],
326 projectRef: 'test-project',
327 connectionString: 'postgresql://localhost:5432/test',
328 })
329
330 expect(mockGenerateSqlPolicy).toHaveBeenCalledWith(
331 expect.objectContaining({
332 columns: ['id', 'title'],
333 })
334 )
335 })
336 })
337
338 describe('generateStartingPoliciesForTable', () => {
339 const mockAiPolicies: GeneratedPolicy[] = [
340 {
341 name: 'ai_policy',
342 sql: 'CREATE POLICY "ai_policy" ON public.posts FOR SELECT USING (true);',
343 command: 'SELECT',
344 table: 'posts',
345 schema: 'public',
346 definition: safeSql`true`,
347 action: 'PERMISSIVE',
348 roles: ['public'],
349 },
350 ]
351
352 it('should use programmatic policies when FK path exists (does not call AI)', async () => {
353 const foreignKeyConstraints: ForeignKeyConstraint[] = [
354 createForeignKey({
355 source_schema: 'public',
356 source_table: 'posts',
357 source_columns: ['user_id'],
358 target_schema: 'auth',
359 target_table: 'users',
360 target_columns: ['id'],
361 }),
362 ]
363
364 const policies = await generateStartingPoliciesForTable({
365 table: { name: 'posts', schema: 'public' },
366 foreignKeyConstraints,
367 columns: [{ name: 'id' }],
368 projectRef: 'test-project',
369 connectionString: 'postgresql://localhost:5432/test',
370 enableAi: true,
371 })
372
373 expect(policies).toHaveLength(4)
374 expect(mockGenerateSqlPolicy).not.toHaveBeenCalled()
375 })
376
377 it('should fall back to AI when no FK path exists and enableAi is true', async () => {
378 mockGenerateSqlPolicy.mockResolvedValue(mockAiPolicies)
379
380 const policies = await generateStartingPoliciesForTable({
381 table: { name: 'posts', schema: 'public' },
382 foreignKeyConstraints: [],
383 columns: [{ name: 'id' }],
384 projectRef: 'test-project',
385 connectionString: 'postgresql://localhost:5432/test',
386 enableAi: true,
387 })
388
389 expect(mockGenerateSqlPolicy).toHaveBeenCalled()
390 expect(policies).toEqual(mockAiPolicies)
391 })
392
393 it('should return empty array when no FK path exists and enableAi is false', async () => {
394 const policies = await generateStartingPoliciesForTable({
395 table: { name: 'posts', schema: 'public' },
396 foreignKeyConstraints: [],
397 columns: [{ name: 'id' }],
398 projectRef: 'test-project',
399 connectionString: 'postgresql://localhost:5432/test',
400 enableAi: false,
401 })
402
403 expect(mockGenerateSqlPolicy).not.toHaveBeenCalled()
404 expect(policies).toEqual([])
405 })
406
407 it('should return empty array when no FK path and AI returns empty', async () => {
408 mockGenerateSqlPolicy.mockResolvedValue([])
409
410 const policies = await generateStartingPoliciesForTable({
411 table: { name: 'posts', schema: 'public' },
412 foreignKeyConstraints: [],
413 columns: [{ name: 'id' }],
414 projectRef: 'test-project',
415 connectionString: 'postgresql://localhost:5432/test',
416 enableAi: true,
417 })
418
419 expect(policies).toEqual([])
420 })
421
422 it('should prioritize programmatic over AI even when both could generate policies', async () => {
423 mockGenerateSqlPolicy.mockResolvedValue(mockAiPolicies)
424
425 const foreignKeyConstraints: ForeignKeyConstraint[] = [
426 createForeignKey({
427 source_schema: 'public',
428 source_table: 'posts',
429 source_columns: ['user_id'],
430 target_schema: 'auth',
431 target_table: 'users',
432 target_columns: ['id'],
433 }),
434 ]
435
436 const policies = await generateStartingPoliciesForTable({
437 table: { name: 'posts', schema: 'public' },
438 foreignKeyConstraints,
439 columns: [{ name: 'id' }],
440 projectRef: 'test-project',
441 connectionString: 'postgresql://localhost:5432/test',
442 enableAi: true,
443 })
444
445 // Should return 4 programmatic policies, not 1 AI policy
446 expect(policies).toHaveLength(4)
447 expect(mockGenerateSqlPolicy).not.toHaveBeenCalled()
448 })
449 })
450})