Postmortem · Bug 01 of 03

Cross-tenant SECURITY DEFINER: the hole RLS can't fix

Bug 01 from our own hardening suite — RLS was enabled the entire time.

The one-sentence version: a privileged SECURITY DEFINER function in our own multi-tenant app trusted a client-supplied tenant identifier — and RLS, which was enabled the entire time, could not stop it. SECURITY DEFINER exists precisely to step around RLS. If that step-around trusts its caller, your tenant isolation has a hole at its most privileged point.

Why SECURITY DEFINER exists

Row Level Security applies to whoever is executing a query. Functions marked SECURITY DEFINER are the exception: they run with the privileges of the function's owner, not the caller. In a Supabase project, migrations typically run as the table owner — and Postgres applies RLS to everyone except the table owner by default. So a SECURITY DEFINER function owned by the same role bypasses the policies entirely.

That's not a flaw — it's the tool you reach for when a job legitimately needs to write rows the caller can't touch: recording metered usage from an API request, accepting an invitation atomically. The function is the trust boundary. Which raises the only question that matters: what does the function trust?

The bug

Our usage-recording RPC accepted a client-supplied organization identifier and wrote metered usage against it — without independently verifying that the caller was actually a member of that organization. RLS did not apply to this code path (owner privileges), so the tenant check that would normally exist in policy form simply wasn't there. The function trusted the caller's claim about whose tenant it was acting on.

The consequence: a caller could submit an organization id belonging to a different tenant, and the privileged function would act on it — recording usage against another tenant's meters. A cross-tenant write, executed by the one function in the system designed to bypass the protections.

RLS was enabled the entire time. That is the entire point of this bug: enabling RLS does not protect you if a SECURITY DEFINER function trusts client input.

How we caught it

Not in code review — the function looked reasonable and did its job correctly on the happy path. It was caught by our own adversarial hardening suite. One of the suite's files, security-definer-boundaries.test.ts, exists for exactly this class of bug: it calls every privileged function in the schema with another tenant's identifiers and asserts the call fails. For this RPC, the forged call succeeded.

That's the difference between declaring an invariant and proving it. “We use RLS” was true. “A tenant cannot act across tenants” was false — and only an executable attack against the real database with the real migrations could tell the two apart.

The fix

The rule fits in one sentence: a SECURITY DEFINER function must re-derive ownership from the authenticated caller — never from its arguments. The shape of the problem, simplified:

-- runs as the table owner: RLS not applied
create function check_and_record_usage(p_org_id uuid, ...)
returns boolean
language plpgsql
security definer
as $$ begin
  -- trusts the caller's claim about the tenant
  insert into usage_records (organization_id, metric, amount, period)
  values (p_org_id, p_metric, p_amount, to_char(now(), 'YYYY-MM'));
  return true;
end;
 $$;

-- re-derive membership from the authenticated caller
if not exists (
  select 1 from organization_members m
  where m.organization_id = p_org_id
    and m.user_id = auth.uid()   -- the caller, not an argument
) then
  raise exception 'FORBIDDEN: not a member of this organization';
end if;

-- only then touch the usage records
insert into usage_records (organization_id, metric, amount, period)
values (p_org_id, p_metric, p_amount, to_char(now(), 'YYYY-MM'));

The shipped fix is migration 000027_fix_usage_rpc_security.sql — the RPC now verifies membership from the caller's identity before touching any rows (the real guard reads IF NOT public.is_organization_member(p_org_id) THEN RAISE EXCEPTION; the snippet above is the simplified pattern). The same migration also pins the function's search_path — a second hardening measure against search_path hijacking of privileged functions. The regression test that caught the bug runs in CI on every push (npm run test:hardening): a refactor that reintroduces this fails the build, not a customer.

The checklist rule

This bug is check #1 in our Hardening Checklist: every SECURITY DEFINER function independently verifies tenant ownership. If you have even one SECURITY DEFINER function in your schema and you cannot point at the exact code inside it that validates the caller's tenant — that is where to look next. Not because you wrote a bug, but because this is the one place where the database will not catch it for you.