0
Challenge · 37Functions & Triggers in the Database
PL/pgSQL & functions: SECURITY INVOKER vs DEFINER
Dataused in ~75% of apps
You've written SQL that answers questions. A Postgres function lets you save that SQL under a name and call it like any other value — but the moment you do, a question appears that a plain query never had to answer: whose privileges does this code run with? Get it right and a function is the cleanest security boundary in your stack. Skip one clause and you've built a hole an attacker can walk an owner's privileges straight through — and it will pass every manual test, because the exploit needs no special access at all.
Core idea. A Postgres function runs its body as one of two identities, chosen by a single clause. SECURITY INVOKER — the default — runs it as whoever called it, so its queries obey the caller's RLS. SECURITY DEFINER runs it as the function's owner, always, regardless of who calls it — which is how it can legitimately read past a caller's RLS. Because Postgres resolves an unqualified table name using search_path, and because every session — even one with zero special grants — can create an object in its own pg_temp schema, which is always searched first, an unpinned SECURITY DEFINER function can be tricked into running the owner's privilege against an attacker's counterfeit table. `set search_path = ''` plus fully-qualified names is the fix, and on a DEFINER function it is mandatory, not optional.
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
calls the function as
bob (authenticated)
bob has
EXECUTE on the function, and nothing more.→
function body runs
executes as
bob (the caller)
bob's RLS policies apply — he sees only what he's normally allowed to see
SECURITY INVOKER (the default) runs the function body as whoever called it. Its internal queries are subject to the caller's row-level security, exactly as if bob had typed them himself in the SQL editor.
The rule:INVOKER inherits the caller's RLS — use it as the default. DEFINER inherits the owner's privileges, including
BYPASSRLS if the owner has it — use it only when the whole point is to read past a policy, and always with set search_path = ''.↑ play a little first, then dig in