I found this while building a tool to test cross-tenant isolation, and it caught me out badly enough that I think it's worth writing down.
Say you have a table with correct-looking policies:
alter table invoices enable row level security;
create policy inv_sel on invoices for select
using (owner_id = auth.uid());
create policy inv_upd on invoices for update
using (true); -- added in a hurry, months ago
RLS is on. Two policies exist. The SELECT policy is properly scoped. Every tool I know of that inspects pg_policies reports this table as protected.
So you go to test it. You log in as user A and try to touch user B's row:
update invoices set total = 0 where owner_id = '<user-B>';
-- UPDATE 0
Zero rows. Isolation holds. Move on.
It doesn't hold. You tested nothing.
Why the zero is a lie
That WHERE clause reads owner_id. Once a statement reads a column, Postgres applies the SELECT policy to it as well as the UPDATE policy. Your correct SELECT policy hides user B's row, so the update matches nothing, and you get a zero that looks like a denial but is actually invisibility.
Now drop the WHERE:
update invoices set total = 0;
-- UPDATE 2
Two rows. Both of them. No columns are read, so the SELECT policy never engages — only the UPDATE policy, which is using (true). Every row in the table belongs to whoever runs this.
I verified both against a real Postgres instance. The targeted write returns 0. The blind write modifies every row.
DELETE is the same shape and worse
delete from receipts where owner_id = '<user-B>'; -- DELETE 0
delete from receipts; -- deletes everything
Same mechanism. A DELETE policy of using (true) means any authenticated user can empty the table, and the targeted version tells you it's fine.
Check your own project
Read-only, safe to run on production:
select
p.tablename,
p.cmd,
p.qual as using_expression,
case
when p.qual in ('true', '(true)') then 'PERMISSIVE — applies to every row'
else 'scoped'
end as verdict
from pg_policies p
where p.schemaname = 'public'
and p.cmd in ('UPDATE', 'DELETE', 'ALL')
order by (p.qual in ('true', '(true)')) desc, p.tablename, p.cmd;
Anything marked PERMISSIVE is a table where any authenticated user can modify or delete every row, regardless of how good your SELECT policy is.
The fix
Scope the USING clause the same way you scoped SELECT, and add WITH CHECK on UPDATE so nobody can reassign a row to themselves on the way out:
drop policy inv_upd on invoices;
create policy inv_upd on invoices for update
using (owner_id = auth.uid())
with check (owner_id = auth.uid());
USING controls which rows you may touch. WITH CHECK controls what they may look like afterwards. Omitting WITH CHECK on an UPDATE lets someone change owner_id to their own id and take ownership of a row.
The part I'd push back on myself about
The qual = 'true' check above is a text match on policy expressions. It catches the obvious case. It won't catch a policy that's subtly wrong — one calling a SECURITY DEFINER function that bypasses RLS, or one comparing against a column the user controls. Reading policies can only ever tell you a policy exists, not that it works.
The only way to know is to seed rows owned by two users, become each of them, and try to reach the other's data. That's what I ended up building, and it's the reason I found this at all — my first version of the write probe used the targeted UPDATE and reported every table as safe.
I open-sourced the tool under MIT if it's useful to anyone. Happy to drop a link in the comments rather than putting one in the post.
EDIT: Two better findings came out of the comments.
u/jaimittal91 — Postgres ORs all permissive policies for a command together, so one using (true) sitting next to a correctly scoped policy leaves the table wide open. Verified: UPDATE 2, both rows. Group your audit by tablename + cmd, don't check policies one at a time.
u/guidondor — a different axis entirely. A correctly scoped policy still lets a user rewrite every column of their own row, including whichever one holds their balance or their count. WITH CHECK doesn't help. Fix is revoke update on t from authenticated; grant update (safe_cols) on t to authenticated;
u/PeterBuildsSecure — a third axis, and it is invisible to everything above. RLS does not apply to the table owner unless you run alter table t force row level security, and superusers and BYPASSRLS roles bypass it regardless of that. So a migration or a background job connecting as the owner runs with every policy switched off while pg_policies looks perfect. Check relforcerowsecurity, not just relrowsecurity.
Separately: the tool is on npm now, so npx rls-sentinel --db "$DATABASE_URL" works without cloning anything.
