supabase-postgres.git / summary / log / commit / refs

commit    52ac42ed2c5daa60425604d6bd17cf732575e916
Author:   Utkarash Kumar Singh <utkarash2991@users.noreply.github.com>
Commit:   GitHub <noreply@github.com>
Date:     Wed May 13 09:32:51 2026 +0000

    fix: prevent non-superuser roles from dropping supabase_privileged_role (#2150)
    
    Add supabase_privileged_role to supautils.reserved_roles so non-superusers
    with CREATEROLE cannot drop it on PG 15, where PG's native ADMIN OPTION
    check (introduced in PG 16) does not apply.
    
    PG 16+ requires ADMIN OPTION to drop a non-superuser role:
    https://github.com/postgres/postgres/blame/REL_16_STABLE/src/backend/commands/user.c#L1175
    
    This check is absent in PG 15:
    https://github.com/postgres/postgres/blob/REL_15_STABLE/src/backend/commands/user.c#L986
    
    Fixes: https://linear.app/supabase/issue/PSQL-1205


 ansible/files/postgresql_config/supautils.conf.j2 |  2 +-
 nix/tests/expected/supautils_reserved_roles.out   | 17 +++++++++++++++++
 nix/tests/sql/supautils_reserved_roles.sql        | 16 ++++++++++++++++
 3 files changed, 34 insertions(+), 1 deletion(-)

diff --git a/ansible/files/postgresql_config/supautils.conf.j2 b/ansible/files/postgresql_config/supautils.conf.j2
index 98409054..bbfd61ce 100644
--- a/ansible/files/postgresql_config/supautils.conf.j2
+++ b/ansible/files/postgresql_config/supautils.conf.j2
@@ -12,5 +12,5 @@ supautils.privileged_extensions_superuser = 'supabase_admin'
 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*'
+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*, supabase_privileged_role'
 supautils.hint_roles = 'anon, authenticated, service_role'
diff --git a/nix/tests/expected/supautils_reserved_roles.out b/nix/tests/expected/supautils_reserved_roles.out
new file mode 100644
index 00000000..0efb85f7
--- /dev/null
+++ b/nix/tests/expected/supautils_reserved_roles.out
@@ -0,0 +1,17 @@
+-- verify non-superuser postgres role cannot drop supabase_privileged_role
+BEGIN;
+-- Switch to the postgres role (non-superuser) to test supautils behavior
+SET ROLE postgres;
+-- SAVEPOINT is needed to recover from the expected error and continue the transaction
+SAVEPOINT before_drop;
+DROP ROLE supabase_privileged_role;
+ERROR:  "supabase_privileged_role" is a reserved role, only superusers can modify it
+ROLLBACK TO SAVEPOINT before_drop;
+RESET ROLE;
+SELECT rolname FROM pg_roles WHERE rolname = 'supabase_privileged_role';
+         rolname          
+--------------------------
+ supabase_privileged_role
+(1 row)
+
+ROLLBACK;
diff --git a/nix/tests/sql/supautils_reserved_roles.sql b/nix/tests/sql/supautils_reserved_roles.sql
new file mode 100644
index 00000000..710fc82c
--- /dev/null
+++ b/nix/tests/sql/supautils_reserved_roles.sql
@@ -0,0 +1,16 @@
+-- verify non-superuser postgres role cannot drop supabase_privileged_role
+BEGIN;
+
+-- Switch to the postgres role (non-superuser) to test supautils behavior
+SET ROLE postgres;
+
+-- SAVEPOINT is needed to recover from the expected error and continue the transaction
+SAVEPOINT before_drop;
+DROP ROLE supabase_privileged_role;
+ROLLBACK TO SAVEPOINT before_drop;
+
+RESET ROLE;
+
+SELECT rolname FROM pg_roles WHERE rolname = 'supabase_privileged_role';
+
+ROLLBACK;


[parent: 60c9ee9a56f1]