RLS was enabled and tenants could still read each other's data — the SECURITY DEFINER hole, and the query that catches it

RLS protects rows from the invoking user's context. It does nothing about a function that runs SECURITY DEFINER — that function executes with the owner's rights, and unless it re-implements tenant filtering internally, every caller rides past your policies. This isn't theoretical: I found exactly this in my own multi-tenant app. RLS was on, policies looked right, and a single RPC that returns usage data still crossed tenants — because it filtered by an org_id the caller passed in as an argument.

The mechanics, in one paragraph. Postgres evaluates RLS against the role executing the query. SECURITY DEFINER swaps that role for the function owner — typically the table owner, which your policies exempt or treat as privileged. If the function does SELECT * FROM usage WHERE org_id = $1 without pinning the tenant to the caller's org, the caller chooses $1. RLS never gets a vote.

How I caught it. Not by reading code — by writing an adversarial test that logs in as org A, calls the RPC with org B's id, and asserts zero rows come back. Rows came back. That test ships in the project's hardening suite now, and the fix went out as a numbered migration.

Three fixes to consider, in order of preference: (1) make the definer honor RLS anyway — explicit org_id filter derived from caller identity (in Supabase, auth.uid() from the JWT; in plain Postgres, current_user or a session GUC), never from arguments; (2) if the function must be definer for a reason, SET search_path explicitly and REVOKE from roles that shouldn't call it; (3) add a regression test that tries the cross-tenant call — if you only test the happy path, this class of bug ships.

Audit query to run today — list every SECURITY DEFINER function in your database, then check each one filters by caller identity rather than arguments:

sql

select n.nspname, p.proname
from pg_proc p join pg_namespace n on n.oid = p.pronamespace
where p.prosecdef = true;
Full write-up with the migration that fixed it is on my site's postmortems page: https://andrady.co/postmortems/security-definer. Disclosure: I also sell a multi-tenant Next.js + Supabase starter (B2B SaaS OS) that ships this test suite — but everything above is useful whether or not you ever look at it.