Hosted Supabase Migration Blocked by 42501 Permission Denied to Change Default Privileges

[tigahmadtiger-lang]

Hello Supabase team and community,

We are investigating a migration failure in our hosted Supabase Development project.

The migration fails before making any changes with:

SQLSTATE 42501: permission denied to change default privileges

Our investigation indicates that the postgres role is not a superuser and is not a member of supabase_admin. The migration requires a default-privileges change that the available role cannot perform.

We need guidance on the supported solution for a hosted Supabase project.

1. Which role is authorized to manage the relevant default privileges?
2. Is there a supported Dashboard or CLI workflow for resolving this?
3. If the operation requires Supabase staff privileges, what is the recommended support process?

We do not want to bypass managed database permission restrictions. We have not applied the failed migration, and Production must remain unchanged.

Thank you.

[cekuu35]

The short answer on hosted Supabase: you can't ALTER DEFAULT PRIVILEGES for roles you don't own, and you shouldn't need to after October 30. Here's the full picture and the supported paths.

Why you're getting 42501

On hosted Supabase, postgres is intentionally not a true superuser (since the 2025 hardening rounds). ALTER DEFAULT PRIVILEGES requires being the target role (or a member of it) or having been granted the privilege explicitly — neither of which applies to postgres for Supabase-managed roles like anon/authenticated/service_role. This is by design, not a misconfiguration.

The October 30 context

This is directly relevant to your migration timing: changelog 45329 — on October 30, 2026, Supabase stops auto-granting anon/authenticated/service_role on newly created tables in existing projects. If your migration was attempting to pre-set default privileges to cover this change, it would have needed exactly the privileges you've found you don't have.

Supported paths for hosted projects

1. Explicit per-object grants (recommended)

Instead of trying to change default privileges, grant explicitly in the migration for each object you create:

-- immediately after each CREATE TABLE / CREATE FUNCTION:
GRANT SELECT, INSERT, UPDATE, DELETE ON public.my_table TO authenticated;
GRANT EXECUTE ON FUNCTION public.my_function() TO authenticated;

This is what Supabase's own migration documentation recommends for the post-Oct-30 world. It's more verbose but works with the privileges postgres actually has (it can grant on objects it creates, since it's the owner).

2. Supabase CLI / Dashboard

There's currently no Dashboard or CLI workflow to change default privileges for Supabase-managed roles — that's the gap you've found. The CLI's supabase db push runs migrations as postgres, so it hits the same 42501.

3. Support process

If your migration genuinely requires changing default privileges for Supabase-managed roles (not just compensating for the Oct 30 change), that would need Supabase staff. Open a support ticket through the Dashboard (Help → Contact Support) — reference the Oct 30 changelog and explain why the per-object grants don't cover your case.

What I'd do in your position

Audit whether the default-privileges change is actually necessary, or whether it's compensating for something that explicit per-object grants handle. If you're using a migration tool that generates ALTER DEFAULT PRIVILEGES (some ORMs and schema-diff tools do), check if it can be configured to emit explicit GRANT statements instead.

---

Small disclosure: I audit Supabase/Postgres setups for production apps, so I care about exactly this class of permission boundary. The doc references above are the load-bearing part.

[spokodev]

That message comes from one check in Postgres: ALTER DEFAULT PRIVILEGES FOR ROLE x, where the current role doesn't have the privileges of x (aclchk.c). FOR ROLE x changes the defaults for objects created by x (docs), so what matters is the role after FOR ROLE, not the grantee such as anon or authenticated.

On a local Postgres 17.2, with postgres as a non-superuser with CREATEROLE and a superuser named supabase_admin, as postgres:

alter default privileges in schema public grant select on tables to anon;
-- ALTER DEFAULT PRIVILEGES
alter default privileges for role postgres in schema public grant select on tables to anon;
-- ALTER DEFAULT PRIVILEGES
alter default privileges for role supabase_admin in schema public grant select on tables to anon;
-- ERROR:  42501: permission denied to change default privileges

So the statement to look for in the failing migration has a FOR ROLE naming a role postgres doesn't have the privileges of, most likely supabase_admin. pg_dump writes an ALTER DEFAULT PRIVILEGES FOR ROLE ... line for every role that has default privileges, and Supabase's initial schema gives supabase_admin default privileges in public (initial-schema.sql), so a migration made from a plain pg_dump of a Supabase database can contain those lines. The Supabase CLI's own schema dump comments exactly those lines out, with the note "do not alter superuser role "supabase_admin"" (dump_schema.sh):

| sed -E 's/^ALTER DEFAULT PRIVILEGES FOR ROLE "supabase_admin"/-- &/' \

Commenting them out in your migration does the same thing. For objects that postgres creates, you can still set the defaults, without FOR ROLE or with FOR ROLE postgres as above.

If the FOR ROLE names a role you created yourself, postgres needs to inherit it first:

create role app_owner nologin;
alter default privileges for role app_owner in schema public grant select on tables to anon;
-- ERROR:  42501: permission denied to change default privileges
grant app_owner to postgres with inherit true;
alter default privileges for role app_owner in schema public grant select on tables to anon;
-- ALTER DEFAULT PRIVILEGES

Changing the defaults of a role whose privileges postgres doesn't have, such as supabase_admin, needs a role that has them. You found that postgres isn't a member of supabase_admin, so that part would be for support.

I tested this on vanilla Postgres 17.2, not on a hosted project.

[elladeniya-dev]

Here is the direct breakdown for each of your three questions regarding SQLSTATE 42501 on hosted Supabase projects:

1. Which Role Is Authorized to Manage Default Privileges?

In PostgreSQL, ALTER DEFAULT PRIVILEGES FOR ROLE <target_role> can only be executed by a superuser or a member of <target_role>.

• On hosted Supabase, the postgres role is not a superuser and is not a member of supabase_admin (which owns managed internal roles/schemas).
• However, postgres can modify default privileges for objects created by postgres itself when you omit FOR ROLE supabase_admin or explicitly target FOR ROLE postgres:

-- ❌ Fails with 42501 on hosted Supabase (if targeting supabase_admin or another unowned role):
ALTER DEFAULT PRIVILEGES FOR ROLE supabase_admin IN SCHEMA public
GRANT SELECT ON TABLES TO anon, authenticated;

-- ✅ Works on hosted Supabase (scopes default privileges to objects created by postgres):
ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA public
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO authenticated;

2. Supported CLI & Migration Workflow (supabase db push)

If your migration file was generated via pg_dump or supabase db diff from an older project or local instance that included FOR ROLE supabase_admin, update your migration SQL file using one of these two supported patterns:

1. Scope ALTER DEFAULT PRIVILEGES to FOR ROLE postgres:
   Replace FOR ROLE supabase_admin with FOR ROLE postgres in your migration script (since all tables/functions created via supabase db push or the SQL Editor run as postgres).
2. Use Explicit Per-Object Grants (Recommended for Production Security):
   With Supabase tightening automatic default grants on public, the cleanest zero-privilege-escalation pattern is to place explicit GRANT statements right after your DDL statements in the migration:

   CREATE TABLE public.orders (
     id uuid PRIMARY KEY DEFAULT gen_random_uuid()
   );
   
   GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE public.orders TO authenticated;
   GRANT ALL ON TABLE public.orders TO service_role;

3. When to Contact Supabase Support

You do not need Supabase Staff intervention unless an extension owned by supabase_admin created objects that postgres cannot alter. If your migration simply needs to set grants on your own application tables/functions, scoping the statement to FOR ROLE postgres (or using explicit per-table GRANT statements) will allow supabase db push to succeed cleanly in both Development and Production without bypassing managed permission boundaries.

If this resolves your migration blocker, please click Mark as answer!

[efekanbahceci]

This error occurs due to the privilege inheritance rules in PostgreSQL regarding ALTER DEFAULT PRIVILEGES.

In PostgreSQL, executing ALTER DEFAULT PRIVILEGES FOR ROLE <target_role> requires either SUPERUSER privileges or explicit membership in <target_role>. On hosted Supabase instances:

• You connect using the postgres role. While postgres has CREATEROLE and CREATEDB, it is intentionally not a SUPERUSER.
• supabase_admin is an internal platform provisioning role. For database isolation and managed service security, postgres is not granted membership in supabase_admin.
• If a migration script attempts to alter default privileges on behalf of supabase_admin, PostgreSQL rejects the command with SQLSTATE 42501 (permission denied to change default privileges).

This statement typically ends up in migration files after running pg_dump or older diff utilities against an existing instance, which captures the initial provisioning DDL executed during project bootstrap.

Here is the direct guidance for your three questions:

1. Which role is authorized to manage the relevant default privileges?

The postgres role is fully authorized to manage default privileges for objects that postgres itself creates. You cannot alter default privileges for supabase_admin, but you can alter them for postgres:

-- Fails with 42501 (postgres is not a member of supabase_admin):
ALTER DEFAULT PRIVILEGES FOR ROLE supabase_admin IN SCHEMA public ...;

-- Succeeds (scoped to objects created by the postgres role):
ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA public
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO authenticated;

If you omit FOR ROLE <role>, PostgreSQL implicitly targets CURRENT_USER (which is postgres when running migrations via the CLI or SQL Editor).

2. Supported Dashboard or CLI workflow

To resolve this in your migration workflow:

1. Audit the migration SQL: Search your migration file for FOR ROLE supabase_admin or any role other than postgres. Change the target role to FOR ROLE postgres (or remove the FOR ROLE clause entirely).
2. Prefer explicit grants on created objects: The cleanest and most robust approach in production migrations is to place explicit GRANT statements directly beneath the table or function definition:

   CREATE TABLE public.example (
     id uuid PRIMARY KEY DEFAULT gen_random_uuid()
   );
   
   GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE public.example TO authenticated;
   GRANT ALL ON TABLE public.example TO service_role;

3. CLI Diff Best Practice: If generating migrations via the Supabase CLI, ensure you are on a recent version (supabase --version) and scope your diffs to your application schemas (e.g. supabase db diff --schema public), which prevents internal platform roles and default privileges from being included in the generated diff.

3. Recommended support process

Contacting Supabase Support is not required for this issue. Supabase Support does not grant superuser privileges or add tenant roles to supabase_admin, as that would breach the managed security boundary. Scoping the default privilege statement to postgres or using explicit grants on your tables allows supabase db push to run cleanly without altering any platform-managed permissions.

[Rawilli]

The answers above cover the error itself: on hosted Supabase, postgres cannot run ALTER DEFAULT PRIVILEGES FOR ROLE x when it does not have the privileges of x, so either scope the statement to FOR ROLE postgres or drop it and write explicit grants per object. One thing worth adding about the grants you write instead, because it decides whether the fix is safe.

Per the changelog for the October 30 change, grants and RLS are separate layers: a grant decides whether a role can reach the table at all, RLS decides which rows it sees (https://supabase.com/changelog/45329-breaking-change-tables-not-exposed-to-data-and-graphql-api-automatically). The same changelog gives a rollback snippet, grant ... on all tables in schema public to anon, authenticated, service_role. That snippet is the one I would keep out of a migration. It reaches every table in public, including any table where RLS is off, and on such a table the anon key then reads, inserts, updates and deletes every row. The changelog does say to revoke private tables one by one after the bulk grant. The risk is the table you forget, and the time in between.

What I do instead, in the same migration that creates the table:

1. Enable RLS first, then write the policies.
2. Grant each table separately, to each role separately, and only the privileges a policy of that role uses. A table with no policy for anon gets no grant for anon.
3. Grant usage on the sequence behind any serial column the API inserts into. Identity columns do not need this.
4. Keep tables that should never be on the API without grants, or revoke them explicitly.

Example:

create table public.orders (id bigint generated always as identity primary key, user_id uuid not null default auth.uid(), total_cents int not null);
alter table public.orders enable row level security;
create policy "own orders read" on public.orders for select to authenticated using ((select auth.uid()) = user_id);
create policy "own orders create" on public.orders for insert to authenticated with check ((select auth.uid()) = user_id);
grant select, insert on table public.orders to authenticated;
grant select, insert, update, delete on table public.orders to service_role;

Then check the result with two normal users, not with the service key: sign in as user A and insert a row, sign in as user B and try to read, update and delete it through the REST API. A grant mistake shows up as a row coming back for user B, or as a write that succeeds. Do this on a staging project before and after the grants migration, so you can see that the migration fixed the 42501 and did not open anything. I ran the example above on a local PostgreSQL 18 with stand-in anon, authenticated and service_role roles: user A inserts, user B sees nothing, and B's update and delete are refused because no grant covers them.

Disclosure: I build RowAttest, which runs that two-user check for you (free tier: one Supabase project, 10 runs a month, no card needed). Run it before and after the migration and compare the two run pages: https://rowattest.com