Supabase hosted PostgreSQL 17: Cannot GRANT existing role membership — ADMIN OPTION required #52395
Replies: 6 comments 1 reply
|
where is this atlas_original_runtime role coming from? is it from an extension or did you create it yoursel? why doesn't postgres have admin options on the role if you created it? So you tried to run |
|
Thank you for responding.
Our verified PostgreSQL 17.6 environment shows:
We have not conclusively established which administrative identity originally created the runtime role. We also have not executed the exact Before attempting a production role change, can you confirm whether PostgreSQL 17 requires Our objective is to preserve the existing restricted privileges without elevating the application login or changing unrelated production roles. Thank you for your guidance. |
|
yes,
good call holding off on production. i'd check the current memberships first: select pg_get_userbyid(m.member) as member,
pg_get_userbyid(m.grantor) as granted_by,
m.admin_option, m.inherit_option, m.set_option
from pg_auth_members as m
join pg_roles as r on r.oid = m.roleid
where r.rolname = 'atlas_original_runtime';these are direct memberships. if no administrator with that authority is accessible to you, i'd add those rows to SU-521743 and ask whether there's a supported way to grant the membership on hosted supabase |
|
Nicholas, thank you for your help. I ran the read-only query you recommended. Result: Success. No rows returned. I also verified the following directly in PostgreSQL 17.6:
Our existing Supabase support request is SU-521743, but we have not received a technical resolution. Given these verified results, is there any supported, safe procedure we can execute using our existing Or does this conclusively require Supabase administrative intervention? We want to preserve our production environment and avoid any privilege escalation or unnecessary changes. I appreciate your guidance. |
|
On PG16+ you need ADMIN OPTION on a role to grant membership in it; select m.rolname member, g.rolname grantor, am.admin_option
from pg_auth_members am
join pg_roles m on m.oid = am.member
join pg_roles g on g.oid = am.grantor
where am.roleid = 'atlas_original_runtime'::regrole;Two ways forward:
|
|
The blocker is real, and on a hosted project there are exactly three paths, in this order. Why it fails
Confirm which situation you are in before changing anything: -- who administers the runtime role?
select r.rolname as role, m.rolname as admin_of, a.rolname as member,
am.admin_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 a on a.oid = am.member
where r.rolname = 'atlas_original_runtime'
or m.rolname = 'atlas_original_runtime';
-- attributes of the roles involved
select rolname, rolsuper, rolcreaterole, rolbypassrls
from pg_roles
where rolname in ('postgres', 'atlas_original_runtime', 'supabase_admin');If Path 1 — recreate the runtime role under your own administrator (cleanest, no ticket needed) If the role has no live dependencies yet — which your description implies, since the application is "not yet activated" — drop and recreate it under -- take a dump of the role's current privileges first
select * from information_schema.role_table_grants
where grantee = 'atlas_original_runtime'
order by table_name, privilege_type;
-- also check defaults: select * from pg_default_acl where defaclrole = 'atlas_original_runtime'::regrole;
drop role if exists atlas_original_runtime; -- fails if dependencies exist; that is the signal to use Path 2
create role atlas_original_runtime nologin;
-- re-apply the captured grants here
grant atlas_original_runtime to atlas_original_app; -- now succeeds: you hold ADMIN OPTIONKeep the membership grant at the end. Re-applying grants before the membership exists is the common ordering mistake — it works, but you test the wrong thing. Path 2 — grant privileges directly to the application login, skip role inheritance Supported with no superuser and no create role atlas_original_app login password '...' noinherit; -- noinherit: no inherited role chain to fix later
grant usage on schema public to atlas_original_app;
grant select, insert, update, delete on all tables in schema public to atlas_original_app;
grant usage, select on all sequences in schema public to atlas_original_app;
alter default privileges in schema public grant select, insert, update, delete on tables to atlas_original_app;
-- and re-grant on each migration, or own the default privileges permanently:
-- alter default privileges for role postgres in schema public grant ...The auditability cost is real: privileges are attached to the app role rather than layered behind a named runtime role, so If you want the inheritance shape without the dependency, create a fresh pair under Path 3 — Supabase support For membership grants on Supabase-managed roles, support is the escalation path; What not to do: do not try to work around this by making Verification before you unblock deploy: after Path 1 or 2, connect as |
Uh oh!
There was an error while loading. Please reload this page.
We need guidance on the supported way to configure least-privilege PostgreSQL role membership in a Supabase-hosted PostgreSQL 17 database.
Environment
atlas_original_runtimeatlas_original_app(not yet installed)postgres, withCREATEROLEbut withoutSUPERUSERsupabase_adminrole exists but is not directly accessibleVerified blocker
Our application requires a dedicated login to inherit the privileges of an existing restricted runtime role.
The available
postgresadministrator does not haveADMIN OPTIONonatlas_original_runtime, preventing the required membership grant.We have not changed production permissions or activated the application.
Questions
We are not requesting superuser access, disabling security controls, or bypassing role authorization.
This is blocking deployment of an existing financial application's restricted database connection.
We have an existing support request, SU-521743, and would appreciate technical guidance on the supported resolution.
Thank you.
All reactions