---
title: "How do I add Supabase Data API grants to my migrations?"
metaTitle: "Supabase grants in your migrations"
metaDescription: "Put each role's grant in the same migration that creates the table, with enable row level security and its policies. Grant anon only for public tables."
fixMetaTitle: "Data API grants in migrations"
slug: supabase-grants-in-migrations
severity: medium
category: Database
summary: "Write each role's grant in the migration that creates the table, next to enable row level security and the policies. Grant anon only for public tables."
platforms:
  - Supabase
updated: "2026-11-02"
published: "2026-10-28"
related:
  - supabase-data-api-grants
  - supabase-oct-30-data-api-change
  - supabase-functions-callable-by-anon
  - supabase-tables-without-rls
securityClaim: true
---

Put each role's `grant` in the same migration file that creates the table, together with `alter table ... enable row level security` and the policies. Grant only what each role needs, and grant `select` to `anon` only when the table is meant to be publicly readable.

This matters for tables your app reaches through the Data API: `supabase-js`, REST, or GraphQL. An app that reads and writes Postgres only over a direct connection (psql, an ORM, or an app server with a connection string) is not affected, per the [Supabase changelog](https://supabase.com/changelog/45329-breaking-change-tables-not-exposed-to-data-and-graphql-api-automatically). The changelog names October 30, 2026 as the day the new behavior is enforced on existing projects, and existing tables keep their grants.

Once it reaches a project, a new table in `public` with no grant is not reachable through the Data API. Writing the grant into the migration keeps it next to the `create table`, so every environment that runs the migration gets it.

## What does a migration with grants look like?

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

```sql
-- 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](https://supabase.com/docs/guides/api/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](https://supabase.com/docs/guides/api/creating-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](/issues/supabase-data-api-grants).

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.

## How do I add grants with the Supabase CLI?

1. Create the migration file:

   ```bash
   supabase migration new create_notes
   ```

   [`supabase migration new`](https://supabase.com/docs/reference/cli/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:

   ```bash
   supabase db reset
   ```

   [`supabase db reset`](https://supabase.com/docs/reference/cli/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:

   ```bash
   supabase db push --dry-run
   supabase db push
   ```

   [`supabase db push`](https://supabase.com/docs/reference/cli/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`](https://supabase.com/docs/reference/cli/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.

## What changes in my local config.toml?

A Supabase CLI commit, [5b12151](https://github.com/supabase/cli/commit/5b12151ed8ef2608e98b67e57c423f8ba5896a84) ("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:

```toml
[api]
# auto_expose_new_tables = true
```

Supabase's [CLI config reference](https://supabase.com/docs/guides/local-development/cli/config) 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.

## How do I add grants with Prisma?

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](https://docs.prisma.io/docs/orm/prisma-migrate/workflows/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:

   ```bash
   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:

   ```bash
   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](https://docs.prisma.io/docs/orm/prisma-migrate/workflows/customizing-migrations) 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.

## How do I add grants with Drizzle?

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

Drizzle's [custom migrations](https://orm.drizzle.team/docs/kit-custom-migrations) page is for DDL that Drizzle Kit does not support. Generate an empty SQL migration:

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

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

```bash
npx drizzle-kit migrate
```

[`drizzle-kit migrate`](https://orm.drizzle.team/docs/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](https://orm.drizzle.team/docs/rls) 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.

## How do I check a table's grants after the migration runs?

Supabase's [Database API 42501 errors](https://supabase.com/docs/guides/troubleshooting/database-api-42501-errors) page gives this query:

```sql
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](/tools/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](/issues/supabase-data-api-grants).

## Do new functions need the same treatment?

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:

```sql
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](https://supabase.com/docs/guides/observability/advisors?lint=0028_anon_security_definer_function_executable) 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?](/issues/supabase-functions-callable-by-anon).

## What if an AI tool creates my tables?

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.

## How to check in 5 minutes

1. Open the migration that creates the table. Confirm there is a `grant` line for each role the app uses, and nothing for `anon` unless the table is public on purpose.
2. Confirm `enable row level security` and at least one `create policy` are in the same file.
3. Run `supabase db reset` locally, then call the table with the publishable key, once with no session and once with a signed-in user's session, to confirm each role gets what you meant.
4. Run the grants query above against the project and read the rows for the table.
5. If any request returns 42501, follow [Supabase's Oct 30 change: new tables aren't exposed automatically](/issues/supabase-data-api-grants).

## How to fix

### Supabase

For a new table that already exists without its grants, add a new migration with the missing grants instead of editing one that has already run. Existing tables keep their grants, per the changelog, so only tables created after the change need this.

1. Create a migration: `supabase migration new grant_notes`.
2. Add only the grants the app uses:

   ```sql
   grant select, insert, update, delete on public.notes to authenticated;
   grant select, insert, update, delete on public.notes to service_role;
   ```

3. Confirm the table has `enable row level security` and policies that cover each command you granted. If either is missing, add it to the same migration.
4. Run `supabase db reset` locally and test as each role.
5. Run `supabase db push`.

Do not add the grant without row level security. Securing your API says tables exposed through the Data API without row level security can be accessed by any role with a matching grant.

## Agent prompt

```text
You are adding Supabase Data API grants to the migrations in this repo. Do not add features. Do not run anything against my database without asking me first.

1. When you create a table in the public schema, write the grants in the same migration file as the create table. Grant only the commands each role the app uses needs: authenticated for signed-in users, service_role for server code with the secret key.
2. Never grant anything to anon unless I say the table is public. For a public table, grant select only.
3. In the same migration, enable row level security on the table and add a policy for each command you granted. Policies for signed-in users check (select auth.uid()) against the owner column. Never turn row level security off.
4. When you create a function in public, revoke execute from public and anon in the same migration, unless I say the function is public. Then grant execute only to the roles that call it.
5. Do not set auto_expose_new_tables to true in supabase/config.toml. If it is there, tell me.
6. If a request returns 42501, read the hint, then add the missing grant for that role in a new migration. Do not grant to anon only because the hint names anon.
7. Show me the SQL before applying anything.

Output the migration SQL, then a short list of each table with its roles and grants.
```

## FAQ

### Does the October 30 change affect my existing tables?

No. The changelog says "Existing tables are not affected in your project, they keep their current grants and stay reachable." Only new tables in `public` need an explicit grant. Write the grant into the migration for each one.

### Do I need grants if I only use Prisma or Drizzle over a direct connection?

No. The changelog says "If your app reads and writes Postgres over a direct connection (via psql, ORM or an app server with a connection string), this change will not affect you." If the app also uses `supabase-js` or REST for the same tables, those tables need grants.

### Should I set auto_expose_new_tables = true to make local work like before?

No. The CLI commit deprecates explicit `true` and schedules removal of the field for October 30, 2026. Keeping the legacy default grants locally can hide missing table grants in your migrations. Use explicit grants and test them with a CLI version containing the changed default.

### Do grants replace row level security?

No. Grants decide whether a role can touch the table, and row level security decides which rows, per the changelog and Securing your API. A missing grant fails before row level security runs. A grant without row level security leaves the table open to that role.

## Sources

- [Supabase changelog: tables not exposed to the Data API automatically](https://supabase.com/changelog/45329-breaking-change-tables-not-exposed-to-data-and-graphql-api-automatically)
- [Securing your API](https://supabase.com/docs/guides/api/securing-your-api)
- [Database API 42501 errors](https://supabase.com/docs/guides/troubleshooting/database-api-42501-errors)
- [Creating API routes](https://supabase.com/docs/guides/api/creating-routes)
- [Supabase CLI commit 5b12151](https://github.com/supabase/cli/commit/5b12151ed8ef2608e98b67e57c423f8ba5896a84)
- [Supabase CLI config](https://supabase.com/docs/guides/local-development/cli/config)
- [Supabase CLI: supabase migration new](https://supabase.com/docs/reference/cli/supabase-migration-new)
- [Supabase CLI: supabase db reset](https://supabase.com/docs/reference/cli/supabase-db-reset)
- [Supabase CLI: supabase db push](https://supabase.com/docs/reference/cli/supabase-db-push)
- [Supabase CLI: supabase db diff](https://supabase.com/docs/reference/cli/supabase-db-diff)
- [Supabase Advisors: lint 0028](https://supabase.com/docs/guides/observability/advisors?lint=0028_anon_security_definer_function_executable)
- [Prisma: Unsupported database features](https://docs.prisma.io/docs/orm/prisma-migrate/workflows/unsupported-database-features)
- [Prisma: Editing a migration](https://docs.prisma.io/docs/orm/prisma-migrate/workflows/customizing-migrations)
- [Drizzle: custom migrations](https://orm.drizzle.team/docs/kit-custom-migrations)
- [Drizzle: row level security](https://orm.drizzle.team/docs/rls)
- [Drizzle: drizzle-kit migrate](https://orm.drizzle.team/docs/drizzle-kit-migrate)