Managed Supabase: How to safely harden supabase_admin default privileges without superuser access? #52237
Replies: 3 comments 5 replies
|
The key distinction is between default privileges and schema-level privileges. On hosted Supabase, postgres is not a superuser, and supabase_admin is an internal platform role. Supabase's documentation describes default privileges for API roles and recommends revoking defaults for objects created by postgres; it does not provide customers with unrestricted access to internal roles' default ACLs. For a fresh installation, I would avoid making the migration depend on changing supabase_admin defaults. Instead:
Supabase's Data API setting for disabling automatic exposure is useful, but its documentation notes that some non-Data-API privileges, including TRUNCATE, REFERENCES, and TRIGGER, may remain in default ACLs. I would confirm the exact supported behavior with Supabase maintainers before relying on a provider-assisted change to internal defaults. |
|
Adding a concrete step to bytesbydev's point about checking effective privileges separately from RLS: before the ERP install, keep a read-only baseline of ownership and RLS status in public. SELECT n.nspname AS schema_name, c.relname,
pg_get_userbyid(c.relowner) AS owner,
c.relrowsecurity AS rls_enabled
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = 'public'
AND c.relkind IN ('r', 'p')
ORDER BY c.relname;Re-run it after migrations or platform operations and diff the output. It shows ownership and whether RLS is on; it doesn't prove the policies are correct. Supabase documents default grants separately from row policies: On whether a managed operation could create tables in public as supabase_admin, I don't know of a documented guarantee either way, so waiting for a maintainer to confirm makes sense. |
|
Supabase has a KB article that answers the open question: https://supabase.com/kb/troubleshooting/cant-change-the-default-privileges-of-supabase_admin-in-the-public-schema Short version:
So your plan is the supported approach. For the alter default privileges for role postgres in schema public
revoke truncate, references, trigger, maintain on tables from anon, authenticated;
-- the PUBLIC execute default for functions can only be removed without IN SCHEMA
alter default privileges for role postgres revoke execute on functions from public;( select relname, relkind from pg_class
where relnamespace = 'public'::regnamespace
and relowner = 'supabase_admin'::regrole;Then grant each table only what it needs and verify with |
Uh oh!
There was an error while loading. Please reload this page.
Hi Supabase community,
We are migrating an existing school ERP application to a fresh, independently managed Supabase project.
Our database migrations include a security-hardening step that removes potentially dangerous table privileges (TRUNCATE, TRIGGER, and REFERENCES) from the anon and authenticated roles.
We have encountered a permissions issue involving the internal supabase_admin role.
Environment
Hosted Supabase project (Mumbai region)
Fresh database with no ERP application tables installed
SQL Editor executing as postgres
postgres is not a superuser
postgres is not a member of supabase_admin and cannot SET ROLE to it
Data API enabled
Automatic exposure of new tables disabled
The issue
Our migration includes an operation equivalent to:
ALTER DEFAULT PRIVILEGES
FOR ROLE supabase_admin
IN SCHEMA public
REVOKE ALL PRIVILEGES ON TABLES
FROM PUBLIC, anon, authenticated;
Read-only catalog checks show that supabase_admin has schema-local default table privileges in public granting broad privileges to anon and authenticated.
However, our postgres executor does not have authority to modify default privileges for supabase_admin.
The migration therefore cannot be executed unchanged under the observed customer role permissions.
We understand that default privileges apply according to the actual object-creating role. We also understand that Row Level Security does not protect against operations such as TRUNCATE.
Questions
Is there an officially supported way for a Supabase customer to modify supabase_admin default table privileges in the public schema?
If customers cannot modify these internal-role defaults, is there a supported provider-assisted process for applying this specific hardening operation?
Is it expected that supabase_admin retains broad default table privileges for anon and authenticated even when automatic exposure of new tables is disabled?
For a fresh installation where all ERP tables are created by the customer-controlled postgres role, is the recommended approach to:
Restrict default privileges for the customer-controlled creating role;
Explicitly revoke dangerous privileges on existing application tables;
Deny client roles CREATE privileges on the application schema;
Verify effective permissions and RLS; and
Prevent application migrations from running under Supabase-managed internal roles?
Are there any additional Supabase-specific security or operational considerations when implementing this approach, particularly around Auth and Storage?
What we have already tested
We developed a local PostgreSQL compatibility candidate using a restricted, non-superuser execution role.
Local tests pass, including permission checks, migration recovery, role-based authorization and financial-integrity tests.
However, the candidate deliberately leaves supabase_admin defaults unchanged, meaning it is not fully equivalent to our original security-hardening migration.
We have not executed the candidate or any ERP migrations against the hosted Supabase project.
We would appreciate guidance on the recommended, officially supported approach for this situation.
Thank you.
All reactions