SaaS architecture
Supabase RLS policy not working? Ten causes, measured
Why a Supabase row-level security policy returns nothing, fails or leaks, measured on PostgreSQL 17 with Supabase's own roles and auth.uid(): ten causes.
A Supabase row-level security policy that “does not work” fails in one of three ways: the query returns no rows, an insert or upsert fails with new row violates row-level security policy, or someone sees rows they should not. We rebuilt Supabase’s roles and its auth.uid() function on PostgreSQL 17.11 and reproduced ten causes. The most surprising ones: an upsert fails with only an INSERT policy even when the row is new, .insert().select() needs a SELECT policy, and an update or delete with a filter but without a SELECT policy changes nothing and reports no error.
Which cause is it?
| What you see | Cause | Fix |
|---|---|---|
| Every query returns 0 rows, insert fails | RLS is on and there is no policy for this command | Add a policy per command you use |
.insert() works, .insert().select() fails with new row violates… | Returning the row needs a SELECT policy | Add a SELECT policy that matches the new row |
.upsert() fails with new row violates…, even for a new row | A conflict column brings in the SELECT policy; an existing row also needs UPDATE | Add INSERT, SELECT and UPDATE policies |
Upsert fails with (USING expression) in the message | The conflicting row is not yours, or there is no UPDATE policy | Add an UPDATE policy; another user’s row stays blocked |
.update().eq(…) or .delete().eq(…) changes nothing, no error | No SELECT policy, so the WHERE finds no rows | Add a SELECT policy |
| 0 rows for a user who should see rows | The request arrived as anon, and the policy is to authenticated | Check that the client sends the user’s session |
| Policies exist, still 0 rows and insert fails | auth.uid() is null: no sub in the claims | Check the JWT; select auth.uid() returns null |
| A request sees the previous user’s rows | Claims set per session on a shared connection | Set them per transaction |
It works with the secret key or as postgres, not in the app | service_role and postgres bypass RLS, force or not | Test as authenticated with a real sub |
| Users see each other’s rows through a view | The view runs as its owner, postgres | security_invoker = true |
Each row is measured below; four complete policy sets that avoid all of them are in Supabase RLS policy examples, tested. Errors are SQLSTATE 42501 unless stated otherwise.
The setup
PostgreSQL 17.11 in a throwaway container. The roles exactly as Supabase’s supabase/postgres repository creates them: anon and authenticated without login, service_role with bypassrls, all three later set to inherit, and postgres demoted from superuser but with BYPASSRLS, as Supabase’s migration 10000000000000_demote-postgres.sql does: ALTER ROLE postgres NOSUPERUSER CREATEDB CREATEROLE LOGIN REPLICATION BYPASSRLS;. auth.uid() and auth.jwt() verbatim from supabase/auth. A table created by postgres:
create table public.todos (
id bigint generated always as identity primary key,
user_id uuid not null default auth.uid(),
title text,
done bool default false
);
grant select, insert, update, delete on public.todos to anon, authenticated, service_role;
alter table public.todos enable row level security;
User A owns rows 1 to 3, user B rows 4 and 5. Each request ran the way PostgREST runs one, in a transaction: set_config('role', 'authenticated', true) and set_config('request.jwt.claims', '{"sub":"<A>","role":"authenticated"}', true), then the statement. PostgREST’s documentation: “PostgREST uses settings tied to the transaction lifetime.” Every policy was to authenticated with user_id = auth.uid() unless we say otherwise.
Why does every query return 0 rows?
Because row-level security without a policy refuses everything. With RLS on and no policy, select * from todos returned 0 rows and an insert failed with new row violates row-level security policy for table "todos". No error tells you a policy is missing; the empty result is the only sign. A policy covers one command, or all. A SELECT policy does nothing for an insert. The opposite case, a table with RLS off, lets anon read and change everything; that is measured in Supabase ‘RLS Disabled in Public’.
Why does .insert().select() fail when .insert() works?
Because returning the new row is a read, and a read needs a SELECT policy. PostgREST adds a RETURNING to every insert: RETURNING 1 for a plain .insert(), the columns for .insert().select(). With only an INSERT policy, insert … returning 1 gave INSERT 0 1, also inside a CTE as PostgREST writes it. The same insert with returning * failed with new row violates row-level security policy for table "todos". The message says “new row”, but the insert with returning 1 had passed the same INSERT policy; what was missing is a SELECT policy for the row to be returned. With a SELECT policy added, the same statement returned the row.
Why does upsert fail with row-level security?
Because naming a conflict column brings in the SELECT policy, also for a new row and also with do nothing. The PostgreSQL documentation of CREATE POLICY: “If an INSERT has an ON CONFLICT DO UPDATE clause, or an ON CONFLICT DO NOTHING clause with an arbiter index or constraint specification, then SELECT permissions are required on the relation, and the rows proposed for insertion are checked using the relation’s SELECT policies.” When the row exists, do update also needs the UPDATE policy. supabase-js turns .upsert() into ON CONFLICT (<columns>) DO UPDATE or, with ignoreDuplicates, DO NOTHING; PostgREST writes the conflict columns out, never a bare ON CONFLICT. With only an INSERT policy:
| Statement | Result |
|---|---|
insert … on conflict (id) do update …, existing row | new row violates row-level security policy |
| the same, new row | new row violates row-level security policy |
insert … on conflict (id) do nothing, new row | new row violates row-level security policy |
insert … on conflict do nothing, no column list, new row | INSERT 0 1 |
Our todos.id is generated always, so these four statements used overriding system value to send an id; through supabase-js you would hit the identity error described below first. With only an INSERT policy, an upsert failed even for a row that did not exist yet, and the only form that worked was one PostgREST does not generate. With INSERT and SELECT policies, an upsert of a new row worked and returned it. An upsert that hit an existing row then failed with a different message, new row violates row-level security policy (USING expression) for table "todos": there was no UPDATE policy. After adding one, the upsert of A’s own row worked. An upsert of B’s row still failed with the (USING expression) message, which is the policy doing its job.
.upsert() as user A: which policy PostgreSQL checks, on which row- The proposed rowChecked against the INSERT policy's
with check.With no policy at all (a plain insert):new row violates row-level security policy - The proposed row, againBecause it is
ON CONFLICT … DO UPDATE, it must also pass the SELECT policy, whether or not there is a conflict.With only an INSERT policy:new row violates row-level security policy, also for a new row - The existing row, on a conflictMust pass the UPDATE policy's
using, and the SELECT policy; failing is an error, not a silent skip as with a plainUPDATE.Without an UPDATE policy, or on B's row:new row violates row-level security policy (USING expression) - The updated rowMust pass the UPDATE policy's
with check, and the SELECT policy.With INSERT, SELECT and UPDATE policies on A's own row:INSERT 0 1, the row updated
Which policy applies to which row is from the PostgreSQL 17 documentation of CREATE POLICY, as is that the UPDATE policy's using comes before its with check; the messages are what we measured.
One more trap if you upsert by id: with generated always as identity, sending the id fails before any policy, with cannot insert a non-DEFAULT value into column "id" (SQLSTATE 428C9). On a table with generated by default as identity, an upsert by id worked: a new row with INSERT and SELECT policies, an existing one after adding the UPDATE policy. We checked those two cases separately from the published script. generated by default lets clients choose ids, which can collide with the sequence later; upserting on a natural key avoids both.
How the new row violates message differs per command, and what each form of it means, is in new row violates row-level security policy.
Why does .update() change nothing without an error?
Because the WHERE of an update reads the rows, and without a SELECT policy it reads none. With an UPDATE policy and a DELETE policy but no SELECT policy:
| Statement | Result |
|---|---|
update todos set done = true where id = 1 (A’s row) | UPDATE 0 |
update todos set done = true (no where) | UPDATE 3 |
update … returning * | 0 rows, UPDATE 0 |
delete from todos where id = 1 | DELETE 0 |
delete from todos (no where) | DELETE 3 |
No error anywhere. .update().eq('id', 1) sends exactly such a WHERE, and gets back nothing. The update without a where did change A’s three rows, which shows the UPDATE policy itself worked. Supabase’s guide warns about it: “To perform an UPDATE operation, a corresponding SELECT policy is required.” With the SELECT policy added, A’s update and delete affected 1 row, and the same on B’s row affected 0.
Why does a logged-in user see nothing?
There are two causes, and neither raises an error.
The request arrived as anon. A policy to authenticated does not apply to anon, and anon holds the table grant, so the query runs and finds nothing it may see: 0 rows. That held even when the claims carried user A’s sub with role anon. As authenticated with the same sub: 3 rows. That is what a request looks like when the client sends only the anon key, without the user’s session.
auth.uid() is null. With no sub in the claims, auth.uid() returned null, user_id = null is never true, and a select returned 0 rows. An insert failed with new row violates row-level security policy, also when we set user_id to A explicitly, and also when we set it to null: the RLS error came before the not-null error. Only with an extra with check (true) policy did the not-null constraint speak: null value in column "user_id" of relation "todos" violates not-null constraint (SQLSTATE 23502).
Can one request see the previous user’s rows?
Yes, if the claims are set per session instead of per transaction. That does not happen through PostgREST, which uses set_config(…, true). It can happen in a backend that connects to the database directly and sets the claims itself with false. Request 1 set A’s claims with set_config(…, false) and committed. Request 2, on the same connection, set only the role, and auth.uid() still returned A. It read A’s three rows. With true, request 2 found no claims and read 0 rows. This is the same leak we measured for tenant settings behind a pooler in PgBouncer and row-level security.
Why does it work with the secret key but not in the app?
Because the secret key and the postgres role do not go through your policies at all. Supabase’s guide: “A secret key authorizes access through the service_role Postgres role, which has the bypassrls attribute.” It adds: “A secret key bypasses RLS only when the request carries no user access token”. And Supabase’s migration gives postgres BYPASSRLS too, as quoted in the setup. As service_role, a count returned all 5 rows, also with claims that carried user A’s sub next to "role":"service_role". As postgres: 5. After alter table todos force row level security, still 5 for both. force only binds an owner without BYPASSRLS: a control owner without it counted 5 rows before force and 0 after. The rules behind this are in who bypasses row-level security.
So a policy tested with the secret key or as postgres is not tested. Test as authenticated, with claims for a real user, the way the request will arrive.
Why do users see each other’s rows through a view?
Because a view runs with the rights of its owner, and on Supabase that is usually postgres, which bypasses RLS. A plain create view todos_v as select * from todos, owned by postgres, showed user A all 5 rows, B’s included, and anon all 5 as well. With security_invoker = true, A saw 3 and anon 0. Supabase’s guide says the same: “Views bypass RLS by default because they are usually created with the postgres user.” More in PostgreSQL views and row-level security.
What if an update changes the owner of a row?
Then a policy with only using stops it, which is usually what you want. With SELECT and UPDATE policies using (user_id = auth.uid()) and no with check, A could change the title of row 1 (UPDATE 1), but setting user_id to B failed with new row violates row-level security policy. Supabase’s guide: “If no with check expression is defined, the using expression decides both which rows are visible and which new rows are allowed.”
Never write with check (true) on an update policy. With it, the owner change went through as soon as the statement read no column: update todos set user_id = '<B>' without a where gave UPDATE 3, and A had handed all three rows to B, with a SELECT policy and without one. Only the combination of a where and a SELECT policy stopped it. Write with check (user_id = (select auth.uid())), or leave with check out. We measured the combinations in USING and WITH CHECK in PostgreSQL row-level security.
Reproduce it
setup.sql creates the roles, the two functions and the table, run.sql runs every case as a request and rolls it back, and run.sh does both in a throwaway container. The container starts with POSTGRES_USER=supabase_admin, because PostgreSQL cannot take superuser away from the user it was initialised with, and Supabase’s postgres is not a superuser.
What we did not measure
PostgREST and supabase-js themselves: we ran simplified equivalents of the SQL they generate, from their documentation and source, in psql; PostgREST wraps a mutation in a CTE, which we checked only for returning 1. ignoreDuplicates with INSERT and SELECT policies together. Supabase’s hosted platform, Storage and Realtime policies, policies that join a membership table, and the speed of any of this; for the last one see when (select auth.uid()) helps.
Sources: PostgreSQL 17 documentation, CREATE POLICY, read on 19 October 2026; Supabase, Row Level Security, read on 19 October 2026; Supabase’s roles from supabase/postgres, initial schema and its migrations 20230529180330_alter_api_roles_for_inherit.sql and 10000000000000_demote-postgres.sql; auth.uid() and auth.jwt() from supabase/auth; PostgREST, Authentication and Transactions; all read on 19 October 2026. All measurements are our own, on PostgreSQL 17.11 in a throwaway container, on 19 October 2026.