Can't revoke a role membership granted by supabase_admin

[supabase-troubleshoot-docs-app[bot]]

Supabase grants some role memberships as supabase_admin, and the postgres role can't revoke them. You see one of the following:

• revoke my_role from postgres completes, but the membership stays.
• revoke ... granted by supabase_admin fails with permission denied to revoke privileges granted by role "supabase_admin".
• drop role my_role fails with permission denied to drop role.

Postgres reports the skipped revoke as a warning in your Postgres logs, not as an error. This page helps you find who granted a membership and work with the roles you create.

If a role you created needs access to Auth data, see Permission errors when granting custom roles access to the auth schema.

Overview

In Postgres, a role can revoke only the privileges and memberships that it granted. For more information about grantors and revoking, see the Postgres documentation on REVOKE.

The postgres role isn't a superuser. It can't become supabase_admin or the other Supabase administrator roles, because only a superuser can grant membership in them. As a result, postgres can't act as these roles to change what they granted.

Postgres records the grantor of each role membership. On Postgres 16 or later, when postgres creates a role, Postgres grants postgres an admin membership in the new role. The bootstrap superuser, supabase_admin, is recorded as the grantor. The memberships that Supabase grants to its own roles and to postgres also have supabase_admin as the grantor.

The admin membership in roles you create

Every role you create gets this admin membership, and you can't remove it. The membership has these properties:

• It lets postgres drop the role and grant it to other roles. Without it, only a superuser could do that.
• It has inherit and set turned off. postgres doesn't receive the role's privileges, and it can run set role to the role only after it grants itself the set option.
• Other roles can act as your role only if you grant it to them.

Memberships that Supabase grants

Supabase grants postgres membership in anon, authenticated, service_role, authenticator, pg_read_all_data, pg_monitor, pg_signal_backend, and other roles. The Data API connects as authenticator and switches to anon, authenticated, or service_role for each request, so authenticator is a member of those roles.

When you run the Supabase CLI without a database password, Supabase creates a temporary cli_login_postgres role that is a member of postgres. To remove it, see 'Permission denied' when deleting the 'cli_login_postgres' role.

Statements that don't work

• revoke admin option for my_role from postgres completes without changing anything, because supabase_admin granted the admin membership. If your migrations contain this statement, remove it.
• grant ... granted by supabase_admin fails with permission denied to grant privileges as role "supabase_admin".

Diagnose the problem

List who granted a role's memberships

To list the memberships of a role and who granted each one, replace my_role in the following query.

Run the following SQL using the SQL Editor in the Supabase Dashboard or with psql over a direct connection. To connect with psql, see Direct connection.

select
  m.roleid::regrole as role,
  m.member::regrole as member,
  m.grantor::regrole as grantor,
  m.admin_option,
  m.inherit_option,
  m.set_option
from pg_auth_members m
where m.roleid = 'my_role'::regrole
   or m.member = 'my_role'::regrole;

Find roles that postgres can't drop or grant

If postgres doesn't have the admin option on a role, it can't drop or grant the role. A grant fails with permission denied to grant role "my_role", and drop role my_role fails with:

ERROR:  42501: permission denied to drop role
DETAIL:  Only roles with the CREATEROLE attribute and the ADMIN option on role "my_role" may drop this role.

The following query lists the roles, other than the Supabase roles, that postgres can't drop or grant.

Run the following SQL using the SQL Editor in the Supabase Dashboard or with psql over a direct connection. To connect with psql, see Direct connection.

select r.rolname
from pg_roles as r
where
  r.rolname not like 'pg\_%'
  and r.rolname not like 'supabase\_%'
  and r.rolname not like 'pgsodium\_%'
  and r.rolname not like 'cli\_login\_%'
  and r.rolname not in (
    'postgres',
    'anon',
    'authenticated',
    'authenticator',
    'service_role',
    'dashboard_user',
    'pgbouncer',
    'pgtle_admin'
  )
  and not pg_has_role('postgres', r.oid, 'member with admin option');

Read the output

• In the first query, if grantor is supabase_admin, postgres can't revoke that membership.
• The second query lists roles that postgres can't drop or grant. You can still stop clients from connecting as them. See Stop clients from connecting as a role.

Solve the problem

Change an object's owner to a role you create

To change an object's owner to your role, postgres must be able to run set role to it, and your role needs create on the object's schema. Otherwise, alter ... owner to fails with must be able to SET ROLE "my_role" or permission denied for schema.

The following SQL grants both for the change only, then removes them.

Run the following SQL using the SQL Editor in the Supabase Dashboard or with psql over a direct connection. To connect with psql, see Direct connection.

grant create on schema private to my_role;
grant my_role to postgres with set true, inherit false;
alter function private.my_function() owner to my_role;
revoke my_role from postgres;
revoke create on schema private from my_role;

The first revoke removes only the membership that postgres granted. The admin membership granted by supabase_admin stays, with set turned off.

Re-create a membership you removed

To re-create a membership, grant it again with the options it had.

Run the following SQL using the SQL Editor in the Supabase Dashboard or with psql over a direct connection. To connect with psql, see Direct connection.

grant my_role to other_role with admin false, inherit false, set true;

Postgres records postgres as the grantor. You can't record supabase_admin as the grantor. The grantor doesn't change what the membership allows. It decides which role can revoke the membership.

Stop clients from connecting as a role

postgres can run alter role on any role you create, including roles it can't drop or grant.

To stop clients from connecting as a role, set it to nologin.

Run the following SQL using the SQL Editor in the Supabase Dashboard or with psql over a direct connection. To connect with psql, see Direct connection.

alter role my_role nologin;

Give services their own roles

To limit what a service or person can do, give them their own role with only the grants they need. Keep the postgres credentials for administration.

Confirm the problem is solved

After you change an owner, check what postgres can do with the role.

Run the following SQL using the SQL Editor in the Supabase Dashboard or with psql over a direct connection. To connect with psql, see Direct connection.

select
  pg_has_role('postgres', 'my_role', 'set') as can_set_role,
  pg_has_role('postgres', 'my_role', 'usage') as has_privileges;

Both values are false after the revoke.

After you re-create a membership, run the query in List who granted a role's memberships. The grantor of the new membership is postgres.

Related topics

• Custom role inherits privileges that were not explicitly granted
• Can't change the default privileges of supabase_admin in the public schema
• How to delete a role in Postgres
• Postgres roles