A view that filters by auth.uid() is safe to read, not to write: a free RLS audit that proves each finding

[Tori-TIC]

Something I did not expect, found while writing tests for an RLS audit, and now proven on a PostgreSQL set up with Supabase's default grants:

create view public.my_notes as
  select * from public.notes where user_id = (select auth.uid());

Reading through this view is fine: user A only ever sees A's rows. But the view runs with its creator's rights, and a simple view like this can be written through. With Supabase's default grants the API roles can insert into it and update it, so with A's token:

• insert into my_notes (user_id, body) values ('<id of user B>', ...) succeeds, and the new row really belongs to B;
• update my_notes set user_id = '<id of user B>' where ... hands one of A's own rows over to B.

Both skip the RLS policies on notes, because the writes run as the view's owner. Either of these refuses them:

alter view public.my_notes set (security_invoker = on);  -- RLS now applies to reads and writes (42501)
alter view public.my_notes set (check_option = local);   -- writes outside the filter fail (44000)

So the audit I publish now treats views in three ways: a view with no user filter is flagged because it returns rows RLS would hide; a view that filters by auth.uid() is flagged only when the API roles can write through it and it has no check option; and a filtered view that cannot be written through is left alone.

https://github.com/Tori-TIC/supabase-rls-audit (MIT) is one read-only SQL file you paste into the SQL editor. It reads the catalog only, never your rows, and runs inside a read-only transaction. Eight checks, worst first, each with a starting-point fix.

The part I care about most is the test suite. It builds a Supabase-shaped database in a throwaway PostgreSQL and, for every check, confirms the audit fires on a dangerous fixture and stays silent on a safe one, then proves the finding's claim by acting as a signed-in user. The view case above is in there, along with a mutant of each new condition that makes the suite fail.

If you have a policy or view shape it gets wrong, I would like to hear it.