github.com/supabase/cli@v1.168.1/internal/db/start/templates/schema.sql (about) 1 \set pgpass `echo "$PGPASSWORD"` 2 \set jwt_secret `echo "$JWT_SECRET"` 3 \set jwt_exp `echo "$JWT_EXP"` 4 5 ALTER DATABASE postgres SET "app.settings.jwt_secret" TO :'jwt_secret'; 6 ALTER DATABASE postgres SET "app.settings.jwt_exp" TO :'jwt_exp'; 7 8 ALTER USER authenticator WITH PASSWORD :'pgpass'; 9 ALTER USER pgbouncer WITH PASSWORD :'pgpass'; 10 ALTER USER supabase_auth_admin WITH PASSWORD :'pgpass'; 11 ALTER USER supabase_storage_admin WITH PASSWORD :'pgpass'; 12 ALTER USER supabase_replication_admin WITH PASSWORD :'pgpass'; 13 ALTER USER supabase_read_only_user WITH PASSWORD :'pgpass'; 14 15 create schema if not exists _realtime; 16 alter schema _realtime owner to postgres; 17 18 create schema if not exists _analytics; 19 alter schema _analytics owner to postgres; 20 21 BEGIN; 22 23 -- Create pg_net extension 24 CREATE EXTENSION IF NOT EXISTS pg_net SCHEMA extensions; 25 26 -- Create supabase_functions schema 27 CREATE SCHEMA supabase_functions AUTHORIZATION supabase_admin; 28 29 GRANT USAGE ON SCHEMA supabase_functions TO postgres, anon, authenticated, service_role; 30 ALTER DEFAULT PRIVILEGES IN SCHEMA supabase_functions GRANT ALL ON TABLES TO postgres, anon, authenticated, service_role; 31 ALTER DEFAULT PRIVILEGES IN SCHEMA supabase_functions GRANT ALL ON FUNCTIONS TO postgres, anon, authenticated, service_role; 32 ALTER DEFAULT PRIVILEGES IN SCHEMA supabase_functions GRANT ALL ON SEQUENCES TO postgres, anon, authenticated, service_role; 33 34 -- supabase_functions.migrations definition 35 CREATE TABLE supabase_functions.migrations ( 36 version text PRIMARY KEY, 37 inserted_at timestamptz NOT NULL DEFAULT NOW() 38 ); 39 40 -- Initial supabase_functions migration 41 INSERT INTO supabase_functions.migrations (version) VALUES ('initial'); 42 43 -- supabase_functions.hooks definition 44 CREATE TABLE supabase_functions.hooks ( 45 id bigserial PRIMARY KEY, 46 hook_table_id integer NOT NULL, 47 hook_name text NOT NULL, 48 created_at timestamptz NOT NULL DEFAULT NOW(), 49 request_id bigint 50 ); 51 CREATE INDEX supabase_functions_hooks_request_id_idx ON supabase_functions.hooks USING btree (request_id); 52 CREATE INDEX supabase_functions_hooks_h_table_id_h_name_idx ON supabase_functions.hooks USING btree (hook_table_id, hook_name); 53 COMMENT ON TABLE supabase_functions.hooks IS 'Supabase Functions Hooks: Audit trail for triggered hooks.'; 54 55 CREATE FUNCTION supabase_functions.http_request() 56 RETURNS trigger 57 LANGUAGE plpgsql 58 AS $function$ 59 DECLARE 60 request_id bigint; 61 payload jsonb; 62 url text := TG_ARGV[0]::text; 63 method text := TG_ARGV[1]::text; 64 headers jsonb DEFAULT '{}'::jsonb; 65 params jsonb DEFAULT '{}'::jsonb; 66 timeout_ms integer DEFAULT 1000; 67 BEGIN 68 IF url IS NULL OR url = 'null' THEN 69 RAISE EXCEPTION 'url argument is missing'; 70 END IF; 71 72 IF method IS NULL OR method = 'null' THEN 73 RAISE EXCEPTION 'method argument is missing'; 74 END IF; 75 76 IF TG_ARGV[2] IS NULL OR TG_ARGV[2] = 'null' THEN 77 headers = '{"Content-Type": "application/json"}'::jsonb; 78 ELSE 79 headers = TG_ARGV[2]::jsonb; 80 END IF; 81 82 IF TG_ARGV[3] IS NULL OR TG_ARGV[3] = 'null' THEN 83 params = '{}'::jsonb; 84 ELSE 85 params = TG_ARGV[3]::jsonb; 86 END IF; 87 88 IF TG_ARGV[4] IS NULL OR TG_ARGV[4] = 'null' THEN 89 timeout_ms = 1000; 90 ELSE 91 timeout_ms = TG_ARGV[4]::integer; 92 END IF; 93 94 CASE 95 WHEN method = 'GET' THEN 96 SELECT http_get INTO request_id FROM net.http_get( 97 url, 98 params, 99 headers, 100 timeout_ms 101 ); 102 WHEN method = 'POST' THEN 103 payload = jsonb_build_object( 104 'old_record', OLD, 105 'record', NEW, 106 'type', TG_OP, 107 'table', TG_TABLE_NAME, 108 'schema', TG_TABLE_SCHEMA 109 ); 110 111 SELECT http_post INTO request_id FROM net.http_post( 112 url, 113 payload, 114 params, 115 headers, 116 timeout_ms 117 ); 118 ELSE 119 RAISE EXCEPTION 'method argument % is invalid', method; 120 END CASE; 121 122 INSERT INTO supabase_functions.hooks 123 (hook_table_id, hook_name, request_id) 124 VALUES 125 (TG_RELID, TG_NAME, request_id); 126 127 RETURN NEW; 128 END 129 $function$; 130 131 -- Supabase super admin 132 DO 133 $$ 134 BEGIN 135 IF NOT EXISTS ( 136 SELECT 1 137 FROM pg_roles 138 WHERE rolname = 'supabase_functions_admin' 139 ) 140 THEN 141 CREATE USER supabase_functions_admin NOINHERIT CREATEROLE LOGIN NOREPLICATION; 142 END IF; 143 END 144 $$; 145 146 GRANT ALL PRIVILEGES ON SCHEMA supabase_functions TO supabase_functions_admin; 147 GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA supabase_functions TO supabase_functions_admin; 148 GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA supabase_functions TO supabase_functions_admin; 149 ALTER USER supabase_functions_admin SET search_path = "supabase_functions"; 150 ALTER table "supabase_functions".migrations OWNER TO supabase_functions_admin; 151 ALTER table "supabase_functions".hooks OWNER TO supabase_functions_admin; 152 ALTER function "supabase_functions".http_request() OWNER TO supabase_functions_admin; 153 GRANT supabase_functions_admin TO postgres; 154 155 -- Remove unused supabase_pg_net_admin role 156 DO 157 $$ 158 BEGIN 159 IF EXISTS ( 160 SELECT 1 161 FROM pg_roles 162 WHERE rolname = 'supabase_pg_net_admin' 163 ) 164 THEN 165 REASSIGN OWNED BY supabase_pg_net_admin TO supabase_admin; 166 DROP OWNED BY supabase_pg_net_admin; 167 DROP ROLE supabase_pg_net_admin; 168 END IF; 169 END 170 $$; 171 172 -- pg_net grants when extension is already enabled 173 DO 174 $$ 175 BEGIN 176 IF EXISTS ( 177 SELECT 1 178 FROM pg_extension 179 WHERE extname = 'pg_net' 180 ) 181 THEN 182 GRANT USAGE ON SCHEMA net TO supabase_functions_admin, postgres, anon, authenticated, service_role; 183 184 ALTER function net.http_get(url text, params jsonb, headers jsonb, timeout_milliseconds integer) SECURITY DEFINER; 185 ALTER function net.http_post(url text, body jsonb, params jsonb, headers jsonb, timeout_milliseconds integer) SECURITY DEFINER; 186 187 ALTER function net.http_get(url text, params jsonb, headers jsonb, timeout_milliseconds integer) SET search_path = net; 188 ALTER function net.http_post(url text, body jsonb, params jsonb, headers jsonb, timeout_milliseconds integer) SET search_path = net; 189 190 REVOKE ALL ON FUNCTION net.http_get(url text, params jsonb, headers jsonb, timeout_milliseconds integer) FROM PUBLIC; 191 REVOKE ALL ON FUNCTION net.http_post(url text, body jsonb, params jsonb, headers jsonb, timeout_milliseconds integer) FROM PUBLIC; 192 193 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; 194 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; 195 END IF; 196 END 197 $$; 198 199 -- Event trigger for pg_net 200 CREATE OR REPLACE FUNCTION extensions.grant_pg_net_access() 201 RETURNS event_trigger 202 LANGUAGE plpgsql 203 AS $$ 204 BEGIN 205 IF EXISTS ( 206 SELECT 1 207 FROM pg_event_trigger_ddl_commands() AS ev 208 JOIN pg_extension AS ext 209 ON ev.objid = ext.oid 210 WHERE ext.extname = 'pg_net' 211 ) 212 THEN 213 GRANT USAGE ON SCHEMA net TO supabase_functions_admin, postgres, anon, authenticated, service_role; 214 215 ALTER function net.http_get(url text, params jsonb, headers jsonb, timeout_milliseconds integer) SECURITY DEFINER; 216 ALTER function net.http_post(url text, body jsonb, params jsonb, headers jsonb, timeout_milliseconds integer) SECURITY DEFINER; 217 218 ALTER function net.http_get(url text, params jsonb, headers jsonb, timeout_milliseconds integer) SET search_path = net; 219 ALTER function net.http_post(url text, body jsonb, params jsonb, headers jsonb, timeout_milliseconds integer) SET search_path = net; 220 221 REVOKE ALL ON FUNCTION net.http_get(url text, params jsonb, headers jsonb, timeout_milliseconds integer) FROM PUBLIC; 222 REVOKE ALL ON FUNCTION net.http_post(url text, body jsonb, params jsonb, headers jsonb, timeout_milliseconds integer) FROM PUBLIC; 223 224 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; 225 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; 226 END IF; 227 END; 228 $$; 229 COMMENT ON FUNCTION extensions.grant_pg_net_access IS 'Grants access to pg_net'; 230 231 DO 232 $$ 233 BEGIN 234 IF NOT EXISTS ( 235 SELECT 1 236 FROM pg_event_trigger 237 WHERE evtname = 'issue_pg_net_access' 238 ) THEN 239 CREATE EVENT TRIGGER issue_pg_net_access ON ddl_command_end WHEN TAG IN ('CREATE EXTENSION') 240 EXECUTE PROCEDURE extensions.grant_pg_net_access(); 241 END IF; 242 END 243 $$; 244 245 INSERT INTO supabase_functions.migrations (version) VALUES ('20210809183423_update_grants'); 246 247 ALTER function supabase_functions.http_request() SECURITY DEFINER; 248 ALTER function supabase_functions.http_request() SET search_path = supabase_functions; 249 REVOKE ALL ON FUNCTION supabase_functions.http_request() FROM PUBLIC; 250 GRANT EXECUTE ON FUNCTION supabase_functions.http_request() TO postgres, anon, authenticated, service_role; 251 252 COMMIT;