PostgreSQL 17 / Supabase — CREATEROLE automatic ADMIN membership blocking migration #52069
Replies: 3 comments
|
This is expected PostgreSQL 17 behavior, including for When a non-superuser with Setting So ordinary I would not simply exempt the automatic Supabase does not provide hosted customers with superuser access. Whether Supabase offers an approved provisioning route for your requirement needs confirmation through your existing support ticket. The PostgreSQL documentation establishes the limitation, but does not establish a supported Supabase-hosted workaround. |
|
Here is the exact explanation of PostgreSQL 17's revised 1. The PostgreSQL 16/17
|
|
A correction, since the two answers above contradict each other. The first one is right. PostgreSQL 17 docs (https://www.postgresql.org/docs/17/role-attributes.html): the created role is granted back "just as if the bootstrap superuser had executed" So it isn't INHERIT TRUE / SET TRUE, and a REVOKE run as select r.rolname as role, m.rolname as member, g.rolname as grantor,
am.admin_option, am.inherit_option, am.set_option
from pg_auth_members am
join pg_roles r on r.oid = am.roleid
join pg_roles m on m.oid = am.member
join pg_roles g on g.oid = am.grantor
where r.rolname = 'trucking_importer_owner';If your checker needs zero memberships, it has to allow this one row (bootstrap superuser grantor, admin yes, inherit/set no), or it will never pass on hosted Supabase. |
Uh oh!
There was an error while loading. Please reload this page.
We're deploying a trucking-data import application on Supabase PostgreSQL 17.11.
Our migration provisions dedicated NOLOGIN owner roles and importer/API roles with strict role-membership isolation.
Problem:
When the staging postgres administrator creates these roles, PostgreSQL 17 automatically grants the creator ADMIN membership.
Our security checks require certain dedicated roles to have zero incoming or outgoing memberships, so the migration fails with:
permission candidate requires all four dedicated roles to have zero membership edges
We confirmed that:
The staging administrator has CREATEROLE but is not a superuser.
The failed migration rolled back completely.
Attempts to revoke bootstrap-superuser-issued memberships may be restricted.
Removing the security guard would violate our intended isolation requirements.
Questions:
Is there a supported Supabase-hosted method to create NOLOGIN owner roles without retaining automatic creator ADMIN memberships?
Can the required memberships be removed without superuser access?
Is there a recommended least-privilege alternative for custom SECURITY DEFINER functions and dedicated importer roles on PostgreSQL 17?
We're looking for a supported deployment approach, not a way to bypass Supabase security restrictions.
We have an open support ticket, SU-517677.
Thank you for any guidance.
All reactions