supabase-postgres.git / summary / log / commit / refs
commit fc5921822cfc175ca4de8eb0d62588cc40f514f3
Author: Bobbie Soedirgo <bobbie@soedirgo.dev>
Commit: Bobbie Soedirgo <31685197+soedirgo@users.noreply.github.com>
Date: Thu Feb 20 06:43:52 2025 +0000
fix: remove pg_net grants
ansible/vars.yml | 4 +-
.../migrations/20250220051611_pg_net_perms_fix.sql | 64 ++++++++++++++++++++++
2 files changed, 66 insertions(+), 2 deletions(-)
diff --git a/ansible/vars.yml b/ansible/vars.yml
index 6d3d096b..cee8b8cf 100644
--- a/ansible/vars.yml
+++ b/ansible/vars.yml
@@ -8,8 +8,8 @@ postgres_major:
# Full version strings for each major version
postgres_release:
- postgresorioledb-17: "17.0.1.036-orioledb"
- postgres15: "15.8.1.040"
+ postgresorioledb-17: "17.0.1.037-orioledb"
+ postgres15: "15.8.1.041"
# Non Postgres Extensions
pgbouncer_release: "1.19.0"
diff --git a/migrations/db/migrations/20250220051611_pg_net_perms_fix.sql b/migrations/db/migrations/20250220051611_pg_net_perms_fix.sql
new file mode 100644
index 00000000..aac7c8ea
--- /dev/null
+++ b/migrations/db/migrations/20250220051611_pg_net_perms_fix.sql
@@ -0,0 +1,64 @@
+-- migrate:up
+DO $$
+BEGIN
+ IF EXISTS (SELECT FROM pg_extension WHERE extname = 'pg_net')
+ THEN
+ CREATE OR REPLACE FUNCTION extensions.grant_pg_net_access()
+ RETURNS event_trigger
+ LANGUAGE plpgsql
+ AS $func$
+ BEGIN
+ IF EXISTS (
+ SELECT 1
+ FROM pg_event_trigger_ddl_commands() AS ev
+ JOIN pg_extension AS ext
+ ON ev.objid = ext.oid
+ WHERE ext.extname = 'pg_net'
+ )
+ THEN
+ IF NOT EXISTS (
+ SELECT 1
+ FROM pg_roles
+ WHERE rolname = 'supabase_functions_admin'
+ )
+ THEN
+ CREATE USER supabase_functions_admin NOINHERIT CREATEROLE LOGIN NOREPLICATION;
+ END IF;
+
+ GRANT USAGE ON SCHEMA net TO supabase_functions_admin, postgres, anon, authenticated, service_role;
+
+ IF EXISTS (
+ SELECT FROM pg_extension
+ WHERE extname = 'pg_net'
+ -- all versions in use on existing projects as of 2025-02-20
+ -- version 0.12.0 onwards don't need these applied
+ AND extversion IN ('0.2', '0.6', '0.7', '0.7.1', '0.8', '0.10.0', '0.11.0')
+ ) THEN
+ ALTER function net.http_get(url text, params jsonb, headers jsonb, timeout_milliseconds integer) SECURITY DEFINER;
+ ALTER function net.http_post(url text, body jsonb, params jsonb, headers jsonb, timeout_milliseconds integer) SECURITY DEFINER;
+
+ ALTER function net.http_get(url text, params jsonb, headers jsonb, timeout_milliseconds integer) SET search_path = net;
+ ALTER function net.http_post(url text, body jsonb, params jsonb, headers jsonb, timeout_milliseconds integer) SET search_path = net;
+
+ REVOKE ALL ON FUNCTION net.http_get(url text, params jsonb, headers jsonb, timeout_milliseconds integer) FROM PUBLIC;
+ REVOKE ALL ON FUNCTION net.http_post(url text, body jsonb, params jsonb, headers jsonb, timeout_milliseconds integer) FROM PUBLIC;
+
+ 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;
+ 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;
+ END IF;
+ END IF;
+ END;
+ $func$;
+
+ ALTER function net.http_get(url text, params jsonb, headers jsonb, timeout_milliseconds integer) SECURITY INVOKER;
+ ALTER function net.http_post(url text, body jsonb, params jsonb, headers jsonb, timeout_milliseconds integer) SECURITY INVOKER;
+
+ REVOKE EXECUTE ON FUNCTION net.http_get(url text, params jsonb, headers jsonb, timeout_milliseconds integer) FROM supabase_functions_admin, postgres, anon, authenticated, service_role;
+ REVOKE EXECUTE ON FUNCTION net.http_post(url text, body jsonb, params jsonb, headers jsonb, timeout_milliseconds integer) FROM supabase_functions_admin, postgres, anon, authenticated, service_role;
+
+ GRANT ALL ON FUNCTION net.http_get(url text, params jsonb, headers jsonb, timeout_milliseconds integer) TO PUBLIC;
+ GRANT ALL ON FUNCTION net.http_post(url text, body jsonb, params jsonb, headers jsonb, timeout_milliseconds integer) TO PUBLIC;
+ END IF;
+END $$;
+
+-- migrate:down
[parent: e88ce4bdfce6]