konnosDashboard
SQLEditor.utils.test.ts726 lines · main
1import { safeSql } from '@supabase/pg-meta'
2import { stripIndent } from 'common-tags'
3import { describe, expect, it, test } from 'vitest'
4
5import {
6 appendEnableRLSStatements,
7 checkAlterDatabaseConnection,
8 checkDestructiveQuery,
9 checkIfAppendLimitRequired,
10 filterTablesCoveredByEnsureRLSTrigger,
11 getCreateTablesMissingRLS,
12 hasActiveEnsureRLSTrigger,
13 isUpdateWithoutWhere,
14 suffixWithLimit,
15} from './SQLEditor.utils'
16import type { DatabaseEventTrigger } from '@/data/database-event-triggers/database-event-triggers-query'
17
18const buildTrigger = (overrides: Partial<DatabaseEventTrigger> = {}): DatabaseEventTrigger => ({
19 oid: 1,
20 name: 'ensure_rls',
21 event: 'ddl_command_end',
22 enabled_mode: 'ORIGIN',
23 tags: ['CREATE TABLE'],
24 function_name: 'rls_auto_enable',
25 function_schema: 'public',
26 owner: 'postgres',
27 function_definition: null,
28 ...overrides,
29})
30
31describe('SQLEditor.utils.ts:checkIfAppendLimitRequired', () => {
32 test('Should return false if limit passed is <= 0', () => {
33 const sql = 'select * from countries;'
34 const limit = -1
35 const { appendAutoLimit } = checkIfAppendLimitRequired(sql, limit)
36 expect(appendAutoLimit).toBe(false)
37 })
38 test('Should return true if limit passed is > 0', () => {
39 const sql = 'select * from countries;'
40 const limit = 100
41 const { appendAutoLimit } = checkIfAppendLimitRequired(sql, limit)
42 expect(appendAutoLimit).toBe(true)
43 })
44 test('Should return false if query already has a limit', () => {
45 const sql = 'select * from countries limit 10;'
46 const limit = 100
47 const { appendAutoLimit } = checkIfAppendLimitRequired(sql, limit)
48 expect(appendAutoLimit).toBe(false)
49 })
50 test('Should return false if query already has a limit (check for case-insensitiveness)', () => {
51 const sql = 'SELECT * FROM countries LIMIT 10;'
52 const limit = 100
53 const { appendAutoLimit } = checkIfAppendLimitRequired(sql, limit)
54 expect(appendAutoLimit).toBe(false)
55 })
56 test('Should return false if query already has a limit and offset', () => {
57 const sql = 'select * from countries limit 10 offset 0;'
58 const limit = 100
59 const { appendAutoLimit } = checkIfAppendLimitRequired(sql, limit)
60 expect(appendAutoLimit).toBe(false)
61 })
62 test('Should return false if query already has a limit and offset (flip order of limit and offset)', () => {
63 const sql = 'select * from countries offset 0 limit 1;'
64 const limit = 100
65 const { appendAutoLimit } = checkIfAppendLimitRequired(sql, limit)
66 expect(appendAutoLimit).toBe(false)
67 })
68 test('Should return false if query already has a limit, even if no value provided for limit', () => {
69 const sql = 'select * from countries limit'
70 const limit = 100
71 const { appendAutoLimit } = checkIfAppendLimitRequired(sql, limit)
72 expect(appendAutoLimit).toBe(false)
73 })
74 test('Should return false if query uses `FETCH FIRST` instead of limit ', () => {
75 const sql = 'select * from countries FETCH FIRST 5 rows only'
76 const limit = 100
77 const { appendAutoLimit } = checkIfAppendLimitRequired(sql, limit)
78 expect(appendAutoLimit).toBe(false)
79 })
80 test('Should return false if query uses `fetch first` instead of limit ', () => {
81 const sql = 'select * from countries fetch first 5 rows only'
82 const limit = 100
83 const { appendAutoLimit } = checkIfAppendLimitRequired(sql, limit)
84 expect(appendAutoLimit).toBe(false)
85 })
86 test('Should return false if query uses `fetch first` (with random spaces) instead of limit ', () => {
87 const sql = 'select * from countries FETCH FIRST 5 rows only'
88 const limit = 100
89 const { appendAutoLimit } = checkIfAppendLimitRequired(sql, limit)
90 expect(appendAutoLimit).toBe(false)
91 })
92 test('Should return false if query is not a select statement', () => {
93 const sql = 'create table test (id int8 primary key, name varchar);'
94 const limit = 100
95 const { appendAutoLimit } = checkIfAppendLimitRequired(sql, limit)
96 expect(appendAutoLimit).toBe(false)
97 })
98 test('Should return false if there are multiple queries I', () => {
99 const sql1 = `
100select * from countries;
101select * from cities;
102`.trim()
103 const limit = 100
104 const { appendAutoLimit } = checkIfAppendLimitRequired(sql1, limit)
105 expect(appendAutoLimit).toBe(false)
106 })
107 test('Should return false if there are multiple queries II', () => {
108 const sql1 = `
109select * from countries;
110select * from cities
111`.trim()
112 const limit = 100
113 const { appendAutoLimit } = checkIfAppendLimitRequired(sql1, limit)
114 expect(appendAutoLimit).toBe(false)
115 })
116 // [Joshen] Opting to just avoid appending in this case to prevent making the logic overly complex atm
117 test('Should return false if query has with a comment I', () => {
118 const sql = `
119-- This is a comment
120select * from cities
121`.trim()
122 const limit = 100
123 const { appendAutoLimit } = checkIfAppendLimitRequired(sql, limit)
124 expect(appendAutoLimit).toBe(false)
125 })
126 test('Should return false if query has with a comment II', () => {
127 const sql = `
128select * from cities
129-- This is a comment
130`.trim()
131 const limit = 100
132 const { appendAutoLimit } = checkIfAppendLimitRequired(sql, limit)
133 expect(appendAutoLimit).toBe(false)
134 })
135})
136
137// [Joshen] These will just need to test the cases when appendAutoLimit returns true then
138describe('SQLEditor.utils.ts:suffixWithLimit', () => {
139 test('Should add the limit param properly if query ends without a semi colon', () => {
140 const sql = safeSql`select * from countries`
141 const limit = 100
142 const formattedSql = suffixWithLimit(sql, limit)
143 expect(formattedSql).toBe('select * from countries limit 100;')
144 })
145 test('Should add the limit param properly if query ends with a semi colon', () => {
146 const sql = safeSql`select * from countries;`
147 const limit = 100
148 const formattedSql = suffixWithLimit(sql, limit)
149 expect(formattedSql).toBe('select * from countries limit 100;')
150 })
151 test('Should add the limit param properly if query ends with multiple semi colon', () => {
152 const sql = safeSql`select * from countries;;;;;;;`
153 const limit = 100
154 const formattedSql = suffixWithLimit(sql, limit)
155 expect(formattedSql).toBe('select * from countries limit 100;')
156 })
157})
158
159describe(`SQLEditor.utils.ts:checkDestructiveQuery`, () => {
160 it('drop statement matches', () => {
161 const match = checkDestructiveQuery('drop table films, distributors;')
162
163 expect(match).toBe(true)
164 })
165
166 it('truncate statement matches', () => {
167 const match = checkDestructiveQuery('truncate films;')
168
169 expect(match).toBe(true)
170 })
171
172 it('delete statement matches', () => {
173 const match = checkDestructiveQuery("delete from films where kind <> 'Musical';")
174
175 expect(match).toBe(true)
176 })
177
178 it('delete statement after another statement matches', () => {
179 const match = checkDestructiveQuery(stripIndent`
180 select * from films;
181
182 delete from films where kind <> 'Musical';
183 `)
184
185 expect(match).toBe(true)
186 })
187
188 it("rls policy containing delete doesn't match", () => {
189 const match = checkDestructiveQuery(stripIndent`
190 create policy "Users can delete their own files"
191 on storage.objects for delete to authenticated using (
192 bucket id = 'files' and (select auth.uid()) = owner
193 );
194 `)
195
196 expect(match).toBe(false)
197 })
198
199 it('capitalized statement matches', () => {
200 const match = checkDestructiveQuery("DELETE FROM films WHERE kind <> 'Musical';")
201
202 expect(match).toBe(true)
203 })
204
205 it("comment containing keyword doesn't match", () => {
206 const match = checkDestructiveQuery(stripIndent`
207 -- Going to drop this in here, might delete later
208 select * from films;
209 `)
210
211 expect(match).toBe(false)
212 })
213})
214
215describe('SQLEditor.utils:updateWithoutWhere', () => {
216 it('contains an update query with a where clause', () => {
217 const match = isUpdateWithoutWhere(stripIndent`
218 UPDATE public.countries SET name = 'New Name' WHERE id = 1;
219 `)
220
221 expect(match).toBe(false)
222 })
223
224 it('contains an update query without a where clause', () => {
225 const match = isUpdateWithoutWhere(stripIndent`
226 UPDATE public.countries SET name = 'New Name';
227 `)
228
229 expect(match).toBe(true)
230 })
231
232 it('contains an update query, with quoted identifiers with a where clause', () => {
233 const match = isUpdateWithoutWhere(stripIndent`
234 UPDATE "public"."countries" SET name = 'New Name' WHERE id = 1;
235 `)
236
237 expect(match).toBe(false)
238 })
239
240 it('contains an update query, with quoted identifiers without a where clause', () => {
241 const match = isUpdateWithoutWhere(stripIndent`
242 UPDATE "public"."countries" SET name = 'New Name';
243 `)
244
245 expect(match).toBe(true)
246 })
247
248 it('catches update on a single quoted table name without a where clause', () => {
249 const match = isUpdateWithoutWhere(`UPDATE "messages" SET id = 1;`)
250 expect(match).toBe(true)
251 })
252
253 it('does not flag update on a single quoted table name with a where clause', () => {
254 const match = isUpdateWithoutWhere(`UPDATE "messages" SET id = 1 WHERE id = 2;`)
255 expect(match).toBe(false)
256 })
257
258 it('catches update on a quoted schema with a bareword table without a where clause', () => {
259 const match = isUpdateWithoutWhere(`UPDATE "public".messages SET id = 1;`)
260 expect(match).toBe(true)
261 })
262
263 it('catches update on a bareword schema with a quoted table without a where clause', () => {
264 const match = isUpdateWithoutWhere(`UPDATE public."messages" SET id = 1;`)
265 expect(match).toBe(true)
266 })
267
268 it('catches update on a quoted table name containing a space without a where clause', () => {
269 const match = isUpdateWithoutWhere(`UPDATE "my table" SET id = 1;`)
270 expect(match).toBe(true)
271 })
272
273 it('catches update on a quoted table name containing escaped quotes without a where clause', () => {
274 const match = isUpdateWithoutWhere(`UPDATE "weird""name" SET id = 1;`)
275 expect(match).toBe(true)
276 })
277
278 it('catches update where a quoted identifier contains the word where', () => {
279 const match = isUpdateWithoutWhere(`UPDATE "where table" SET id = 1;`)
280 expect(match).toBe(true)
281 })
282
283 it('catches update where a string literal contains the word where', () => {
284 const match = isUpdateWithoutWhere(`UPDATE messages SET name = 'where x';`)
285 expect(match).toBe(true)
286 })
287
288 it('does not flag update where the only "where" sits inside a string literal but a real where clause exists', () => {
289 const match = isUpdateWithoutWhere(`UPDATE messages SET name = 'where x' WHERE id = 1;`)
290 expect(match).toBe(false)
291 })
292
293 it('contains both an update query and a delete query, triggers destructive', () => {
294 const match = checkDestructiveQuery(stripIndent`
295 delete from countries; update countries set name = 'hello';
296 `)
297
298 expect(match).toBe(true)
299 })
300
301 it('contains both an update query and a delete query, triggers no where', () => {
302 const match = isUpdateWithoutWhere(stripIndent`
303 delete from countries; update countries set name = 'hello';
304 `)
305
306 expect(match).toBe(true)
307 })
308 it('contains both an update query and a delete query, triggers no where', () => {
309 const match = isUpdateWithoutWhere(stripIndent`
310 delete from countries; update countries set name = 'hello';
311 `)
312
313 expect(match).toBe(true)
314 })
315
316 it('should catch potential destructive queries', () => {
317 const DESTRUCTIVE_QUERIES = [
318 `ALTER TABLE test DROP COLUMN test;`,
319 `DELETE FROM test;`,
320 `DROP TABLE test;`,
321 `TRUNCATE TABLE test;`,
322 ]
323
324 DESTRUCTIVE_QUERIES.forEach((query) => {
325 expect(checkDestructiveQuery(query), `Query ${query} should be destructive`).toBe(true)
326 })
327 })
328})
329
330describe('SQLEditor.utils:getCreateTablesMissingRLS', () => {
331 it('flags a basic CREATE TABLE without RLS', () => {
332 const result = getCreateTablesMissingRLS('create table foo (id int8 primary key);')
333 expect(result).toEqual([{ schema: undefined, tableName: 'foo' }])
334 })
335
336 it('flags CREATE TABLE IF NOT EXISTS', () => {
337 const result = getCreateTablesMissingRLS(
338 'create table if not exists foo (id int8 primary key);'
339 )
340 expect(result).toHaveLength(1)
341 expect(result[0].tableName).toBe('foo')
342 })
343
344 it('flags schema-qualified CREATE TABLE', () => {
345 const result = getCreateTablesMissingRLS('create table public.foo (id int8 primary key);')
346 expect(result).toEqual([{ schema: 'public', tableName: 'foo' }])
347 })
348
349 it('flags quoted identifiers', () => {
350 const result = getCreateTablesMissingRLS(
351 'create table "public"."user_table" (id int8 primary key);'
352 )
353 expect(result).toEqual([{ schema: 'public', tableName: 'user_table' }])
354 })
355
356 it('flags quoted identifiers containing spaces', () => {
357 const result = getCreateTablesMissingRLS(
358 'create table "public"."My Table" (id int8 primary key);'
359 )
360 expect(result).toEqual([{ schema: 'public', tableName: 'My Table' }])
361 })
362
363 it('matches RLS to a table whose name contains spaces', () => {
364 const sql = stripIndent`
365 create table "My Table" (id int8 primary key);
366 alter table "My Table" enable row level security;
367 `
368 expect(getCreateTablesMissingRLS(sql)).toEqual([])
369 })
370
371 it('does not flag when ENABLE ROW LEVEL SECURITY is in the same SQL', () => {
372 const sql = stripIndent`
373 create table foo (id int8 primary key);
374 alter table foo enable row level security;
375 `
376 expect(getCreateTablesMissingRLS(sql)).toEqual([])
377 })
378
379 it('does not flag when ENABLE RLS shorthand is in the same SQL', () => {
380 const sql = stripIndent`
381 create table foo (id int8 primary key);
382 alter table foo enable rls;
383 `
384 expect(getCreateTablesMissingRLS(sql)).toEqual([])
385 })
386
387 it('matches RLS to the right table when multiple tables created', () => {
388 const sql = stripIndent`
389 create table foo (id int8 primary key);
390 create table bar (id int8 primary key);
391 alter table foo enable row level security;
392 `
393 const result = getCreateTablesMissingRLS(sql)
394 expect(result).toHaveLength(1)
395 expect(result[0].tableName).toBe('bar')
396 })
397
398 it('does not flag when CREATE TABLE is inside a comment', () => {
399 const sql = stripIndent`
400 -- create table foo (id int8 primary key);
401 select 1;
402 `
403 expect(getCreateTablesMissingRLS(sql)).toEqual([])
404 })
405
406 it('does not flag when there is no CREATE TABLE at all', () => {
407 expect(getCreateTablesMissingRLS('select * from foo;')).toEqual([])
408 })
409
410 it('schema-qualified RLS matches schema-qualified CREATE', () => {
411 const sql = stripIndent`
412 create table public.foo (id int8 primary key);
413 alter table public.foo enable row level security;
414 `
415 expect(getCreateTablesMissingRLS(sql)).toEqual([])
416 })
417
418 it('does not flag when ALTER TABLE IF EXISTS enables RLS', () => {
419 const sql = stripIndent`
420 CREATE TABLE IF NOT EXISTS public."Conversations" (id int8 primary key);
421 ALTER TABLE IF EXISTS public."Conversations" ENABLE ROW LEVEL SECURITY;
422 GRANT ALL ON TABLE public."Conversations" TO postgres, anon, authenticated, service_role;
423 `
424 expect(getCreateTablesMissingRLS(sql)).toEqual([])
425 })
426
427 it('flags CREATE TEMP TABLE', () => {
428 const result = getCreateTablesMissingRLS('create temp table foo (id int8 primary key);')
429 expect(result).toHaveLength(1)
430 expect(result[0].tableName).toBe('foo')
431 })
432
433 it('does not flag `select ... into var` inside a plpgsql function body', () => {
434 // Regression: the SELECT..INTO detector used to match variable assignments
435 // inside $$...$$ function bodies and surface them as \"new tables\".
436 const sql = stripIndent`
437 create or replace function schema_checks()
438 returns jsonb
439 language plpgsql
440 as $$
441 declare
442 ret jsonb;
443 begin
444 select jsonb_build_object('value', 'ok')
445 into ret;
446 return ret;
447 end;
448 $$;
449 `
450 expect(getCreateTablesMissingRLS(sql)).toEqual([])
451 })
452
453 it('does not flag `select ... into var` inside a DO block', () => {
454 const sql = stripIndent`
455 do $$
456 declare
457 result int;
458 begin
459 select count(*) into result from information_schema.tables;
460 end
461 $$;
462 `
463 expect(getCreateTablesMissingRLS(sql)).toEqual([])
464 })
465
466 it('does not flag CREATE TABLE text that appears inside a function body', () => {
467 const sql = stripIndent`
468 create or replace function noop()
469 returns void
470 language plpgsql
471 as $$
472 begin
473 -- create table foo (id int);
474 perform 1;
475 end;
476 $$;
477 `
478 expect(getCreateTablesMissingRLS(sql)).toEqual([])
479 })
480
481 it('flags top-level CREATE TABLE alongside a function with INTO assignments', () => {
482 const sql = stripIndent`
483 create table public.foo (id int8 primary key);
484 create or replace function bar()
485 returns int
486 language plpgsql
487 as $$
488 declare
489 v int;
490 begin
491 select 1 into v;
492 return v;
493 end;
494 $$;
495 `
496 const result = getCreateTablesMissingRLS(sql)
497 expect(result).toEqual([{ schema: 'public', tableName: 'foo' }])
498 })
499
500 it('does not flag CREATE TABLE inside nested dollar-quoted dynamic SQL', () => {
501 // Regression: the `$sql$...$sql$` block inside the outer `$fn$...$fn$`
502 // body was previously pairing with the outer tag, letting the inner
503 // semicolon split the statement and exposing `create table fake` to the
504 // RLS warning.
505 const sql = stripIndent`
506 create function f()
507 returns void
508 language plpgsql
509 as $fn$
510 begin
511 execute $sql$create table fake(id int);$sql$;
512 end;
513 $fn$;
514 `
515 expect(getCreateTablesMissingRLS(sql)).toEqual([])
516 })
517
518 it('handles custom dollar-quote tags (e.g. $body$...$body$)', () => {
519 const sql = stripIndent`
520 create or replace function f()
521 returns int
522 language plpgsql
523 as $body$
524 declare
525 v int;
526 begin
527 select 1 into v;
528 return v;
529 end;
530 $body$;
531 `
532 expect(getCreateTablesMissingRLS(sql)).toEqual([])
533 })
534
535 it('does not collide quoted identifiers that differ only by case', () => {
536 // "MyTable" and "mytable" are distinct tables in Postgres, so the ALTER
537 // here targets a different table than the CREATE — the warning must fire.
538 const sql = stripIndent`
539 create table "MyTable" (id int8 primary key);
540 alter table "mytable" enable row level security;
541 `
542 const result = getCreateTablesMissingRLS(sql)
543 expect(result).toHaveLength(1)
544 expect(result[0].tableName).toBe('MyTable')
545 })
546})
547
548describe('SQLEditor.utils:appendEnableRLSStatements', () => {
549 it('appends a single ALTER TABLE ENABLE RLS statement', () => {
550 const result = appendEnableRLSStatements('create table foo (id int8 primary key);', [
551 { tableName: 'foo' },
552 ])
553 expect(result).toContain('ALTER TABLE foo ENABLE ROW LEVEL SECURITY;')
554 })
555
556 it('appends one ALTER per table', () => {
557 const result = appendEnableRLSStatements(
558 'create table foo (id int8); create table bar (id int8);',
559 [{ tableName: 'foo' }, { tableName: 'bar' }]
560 )
561 expect(result).toContain('ALTER TABLE foo ENABLE ROW LEVEL SECURITY;')
562 expect(result).toContain('ALTER TABLE bar ENABLE ROW LEVEL SECURITY;')
563 })
564
565 it('schema-qualifies the table when schema is provided', () => {
566 const result = appendEnableRLSStatements('create table public.foo (id int8);', [
567 { schema: 'public', tableName: 'foo' },
568 ])
569 expect(result).toContain('ALTER TABLE public.foo ENABLE ROW LEVEL SECURITY;')
570 })
571
572 it('quotes identifiers that are not simple', () => {
573 const result = appendEnableRLSStatements('create table "My Table" (id int8);', [
574 { tableName: 'My Table' },
575 ])
576 expect(result).toContain('ALTER TABLE "My Table" ENABLE ROW LEVEL SECURITY;')
577 })
578
579 it('quotes mixed-case identifiers so Postgres does not fold them to lowercase', () => {
580 const result = appendEnableRLSStatements('create table "MyTable" (id int8);', [
581 { tableName: 'MyTable' },
582 ])
583 expect(result).toContain('ALTER TABLE "MyTable" ENABLE ROW LEVEL SECURITY;')
584 })
585
586 it('quotes mixed-case schema and table identifiers', () => {
587 const result = appendEnableRLSStatements('create table "MySchema"."MyTable" (id int8);', [
588 { schema: 'MySchema', tableName: 'MyTable' },
589 ])
590 expect(result).toContain('ALTER TABLE "MySchema"."MyTable" ENABLE ROW LEVEL SECURITY;')
591 })
592
593 it('returns the original SQL unchanged when there are no tables', () => {
594 const sql = 'select 1;'
595 expect(appendEnableRLSStatements(sql, [])).toBe(sql)
596 })
597
598 it('puts the terminator on its own line when SQL ends with a line comment', () => {
599 // Without this, the appended ';' would be swallowed by the line comment and
600 // the following ALTER TABLE would be parsed as part of the CREATE TABLE.
601 const sql = stripIndent`
602 create table foo (id int)
603 -- forgot the semicolon
604 `
605 const result = appendEnableRLSStatements(sql, [{ tableName: 'foo' }])
606 expect(result).toMatch(/-- forgot the semicolon\n;\n/)
607 expect(result).toContain('ALTER TABLE foo ENABLE ROW LEVEL SECURITY;')
608 })
609})
610
611describe('SQLEditor.utils:hasActiveEnsureRLSTrigger', () => {
612 it('returns false for undefined triggers', () => {
613 expect(hasActiveEnsureRLSTrigger(undefined)).toBe(false)
614 })
615
616 it('returns false for an empty list', () => {
617 expect(hasActiveEnsureRLSTrigger([])).toBe(false)
618 })
619
620 it('returns true when a trigger named "ensure_rls" is active', () => {
621 expect(hasActiveEnsureRLSTrigger([buildTrigger()])).toBe(true)
622 })
623
624 it('returns true when a trigger uses the rls_auto_enable function (renamed trigger)', () => {
625 expect(
626 hasActiveEnsureRLSTrigger([
627 buildTrigger({ name: 'something_else', function_name: 'rls_auto_enable' }),
628 ])
629 ).toBe(true)
630 })
631
632 it('returns false when the matching trigger is DISABLED', () => {
633 expect(hasActiveEnsureRLSTrigger([buildTrigger({ enabled_mode: 'DISABLED' })])).toBe(false)
634 })
635
636 it('ignores unrelated triggers', () => {
637 expect(
638 hasActiveEnsureRLSTrigger([buildTrigger({ name: 'audit_log', function_name: 'log_changes' })])
639 ).toBe(false)
640 })
641})
642
643describe('SQLEditor.utils:filterTablesCoveredByEnsureRLSTrigger', () => {
644 it('returns the input unchanged when the trigger is not present', () => {
645 const tables = [{ tableName: 'foo' }, { schema: 'private', tableName: 'bar' }]
646 expect(filterTablesCoveredByEnsureRLSTrigger(tables, false)).toEqual(tables)
647 })
648
649 it('drops public-schema tables when the trigger is present', () => {
650 const tables = [
651 { schema: 'public', tableName: 'foo' },
652 { tableName: 'bar' }, // no schema → defaults to public
653 ]
654 expect(filterTablesCoveredByEnsureRLSTrigger(tables, true)).toEqual([])
655 })
656
657 it('keeps tables in non-public schemas when the trigger is present', () => {
658 const tables = [
659 { schema: 'public', tableName: 'foo' },
660 { schema: 'private', tableName: 'bar' },
661 { schema: 'app', tableName: 'baz' },
662 ]
663 expect(filterTablesCoveredByEnsureRLSTrigger(tables, true)).toEqual([
664 { schema: 'private', tableName: 'bar' },
665 { schema: 'app', tableName: 'baz' },
666 ])
667 })
668
669 it('matches the public schema case-insensitively', () => {
670 const tables = [{ schema: 'PUBLIC', tableName: 'foo' }]
671 expect(filterTablesCoveredByEnsureRLSTrigger(tables, true)).toEqual([])
672 })
673})
674
675describe('SQLEditor.utils:checkAlterDatabaseConnection', () => {
676 it('detects connection limit 0', () => {
677 const match = checkAlterDatabaseConnection('alter database postgres connection limit 0;')
678 expect(match).toBe(true)
679 })
680
681 it('detects allow_connections false', () => {
682 const match = checkAlterDatabaseConnection('alter database postgres allow_connections false;')
683 expect(match).toBe(true)
684 })
685
686 it('detects case-insensitive match', () => {
687 const match = checkAlterDatabaseConnection('ALTER DATABASE postgres CONNECTION LIMIT 0;')
688 expect(match).toBe(true)
689 })
690
691 it('detects statement among multiple statements', () => {
692 const match = checkAlterDatabaseConnection(stripIndent`
693 select * from countries;
694 alter database postgres connection limit 0;
695 `)
696 expect(match).toBe(true)
697 })
698
699 it('does not flag unrelated alter database statement', () => {
700 const match = checkAlterDatabaseConnection(
701 'alter database postgres set statement_timeout = 60000;'
702 )
703 expect(match).toBe(false)
704 })
705
706 it('does not flag non-alter statements', () => {
707 const match = checkAlterDatabaseConnection('select * from countries;')
708 expect(match).toBe(false)
709 })
710
711 it('ignores statements inside comments', () => {
712 const match = checkAlterDatabaseConnection(stripIndent`
713 -- alter database postgres connection limit 0;
714 select 1;
715 `)
716 expect(match).toBe(false)
717 })
718
719 it('detects both dangerous statements in same query', () => {
720 const match = checkAlterDatabaseConnection(stripIndent`
721 alter database postgres connection limit 0;
722 alter database postgres allow_connections false;
723 `)
724 expect(match).toBe(true)
725 })
726})