konnosDashboard
webhooks.sql208 lines · main
1BEGIN;
2 -- Create pg_net extension
3 CREATE EXTENSION IF NOT EXISTS pg_net SCHEMA extensions;
4 -- Create briven_functions schema
5 CREATE SCHEMA briven_functions AUTHORIZATION briven_admin;
6 GRANT USAGE ON SCHEMA briven_functions TO postgres, anon, authenticated, service_role;
7 ALTER DEFAULT PRIVILEGES IN SCHEMA briven_functions GRANT ALL ON TABLES TO postgres, anon, authenticated, service_role;
8 ALTER DEFAULT PRIVILEGES IN SCHEMA briven_functions GRANT ALL ON FUNCTIONS TO postgres, anon, authenticated, service_role;
9 ALTER DEFAULT PRIVILEGES IN SCHEMA briven_functions GRANT ALL ON SEQUENCES TO postgres, anon, authenticated, service_role;
10 -- briven_functions.migrations definition
11 CREATE TABLE briven_functions.migrations (
12 version text PRIMARY KEY,
13 inserted_at timestamptz NOT NULL DEFAULT NOW()
14 );
15 -- Initial briven_functions migration
16 INSERT INTO briven_functions.migrations (version) VALUES ('initial');
17 -- briven_functions.hooks definition
18 CREATE TABLE briven_functions.hooks (
19 id bigserial PRIMARY KEY,
20 hook_table_id integer NOT NULL,
21 hook_name text NOT NULL,
22 created_at timestamptz NOT NULL DEFAULT NOW(),
23 request_id bigint
24 );
25 CREATE INDEX briven_functions_hooks_request_id_idx ON briven_functions.hooks USING btree (request_id);
26 CREATE INDEX briven_functions_hooks_h_table_id_h_name_idx ON briven_functions.hooks USING btree (hook_table_id, hook_name);
27 COMMENT ON TABLE briven_functions.hooks IS 'Briven Functions Hooks: Audit trail for triggered hooks.';
28 CREATE FUNCTION briven_functions.http_request()
29 RETURNS trigger
30 LANGUAGE plpgsql
31 AS $function$
32 DECLARE
33 request_id bigint;
34 payload jsonb;
35 url text := TG_ARGV[0]::text;
36 method text := TG_ARGV[1]::text;
37 headers jsonb DEFAULT '{}'::jsonb;
38 params jsonb DEFAULT '{}'::jsonb;
39 timeout_ms integer DEFAULT 1000;
40 BEGIN
41 IF url IS NULL OR url = 'null' THEN
42 RAISE EXCEPTION 'url argument is missing';
43 END IF;
44
45 IF method IS NULL OR method = 'null' THEN
46 RAISE EXCEPTION 'method argument is missing';
47 END IF;
48
49 IF TG_ARGV[2] IS NULL OR TG_ARGV[2] = 'null' THEN
50 headers = '{"Content-Type": "application/json"}'::jsonb;
51 ELSE
52 headers = TG_ARGV[2]::jsonb;
53 END IF;
54
55 IF TG_ARGV[3] IS NULL OR TG_ARGV[3] = 'null' THEN
56 params = '{}'::jsonb;
57 ELSE
58 params = TG_ARGV[3]::jsonb;
59 END IF;
60
61 IF TG_ARGV[4] IS NULL OR TG_ARGV[4] = 'null' THEN
62 timeout_ms = 1000;
63 ELSE
64 timeout_ms = TG_ARGV[4]::integer;
65 END IF;
66
67 CASE
68 WHEN method = 'GET' THEN
69 SELECT http_get INTO request_id FROM net.http_get(
70 url,
71 params,
72 headers,
73 timeout_ms
74 );
75 WHEN method = 'POST' THEN
76 payload = jsonb_build_object(
77 'old_record', OLD,
78 'record', NEW,
79 'type', TG_OP,
80 'table', TG_TABLE_NAME,
81 'schema', TG_TABLE_SCHEMA
82 );
83
84 SELECT http_post INTO request_id FROM net.http_post(
85 url,
86 payload,
87 params,
88 headers,
89 timeout_ms
90 );
91 ELSE
92 RAISE EXCEPTION 'method argument % is invalid', method;
93 END CASE;
94
95 INSERT INTO briven_functions.hooks
96 (hook_table_id, hook_name, request_id)
97 VALUES
98 (TG_RELID, TG_NAME, request_id);
99
100 RETURN NEW;
101 END
102 $function$;
103 -- Briven super admin
104 DO
105 $$
106 BEGIN
107 IF NOT EXISTS (
108 SELECT 1
109 FROM pg_roles
110 WHERE rolname = 'briven_functions_admin'
111 )
112 THEN
113 CREATE USER briven_functions_admin NOINHERIT CREATEROLE LOGIN NOREPLICATION;
114 END IF;
115 END
116 $$;
117 GRANT ALL PRIVILEGES ON SCHEMA briven_functions TO briven_functions_admin;
118 GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA briven_functions TO briven_functions_admin;
119 GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA briven_functions TO briven_functions_admin;
120 ALTER USER briven_functions_admin SET search_path = "briven_functions";
121 ALTER table "briven_functions".migrations OWNER TO briven_functions_admin;
122 ALTER table "briven_functions".hooks OWNER TO briven_functions_admin;
123 ALTER function "briven_functions".http_request() OWNER TO briven_functions_admin;
124 GRANT briven_functions_admin TO postgres;
125 -- Remove unused briven_pg_net_admin role
126 DO
127 $$
128 BEGIN
129 IF EXISTS (
130 SELECT 1
131 FROM pg_roles
132 WHERE rolname = 'briven_pg_net_admin'
133 )
134 THEN
135 REASSIGN OWNED BY briven_pg_net_admin TO briven_admin;
136 DROP OWNED BY briven_pg_net_admin;
137 DROP ROLE briven_pg_net_admin;
138 END IF;
139 END
140 $$;
141 -- pg_net grants when extension is already enabled
142 DO
143 $$
144 BEGIN
145 IF EXISTS (
146 SELECT 1
147 FROM pg_extension
148 WHERE extname = 'pg_net'
149 )
150 THEN
151 GRANT USAGE ON SCHEMA net TO briven_functions_admin, postgres, anon, authenticated, service_role;
152 ALTER function net.http_get(url text, params jsonb, headers jsonb, timeout_milliseconds integer) SECURITY DEFINER;
153 ALTER function net.http_post(url text, body jsonb, params jsonb, headers jsonb, timeout_milliseconds integer) SECURITY DEFINER;
154 ALTER function net.http_get(url text, params jsonb, headers jsonb, timeout_milliseconds integer) SET search_path = net;
155 ALTER function net.http_post(url text, body jsonb, params jsonb, headers jsonb, timeout_milliseconds integer) SET search_path = net;
156 REVOKE ALL ON FUNCTION net.http_get(url text, params jsonb, headers jsonb, timeout_milliseconds integer) FROM PUBLIC;
157 REVOKE ALL ON FUNCTION net.http_post(url text, body jsonb, params jsonb, headers jsonb, timeout_milliseconds integer) FROM PUBLIC;
158 GRANT EXECUTE ON FUNCTION net.http_get(url text, params jsonb, headers jsonb, timeout_milliseconds integer) TO briven_functions_admin, postgres, anon, authenticated, service_role;
159 GRANT EXECUTE ON FUNCTION net.http_post(url text, body jsonb, params jsonb, headers jsonb, timeout_milliseconds integer) TO briven_functions_admin, postgres, anon, authenticated, service_role;
160 END IF;
161 END
162 $$;
163 -- Event trigger for pg_net
164 CREATE OR REPLACE FUNCTION extensions.grant_pg_net_access()
165 RETURNS event_trigger
166 LANGUAGE plpgsql
167 AS $$
168 BEGIN
169 IF EXISTS (
170 SELECT 1
171 FROM pg_event_trigger_ddl_commands() AS ev
172 JOIN pg_extension AS ext
173 ON ev.objid = ext.oid
174 WHERE ext.extname = 'pg_net'
175 )
176 THEN
177 GRANT USAGE ON SCHEMA net TO briven_functions_admin, postgres, anon, authenticated, service_role;
178 ALTER function net.http_get(url text, params jsonb, headers jsonb, timeout_milliseconds integer) SECURITY DEFINER;
179 ALTER function net.http_post(url text, body jsonb, params jsonb, headers jsonb, timeout_milliseconds integer) SECURITY DEFINER;
180 ALTER function net.http_get(url text, params jsonb, headers jsonb, timeout_milliseconds integer) SET search_path = net;
181 ALTER function net.http_post(url text, body jsonb, params jsonb, headers jsonb, timeout_milliseconds integer) SET search_path = net;
182 REVOKE ALL ON FUNCTION net.http_get(url text, params jsonb, headers jsonb, timeout_milliseconds integer) FROM PUBLIC;
183 REVOKE ALL ON FUNCTION net.http_post(url text, body jsonb, params jsonb, headers jsonb, timeout_milliseconds integer) FROM PUBLIC;
184 GRANT EXECUTE ON FUNCTION net.http_get(url text, params jsonb, headers jsonb, timeout_milliseconds integer) TO briven_functions_admin, postgres, anon, authenticated, service_role;
185 GRANT EXECUTE ON FUNCTION net.http_post(url text, body jsonb, params jsonb, headers jsonb, timeout_milliseconds integer) TO briven_functions_admin, postgres, anon, authenticated, service_role;
186 END IF;
187 END;
188 $$;
189 COMMENT ON FUNCTION extensions.grant_pg_net_access IS 'Grants access to pg_net';
190 DO
191 $$
192 BEGIN
193 IF NOT EXISTS (
194 SELECT 1
195 FROM pg_event_trigger
196 WHERE evtname = 'issue_pg_net_access'
197 ) THEN
198 CREATE EVENT TRIGGER issue_pg_net_access ON ddl_command_end WHEN TAG IN ('CREATE EXTENSION')
199 EXECUTE PROCEDURE extensions.grant_pg_net_access();
200 END IF;
201 END
202 $$;
203 INSERT INTO briven_functions.migrations (version) VALUES ('20210809183423_update_grants');
204 ALTER function briven_functions.http_request() SECURITY DEFINER;
205 ALTER function briven_functions.http_request() SET search_path = briven_functions;
206 REVOKE ALL ON FUNCTION briven_functions.http_request() FROM PUBLIC;
207 GRANT EXECUTE ON FUNCTION briven_functions.http_request() TO postgres, anon, authenticated, service_role;
208COMMIT;