October 4, 20266 min read

A Supabase Image as the Atlas Dev Database, Grants Included

Generate migrations against a dev database built from supabase/postgres while Ptah Compat keeps the image's grants and serial sequences intact.

Simon HartSolutions Engineer

Ptah helps you plan, review, and apply database migrations. Try in your browser

A Supabase project depends on what its database image provides: the auth schema, auth.uid(), the anon, authenticated and service_role roles, and the grants between them. So when such a project generates migrations with atlas migrate diff, the dev database is built from the same supabase/postgres image it runs locally.

ariga/atlas#3807 reports what happens when that project also manages permissions. Between the two uses of the dev database, the reset revokes every grant the image created, and the next migration “restores” more than thirty of them. A bigserial column whose sequence carries a grant fares worse. Its sequence is treated as a standalone sequence, the column loses its default, and the next run stops with connected database is not clean: found schema "auth".

We built a project in the report’s shape and ran it with Ptah Compat edge, installed under the atlas name, on supabase/postgres:17.6.1.011. The supporting files contain the project, the generated migration, and every recorded result.

What the dev database starts from

A dev database is a scratch copy of the schema. Atlas and Ptah replay the migration directory on it, load the desired state on it, compare the two, and reset it between those uses. The rule that keeps this honest is that the dev database starts clean.

The Supabase image is not clean. It creates roles and schemas with grants on them, default privileges that grant every new table, sequence and function in public to the API roles, event triggers, and a publication. A dev URL that pins no schema reads all of that as leftovers.

The docker block’s baseline resolves the conflict: whatever the image and the baseline SQL leave is the dev database’s starting point. Ptah Compat edge holds that starting point fixed:

every reset returns the database to it, grants and default privileges included;
every comparison leaves it out unless the desired state declares it;
an object the project creates is the project’s, and a sequence a bigserial column owns stays the column’s.

The project

The atlas.hcl keeps the report’s structure: a docker block with a build and an empty baseline, a composite desired state, and permissions mode.

docker "postgres" "dev" {
  image = "supabase-dev:local"
  build {
    context    = "."
    dockerfile = "Dockerfile"
  }
  baseline = <<-SQL
  -- Accept the state the image starts with.
  SQL
}

data "composite_schema" "app" {
  schema "public" {
    url = "file://schema/tables.hcl"
  }
  schema "public" {
    url = "file://schema/security.sql"
  }
}

env "local" {
  url = getenv("DATABASE_URL")
  dev = docker.postgres.dev.url
  migration {
    dir     = "file://migrations"
    tx_mode = "none"
  }
  schema {
    src = data.composite_schema.app.url
    mode {
      permissions = true
    }
  }
}

The Dockerfile starts from supabase/postgres:17.6.1.011 and adds one helper the hosted project has and the stock image lacks. The helper carries its own grants, so the image’s starting state includes them:

-- A helper the hosted project provides and the stock image lacks.
CREATE FUNCTION auth.tenant_id() RETURNS uuid
LANGUAGE sql STABLE
AS $$
  SELECT nullif(current_setting('request.jwt.claims', true)::jsonb ->> 'tenant_id', '')::uuid
$$;

REVOKE ALL ON FUNCTION auth.tenant_id() FROM PUBLIC;
GRANT EXECUTE ON FUNCTION auth.tenant_id() TO authenticated, service_role;

The desired state has two parts. schema/tables.hcl declares public.todos with a bigserial key. schema/security.sql adds row-level security, a policy that calls auth.uid() from the image and auth.tenant_id() from the helper, a function that returns SETOF public.todos, and the grants:

ALTER TABLE public.todos ENABLE ROW LEVEL SECURITY;

CREATE POLICY todos_owner ON public.todos
  FOR ALL TO authenticated
  USING (owner = auth.uid() AND tenant_id = auth.tenant_id());

CREATE FUNCTION public.my_todos() RETURNS SETOF public.todos
LANGUAGE sql STABLE
AS $$ SELECT * FROM public.todos WHERE owner = auth.uid() $$;

GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE public.todos TO authenticated;
GRANT ALL ON TABLE public.todos TO service_role;
GRANT USAGE ON SEQUENCE public.todos_id_seq TO authenticated;
GRANT ALL ON SEQUENCE public.todos_id_seq TO service_role;

Generate the first migration

atlas migrate diff init --env local

Created migration file: /work/project/migrations/20261003215538_init.sql
Updated migration checksum: /work/project/migrations/atlas.sum

The generated migration holds the project and nothing else:

  CREATE SCHEMA IF NOT EXISTS "public";
  -- POSTGRES TABLE: public.todos --
  CREATE TABLE "public"."todos" (
    "id" bigserial PRIMARY KEY NOT NULL,
    "tenant_id" uuid NOT NULL,
    "owner" uuid NOT NULL,
    "title" text NOT NULL
  );
  CREATE FUNCTION "public"."my_todos"() RETURNS SETOF public.todos AS $$
SELECT * FROM public.todos WHERE owner = auth.uid()
$$
  LANGUAGE sql SECURITY INVOKER STABLE;
  -- Enable RLS for public.todos table
  ALTER TABLE "public"."todos" ENABLE ROW LEVEL SECURITY;
  DROP POLICY IF EXISTS "todos_owner" ON "public"."todos";
  CREATE POLICY "todos_owner" ON "public"."todos" FOR ALL TO "authenticated"
      USING (owner = auth.uid() AND tenant_id = auth.tenant_id());
  GRANT USAGE ON SEQUENCE "public"."todos_id_seq" TO "authenticated";
  GRANT ALL ON SEQUENCE "public"."todos_id_seq" TO "service_role";
  GRANT DELETE ON TABLE "public"."todos" TO "authenticated";
  GRANT INSERT ON TABLE "public"."todos" TO "authenticated";
  GRANT SELECT ON TABLE "public"."todos" TO "authenticated";
  GRANT UPDATE ON TABLE "public"."todos" TO "authenticated";
  GRANT ALL ON TABLE "public"."todos" TO "service_role";

No statement changes auth, storage or the grants on the public schema. The image created those, and they are part of the starting point. The grants on todos_id_seq are grants on the sequence the id column owns. The column stays bigserial, and no CREATE SEQUENCE appears.

Generate again

atlas migrate diff again --env local

The migration directory is synced with the desired state, no changes to be made

The second run replays the first migration on a dev database built from the image and compares the result with the desired state. Both sides start from the image’s state, its grants included, and the comparison leaves that state out. The migration directory already covers the rest, so nothing is left to generate, and no migration has to restore a grant.

Apply and compare the target

The target is a container built from the same Dockerfile, a stand-in for the local Supabase database. It already holds the image’s schemas, so migrate apply needs --allow-dirty, as it does on Atlas CE:

atlas migrate apply --env local --allow-dirty

Migrating to version 20261003215538 from 1 pending migrations.
Migration complete. Current version: 20261003215538

atlas migrate diff settled --env local

The migration directory is synced with the desired state, no changes to be made

We read the grants and default privileges of the image’s objects before and after the apply, with the same catalog query. Before the apply it returned 33 rows: 9 for schemas, functions and relations the image created, and 24 default-privilege entries. Afterwards it returned those 33 rows unchanged, plus two new ones (excerpt):

 function auth.tenant_id()               | postgres=X/postgres authenticated=X/postgres service_role=X/postgres dashboard_user=X/postgres
 function auth.uid()                     | =X/supabase_auth_admin supabase_auth_admin=X/supabase_auth_admin dashboard_user=X/supabase_auth_admin
 relation todos                          | postgres=arwdDxtm/postgres anon=arwdDxtm/postgres authenticated=arwdDxtm/postgres service_role=arwdDxtm/postgres
 relation todos_id_seq                   | postgres=rwU/postgres anon=rwU/postgres authenticated=rwU/postgres service_role=rwU/postgres

The todos row deserves a second look. anon holds every privilege on the table, although the desired state grants it none. The image’s default privileges in public grant every new table to the three API roles, so the privileges arrive with CREATE TABLE. The comparison treats them as implied by those default privileges and plans no REVOKE. Row-level security still decides which rows each role can see.

The key column kept its sequence:

            id_default             |   owned_sequence
-----------------------------------+---------------------
 nextval('todos_id_seq'::regclass) | public.todos_id_seq

A plain dev URL keeps Atlas CE’s rule

The starting point comes from the docker block. The same image named through a plain dev URL that pins no schema is refused, as Atlas CE refuses it:

atlas migrate diff probe --env local \
  --dev-url "docker+postgres://_/supabase-local:target/postgres"

Error: sql/migrate: taking database snapshot: sql/migrate: connected database is not clean: found schema "auth"

A plain URL that pins public asks Ptah to judge that schema alone and to leave the image’s other schemas as they are. That route works without the docker block:

atlas migrate diff pinned --env local \
  --dev-url "docker+postgres://_/supabase-local:target/postgres?search_path=public"

The migration directory is synced with the desired state, no changes to be made

What Ptah does with the starting point

The run above depends on this behavior in Ptah Compat edge:

The container gets POSTGRES_PASSWORD and POSTGRES_DB and keeps the image’s own POSTGRES_USER. The Supabase image runs its init scripts as supabase_admin.
If the container exits before its server answers, the run ends at once with the exit code and the end of the container’s log.
The claim records the schemas, objects, extensions, default privileges, event triggers and publications the database starts with.
Each reset runs in one transaction. It drops what the run added in any schema, a trigger on auth.users included. It puts back grants, default privileges, owners, comments and column settings the run changed. When the run dropped part of the starting point or changed something it cannot put back, such as a column type, the reset refuses and rolls back.
A grant on the sequence a serial or identity column owns is read back and matched through the owning column.

Boundaries

A reset restores the schema, not rows.
GRANT ... ON ALL ... IN SCHEMA in a desired-state file is refused. It applies to whatever exists when it runs, so the desired state names each object instead.
A plain dev URL, and a MySQL or MariaDB docker block, keep Atlas CE’s rule for what counts as a clean dev database.
Strict CE mode (PTAH_ATLAS_STRICT_COMPAT=1) refuses the docker block and composite_schema as Atlas CE does.
These capabilities are in edge builds and not in a release yet.

The docker block reference lists the attributes Ptah reads, and the named-image dev URL covers the plain-URL form.

Put Ptah to work

Example files

← All articles