Multi-tenant isolation in Postgres: deny by default

Multi-tenant data isolation in Postgres is usually built on row-level security, and RLS is genuinely the right tool — enforcement lives in the database, so a forgotten WHERE clause in application code can’t leak another tenant’s rows. The danger is subtler: RLS that is enabled but wrongly permissive reads as protected in every review, while protecting nothing.

Enabled is not the same as enforced

Turning RLS on for a table denies everything by default. That part is safe. The risk enters with the first policy, because policies are permissive — they grant, and multiple policies OR together.

So a table with one careful tenant-scoped policy and one forgotten using (true) from a debugging session is fully readable. Nothing errors. Nothing looks wrong.

The habit worth forming: read every policy on a table together, never one at a time, and ask what the union permits.

Separate the roles that read from the roles that write

A pattern that has held up well for us: some tables should have no client-facing write path at all.

Our email log, suppression list and one-time codes are written only by server routes using a service-role key, and read only by admins. The policy set is deliberately incomplete:

alter table public.email_log enable row level security;

create policy email_log_admin_read on public.email_log
  for select using (public.is_admin_or_staff());
-- No insert/update policy. Service role bypasses RLS; everyone else is denied.

The absence of an insert policy is the security control. A leaked anon key exposes neither who you email nor when.

That said, service_role bypassing RLS is a loaded gun. It belongs only in server-side code paths, never in anything that reaches a browser bundle — and it’s worth a runtime assertion that the key you loaded is actually the service key, because pasting the anon key into that slot fails open: RLS-protected writes silently return “no rows” and every code path looks like it worked.

The SECURITY DEFINER trap

This one is easy to miss and completely undoes the rest.

A SECURITY DEFINER function runs with its creator’s privileges — which is the point, since it can do things the caller can’t. But Postgres grants EXECUTE on new functions to PUBLIC by default.

So a function you wrote to be called by trusted server code is, unless you say otherwise, callable by your anonymous role. If it consumes a one-time code or mutates tenant state, you’ve built a bypass around every policy on the table.

revoke all on function public.consume_email_code(citext, text, text) from public;
revoke all on function public.consume_email_code(citext, text, text) from anon, authenticated;

Also set search_path explicitly on definer functions, or a caller-controlled path can point your unqualified table names at objects they created.

Assert the invariants in the migration

Policies drift. Someone adds one to unblock a feature and nobody re-reads the union. So encode the invariant where it can fail loudly:

do $$
begin
  if exists (
    select 1 from pg_policies
     where schemaname = 'public' and tablename = 'email_codes'
       and (roles::text[] && array['anon','authenticated']) and cmd <> 'SELECT'
  ) then
    raise exception 'email_codes must not grant writes to anon/authenticated';
  end if;

  if has_function_privilege('anon', 'public.consume_email_code(citext,text,text)', 'execute') then
    raise exception 'anon can execute consume_email_code — bypass is open';
  end if;
end $$;

Now a future migration that removes the revoke fails at deploy rather than shipping quietly. This is the highest-value paragraph in the file, because it converts a silent regression into a loud one.

Test from outside the database

Reading policies proves what you intended. It doesn’t prove what the API does.

The test that actually counts: insert a row as service role, then hit your REST endpoint with the anon key and confirm you get nothing.

GET  /rest/v1/email_log        →  []
POST /rest/v1/email_suppressions →  42501 new row violates row-level security policy

An empty array where a row exists is the proof. We run this after every migration touching policies, because it’s the only check that exercises the whole path — role, policy, grant and function privilege together.

The four questions

For any table holding tenant data:

1. Is RLS enabled — and does the union of its policies permit only what you intend?
2. Do write paths exist for roles that shouldn’t have them?
3. Is every SECURITY DEFINER function revoked from PUBLIC?
4. Have you queried it as an untrusted role and seen nothing?

Question 4 is the one that finds real bugs. The other three are how you avoid creating them.

See also: storing one-time codes securely, and scoping agent credentials.