"permission denied for table" (42501) errors

What you see

In Studio's SQL editor, psql or a database function:

ERROR:  42501: permission denied for table invoices

From the Data API (the error object in supabase-js), with HTTP 401 for signed-out requests and 403 for signed-in ones:

{ "code": "42501", "details": null, "hint": null, "message": "permission denied for table invoices" }

The same code also covers permission denied for sequence invoices_id_seq, permission denied for schema billing, must be owner of table users and new row violates row-level security policy for table "invoices".

Why it happens

Postgres checks access in two layers:

1. Privileges (GRANT). The role running the query (anon, authenticated, service_role or postgres) needs SELECT, INSERT, UPDATE or DELETE on the table. Without it you get "permission denied for table".
2. Row Level Security. Once the role has the privilege, policies decide which rows it can touch. A write that fails a policy's with check gives "new row violates row-level security policy". A read that fails a policy doesn't error at all; it returns no rows (see select returns an empty array).

Grants usually go missing because the table was created in a new schema, created by a role other than postgres, or someone ran revoke all. Inserting into a serial or bigserial column also needs USAGE on the column's sequence. service_role skips RLS, but it still needs grants.

How to fix it

1. See who has what on the table:

   select grantee, string_agg(privilege_type, ', ' order by privilege_type) as privileges
   from information_schema.role_table_grants
   where table_schema = 'public' and table_name = 'invoices'
   group by grantee;
   

2. Grant only what each API role needs:

   grant select, insert, update, delete on table public.invoices to authenticated;
   grant all on table public.invoices to service_role;
   -- only if signed-out visitors should read it:
   grant select on table public.invoices to anon;
   

3. For serial ids, also grant the sequence:

   grant usage, select on sequence public.invoices_id_seq to authenticated;
   

4. For a table in another schema, grant the schema too: grant usage on schema billing to authenticated;.

5. Grants open the door; RLS is what keeps data private. Make sure it's on and has policies:

   alter table public.invoices enable row level security;
   
   create policy "Owners read their invoices"
   on public.invoices for select
   to authenticated
   using ((select auth.uid()) = user_id);
   

6. To stop it happening for new tables, set default privileges for tables the postgres role creates:

   alter default privileges in schema public
     grant select, insert, update, delete on tables to authenticated;
   

On SupaHost

• The Data API only serves the public, storage, graphql_public and pgmq_public schemas, and that list can't be changed. Granting on a billing schema lets your functions use it, but the API still can't query it directly. See exposed schemas.
• An AI agent using a read-only MCP token runs SQL as the sh_readonly role, which can read everything and write nothing. A 42501 on an INSERT from that agent is expected. Create a token without Read-only if it should write.
• Tables in auth, storage and other Supabase schemas belong to Supabase's service roles, so the postgres role gets must be owner of table when it tries to alter or drop them. Keep your changes on your own tables.
• Studio, the SSH tunnel and sudo supahost-psql all sign in as postgres, so a query that works there can still fail for authenticated through the API.

Related

• Roles and permissions
• Row Level Security
• service_role client still gets RLS errors
• select returns an empty array but the table has data