The Policy Was Enabled. One Role Still Read Across Tenants.

The upper path is checked row by row. The lower path never meets the policy.

A multi-role SaaS heading for a controlled pilot usually has the same shape: Next.js on the front, Supabase and Postgres behind it, authentication working, core workflows clicking through in demos. The question the pilot answers is narrower: can one user, role, or organization reach rows that belong to another. I build Genesis, a multi-tenant developer platform on GCP. Customers design backend infrastructure on a visual canvas, the platform compiles that to Terraform and applies it inside the customer’s own Google Cloud project. Go is my strongest language, Node and TypeScript daily. That combination is why I audit data isolation at the database layer first, because the app can look correct while the policy underneath stays open.

The failure that repeats is not a missing toggle. RLS is on. A policy exists. One role still reads across tenants, because the read took a path the policy never constrained.

RLS constrains queries, not the whole system

Row Level Security in Postgres applies when a query runs as a role that the policy covers, with the request identity attached. In Supabase that means the anon key plus the user JWT, evaluated per row against an expression like tenant matches the authenticated user. It does not apply to the service role key, which bypasses RLS by design for server jobs, migrations, and admin work.

That split is the entire audit. Every read in the app must be assigned to one of two buckets: checked by a policy, or intentionally holding the service key with its own guard. A read with no bucket is a finding. I learned the reporting habit on infrastructure handover work, where absence from an inventory is indistinguishable from absence from the cloud, so an area that cannot be checked is reported as a finding, not as silence. The same rule holds here: an endpoint whose database path you cannot trace gets written up, not waved through.

The audit starts with two users and one forbidden row

Theory reviews miss open policies because the policy text reads sensibly. Probe rows catch them because the database answers directly. The setup is small on purpose: two tenants, one user in each, one private row per tenant, plus one role with a cross tenant job like a dispatcher or an admin who should see a bounded set.

alter table work_orders enable row level security;

create policy tenant_isolation
on work_orders
for all
to authenticated
using (
  tenant_id = (
    select tenant_id from profiles where id = auth.uid()
  )
)
with check (
  tenant_id = (
    select tenant_id from profiles where id = auth.uid()
  )
);

The shape matters more than the syntax. The tenant comes from the token through auth.uid() joined to a profiles table, never from a field the client sends. The using clause guards reads. The with check clause guards writes. If either clause pulls the tenant from the request body, the policy is decoration, because the caller chooses their own boundary.

Then query as each user and record what returns:

import { createClient } from "@supabase/supabase-js";

const url = process.env.SUPABASE_URL ?? "";
const anon = process.env.SUPABASE_ANON_KEY ?? "";

const asAlice = createClient(url, anon);
await asAlice.auth.signInWithPassword({ email: "alice@tenant-a.test", password: "test-password" });
const own = await asAlice.from("work_orders").select("id, tenant_id");
const cross = await asAlice.from("work_orders").select("id").eq("tenant_id", "tenant-b");

const asBob = createClient(url, anon);
await asBob.auth.signInWithPassword({ email: "bob@tenant-b.test", password: "test-password" });
const bobOwn = await asBob.from("work_orders").select("id, tenant_id");

if (cross.data && cross.data.length > 0) {
  throw new Error("isolation broken: tenant A read tenant B rows");
}

A passing app plus a failing probe is the normal shape of this bug. The UI never offers the cross tenant link, so manual clicking never finds it. A direct select with a guessed id does. The pilot will contain at least one curious user, so the probe models the pilot honestly.

Four steps, same order every table. A table that skips the probe keeps its finding open.

The bypass is usually the service key in app code

In the audits I have run on Node and TypeScript backends, the open door is rarely a clever SQL injection. It is a service role key sitting where a user key belongs. The pattern looks innocent: a Next.js API route creates one admin client at import time and reuses it for every request, then applies the tenant filter from the query string. RLS sees the service role, steps aside, and the string filter becomes the only boundary. Any caller who edits the string reads another tenant.

The fix keeps the service key but moves it. User facing reads use the anon key with the caller JWT forwarded, so policies evaluate. The service key lives only in server jobs with no request identity: cron reconciliation, storage cleanup, outbound webhooks. Each of those states its tenant scope in code and logs it, because a key that sees everything needs a reader that checks what it touched. I keep provider tokens sealed the same way in edge functions, where the browser holds a session and the function holds the secret, which I described in the token boundary post. The principle transfers directly: identity travels with the request, secrets stay server side.

A second variant hides in realtime and storage. Table policies pass, then a Supabase Realtime subscription or a storage bucket ships with its own permissive rule, and rows or files cross tenants through the side channel the test never queried. The audit lists those channels explicitly: which tables broadcast, which buckets serve, which edge function reads with elevated rights. Anything unlisted is closed before the pilot.

Auth identity must come from the token

Multi-role apps add one more failure above tenant isolation: role confusion. A policy that checks a role column the client can write lets a member promote themselves by updating their own profile. The guard is a profiles table the user cannot write to directly, with tenant and role set by a trigger or a privileged function at invite time, plus a unique constraint on user id so one identity cannot hold two tenant rows that disagree.

alter table profiles enable row level security;

create policy profile_read_own
on profiles for select to authenticated
using (id = auth.uid());

create policy profile_no_self_write
on profiles for update to authenticated
using (id = auth.uid() and false)
with check (false);

Self reads stay allowed so the app can render the session. Self writes to role and tenant are refused, which reads harshly and is the point: membership changes go through an invite function that checks the inviter holds an admin row in the same tenant. That function is small enough to review in one screen, and its tests attempt the forbidden promotion and assert refusal, the same way ledger constraints ship with a test that attempts the unbalanced write. Happy path tests show the feature works. Refusal tests show the control exists.

New tables inherit the gap

Pilots grow schema weekly, and each migration is a chance to reopen isolation. A new table ships without RLS enabled, or with RLS enabled and no policy, which denies all reads until someone unblocks the demo with a permissive rule that allows all. Both states must fail CI before merge. The check is a query against the catalog plus the probe suite: every user facing table has RLS on, every one has at least one restrictive policy, and the cross tenant probe returns nothing for a fresh migration. I run a parallel habit on generated infrastructure, where the review belongs upstream before the generator emits the config, because catching the shape at generation time beats patching hundreds of emitted resources later.

Error handling deserves one paragraph because pilots surface it first. A policy refusal returns nothing, which the UI renders as an empty list. Users read emptiness as missing data and retry, support reads it as a bug, and someone widens the policy to stop the tickets. Log policy denials distinctly from empty results, with tenant hash and query target, never with row contents. When the pilot reports a blank screen, the log should answer whether the rows were absent or merely invisible to that caller.

What I would tell someone building this

Trace every read to a bucket before the pilot. Either a user JWT evaluated by a policy, or the service key inside a named server job. An endpoint with no bucket is a finding, and a finding with no owner stays open.

Derive tenant from the token on every policy. If the tenant id arrives from the client body or query string, the boundary moves with the request. Join through the authenticated identity and refuse self promotion to another tenant.

Quarantine the service key and log what it touches. One server client per job, tenant scope stated in code, row counts and tenant hash in the log. A key that bypasses RLS earns review proportional to what it can read.

Probe per role, plus realtime and storage, on every migration. Seed rows, query as each tenant, assert cross reads return nothing, repeat for subscriptions and buckets. New tables enter the suite before they enter the pilot.