Data API grants

From 2026-10-30, Supabase stops granting Data API access to new tables in public on its own. A table with no grant answers "permission denied" (42501) to supabase-js, PostgREST, and GraphQL.

Row level security does not help here. Postgres checks the grant first.

Rules for every layer

• A migration that creates a table in public grants the table in the same file. Use the next numbered file, and never edit a shipped file.
• Grant the smallest set that the policies need. Do not copy a broad template.
• anon gets select only. It gets it only on a table with an anon or public select policy.
• authenticated gets only the privileges that its policies allow. profiles has a select policy and no update policy on purpose, so it gets select only.
• service_role gets select, insert, update, delete.
• Never grant truncate, references, trigger, or all.
• Use identity columns (generated ... as identity), not serial. An identity column needs no sequence grant. A serial column needs grant usage on sequence ... for an insert through the API.
• A new function that the API calls gets grant execute on function ... to <roles>.
• A grant is additive. Write the file so it is safe to run twice.

Each layer has a unit test, test/data-api-grants.test.ts. It fails when a table has no grant to authenticated, when anon has more than select, or when a file grants a banned privilege.

grant select on public.my_table to anon;
grant select, insert, update, delete on public.my_table to authenticated;
grant select, insert, update, delete on public.my_table to service_role;

Grant files in the shipped layers

The baselines were written when Supabase granted every new table automatically. These files add the grants for a new database:

They change nothing on an older database, which already holds broader privileges. The runner applies them right after the baseline on a fresh install.

If the role in DATABASE_URL does not own the tables, grant only warns, and the file is still recorded. Check the result with has_table_privilege.

Restricted reads

007_restrict_staff_reads.sql narrows four read policies:

• settings is readable by staff. Staff is a user with at least one capability, checked through my_capabilities().
• pluto_migrations, roles, and role_capabilities are readable by is_admin() only.

A plain signed-up user reads none of them. dashboard_user keeps its read access on settings and pluto_migrations.

006_data_api_grants.sql also grants execute on is_admin(), has_capability(text), and my_capabilities() to authenticated and service_role, and on set_my_locale(text) to authenticated. It does not revoke the default public execute, because policies that apply to anon still call these functions.