How do I add Supabase Data API grants to my migrations?

One migration file. The table first, then the three steps in the order the changelog lists them: grants, enable row level security, policies.

-- supabase/migrations/<timestamp>_create_notes.sql
create table public.notes (
  id bigint generated always as identity primary key,
  user_id uuid not null references auth.users (id) on delete cascade,
  body text not null
);

-- 1. Grants: only the roles and commands the app uses
grant select, insert, update, delete on public.notes to authenticated;
grant select, insert, update, delete on public.notes to service_role;
-- Only if the table is meant to be publicly readable:
-- grant select on public.notes to anon;

-- 2. Enable RLS
alter table public.notes enable row level security;

-- 3. Policy: signed-in users reach only their own rows
create policy "users manage their own notes"
  on public.notes
  for all
  to authenticated
  using ((select auth.uid()) = user_id)
  with check ((select auth.uid()) = user_id);

The changelog says to treat grants, row level security, and policies as a unit. If the grant is missing, Postgres rejects the query before row level security comes into play. Securing your API says to grant the minimum privileges each role needs and to bundle grants with your row level security setup in the same migration. The same page splits the job: grants decide which roles can reach a table, and row level security decides which rows.

Creating API routes shows the same shape: read-only for anon, full read and write for authenticated and service_role. It says "Behind the scenes, the API checks your Postgres permissions", and that only objects with explicit grants are exposed. The example above leaves anon out, because notes belong to signed-in users.

The service_role line is for server code that uses the secret key. The changelog and Securing your API both grant to service_role in their examples.

A missing grant returns error 42501, and the full error and fix are in Supabase's Oct 30 change: new tables aren't exposed automatically.

The changelog's revoke statements also cover sequences. The example uses an identity column. If a table uses a serial column, test an insert as authenticated, and add a sequence grant for that role if it fails.

1. Create the migration file:

   supabase migration new create_notes
   

   supabase migration new creates a new file in supabase/migrations named <timestamp>_create_notes.sql.

2. Write the SQL from the example above into that file.

3. Apply it locally:

   supabase db reset
   

   supabase db reset requires the local stack to be running. It recreates the local Postgres container, applies every migration in supabase/migrations, and seeds test data if it is defined in supabase/seed.sql.

4. Preview, then push to the linked project:

   supabase db push --dry-run
   supabase db push
   

   supabase db push pushes all local migrations to the linked remote database, so run supabase link first. --dry-run prints the migrations that would be applied without applying them.

supabase db diff diffs the schema changes, and -f <name> "Saves schema diff to a new migration file". The CLI reference does not say whether that file contains grant statements. Open the generated file, check it for grant statements, and add any that are missing before you push.

A Supabase CLI commit, 5b12151 ("feat(api): flip auto_expose_new_tables default to false", committed June 10, 2026), describes a setting in supabase/config.toml: [api].auto_expose_new_tables. It controls default grants to anon, authenticated, and service_role on new tables, views, sequences, and functions that postgres creates in public. For functions, revoking those named roles' default grants does not remove the global PUBLIC EXECUTE default, so functions still need the separate treatment below.

• Unset: in CLI versions containing this change, local database setup revokes the default Data API privileges covered by the commit.
• false: same as unset.
• true: preserves the legacy default grants and prints a deprecation warning. The commit schedules removal of the field for October 30, 2026.

The warning text in the commit is: "api.auto_expose_new_tables is deprecated and will be removed on 2026-10-30. Remove the field or set it to false to adopt the new default of revoking Data API privileges on new entities in the public schema."

If your config.toml has auto_expose_new_tables = true, remove it and put explicit grants in your migrations. With a CLI version containing this change, replay your migrations using supabase db reset in your local test database and test access as each role. This checks your migrations against the changed table defaults; it does not establish that every privilege matches your hosted project. The generated template in that commit carries this commented setting:

[api]
# auto_expose_new_tables = true

Supabase's CLI config reference does not list this setting as of October 28, 2026, so the commit is the source for it. That page does say config changes need a restart: run supabase stop, then supabase start. The develop JSON schema still describes the previous unset behavior, contradicting the commit's implementation and the develop template.

This only matters when the app also reads these tables through supabase-js or another Data API client. Prisma over a direct connection is not affected by the October 30 change.

Prisma's Unsupported database features page, written for Prisma ORM 7, gives the steps for SQL the Prisma schema cannot represent:

1. Generate the migration without applying it:

   npx prisma migrate dev --create-only
   

2. Open the generated migration.sql. After the create table Prisma wrote, add the grant, enable row level security, and create policy statements from the example above.

3. Apply it:

   npx prisma migrate dev
   

4. Commit the modified migration.

Prisma's page names triggers, views, and stored procedures as its examples. Grants and policies follow the same steps.

Prisma's current Editing a migration page describes Prisma ORM 8, where you edit a migration.ts file, recompile it, and write raw SQL with rawSql. The --create-only flow above is the ORM 7 flow. If you are on ORM 8, follow that page.

Same condition: this only matters when the app also uses the Data API for these tables.

Drizzle's custom migrations page is for DDL that Drizzle Kit does not support. Generate an empty SQL migration:

npx drizzle-kit generate --custom --name=create_notes

Write the SQL from the example above into it, then apply it:

npx drizzle-kit migrate

drizzle-kit migrate reads the migration .sql files, applies the ones not yet applied, and records them in a migrations log table.

Drizzle has native helpers for part of this. Its RLS page documents pgTable.withRLS(...) to enable row level security, pgPolicy to define policies, and the predefined roles anonRole, authenticatedRole, and serviceRole exported from drizzle-orm/supabase. That page does not document a grant helper, so keep the grant statements in the custom SQL migration.

Supabase's Database API 42501 errors page gives this query:

select grantee, privilege_type
from information_schema.role_table_grants
where table_name = 'your_table';

Each row is a role and one privilege it holds on that table. The roles you granted should be there, and anon should appear only if the table is public on purpose. If another schema has a table with the same name, add and table_schema = 'public'.

To see whether the anon key can read rows, the Supabase RLS checker counts them table by table in your browser. It does not cover grants: a 401, 403, or 42501 from it is inconclusive for the grant change, so run the query above for grants. The full 42501 flow is in Supabase's Oct 30 change: new tables aren't exposed automatically.

Yes, and the October 30 change does not do it for you. Securing your API says new functions are callable by every role: Postgres grants execute to PUBLIC through a global default, and a revoke limited to one schema cannot remove it. Put the revoke in the same migration that creates the function:

create function public.count_my_notes()
returns bigint
language sql
security invoker
set search_path = ''
as $$
  select count(*) from public.notes where user_id = (select auth.uid())
$$;

revoke execute on function public.count_my_notes() from public, anon;
grant execute on function public.count_my_notes() to authenticated;

Supabase's lint 0028 docs say to revoke from PUBLIC as well, because the Postgres default grant lives there, and that revoking from anon alone usually leaves the function callable by every signed-up user. The full guide is Can anyone call your Supabase functions?.

The changelog says that without a grant, the API cannot see the table however it was created: the SQL editor, migrations, the Management API, MCP, the CLI, or an AI coding tool. For AI coding tools, it says to update the tool's system prompt or adopt the Supabase agent skill, which "includes the grants step", and that agents can self-correct from the PostgREST error hint. The agent prompt below can be pasted into the tool's instructions.