EventTriggers.constants.ts90 lines · main

1	import { safeSql } from '@supabase/pg-meta'
2
3	export const DEFAULT_EVENT_TRIGGER_SQL = safeSql`CREATE OR REPLACE FUNCTION event_trigger_fn()
4	RETURNS event_trigger
5	LANGUAGE plpgsql
6	AS $$
7	BEGIN
8	-- Add logic here
9	END;
10	$$;
11
12	DROP EVENT TRIGGER IF EXISTS event_trigger_name;
13	CREATE EVENT TRIGGER event_trigger_name
14	ON ddl_command_end
15	EXECUTE FUNCTION event_trigger_fn();
16	`
17
18	export const AUTO_ENABLE_RLS_EVENT_TRIGGER_SQL = safeSql`
19	CREATE OR REPLACE FUNCTION rls_auto_enable()
20	RETURNS EVENT_TRIGGER
21	LANGUAGE plpgsql
22	SECURITY DEFINER
23	SET search_path = pg_catalog
24	AS $$
25	DECLARE
26	cmd record;
27	BEGIN
28	FOR cmd IN
29	SELECT *
30	FROM pg_event_trigger_ddl_commands()
31	WHERE command_tag IN ('CREATE TABLE', 'CREATE TABLE AS', 'SELECT INTO')
32	AND object_type IN ('table','partitioned table')
33	LOOP
34	IF cmd.schema_name IS NOT NULL AND cmd.schema_name IN ('public') AND cmd.schema_name NOT IN ('pg_catalog','information_schema') AND cmd.schema_name NOT LIKE 'pg_toast%' AND cmd.schema_name NOT LIKE 'pg_temp%' THEN
35	BEGIN
36	EXECUTE format('alter table if exists %s enable row level security', cmd.object_identity);
37	RAISE LOG 'rls_auto_enable: enabled RLS on %', cmd.object_identity;
38	EXCEPTION
39	WHEN OTHERS THEN
40	RAISE LOG 'rls_auto_enable: failed to enable RLS on %', cmd.object_identity;
41	END;
42	ELSE
43	RAISE LOG 'rls_auto_enable: skip % (either system schema or not in enforced list: %.)', cmd.object_identity, cmd.schema_name;
44	END IF;
45	END LOOP;
46	END;
47	$$;
48
49	DROP EVENT TRIGGER IF EXISTS ensure_rls;
50	CREATE EVENT TRIGGER ensure_rls
51	ON ddl_command_end
52	WHEN TAG IN ('CREATE TABLE', 'CREATE TABLE AS', 'SELECT INTO')
53	EXECUTE FUNCTION rls_auto_enable();`
54
55	export const EVENT_TRIGGER_TEMPLATES = [
56	{
57	name: 'Auto enable RLS',
58	description: 'Enable RLS automatically on new tables in selected schemas.',
59	content: AUTO_ENABLE_RLS_EVENT_TRIGGER_SQL,
60	},
61	{
62	name: 'Prevent table drops',
63	description: 'Block dropping tables using the sql_drop event trigger.',
64	content: safeSql`-- Function
65	CREATE OR REPLACE FUNCTION dont_drop_function()
66	RETURNS event_trigger LANGUAGE plpgsql AS $$
67	DECLARE
68	obj record;
69	tbl_name text;
70	BEGIN
71	FOR obj IN SELECT * FROM pg_event_trigger_dropped_objects()
72	LOOP
73	IF obj.object_type = 'table' THEN
74	RAISE EXCEPTION 'ERROR: All tables in this schema are protected and cannot be dropped';
75	END IF;
76	END LOOP;
77	END;
78	$$;
79	-- Event trigger
80	CREATE EVENT TRIGGER dont_drop_trigger
81	ON sql_drop
82	EXECUTE FUNCTION dont_drop_function();
83	`,
84	},
85	{
86	name: 'Blank event trigger',
87	description: 'A minimal scaffold to start from.',
88	content: DEFAULT_EVENT_TRIGGER_SQL,
89	},
90	]