1
BEGIN;
2
  -- Create pg_net extension
3
  CREATE EXTENSION IF NOT EXISTS pg_net SCHEMA extensions;
4
  -- Create supabase_functions schema
5
  CREATE SCHEMA supabase_functions AUTHORIZATION supabase_admin;
6
  GRANT USAGE ON SCHEMA supabase_functions TO postgres, anon, authenticated, service_role;
7
  ALTER DEFAULT PRIVILEGES IN SCHEMA supabase_functions GRANT ALL ON TABLES TO postgres, anon, authenticated, service_role;
8
  ALTER DEFAULT PRIVILEGES IN SCHEMA supabase_functions GRANT ALL ON FUNCTIONS TO postgres, anon, authenticated, service_role;
9
  ALTER DEFAULT PRIVILEGES IN SCHEMA supabase_functions GRANT ALL ON SEQUENCES TO postgres, anon, authenticated, service_role;
10
  -- supabase_functions.migrations definition
11
  CREATE TABLE supabase_functions.migrations (
12
    version text PRIMARY KEY,
13
    inserted_at timestamptz NOT NULL DEFAULT NOW()
14
  );
15
  -- Initial supabase_functions migration
16
  INSERT INTO supabase_functions.migrations (version) VALUES ('initial');
17
  -- supabase_functions.hooks definition
18
  CREATE TABLE supabase_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 supabase_functions_hooks_request_id_idx ON supabase_functions.hooks USING btree (request_id);
26
  CREATE INDEX supabase_functions_hooks_h_table_id_h_name_idx ON supabase_functions.hooks USING btree (hook_table_id, hook_name);
27
  COMMENT ON TABLE supabase_functions.hooks IS 'Supabase Functions Hooks: Audit trail for triggered hooks.';
28
  CREATE FUNCTION supabase_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 supabase_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
  -- Supabase super admin
104
  DO
105
  $$
106
  BEGIN
107
    IF NOT EXISTS (
108
      SELECT 1
109
      FROM pg_roles
110
      WHERE rolname = 'supabase_functions_admin'
111
    )
112
    THEN
113
      CREATE USER supabase_functions_admin NOINHERIT CREATEROLE LOGIN NOREPLICATION;
114
    END IF;
115
  END
116
  $$;
117
  GRANT ALL PRIVILEGES ON SCHEMA supabase_functions TO supabase_functions_admin;
118
  GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA supabase_functions TO supabase_functions_admin;
119
  GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA supabase_functions TO supabase_functions_admin;
120
  ALTER USER supabase_functions_admin SET search_path = "supabase_functions";
121
  ALTER table "supabase_functions".migrations OWNER TO supabase_functions_admin;
122
  ALTER table "supabase_functions".hooks OWNER TO supabase_functions_admin;
123
  ALTER function "supabase_functions".http_request() OWNER TO supabase_functions_admin;
124
  GRANT supabase_functions_admin TO postgres;
125
  -- Remove unused supabase_pg_net_admin role
126
  DO
127
  $$
128
  BEGIN
129
    IF EXISTS (
130
      SELECT 1
131
      FROM pg_roles
132
      WHERE rolname = 'supabase_pg_net_admin'
133
    )
134
    THEN
135
      REASSIGN OWNED BY supabase_pg_net_admin TO supabase_admin;
136
      DROP OWNED BY supabase_pg_net_admin;
137
      DROP ROLE supabase_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 supabase_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 supabase_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 supabase_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 supabase_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 supabase_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 supabase_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 supabase_functions.migrations (version) VALUES ('20210809183423_update_grants');
204
  ALTER function supabase_functions.http_request() SECURITY DEFINER;
205
  ALTER function supabase_functions.http_request() SET search_path = supabase_functions;
206
  REVOKE ALL ON FUNCTION supabase_functions.http_request() FROM PUBLIC;
207
  GRANT EXECUTE ON FUNCTION supabase_functions.http_request() TO postgres, anon, authenticated, service_role;
208
COMMIT;