postgres lacks ADMIN OPTION on custom NOLOGIN roles in hosted Supabase

[XinyuLuSean]

Problem

We are using a hosted Supabase project.

A migration created seven custom NOLOGIN group roles through a temporary role that had CREATEROLE:

compass_migrator
compass_enrollware_ingest
compass_ramp_sync
compass_app_runtime
compass_sandbox_writer
compass_issue_approver
compass_mapping_reviewer

The temporary creator role was dropped at the end of the migration.

The custom roles still exist, but the postgres role is not a member of them and does not have ADMIN OPTION. We therefore cannot manage their memberships or grants through the Dashboard SQL Editor.

Command that fails

GRANT compass_migrator,
      compass_enrollware_ingest,
      compass_ramp_sync,
      compass_app_runtime,
      compass_sandbox_writer,
      compass_issue_approver,
      compass_mapping_reviewer
TO postgres WITH ADMIN OPTION;

Exact error:

Error running SQL query

Failed to run sql query: ERROR:  42501: permission denied to grant role "compass_migrator"
DETAIL:  Only roles with the ADMIN option on role "compass_migrator" may grant this role.

The operation fails because the hosted Supabase postgres role does not have sufficient administrative privileges over these custom roles.

Diagnostic query

select
  granted_role.rolname as granted_role,
  member_role.rolname as member_role,
  grantor_role.rolname as grantor_role,
  m.admin_option
from pg_auth_members m
join pg_roles granted_role
  on granted_role.oid = m.roleid
join pg_roles member_role
  on member_role.oid = m.member
join pg_roles grantor_role
  on grantor_role.oid = m.grantor
where granted_role.rolname like 'compass\_%' escape '\'
order by 1, 2;

There is no usable role available to us with ADMIN OPTION on these roles.

Desired result

We need a sufficiently privileged role to execute the equivalent of:

GRANT compass_migrator,
      compass_enrollware_ingest,
      compass_ramp_sync,
      compass_app_runtime,
      compass_sandbox_writer,
      compass_issue_approver,
      compass_mapping_reviewer
TO postgres WITH ADMIN OPTION;

We do not need postgres to automatically inherit or assume these roles.

Questions

1. Is there a supported self-service recovery method on hosted Supabase?
2. Does a Supabase-managed administrative role need to restore these memberships?
3. If staff intervention is required, is a support ticket the only supported recovery path?

Support ticket already submitted: SU-439959.

The project is currently on the Free plan.

[Chetana12047]

Based on the error, PostgreSQL appears to be enforcing its normal role administration rules: only a role that already has the ADMIN OPTION (or a sufficiently privileged administrative role) can grant membership or the ADMIN OPTION on another role.

Since the temporary role that originally created these NOLOGIN roles has been dropped, it seems there is no remaining role in the project that has the required administrative privileges to manage them.

Given that, I’m wondering if this is one of those cases where there simply isn’t a SQL-only recovery path from within a hosted project. If that’s correct, it sounds like a Supabase-managed administrative role would need to restore the required memberships or ADMIN OPTION.

Since you’ve already opened support ticket SU-439959, I think that’s the right next step. Hopefully a Supabase team member can confirm whether there’s any supported self-service recovery method for hosted projects, or whether staff intervention is required in this scenario.

[XinyuLuSean]

Thanks, this matches my understanding as well.

Since the original role holding the ADMIN OPTION was dropped, it looks like there may be no remaining role available to us that can repair the memberships through normal SQL.

I’ll keep the support ticket open and update this discussion once Supabase confirms whether staff intervention is required or provides a supported recovery method.

[cekuu35]

The mechanism here is documented, and it makes the outcome unavoidable once that migration ran — worth knowing because it lets you rule out a self-service fix immediately instead of hunting for one.

Two behaviours combine.

1. The creator got ADMIN OPTION automatically. From Role Attributes → Role creation: "Such a grant occurs automatically when a CREATEROLE user that is not a superuser creates a new role, so that by default, a CREATEROLE user can alter and drop the roles which they have created." That is why the migration was able to manage the compass_* roles at the time.

2. DROP ROLE revoked exactly that. From the DROP ROLE page: "DROP ROLE automatically revokes any memberships of the target role in other roles, and of other roles in the target role."

So the only ADMIN OPTION that ever existed on those seven roles was the temporary creator's, and dropping it revoked that membership. The pg_auth_members rows are gone rather than left pointing at a dropped grantor — worth keeping in mind when reading your diagnostic, since an inner join on the grantor would hide a dangling row but here there is simply no row.

That also settles the self-service question rather than leaving it a guess. postgres has CREATEROLE, but per the same page, "a CREATEROLE user can only exercise special privileges with regard to an existing role if they have ADMIN OPTION on it." No admin option, and no superuser reachable in a hosted project, means there is no SQL path back. SU-439959 is the correct and only route.

For the rebuild — and for anyone scripting this pattern later — grant admin to a role that will outlive the migration before dropping the creator, in the same transaction:

GRANT compass_migrator, compass_enrollware_ingest, compass_ramp_sync,
      compass_app_runtime, compass_sandbox_writer, compass_issue_approver,
      compass_mapping_reviewer
  TO postgres WITH ADMIN OPTION;

DROP ROLE compass_temp_creator;

The order is the whole thing: after the DROP, nobody is left who can issue that GRANT.

[XinyuLuSean]

Thanks, this is really helpful!

That also explains why recreating the temporary role or trying SET ROLE wouldn’t help.

In my case I’ve already moved the application over to a replacement schema/migration path and deprecated the original one, so I’m no longer blocked on this.

Appreciate the detailed explanation.

[cekuu35]

Glad it helped, and good call moving to a replacement path rather than fighting it.

One small thing worth keeping the ticket open for even though you are unblocked: those seven roles are now orphaned. Nobody in the project can drop them either, because DROP ROLE needs the same ADMIN OPTION that no longer exists. They will keep showing up in \du, and any future cleanup migration that tries to remove them will fail with the same 42501. Cheap to have support delete them while the ticket is already in front of someone.

[XinyuLuSean]

Thanks for catching that! I hadn’t considered the cleanup side of it. I’ll keep the ticket open and ask support to remove those orphaned roles as well.

[cekuu35]

Glad it's unblocked — moving to a fresh schema/migration path is the pragmatic call. And +1 on keeping the ticket open for the orphaned NOLOGIN roles: even though they can't log in, they linger as grantees on objects and still show up in audits and dumps, so having support drop them keeps the role list clean. Good luck with the rest of the migration.

[PARTH1s]

From the PostgreSQL side, the error looks consistent with the fact that postgres does not have ADMIN OPTION on those custom roles. CREATEROLE by itself does not let a role grant memberships for roles it does not administer.

Since the original role that created the compass_* roles has been dropped, I don't see a self-service SQL path that would let the current postgres role recover the missing ADMIN OPTION without another sufficiently privileged role.

Given that this is a hosted Supabase project and you've already confirmed there is no remaining role with ADMIN OPTION, I would expect this to require Supabase-side intervention rather than trying to work around it with ALTER ROLE/GRANT from the SQL Editor.

Your support ticket (SU-439959) seems like the correct recovery path. I'd include the output of the pg_auth_members diagnostic query and explicitly mention that the original role which owned/administered the memberships was dropped, leaving no available role with ADMIN OPTION.

One additional thing I'd verify before support takes action is whether the dropped creator role is still referenced anywhere in the catalog:

SELECT
    r.rolname,
    r.rolcanlogin,
    r.rolcreaterole,
    r.rolsuper
FROM pg_roles r
WHERE r.rolname LIKE 'compass_%'
   OR r.rolname = 'postgres';

If none of the remaining roles has the required administration privilege, I would avoid attempting to recreate the roles with the same names, since that could make the migration/grants state even harder to reason about.

[XinyuLuSean]

This lines up with what I’m seeing as well.

Thanks for the query and I’ll include the pg_auth_members output and avoid trying to recreate the old creator role.

Appreciate the confirmation!