permission denied for schema public

Why it happens

The message is Postgres SQLSTATE 42501 (insufficient privilege). The role on the current connection is trying to read, create, or alter an object in a schema for which it lacks the required privilege. There are three common causes during a Supabase migration:

1. Postgres 15 removed the implicit CREATE grant. Before Postgres 15, the built-in PUBLIC role had CREATE on schema public, so almost any role could run CREATE TABLE. Postgres 15 dropped that default. A role that migrated fine on PG 14 now fails the moment schema replay runs its first CREATE TABLE.

2. Running DDL as anon or authenticated. These Supabase roles are meant for row-level API access, not schema changes. They typically have USAGE on public but not CREATE, and no rights at all on auth, storage, or other reserved schemas. If your connection string or pooler session resolves to one of these roles, DDL fails.

3. The target object is owned by another role. ALTER TABLE, DROP, or CREATE INDEX on an existing object requires ownership (or superuser). If the destination already has objects owned by postgres and you connect as a different role, you get 42501 on the alter even though you can SELECT the table.

How to fix it

First, confirm which role is failing and what it currently has. Run this on the exact connection that errored:

select current_user, session_user;

-- privileges the current role has on schema public
select
  has_schema_privilege(current_user, 'public', 'USAGE')  as has_usage,
  has_schema_privilege(current_user, 'public', 'CREATE') as has_create;

\dn+ lists every schema with its owner and ACLs (the psql meta-command; use the query below if you are not in psql):

select n.nspname as schema,
       pg_catalog.pg_get_userbyid(n.nspowner) as owner,
       n.nspacl as privileges
from pg_catalog.pg_namespace n
where n.nspname = 'public';

If has_create is false, grant the missing privileges. Run these as postgres (or the schema owner):

grant usage  on schema public to authenticated;
grant create on schema public to authenticated;

Replace authenticated with whatever current_user returned. For a dedicated migration role:

grant usage, create on schema public to migrator;

If the failure is on an existing object owned by someone else, either reassign ownership or run the DDL as the owner. To transfer ownership so your role can alter it:

alter table public.orders owner to migrator;

The simplest fix for the whole migration is to connect as postgres. On a Supabase destination, the direct-connection URI (not the pooler's anon context) authenticates as postgres, which owns schema public and needs no extra grants. If you must migrate auth users, note that the auth schema is owned by supabase_auth_admin; DDL there requires that role or postgres.

How to prevent it

• Run schema replay as postgres, not anon/authenticated. Point the DDL phase at the direct connection string that resolves to postgres. See the Supabase connection string builder to assemble the correct URI, and the service-role key note on why server-side steps bypass row-level restrictions.

• Grant privileges up front, once. When you provision a custom migration role, issue grant usage, create on schema public to <role>; before the first CREATE TABLE rather than reacting to 42501 mid-run.

• Set default privileges for future objects so later steps inherit access:

  alter default privileges in schema public
    grant all on tables to migrator;
  

• Verify with has_schema_privilege before you start. A one-line check in your preflight catches the missing CREATE grant before schema replay begins, instead of failing partway through. SupaMigrate runs this preflight against the destination and connects each phase as the role that has the rights it needs.

For the underlying concepts, see schema replay and search_path. If a later step fails because the object was not created at all, that surfaces as relation does not exist.