supabase-postgres.git / summary / log / commit / refs
commit 950efd97dbc3c099c3d7b080350c3ee310acba96
Author: Bobbie Soedirgo <31685197+soedirgo@users.noreply.github.com>
Commit: GitHub <noreply@github.com>
Date: Fri Feb 27 13:54:14 2026 +0000
feat: add `supabase_privileged_role` (#2040)
* feat/rename-supabase-privileged-role
* chore: bump version
ansible/files/postgresql_config/supautils.conf.j2 | 2 +-
ansible/vars.yml | 2 +-
.../20260211120934_supabase_privileged_role.sql | 10 ++
nix/tests/expected/roles.out | 6 +-
nix/tests/expected/z_15_roles.out | 50 +++++-----
nix/tests/expected/z_17_roles.out | 106 +++++++++++----------
6 files changed, 97 insertions(+), 79 deletions(-)
diff --git a/ansible/files/postgresql_config/supautils.conf.j2 b/ansible/files/postgresql_config/supautils.conf.j2
index f68bb82d..e23ab99b 100644
--- a/ansible/files/postgresql_config/supautils.conf.j2
+++ b/ansible/files/postgresql_config/supautils.conf.j2
@@ -9,7 +9,7 @@ supautils.drop_trigger_grants = '{"postgres":["auth.audit_log_entries","auth.flo
supautils.privileged_extensions = 'address_standardizer, address_standardizer_data_us, autoinc, bloom, btree_gin, btree_gist, citext, cube, dblink, dict_int, dict_xsyn, earthdistance, fuzzystrmatch, hstore, http, hypopg, index_advisor, insert_username, intarray, isn, ltree, moddatetime, orioledb, pg_buffercache, pg_cron, pg_graphql, pg_hashids, pg_jsonschema, pg_net, pg_prewarm, pg_repack, pg_stat_monitor, pg_stat_statements, pg_tle, pg_trgm, pg_walinspect, pgaudit, pgcrypto, pgjwt, pgroonga, pgroonga_database, pgrouting, pgrowlocks, pgsodium, pgstattuple, pgtap, plcoffee, pljava, plls, plpgsql_check, plv8, postgis, postgis_raster, postgis_sfcgal, postgis_tiger_geocoder, postgis_topology, postgres_fdw, refint, rum, seg, sslinfo, supabase_vault, supautils, tablefunc, tcn, timescaledb, tsm_system_rows, tsm_system_time, unaccent, uuid-ossp, vector, wrappers'
supautils.extension_custom_scripts_path = '/etc/postgresql-custom/extension-custom-scripts'
supautils.privileged_extensions_superuser = 'supabase_admin'
-supautils.privileged_role = 'postgres'
+supautils.privileged_role = 'supabase_privileged_role'
supautils.privileged_role_allowed_configs = 'auto_explain.*, deadlock_timeout, log_lock_waits, log_min_duration_statement, log_min_messages, log_parameter_max_length, log_replication_commands, log_statement, log_temp_files, pg_net.batch_size, pg_net.ttl, pg_stat_statements.*, pgaudit.log, pgaudit.log_catalog, pgaudit.log_client, pgaudit.log_level, pgaudit.log_relation, pgaudit.log_rows, pgaudit.log_statement, pgaudit.log_statement_once, pgaudit.role, pgrst.*, plan_filter.*, safeupdate.enabled, session_replication_role, track_functions, track_io_timing, wal_compression'
supautils.reserved_memberships = 'pg_read_server_files, pg_write_server_files, pg_execute_server_program, supabase_admin, supabase_auth_admin, supabase_storage_admin, supabase_read_only_user, supabase_realtime_admin, supabase_replication_admin, supabase_etl_admin, dashboard_user, pgbouncer, authenticator'
supautils.reserved_roles = 'supabase_admin, supabase_auth_admin, supabase_storage_admin, supabase_read_only_user, supabase_realtime_admin, supabase_replication_admin, supabase_etl_admin, dashboard_user, pgbouncer, service_role*, authenticator*, authenticated*, anon*'
diff --git a/ansible/vars.yml b/ansible/vars.yml
index b7ae37b9..ae96bbdf 100644
--- a/ansible/vars.yml
+++ b/ansible/vars.yml
@@ -18,7 +18,7 @@ postgres_release:
pgbouncer_release: 1.25.1
pgbouncer_release_checksum: sha256:6e566ae92fe3ef7f6a1b9e26d6049f7d7ca39c40e29e7b38f6d5500ae15d8465
-# The checksum can be found under "Assets", in the GitHub release page for each version.
+# The checksum can be found under "Assets", in the GitHub release page for each version.
# The binaries used are: ubuntu-aarch64 and linux-static.
# https://github.com/PostgREST/postgrest/releases
postgrest_release: 14.5
diff --git a/migrations/db/migrations/20260211120934_supabase_privileged_role.sql b/migrations/db/migrations/20260211120934_supabase_privileged_role.sql
new file mode 100644
index 00000000..264b8ca0
--- /dev/null
+++ b/migrations/db/migrations/20260211120934_supabase_privileged_role.sql
@@ -0,0 +1,10 @@
+-- migrate:up
+do $$
+begin
+ if not exists (select from pg_roles where rolname = 'supabase_privileged_role') then
+ create role supabase_privileged_role;
+ grant supabase_privileged_role to postgres, supabase_etl_admin;
+ end if;
+end $$;
+
+-- migrate:down
diff --git a/nix/tests/expected/roles.out b/nix/tests/expected/roles.out
index 0cd94fc8..a457f402 100644
--- a/nix/tests/expected/roles.out
+++ b/nix/tests/expected/roles.out
@@ -48,10 +48,11 @@ order by rolname;
supabase_auth_admin | t | t | f | f | f | f | -1 | f |
supabase_etl_admin | f | t | f | t | f | t | -1 | t |
supabase_functions_admin | t | t | f | f | f | f | -1 | f |
+ supabase_privileged_role | f | f | f | t | f | f | -1 | f |
supabase_read_only_user | f | t | f | t | f | f | -1 | t |
supabase_replication_admin | f | t | f | t | f | t | -1 | f |
supabase_storage_admin | t | t | f | f | f | f | -1 | f |
-(30 rows)
+(31 rows)
select
rolname,
@@ -88,10 +89,11 @@ order by rolname;
supabase_auth_admin | {search_path=auth,idle_in_transaction_session_timeout=60000,log_statement=none}
supabase_etl_admin |
supabase_functions_admin |
+ supabase_privileged_role |
supabase_read_only_user | {default_transaction_read_only=on}
supabase_replication_admin |
supabase_storage_admin | {search_path=storage,log_statement=none}
-(30 rows)
+(31 rows)
-- Check all privileges of the roles on the schemas
select schema_name, privilege_type, grantee, default_for
diff --git a/nix/tests/expected/z_15_roles.out b/nix/tests/expected/z_15_roles.out
index 1f967bd9..a051ab1d 100644
--- a/nix/tests/expected/z_15_roles.out
+++ b/nix/tests/expected/z_15_roles.out
@@ -11,30 +11,32 @@ left join
pg_roles g on m.roleid = g.oid
order by
r.rolname, g.rolname;
- member | member_of (can become) | admin_option
--------------------------+------------------------+--------------
- authenticator | anon | f
- authenticator | authenticated | f
- authenticator | service_role | f
- pg_monitor | pg_read_all_settings | f
- pg_monitor | pg_read_all_stats | f
- pg_monitor | pg_stat_scan_tables | f
- pgsodium_keyholder | pgsodium_keyiduser | f
- pgsodium_keymaker | pgsodium_keyholder | f
- pgsodium_keymaker | pgsodium_keyiduser | f
- postgres | anon | f
- postgres | authenticated | f
- postgres | pg_monitor | f
- postgres | pg_read_all_data | f
- postgres | pg_signal_backend | f
- postgres | pgtle_admin | f
- postgres | service_role | f
- supabase_etl_admin | pg_monitor | f
- supabase_etl_admin | pg_read_all_data | f
- supabase_read_only_user | pg_monitor | f
- supabase_read_only_user | pg_read_all_data | f
- supabase_storage_admin | authenticator | f
-(21 rows)
+ member | member_of (can become) | admin_option
+-------------------------+--------------------------+--------------
+ authenticator | anon | f
+ authenticator | authenticated | f
+ authenticator | service_role | f
+ pg_monitor | pg_read_all_settings | f
+ pg_monitor | pg_read_all_stats | f
+ pg_monitor | pg_stat_scan_tables | f
+ pgsodium_keyholder | pgsodium_keyiduser | f
+ pgsodium_keymaker | pgsodium_keyholder | f
+ pgsodium_keymaker | pgsodium_keyiduser | f
+ postgres | anon | f
+ postgres | authenticated | f
+ postgres | pg_monitor | f
+ postgres | pg_read_all_data | f
+ postgres | pg_signal_backend | f
+ postgres | pgtle_admin | f
+ postgres | service_role | f
+ postgres | supabase_privileged_role | f
+ supabase_etl_admin | pg_monitor | f
+ supabase_etl_admin | pg_read_all_data | f
+ supabase_etl_admin | supabase_privileged_role | f
+ supabase_read_only_user | pg_monitor | f
+ supabase_read_only_user | pg_read_all_data | f
+ supabase_storage_admin | authenticator | f
+(23 rows)
-- Check all privileges of non-superuser roles on functions
select
diff --git a/nix/tests/expected/z_17_roles.out b/nix/tests/expected/z_17_roles.out
index 5f598da1..e7ee4807 100644
--- a/nix/tests/expected/z_17_roles.out
+++ b/nix/tests/expected/z_17_roles.out
@@ -46,32 +46,34 @@ left join
pg_roles g on m.roleid = g.oid
order by
r.rolname, g.rolname;
- member | member_of (can become) | admin_option
--------------------------+------------------------+--------------
- authenticator | anon | f
- authenticator | authenticated | f
- authenticator | service_role | f
- pg_monitor | pg_read_all_settings | f
- pg_monitor | pg_read_all_stats | f
- pg_monitor | pg_stat_scan_tables | f
- pgsodium_keyholder | pgsodium_keyiduser | f
- pgsodium_keymaker | pgsodium_keyholder | f
- pgsodium_keymaker | pgsodium_keyiduser | f
- postgres | anon | t
- postgres | authenticated | t
- postgres | authenticator | t
- postgres | pg_create_subscription | t
- postgres | pg_monitor | t
- postgres | pg_read_all_data | t
- postgres | pg_signal_backend | t
- postgres | pgtle_admin | f
- postgres | service_role | t
- supabase_etl_admin | pg_monitor | f
- supabase_etl_admin | pg_read_all_data | f
- supabase_read_only_user | pg_monitor | f
- supabase_read_only_user | pg_read_all_data | f
- supabase_storage_admin | authenticator | f
-(23 rows)
+ member | member_of (can become) | admin_option
+-------------------------+--------------------------+--------------
+ authenticator | anon | f
+ authenticator | authenticated | f
+ authenticator | service_role | f
+ pg_monitor | pg_read_all_settings | f
+ pg_monitor | pg_read_all_stats | f
+ pg_monitor | pg_stat_scan_tables | f
+ pgsodium_keyholder | pgsodium_keyiduser | f
+ pgsodium_keymaker | pgsodium_keyholder | f
+ pgsodium_keymaker | pgsodium_keyiduser | f
+ postgres | anon | t
+ postgres | authenticated | t
+ postgres | authenticator | t
+ postgres | pg_create_subscription | t
+ postgres | pg_monitor | t
+ postgres | pg_read_all_data | t
+ postgres | pg_signal_backend | t
+ postgres | pgtle_admin | f
+ postgres | service_role | t
+ postgres | supabase_privileged_role | f
+ supabase_etl_admin | pg_monitor | f
+ supabase_etl_admin | pg_read_all_data | f
+ supabase_etl_admin | supabase_privileged_role | f
+ supabase_read_only_user | pg_monitor | f
+ supabase_read_only_user | pg_read_all_data | f
+ supabase_storage_admin | authenticator | f
+(25 rows)
-- Check version-specific privileges of the roles on the schemas
select schema_name, privilege_type, grantee, default_for
@@ -141,31 +143,33 @@ where r.rolname not in ('pg_create_subscription', 'pg_maintain', 'pg_use_reserve
and g.rolname not in ('pg_create_subscription', 'pg_maintain', 'pg_use_reserved_connections')
order by
r.rolname, g.rolname;
- member | member_of (can become) | admin_option
--------------------------+------------------------+--------------
- authenticator | anon | f
- authenticator | authenticated | f
- authenticator | service_role | f
- pg_monitor | pg_read_all_settings | f
- pg_monitor | pg_read_all_stats | f
- pg_monitor | pg_stat_scan_tables | f
- pgsodium_keyholder | pgsodium_keyiduser | f
- pgsodium_keymaker | pgsodium_keyholder | f
- pgsodium_keymaker | pgsodium_keyiduser | f
- postgres | anon | t
- postgres | authenticated | t
- postgres | authenticator | t
- postgres | pg_monitor | t
- postgres | pg_read_all_data | t
- postgres | pg_signal_backend | t
- postgres | pgtle_admin | f
- postgres | service_role | t
- supabase_etl_admin | pg_monitor | f
- supabase_etl_admin | pg_read_all_data | f
- supabase_read_only_user | pg_monitor | f
- supabase_read_only_user | pg_read_all_data | f
- supabase_storage_admin | authenticator | f
-(22 rows)
+ member | member_of (can become) | admin_option
+-------------------------+--------------------------+--------------
+ authenticator | anon | f
+ authenticator | authenticated | f
+ authenticator | service_role | f
+ pg_monitor | pg_read_all_settings | f
+ pg_monitor | pg_read_all_stats | f
+ pg_monitor | pg_stat_scan_tables | f
+ pgsodium_keyholder | pgsodium_keyiduser | f
+ pgsodium_keymaker | pgsodium_keyholder | f
+ pgsodium_keymaker | pgsodium_keyiduser | f
+ postgres | anon | t
+ postgres | authenticated | t
+ postgres | authenticator | t
+ postgres | pg_monitor | t
+ postgres | pg_read_all_data | t
+ postgres | pg_signal_backend | t
+ postgres | pgtle_admin | f
+ postgres | service_role | t
+ postgres | supabase_privileged_role | f
+ supabase_etl_admin | pg_monitor | f
+ supabase_etl_admin | pg_read_all_data | f
+ supabase_etl_admin | supabase_privileged_role | f
+ supabase_read_only_user | pg_monitor | f
+ supabase_read_only_user | pg_read_all_data | f
+ supabase_storage_admin | authenticator | f
+(24 rows)
-- Check all privileges of non-superuser roles on functions
select
[parent: 70368d2e84af]