Permission denied after a Supabase restore: finding and fixing the missing grants

The restore finished, the rows are all there, and every request from your app comes back with this:

HTTP/1.1 401 Unauthorized
{"code":"42501","details":null,"hint":null,"message":"permission denied for table orders"}

If the rows are there, the backup is probably fine and the grants are what’s missing. Supabase used to grant every new table in public to anon, authenticated and service_role the moment it was created. It stopped doing that for new projects from May 30, 2026 and for existing projects on October 30, 2026. A restore that recreates your tables creates them as new tables, so unless it also brings back the grants, the tables exist and the Data API is not allowed to touch them. Postgres refuses the query with error 42501 before row level security runs.

The fix is a handful of GRANT statements. The rest of this page is about writing the right ones: finding which tables lost access, granting only what the app uses, and avoiding the default-privileges shortcut that only half works.

Which restores lose the grants

Whether you hit this depends on whether the grants travel with the dump and get applied on the way in. We restored the same small schema into a Postgres 17 container from the supabase/postgres image with the new defaults switched on, each way listed here:

• pg_restore --no-privileges, or any dump made with pg_dump --no-privileges (-x): none of the source’s grants come back, so the tables get only what the target hands out by default. On a project that has switched that leaves no select, insert, update or delete for the API roles. This is the common case, because --no-owner --no-privileges is the usual advice for restoring into a project whose roles differ from the source.
• A data-only dump loaded into a schema built from your migrations: the tables get whatever the migrations grant. Migrations written before the change usually grant nothing, because they never had to.
• A migration that drops and recreates a table: the new table starts with no grants. Its enable row level security and create policy statements run again, so it looks complete until the API refuses it.
• A full pg_restore without --no-privileges, or the schema file from supabase db dump: the GRANT lines are in the dump and the tables come back with the source’s grants. The catch is that the dump also carries the source’s ALTER DEFAULT PRIVILEGES lines, so restoring a dump from an older project turns automatic grants back on for future tables the postgres role creates. In our test the target’s default ACL went straight back to anon=arwdDxtm.

To see which kind of file you have, count the grant entries. Zero means the restore cannot bring any grants back:

pg_restore --list app.dump | grep -c ' ACL '   # custom-format archive
grep -c '^GRANT ' schema.sql                    # plain SQL file

Find the tables that lost their grants

Run this in the SQL editor of the restored project. Every row is a table or view that the role cannot read at all, so it is a 42501 waiting to happen if the app touches it as that role. has_any_column_privilege also counts column-level grants, which a check on table-level grants alone misses:

-- Tables and views in public that a role cannot read at all, not even one column
select c.relname as table_name, r.rolname as role
from pg_class c
join pg_namespace n on n.oid = c.relnamespace
cross join pg_roles r
where n.nspname = 'public'
  and c.relkind in ('r', 'p', 'v', 'm', 'f')
  and r.rolname in ('anon', 'authenticated', 'service_role')
  and not has_any_column_privilege(r.oid, c.oid, 'select')
order by 1, 2;

Some of those rows are correct. If anon was never meant to see audit_log, it belongs on the list. To tell the two apart, run the longer query below on the restored project and, if the old one still exists, on that too, then compare. For each table and view it lists each role’s SELECT, INSERT, UPDATE, DELETE and TRUNCATE, including grants to PUBLIC, inherited and column-level grants:

-- What anon, authenticated and service_role can do with each table and view in public
select c.relname as table_name,
       c.relkind as kind,   -- r table, p partitioned, v view, m materialized view, f foreign
       c.relrowsecurity as rls,
       r.rolname as role,
       coalesce(nullif(concat_ws(', ',
         case when has_table_privilege(r.oid, c.oid, 'select') then 'select'
              when has_any_column_privilege(r.oid, c.oid, 'select') then 'select (some columns)' end,
         case when has_table_privilege(r.oid, c.oid, 'insert') then 'insert'
              when has_any_column_privilege(r.oid, c.oid, 'insert') then 'insert (some columns)' end,
         case when has_table_privilege(r.oid, c.oid, 'update') then 'update'
              when has_any_column_privilege(r.oid, c.oid, 'update') then 'update (some columns)' end,
         case when has_table_privilege(r.oid, c.oid, 'delete') then 'delete' end,
         case when has_table_privilege(r.oid, c.oid, 'truncate') then 'truncate' end
       ), ''), '(none)') as access
from pg_class c
join pg_namespace n on n.oid = c.relnamespace
cross join pg_roles r
where n.nspname = 'public'
  and c.relkind in ('r', 'p', 'v', 'm', 'f')
  and r.rolname in ('anon', 'authenticated', 'service_role')
order by c.relname, r.rolname;

Don’t be surprised by truncate on a table that has nothing else. The revoke Supabase publishes for switching an existing project only covers select, insert, update and delete, so in our test restored tables still got TRUNCATE, REFERENCES, TRIGGER and MAINTAIN for all three roles. The Data API doesn’t use them, and TRUNCATE ignores row level security, so the grant example below clears them first.

Grant only what the app uses

Supabase’s own announcement shows the bulk form, grant ... on all tables in schema public, as the way to roll back. It applies to every table in public, including tables with row level security switched off and tables you never meant anon to reach. After a restore you are writing these grants anyway, so write the narrow ones. Go through what the app does with each table, as each role:

-- Start from nothing, so leftovers like TRUNCATE and grants to PUBLIC go too
revoke all on public.posts, public.notes, public.recent_posts from public, anon, authenticated, service_role;

-- Signed-out visitors read posts. Signed-in users manage their own notes.
grant select on public.posts to anon, authenticated;
grant select, insert, update, delete on public.notes to authenticated;

-- Server code and Edge Functions using the service role key
grant select, insert, update, delete on public.posts, public.notes to service_role;

-- Views need their own grant, separate from the tables under them
grant select on public.recent_posts to anon, authenticated;

Before granting a view to anon, check how it runs. A view without security_invoker reads its tables with the privileges of its owner. After a restore with --no-owner, the role that ran it owns the view, usually postgres. In the supabase/postgres image that role has BYPASSRLS, so the view skips the tables’ row level security, even with force row level security set. In our test anon got 0 rows from a table whose policy hid everything, and 2 rows from a plain view over the same table. Switch such views over with alter view ... set (security_invoker = true) (Postgres 15 and later) or keep them away from the API roles. Materialized views can’t be switched: they hold a stored copy of the rows, and in the same test anon read both rows from one. Grant those only if every row in them is meant to be public. This lists the ones to look at:

-- Views and materialized views the API roles can read that don't apply the caller's RLS
select c.oid::regclass as view_name, c.relkind as kind, r.rolname as role
from pg_class c
cross join pg_roles r
where c.relnamespace = 'public'::regnamespace
  and r.rolname in ('anon', 'authenticated')
  and has_any_column_privilege(r.oid, c.oid, 'select')
  and (c.relkind = 'm'
       or (c.relkind = 'v' and not exists (
             select 1
             from unnest(c.reloptions) as option
             where option like 'security_invoker=%'
               and substring(option from 18)::boolean)))
order by 1, 3;

anon is every visitor who is not signed in, so it usually needs select on a few public tables and nothing else. Row level security still decides which rows each role sees; the grant only lets the query start. Put these statements in a migration as well, next to the create table, so the next restore or supabase db reset gets them.

Sequences are the part people forget. A serial or bigserial column fills itself from a sequence, and the role inserting the row needs USAGE on that sequence (UPDATE also works, but USAGE is the smaller grant). Without either, the insert fails with permission denied for sequence posts_id_seq even though the table grant is fine. Identity columns (generated ... as identity) and uuid keys don’t need it: in our test an identity insert worked with no privilege on its sequence. This lists the sequences that do:

-- serial columns: inserts that use the column default need USAGE (or UPDATE) on these sequences
select s.oid::regclass as sequence_name,
       t.oid::regclass as table_name,
       a.attname as column_name,
       has_sequence_privilege('authenticated', s.oid, 'usage, update') as authenticated_can_use,
       has_sequence_privilege('service_role', s.oid, 'usage, update') as service_role_can_use
from pg_depend d
join pg_class s on s.oid = d.objid and s.relkind = 'S'
join pg_class t on t.oid = d.refobjid
join pg_attribute a on a.attrelid = t.oid and a.attnum = d.refobjsubid
where d.classid = 'pg_class'::regclass
  and d.refclassid = 'pg_class'::regclass
  and d.deptype = 'a'
  and t.relnamespace = 'public'::regnamespace;

-- Only for roles that insert into the table
grant usage on sequence public.posts_id_seq to authenticated, service_role;

Functions go the other way. Postgres gives EXECUTE on every new function to PUBLIC, so after a restore without privileges your RPC functions work, including the ones you had closed with REVOKE ... FROM PUBLIC. Depending on the target’s default privileges, anon and authenticated may also get EXECUTE directly, so revoking from PUBLIC alone is not enough. Check both roles, take EXECUTE away from all of them, and grant it back only to the roles that should call each function. A SECURITY DEFINER function runs with its owner’s rights, so one meant only for your backend should not be callable by signed-in users either. In our test a restored SECURITY DEFINER function that the source had kept to service_role came back callable by both anon and authenticated:

-- Functions in public that anon or authenticated can call through /rest/v1/rpc
select p.oid::regprocedure as function_name,
       p.prosecdef as security_definer,
       r.rolname as role
from pg_proc p
cross join pg_roles r
where p.pronamespace = 'public'::regnamespace
  and r.rolname in ('anon', 'authenticated')
  and has_function_privilege(r.oid, p.oid, 'execute')
order by 1, 3;

-- Take EXECUTE away from every role the API runs as, then give it back on purpose
revoke execute on function public.add_note(text) from public, anon, authenticated;
grant execute on function public.add_note(text) to authenticated, service_role;

-- Backend-only functions, SECURITY DEFINER ones above all: service_role only
revoke execute on function public.purge_old_notes() from public, anon, authenticated;
grant execute on function public.purge_old_notes() to service_role;

Replaying the grants from the dump

If the archive has ACL entries (the count above was not zero) but the restore skipped them, you can apply just those entries afterwards. Leave out DEFAULT ACL, which is the part that switches automatic grants back on:

pg_restore --list app.dump | grep ' ACL ' | grep -v 'DEFAULT ACL' > grants.list
pg_restore --use-list=grants.list --dbname "$TARGET_URL" app.dump

In our test this put the table, view, sequence and function grants back as the source had them. It has two limits. It reproduces the source, so a project that ran on the old defaults gets ALL for anon on every table again. And extra grants the target added can stay. The dump’s own REVOKE statements run too, so REVOKE ALL ... FROM PUBLIC on a function took PUBLIC’s EXECUTE away. But the target had also granted EXECUTE to anon and authenticated directly when the restore created the function, and the dump has no statement for that, so those grants stayed. Run the census and function queries after it either way.

The trap in alter default privileges

The tempting fix is one statement that brings the old behavior back: default privileges that grant new tables to the API roles again. It is also what Supabase gives as the rollback. Three things about it matter when the tables come from a restore. We checked each one:

• It only covers tables created afterwards. Run it after the restore and the restored tables stay exactly as they were.
• It only covers tables created by one role: FOR ROLE postgres, or the role running the statement if you leave that out. A table created by any other role, such as a separate migration user or a tool that connects with its own credentials, gets nothing from it.
• Set before the restore, it does work: tables that pg_restore created as postgres picked up the grant. But it grants the same privileges on every table, which is the blanket access the change was meant to end, and it keeps doing so for every table created later until you revoke it.

Explicit grants in your migrations don’t depend on who ran the restore, when, or which defaults the target project has, which is why they are the better place to keep this.

After the restore: a checklist

1. Check that the API roles can use the schema at all. On Supabase, public has this by default. After a restore with --clean, check it again: if public was recreated and its grants were not restored, the API roles may have lost it.

   -- Each API role needs USAGE on the schema before any table grant matters
   select r.rolname as role,
          has_schema_privilege(r.oid, 'public', 'usage') as public_usage
   from pg_roles r
   where r.rolname in ('anon', 'authenticated', 'service_role');

2. Run the missing-grants query and compare it with the source, or with what the app actually does with each table.
3. Grant per table and role. Add views. Add USAGE on serial sequences for roles that insert.
4. Make sure every table the API roles can read or write has row level security on. Policies are part of the schema and come back with the restore; the switch is per table, so check it anyway, and check views and materialized views as well:

   -- Tables with row level security off that anon or authenticated can read or write
   select c.relname as table_name, r.rolname as role
   from pg_class c
   cross join pg_roles r
   where c.relnamespace = 'public'::regnamespace
     and c.relkind in ('r', 'p')
     and not c.relrowsecurity
     and r.rolname in ('anon', 'authenticated')
     and (has_any_column_privilege(r.oid, c.oid, 'select, insert, update')
          or has_table_privilege(r.oid, c.oid, 'delete'))
   order by 1, 2;

5. Close the functions anon and authenticated should not call.
6. Hit the API with the anon key and with a signed-in user, the way the app does, and compare the answers with your grants and policies. A missing grant gives 401 for anon and 403 for a signed-in user. For a read, a grant with a policy that filters every row out gives 200 and an empty list. An insert or update whose row fails a policy’s WITH CHECK also comes back as 42501, with the message new row violates row-level security policy. That one is the policy doing its job, so don’t fix it with a grant:

   curl "https://<project-ref>.supabase.co/rest/v1/posts?select=id&limit=1" \
     -H "apikey: $SUPABASE_ANON_KEY"

7. Copy the grants into a migration, so the next environment built from your migrations doesn’t need this page.

Everything above was run against Postgres 17 containers from the supabase/postgres image, with the defaults switched using the SQL from Supabase’s announcement, and the HTTP status codes came from PostgREST 14.8. We did not run it against a hosted project; Supabase’s own error response also carries a hint with the GRANT it expects.

How BackupDrill handles grants

BackupDrill dumps with --no-privileges, because grants for platform roles fail on other targets, so its dumps are in the first group above. Since CLI 2.9.0, each backup also records what PUBLIC, anon, authenticated and service_role hold on every table, view, sequence and function in the backed-up schemas, column grants and schema USAGE included, in the snapshot’s manifest.json. backupdrill restore and backupdrill drill take those roles off every restored object, grant back what the source had whatever the target’s defaults are, and read the grants back to check. If setting them fails, restore tries to revoke those roles’ access to the restored objects instead. When that works, the app gets 42501 until you grant what it needs; if the revoke fails too, the error tells you to check the target’s default grants before the app goes live.

From 2.9.1, a snapshot’s RECOVERY.md has the same steps as one do block for restoring by hand. Run it on the restored database as the role that ran pg_restore: it checks that the role owns every restored object before changing anything. Snapshots from 2.9.0 should go through backupdrill restore. From 2.9.2, grants are read before and after the dump, and only grants present in both reads are recorded. One added or removed while the dump ran is left off until a later backup sees it in both reads; one taken away and given back between the reads looks unchanged and is kept.

Two limits. Snapshots taken before 2.9.0 have no record: restoring one gives the three roles Supabase’s old default access to public, the report says so, and you revoke it on anything you had kept private. Grants to other roles, such as your own roles or supabase_admin, are not recorded. The restore docs cover the by-hand path step by step.

FAQ