Field notes: deny-by-default RLS for multi-tenant apps

Most multi-tenant data leaks we see in assessments aren't exotic. They're a WHERE org_id = ? that somebody forgot on one query out of two hundred. Application-level tenancy filtering fails open: forget the filter once, and the database happily returns everything.

Postgres row-level security inverts that. Enable RLS on a table and define no policies, and the answer to every query is nothing. Forgetting something now fails closed. These are the patterns we use in our own client portal, in the order we apply them.

1. Enable RLS before you need it

Turn RLS on in the same migration that creates the table — even if auth doesn't exist yet and you can't write real policies:

create table if not exists projects (
  id uuid primary key default gen_random_uuid(),
  org_id uuid not null references organizations (id) on delete cascade,
  title text not null
);
 
alter table projects enable row level security;
-- No policies yet. Deny-all is the correct default.

A table that's readable by nobody is annoying in development and safe in production. The reverse trade is much worse.

2. Resolve "who is asking" in one place

Policies need to answer what org does the caller belong to and is the caller staff. Answer those in security definer helper functions rather than repeating subqueries in every policy — it keeps policies readable, and it sidesteps the classic RLS-recursion trap on the users table:

create or replace function app_org_id() returns uuid
language sql stable security definer set search_path = public as
$$ select org_id from users where auth_id = auth.uid() $$;
 
create or replace function app_is_staff() returns boolean
language sql stable security definer set search_path = public as
$$ select exists (
     select 1 from users
     where auth_id = auth.uid() and role in ('staff_admin', 'staff_member')
   ) $$;

Two details that matter:

• security definer means the function runs as its owner, bypassing RLS on users — without it, a policy on users that calls a function that reads users recurses.
• set search_path = public pins name resolution. A definer function without a pinned search path is a privilege-escalation footgun.

3. Write policies as "mine, or I'm staff"

With the helpers in place, tenant policies collapse to one readable line each:

create policy projects_select on projects for select
  using (org_id = app_org_id() or app_is_staff());
 
create policy projects_write_staff on projects for insert
  with check (app_is_staff());

Note the asymmetry: clients read their org's rows, but only staff write. Being explicit per-verb (for select, for insert, for update) beats a single for all policy — you'll almost always want different rules per verb eventually.

4. Prove isolation with a probe, not a promise

RLS is testable. Impersonate a tenant in a throwaway transaction and count what they can see:

begin;
select set_config('request.jwt.claims',
  json_build_object('sub', '<some-user-auth-id>', 'role', 'authenticated')::text, true);
set local role authenticated;
 
select count(*) from projects;     -- expect: only their org's rows
select count(*) from submissions;  -- expect: 0 for non-staff
 
rollback;

Run probes like this after every policy change. "The app looked right" is not evidence — the app goes through your query layer, which was never the thing you were worried about.

The takeaway

Broken features get bug reports. Silent leaks get incident reports. Choose the failure mode you'd rather debug.