The View That Ignored Row Level Security

Row level security was on. Every table had policies. Every policy was correct. And the published key that ships inside every page load could read 2,028 rows of invoice totals.

Nothing was misconfigured. The database was doing exactly what Postgres says it does.

The short version

A view created with plain create view runs with the permissions of the user who created it. It does not check row level security on the tables underneath it. Policies on those tables are simply not consulted.

So a locked table plus an ordinary view equals an unlocked table with extra steps.

What we actually had

A multi tenant shop management system. Thousands of customers, jobs and invoices, one database, one published anon key sitting in the browser on every page load. Tenant separation was the whole security model, and it was RLS doing the separating.

Somebody needed a summary for a dashboard. Totals by month, nothing exotic. They wrote what anybody would write.

create view invoice_totals as
select shop_id, date_trunc('month', created_at) as month,
       sum(total) as total
  from invoices
 group by 1, 2;

invoices had RLS on and a policy restricting rows to the caller's own shop. The view had no policies of its own, because views do not take policies.

Postgres then did two things, both documented, neither obvious in the moment.

It ran the view as its owner, which is the migration role, which bypasses RLS. And Supabase's default privileges granted anon select on the new view, because it is a new object in the public schema.

The result was a readable, unfiltered, cross tenant summary of money, reachable with a key that is printed into the page source.

Why nobody caught it

This is the part worth sitting with, because the failure is not really the view.

The migration read correctly. Somebody reviewing it sees RLS enabled on invoices, a policy scoping rows by shop, and a view that selects from invoices. Every line is right. The conclusion drawn from those lines is wrong.

Nothing threw. No error, no warning, no failed assertion. The dashboard worked. The numbers were correct. Correct numbers are what you get when a query can see everything.

The tests passed. They were written against the same mental model as the code, so they asked whether the policy existed. It did.

Reading the migration files is not an audit. It never has been. A migration is a statement of intent, and the database is free to disagree with it.

The query that finds it

Ask the catalog instead. This lists every view in the public schema that is not running as the invoker, which means every view that is ignoring RLS on its base tables.

select c.relname as view_name,
       pg_get_userbyid(c.relowner) as owner,
       has_table_privilege('anon', c.oid, 'SELECT') as anon_can_select
  from pg_class c
  join pg_namespace n on n.oid = c.relnamespace
 where n.nspname = 'public'
   and c.relkind in ('v','m')
   and coalesce((c.reloptions::text like '%security_invoker=on%'), false) = false
 order by anon_can_select desc, c.relname;

Any row where anon_can_select is true is readable right now by the key in your page source. Run it before you finish reading this article. It takes a second and it is read only.

The fix is two lines

alter view public.invoice_totals set (security_invoker = on);
revoke all on public.invoice_totals from anon;

security_invoker = on makes the view run as whoever is calling it, so the policies on the base tables apply the way everyone assumed they already did. The revoke is belt and braces, and it matters because default privileges will keep handing anon a grant on the next new object too.

Then prove it as a real user rather than by rereading the migration.

set local role authenticated;
set local request.jwt.claims = '{"sub":"<some-user-id>","role":"authenticated"}';
select count(*) from public.invoice_totals;

If that count is the whole table, you have not fixed it.

The same shape, three more times

Once we started asking the catalog instead of the code, the same bug wearing different clothes turned up all over the database.

A grant that adds instead of restricting. grant execute on function f() to authenticated reads like a restriction. It is an addition. The function already had a grant to public, which includes anon, so the line changed nothing except the reviewer's confidence. Across 85 migrations that all read correctly, 48 of 53 security definer functions were callable by the anon key.

A table born with RLS off. create table as produces a table with row level security disabled, because the property does not come along with the data. Seven backup tables of names, phones and email addresses were readable with the site's own key. They had names like customers_backup_20260901 and everyone had forgotten they existed.

Permissive policies that combine with OR. Tightening one of five permissive policies tightens nothing, because any one of them being true is enough. A migration asserted the column existed, the function existed and the policy text existed. Every assertion passed. The behavior did not change by one row.

Every one of these is the same sentence. Something reads as a restriction and is actually an addition, a default, or a name. The file is honest. The database disagrees with it. Nothing announces the disagreement.

What to do about it

Three habits, and none of them are expensive.

Query the catalog for state. What is actually granted, which views are invokers, which tables have RLS on with zero policies. That last combination is safe, by the way. It fails closed.

Impersonate a user for behavior. set local role plus a JWT claim tells you what a real signed in person sees. It is the only thing that does.

Verify by calling, never by reading. This applies far past RLS. An update that matches zero rows returns 200. A deploy that published the wrong directory says Published. A scheduled job that has not run in six days looks identical to one that had nothing to do.

The database will answer honestly every time you ask it a direct question. The trouble only starts when you ask the migration file instead.