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.
Playground, this isn't a screenshot
create view all_posts as
  select * from posts;
-- security_invoker defaults to OFF
bob queries the viewruns as: the VIEW OWNER
titleownerbob sees it?
Public postalicevisible
Alice draftalicevisible
Bob draftbobvisible

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