0
Challenge · 36Correct & Fast
Views & security_invoker
Schemaused in ~55% of apps
DevLog wants one clean query for the home page — published posts joined to their authors — instead of every route repeating the same JOIN. A view is the obvious answer. Ship it as written, though, and a week later a security review shows a stranger reading every private draft through it, even though the RLS policy on the underlying table is completely correct and has not changed. The view itself was the leak.
Core idea. A view is not just a saved SELECT — by default, Postgres runs it with the privileges and row-visibility of the view's OWNER, not the person querying it. That means a view can both bypass the caller's RLS policies and grant access to data the caller was never given a GRANT on. `security_invoker = on` (Postgres 15+; always available on Supabase's PG17) flips that: the view then runs as the actual caller, honoring RLS and GRANTs exactly as if the query had been written directly against the table.
poke it, drag it,
try to break it
try to break it
no slides here.
you drive.
you drive.
Playground, this isn't a screenshot
create view all_posts as
select * from posts;
-- security_invoker defaults to OFFbob queries the viewruns as: the VIEW OWNER
| title | owner | bob sees it? |
|---|---|---|
| Public post | alice | visible |
| Alice draft | alice | visible |
| Bob draft | bob | visible |
The owner created posts, so the owner's row-visibility (and grants) apply — not bob's. Every policy is evaluated as if the owner were asking. Bob, querying as himself, gets everything, including a post he was never supposed to see.
↑ play a little first, then dig in