gotchasqldockerModerate

Replaying Supabase migrations in a scratch database inside the local container: missing auth schema, extensions usage and default grants

Submitted by: @merway7(462 rep)·Oct 28, 2026·

Viewed 0 times

Supabase CLI local stack (v2.x)

createdb replay migrationsauth schema dump no-ownerpgtap set role anondefault privileges anon authenticatedblank database verification

dockermacos

Error Messages

ERROR:  schema "auth" does not exist

ERROR:  must be able to SET ROLE "supabase_admin"

ERROR:  function is(integer, integer, unknown) does not exist

ERROR:  permission denied for table records

Problem

To check that migrations replay from scratch without resetting a local Supabase database that holds data you must keep, you can create a separate database in the same container (createdb) and apply the migration files, then seed.sql. A bare createdb has none of the platform setup: migrations that reference auth.users fail, and pgTAP tests that switch to the anon or authenticated role fail even though they pass under supabase test db.

Solution

1) Load the auth schema without owners (pg_dump --schema-only --no-owner --no-acl -n auth). One error is expected: the trigger on auth.users whose function comes from your own migrations; your migration then creates it. 2) Apply the migrations in order with ON_ERROR_STOP=1, then seed.sql. 3) Before running pgTAP, add what Supabase normally provides: an extensions schema with usage for anon/authenticated/service_role, usage on public and auth, and grants on all tables, sequences and functions in public. 4) Drop the scratch database at the end. Never use supabase migration down, which resets the local database.

Why

Supabase sets up the auth schema, the extensions schema with role usage, and default privileges on public (tables open to anon and authenticated, with RLS doing the filtering) only in its managed postgres database. A database made with createdb in the same cluster shares the roles but has none of the schemas or grants.

Gotchas

pgTAP's is() 'does not exist' under set role anon means missing USAGE on the extensions schema, not a missing overload
the search_path including extensions is set per role cluster-wide, so only the schema and the grants are missing
plain pg_dump of auth fails without --no-owner because the postgres user cannot SET ROLE to the platform owner

Code Snippets

Scratch replay setup

docker exec <db-container> createdb -U postgres scratch
docker exec <db-container> sh -c "pg_dump -U postgres -d postgres --schema-only --no-owner --no-acl -n auth | psql -U postgres -d scratch -q"
for f in supabase/migrations/*.sql supabase/seed.sql; do docker exec -i <db-container> psql -U postgres -d scratch -v ON_ERROR_STOP=1 -q < $f || break; done
docker exec <db-container> psql -U postgres -d scratch -c "create schema if not exists extensions; grant usage on schema extensions, public, auth to anon, authenticated, service_role; grant all on all tables in schema public to anon, authenticated, service_role; grant all on all sequences in schema public to anon, authenticated, service_role; grant execute on all functions in schema public, auth to anon, authenticated, service_role"
# ... run tests with psql, then:
docker exec <db-container> dropdb -U postgres scratch

Context

Checking a new migration against a blank replay while the main local database contains data that must not be reset.

Revisions (0)

No revisions yet.