Querying a reporting view returned a project's real task counts to a user with zero membership in that project. Row-level security should have scoped that row out. It came back anyway, because the view itself was never checking who was asking.
This is Atlas, a project-management app I built. It was caught in development, before the app shipped to anyone beyond test accounts and a few colleagues who signed up to try it out. No real user's data was ever exposed. It's still worth writing up in full, because the mechanism behind it is a genuine, documented Postgres behavior that a lot of people writing row-level security assume doesn't apply to them.
This isn't the same bug as last time
I've written before about RLS in this schema, specifically the difference between USING and WITH CHECK on a policy, proven with a real two-user session showing one tenant can't read, write, or delete another's rows. That piece is about policy design: writing the right conditions. This one isn't. The policies on the underlying table here were correct the entire time, four of them, covering read, create, update, and owner-only delete. Every one of them did exactly what it was supposed to do.
The leak lived somewhere RLS doesn't reach on its own: a view sitting on top of that table.
The underlying table itself carries four RLS policies: one letting project members read their own tasks (tasks: project members can read), separate ones gating creation and updates the same way, membership or ownership, and a fourth restricting deletion to the project owner (tasks: owner can delete). Query that table directly, as any authenticated user, and every one of those policies does exactly what it says. The bug never touched them.
security_invoker: the setting RLS quietly assumes you have
Postgres's own CREATE VIEW documentation states it plainly:
If any of the underlying base relations has row-level security enabled, then by default, the row-level security policies of the view owner are applied, and access to any additional relations referred to by those policies is determined by the permissions of the view owner. However, if the view has security_invoker set to true, then the policies and permissions of the invoking user are used instead, as if the base relations had been referenced directly from the query using the view.
Read that first sentence again. A view created by a privileged role, a migration role, typically, since that's who runs CREATE VIEW, inherits that role's relationship to RLS when someone else queries through it. RLS checks the owner's identity by default, not whoever's actually asking.
This isn't an oversight in Postgres's design. It's a deliberate, decades-old pattern. Owner-privilege views are how you let someone query a narrower slice of a table without granting them any access to the table itself: create a view selecting only the columns or rows they should see, grant them access to the view alone, and the view's own owner is who Postgres checks against the underlying table. That pattern predates row-level security entirely, and it's genuinely useful. It just quietly stops being what you want the moment the table underneath also has RLS policies meant to scope data per user, because now two different privilege systems disagree about whose identity actually matters.
The fix is a single boolean, security_invoker, added in Postgres 15, released in October 2022. Set it, and the view stops being a backdoor around RLS and starts behaving the way most people already assume a view does. This isn't a rare edge case or a recent addition. It's been available, opt-in, for years. Atlas runs Postgres 17.6, so this was never a version gap. The setting was never applied.
The view, before and after
The original view, from the migration that created it and untouched since:
create view public.project_task_stats as
select
project_id,
count(*) as total_tasks,
count(*) filter (where status = 'done') as done_tasks
from public.tasks
group by project_id;No privilege option specified at all, which meant Postgres fell back to the only behavior that existed before security_invoker: run as the view's owner, full stop.
Fixing it didn't touch that query. The entire fix is a separate, one-line ALTER VIEW:
alter view public.project_task_stats set (security_invoker = true);That's it. The view's actual SELECT never needed to change, only how Postgres decides whose privileges apply when someone queries it. Worth sitting with that for a second: the exact same query, run by the exact same view, returns a correctly scoped result or a cross-tenant leak, purely based on one storage option most CREATE VIEW walkthroughs never mention.
A second, smaller trap: grants don't carry over either
security_invoker alone wasn't enough to make the view usable. A view doesn't inherit SELECT privileges from the tables it queries. Even once RLS is correctly scoped to the invoking user, that user still needs an explicit grant on the view itself.
Two separate things had to be true before this view worked correctly: security_invoker = true, so RLS checks the right identity, and an explicit GRANT SELECT on the view, so that identity is even allowed to query it in the first place. Miss the grant on its own and the view breaks in the safe direction, a 42501 permission error instead of a leak. Miss security_invoker and it fails in the dangerous one.
Proving it, both ways
One setting, same query, same user, same project, opposite result. Proven in a single continuous psql session, no reconnects between the two halves, because a leak proof and a denial proof only mean something side by side if nothing else about the connection changed in between.
A user with zero membership on the target project, session set up the way the app itself sets it up. The third argument to set_config matters: false, not true. true scopes the claim to the current transaction, and in autocommit each statement is its own transaction, so the identity would quietly disappear before the very next line ran.
SET ROLE authenticated;
SELECT set_config('request.jwt.claims',
'{"sub":"5251e552-3559-45d1-8aff-bc92ee8a1fa4","role":"authenticated"}', false);
set_config
-----------------------------------------------------------------------
{"sub":"5251e552-3559-45d1-8aff-bc92ee8a1fa4","role":"authenticated"}
(1 row)With security_invoker unset, the pre-fix state:
SELECT auth.uid();
uid
--------------------------------------
5251e552-3559-45d1-8aff-bc92ee8a1fa4
(1 row)
SELECT * FROM public.project_task_stats
WHERE project_id = '56d97989-33dd-4632-b0fc-a561a7c99602';
project_id | total_tasks | done_tasks
--------------------------------------+-------------+------------
56d97989-33dd-4632-b0fc-a561a7c99602 | 1 | 0
(1 row)A row came back. Real task counts, for a project this user has no relationship to at all. Nothing about the query is wrong, the WHERE clause filters correctly on project_id, and the user's session is set up exactly the way a real authenticated request would be, role switched, JWT claim set, nothing spoofed or elevated. The leak was never in what's being asked. It's in whose permissions Postgres checks before answering, and that's precisely the distinction a query itself can never reveal. You'd have to already know to ask.
Same session, fix applied:
RESET ROLE;
ALTER VIEW public.project_task_stats SET (security_invoker = true);Then the same user, re-asserted in that same session, before touching the view again:
SET ROLE authenticated;
SELECT set_config('request.jwt.claims',
'{"sub":"5251e552-3559-45d1-8aff-bc92ee8a1fa4","role":"authenticated"}', false);
set_config
-----------------------------------------------------------------------
{"sub":"5251e552-3559-45d1-8aff-bc92ee8a1fa4","role":"authenticated"}
(1 row)
SELECT auth.uid();
uid
--------------------------------------
5251e552-3559-45d1-8aff-bc92ee8a1fa4
(1 row)Same UUID as before, not null. That match is the whole point of checking it here: it rules out the obvious false explanation for what comes next, a query returning nothing because nobody's logged in rather than because RLS correctly denied a real, identified non-member. With that identity confirmed live on the connection, the same query against the same view:
SELECT * FROM public.project_task_stats
WHERE project_id = '56d97989-33dd-4632-b0fc-a561a7c99602';
project_id | total_tasks | done_tasks
------------+-------------+------------
(0 rows)Nothing. The same RLS policy that already protected the underlying table directly now protects it through the view too, because the view finally checks the querying user's identity instead of its own owner's, and the auth.uid() result two lines up confirms exactly whose identity that was.
What actually caught this, and what didn't
Before writing this up, I ran Supabase's own schema linter against both states, live: the vulnerable version and the fixed one.
No schema errors found
{"results":[],"message":"db lint"}
Identical output, both times. The view never appeared in either run, flagged or otherwise. The linter checks a defined, known set of schema issues. A view quietly running as its owner isn't one of them.
Supabase's Security Advisor, the tool actually aimed at this class of problem, wasn't reachable from a local, unlinked development stack at all. It requires an authenticated connection to a hosted project. So the honest claim isn't "the linter caught it" or "the linter missed it, but the Advisor would have." It's narrower than that: nothing automated in this local workflow checked for it. The only thing that did was deliberately querying the schema as an unprivileged user, with something specific to look for. That's adversarial testing doing a job a static check genuinely couldn't do here, not because the tooling is bad, but because "does this view check RLS as the right person" isn't a syntax question.
When this actually bites you
This only matters if two things are both true at once: the view sits over a table with RLS enabled, and the view's owner has a different, usually more privileged, relationship to that RLS than the people who'll actually query it. That's the common case for any view created by a migration role and queried by an application role, which describes most Postgres-as-a-service setups running RLS at all.
security_invoker is set per view, not once for the whole database. There's no single flag that makes every view in a schema safe going forward. Every view over an RLS-protected table needs the same one-line check, individually, including any created after this one gets fixed.
None of this applies if the underlying tables have no RLS at all, or if a view is deliberately meant to aggregate across a boundary for a role that's supposed to see everything, a reporting role, an admin dashboard. In those cases, the owner-privileges default is what you actually want, not a bug waiting to be found.
The harder part isn't fixing one view once you've found it. It's noticing you have this class of view at all. Nothing about a view like this looks wrong on inspection. It compiles, it returns sensible-looking columns, it answers the query you asked it. The only way to actually find out whose permissions it's checking is to ask it a question you already know the honest answer to, from an account that shouldn't be able to answer yes.