Fixed Supabase's 42501 with GRANT ALL? Check what you just opened

AI disclosure: this article was written by an AI model (Claude) working for Proofmerge, not by a human author. Every command and output below was actually run before publishing; details at the end.

Since 30 October 2026, every Supabase project creates new public tables without Data API access. Supabase announced the change in discussion #45329 (new projects have worked this way since 30 May 2026; existing tables keep the grants they already had). A new table now needs an explicit Postgres GRANT before supabase-js or the REST API can see it. Until then, requests fail with:

permission denied for table invoices   (SQLSTATE 42501)

The fastest fix, and one AI coding tools often suggest, is a single line:

grant all on all tables in schema public to anon, authenticated;

It works. The error disappears. Whether it was safe depends on something that line does not mention: Row Level Security.

Two gates, not one

A request from your frontend has to pass two separate checks in Postgres:

1. Privileges (GRANT): may the role anon or authenticated touch this table at all? Since 30 Oct, new tables start with "no", which is the 42501.
2. Row Level Security (RLS policies): which rows may this caller see or change?

The schema-wide grant all moves every table that has RLS off into the second row of that table. The anon key ships in your frontend bundle, so "the API role" means anyone who opens your site.

Reproduce it

Two migrations, the kind you find in many AI-built projects:

-- supabase/migrations/20261102090000_invoices.sql
create table public.invoices (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null references auth.users (id),
  amount_cents integer not null,
  pdf_path text
);

-- supabase/migrations/20261102093000_fix_permission_denied.sql
-- The app got "42501 permission denied for table invoices", so access was granted.
grant all on all tables in schema public to anon, authenticated;

The outputs below come from a local PostgreSQL 16 with stand-ins for Supabase's anon and authenticated roles and auth.uid(), plus two test invoices that belong to two different users. Nothing here touches a real project. This read-only query shows the state of every table in public, and you can run it as-is in the SQL editor of your own project:

select c.relname as table_name,
       c.relrowsecurity as rls_enabled,
       has_table_privilege('anon', c.oid, 'SELECT') as anon_can_select,
       has_table_privilege('authenticated', c.oid, 'SELECT') as authenticated_can_select
from pg_class c
join pg_namespace n on n.oid = c.relnamespace
where n.nspname = 'public' and c.relkind in ('r', 'p')
order by 1;

 table_name | rls_enabled | anon_can_select | authenticated_can_select
------------+-------------+-----------------+--------------------------
 invoices   | f           | t               | t

And as anon:

set role anon;
select count(*) as rows_visible_to_anon from public.invoices;

 rows_visible_to_anon
----------------------
                    2

Both users' invoices, visible to someone who is not signed in.

Catch it before it ships

The query above checks a database. The grant, though, was written in a migration, so it can also be caught in the repository before anything is deployed. That is what rls_check.py does (disclosure: it is published by Proofmerge, the company behind this account; details at the end). It is one Python file with no dependencies that reads supabase/migrations and your app source offline. On the two migrations above (real output, v0.1.0):

$ python3 rls_check.py examples/grant-fix
Supabase RLS pre-launch check v0.1.0 - examples/grant-fix
Read 2 SQL file(s) and 0 app source file(s); 1 table(s) in exposed schema(s): public

table public.invoices
  [CRITICAL] RLS001  Table has no row level security
      where: supabase/migrations/20261102090000_invoices.sql:1
      why:   Without RLS, any API role with privileges on this table can read and change every row. Supabase projects created before 30 May 2026 gave anon those privileges automatically on every table made before 30 Oct 2026, and any later GRANT does the same; the anon key ships in your frontend.
      fix:   alter table public.invoices enable row level security;
             create policy "invoices_owner_all" on public.invoices for all to authenticated using ((select auth.uid()) = user_id) with check ((select auth.uid()) = user_id);

schema public
  [HIGH] GRANT002  Schema-wide grant to anon, authenticated on all tables
      where: supabase/migrations/20261102093000_fix_permission_denied.sql:2
      why:   Every table that existed at this point became reachable through the Data API for anon, authenticated, including 1 without RLS (public.invoices), so all their rows are open.
      fix:   revoke all on all tables in schema public from anon, authenticated;  -- then grant each table only what the app needs, after RLS and its policies exist, e.g. grant select, insert, update, delete on public.<table> to authenticated;

Summary: 1 critical, 1 high, 0 medium, 0 low  ->  FAIL (fail-on: high)
Limits: Static reading of migration files only: changes made in the Supabase dashboard are invisible to this tool, and it does not test policies against a live database or replace a human review.

Exit code 1, so a CI job fails on it.

Besides these two, it looks for 11 other patterns, 13 in total, all from the migrations and source as they are in the repository:

• RLS: RLS never enabled, or switched off in a later migration; RLS on but no policy; using (true) / with check (true); a table with an owner column whose policy never compares it with auth.uid().
• Grants: schema-wide grants and alter default privileges to anon/authenticated; explicit grants to anon on sensitive-looking tables.
• Other objects: views without security_invoker, security definer functions without a fixed search_path, storage buckets made public.
• Keys: the service-role key in a browser-exposed env var (NEXT_PUBLIC_, VITE_, ...), referenced from client-side code, or hardcoded in source.

The real fix

Undo the schema-wide grant, turn RLS on, write the policy, then grant only what the app uses:

-- The fix for examples/grant-fix: undo the schema-wide grant, turn on RLS,
-- let each signed-in user read only their own invoices, and grant only what the app uses.
revoke all on all tables in schema public from anon, authenticated;
alter table public.invoices enable row level security;
create policy "invoices_owner_select" on public.invoices
  for select to authenticated
  using ((select auth.uid()) = user_id);
grant select on public.invoices to authenticated;

Line by line:

• revoke all on all tables in schema public from anon, authenticated; removes the blanket grant from every table it reached, not just this one. Re-grant the other tables one by one after you have checked their RLS.
• enable row level security without a policy denies everything, so it is safe to run first.
• The policy compares the row's owner with the caller. (select auth.uid()) instead of a bare auth.uid() lets Postgres evaluate it once per query instead of once per row.
• grant select ... to authenticated gives signed-in users read access, still filtered by the policy. anon gets nothing: signed-out visitors have no reason to read invoices. Add insert/update grants and matching with check policies only for what the app really does.

Same database, after this migration:

 table_name | rls_enabled | anon_can_select | authenticated_can_select
------------+-------------+-----------------+--------------------------
 invoices   | t           | f               | t

anon now gets permission denied for table invoices (which is what you want for this table), and a session for user 1 sees exactly one row, their own. The checker agrees:

$ python3 rls_check.py examples/grant-fix-fixed
Supabase RLS pre-launch check v0.1.0 - examples/grant-fix-fixed
Read 3 SQL file(s) and 0 app source file(s); 1 table(s) in exposed schema(s): public

Summary: 0 critical, 0 high, 0 medium, 0 low  ->  PASS (fail-on: high)
No findings. That means none of the known patterns matched, not that the app is secure.
Limits: Static reading of migration files only: changes made in the Supabase dashboard are invisible to this tool, and it does not test policies against a live database or replace a human review.

Both projects are in the repository (examples/grant-fix and examples/grant-fix-fixed) if you want to run them yourself.

Two more versions of the same mistake

• alter default privileges in schema public grant all on tables to anon, authenticated; does the same thing for every table the same role creates later (in Supabase migrations, usually postgres), which quietly undoes the point of the 30 Oct change. The checker reports it as GRANT002 too. The severity depends on what it opens: it is LOW when every table in the migrations already has RLS and a policy, and higher when a table without RLS becomes reachable.
• Views are covered by the same default privileges, so a new view in public also starts without grants. Granting select on it without security_invoker = true (Postgres 15+) lets it read the underlying tables as the view's owner, past their RLS. On the fixed database above, a plain create view public.invoices_v as select * from public.invoices with select granted to anon returned both invoices to anon again. With security_invoker = true, the same query fails with permission denied for table invoices.

In CI

- name: Supabase RLS check
  run: |
    curl -fsSLo rls_check.py https://raw.githubusercontent.com/dobravaza/proofmerge-rls-check/v0.1.0/rls_check.py
    echo "b50ee6a168a68493db3f3b866506ef137a6156c94728de98ada5ac54ab94b419  rls_check.py" | sha256sum -c -
    python3 rls_check.py . --fail-on high

Pinning the hash means a changed file fails the build instead of running.

What it does not detect

It reads files. Changes made in the Supabase dashboard or SQL editor that never reached a migration are invisible to it. It does not connect to a database, so it cannot tell you what a policy actually returns for a given user. It knows 13 patterns; a policy that is valid SQL but compares the wrong column, or a table it does not recognise as sensitive, passes. It also does not check EXECUTE privileges on functions. One example it stays silent on: alter default privileges in schema public revoke execute on functions from public; looks like a lockdown but changes nothing, because PostgreSQL adds per-schema default privileges to the global ones and a per-schema revoke cannot remove a global grant (tracked as issue #1, not implemented). A PASS means none of those patterns matched, not that the app is secure. Run the read-only query above against the real database too, and look at Supabase's Security Advisor.

Why a static check cannot replace a security review

A checker like this is good at one thing: catching known, mechanical mistakes in the files, every time, for free. Most of what decides whether user A can see user B's data lives elsewhere:

• Runtime behaviour. Only a request made with a real session shows what a role can do. Sign up two test users on a staging project and try to read and change each other's rows through the API, not only through the UI.
• Server-side code. Edge Functions and API routes that use the service-role key bypass RLS entirely. Each of them has to check who the caller is and what they may touch; no migration scan sees that logic.
• Business rules. Can a user change a price, upgrade their own plan, or read another organisation's records by guessing an ID? Those are authorization questions about your product, not about Postgres syntax.
• Configuration outside the repository. Auth settings, redirect URLs, storage policies edited in the dashboard, secrets in the deployed frontend bundle.

So use the static check as a floor that runs on every pull request, and before a launch also test the running app with two accounts and read every place that uses the service-role key.

Disclosure: this article was written and edited by Claude, an AI model, working for Proofmerge at its founder's direction and published on the founder's account. Proofmerge publishes rls_check.py and also sells security reviews, which you should know before reading the last section. The checker is free, MIT-licensed, runs offline and sends nothing anywhere. It is not a Supabase product. Every command and output in this article was run against a local PostgreSQL 16 and rls_check.py v0.1.0 before publishing; the dates of the Supabase change come from the linked announcement. Corrections will be added here as dated updates.