Why hostname routing can't isolate tenants in a Supabase app

I recently worked on taking a construction ERP from single-tenant to multi-tenant SaaS. The app is a React SPA that talks straight to Supabase: around 110 tables and views and 56 RPC functions, with no application server in between. That last detail ended up shaping every decision.

The tempting design

Each customer gets its own domain, so the obvious move is to read the hostname in Next.js middleware, work out the tenant, and scope everything to it.

The problem is that data requests never pass through that middleware. The SPA calls PostgREST directly with the user’s JWT, and anyone can open devtools and replay the same request with a different filter. Hostname routing is fine for choosing which logo to show. It is not a security boundary.

Where the boundary actually is

The only thing Postgres can trust is a signed JWT. So the tenant lives in the user’s app_metadata (which users can’t edit themselves), and Row Level Security reads it from there.

1. A tenant_id on every base table

Shared schema, with a tenant_id column on every base table. At this size that’s simpler to migrate and run than a schema per tenant.

2. RESTRICTIVE policies

Postgres combines permissive policies with OR, so a permissive policy added later for some feature can quietly widen access. A restrictive tenant policy is ANDed with everything else, so it holds whatever other policies exist:

create policy tenant_isolation on projects
  as restrictive
  for all
  to authenticated
  using (tenant_id = (auth.jwt() -> 'app_metadata' ->> 'tenant_id')::uuid)
  with check (tenant_id = (auth.jwt() -> 'app_metadata' ->> 'tenant_id')::uuid);

3. Stamp tenant_id on write

Don’t trust the client to send the right tenant. A BEFORE INSERT OR UPDATE trigger sets it from the JWT:

create function stamp_tenant() returns trigger
language plpgsql as $$
begin
  new.tenant_id := (auth.jwt() -> 'app_metadata' ->> 'tenant_id')::uuid;
  return new;
end $$;

create trigger stamp_tenant
  before insert or update on projects
  for each row execute function stamp_tenant();

4. Composite foreign keys

RLS stops you reading another tenant’s rows, but a row in tenant A could still point at an id that belongs to tenant B. Making foreign keys composite, (tenant_id, project_id) referencing (tenant_id, id), makes that reference structurally impossible:

alter table projects add unique (tenant_id, id);

alter table invoices
  add constraint invoices_project_fk
  foreign key (tenant_id, project_id)
  references projects (tenant_id, id);

The parts that are easy to forget

• RPC functions. security definer functions bypass RLS, so each one needs its own tenant guard. With 56 of them, that’s a real checklist.
• Permissions. Boolean permission columns don’t scale across tenants. A normalized permission catalog with per-user grants does.
• Tests. The only proof of isolation is a test that signs in as tenant A and tries to read, write and reference tenant B’s data. That suite belongs in CI.

Takeaway

If your frontend talks to the database directly, isolation has to live in the database. Routing decides what users see; signed claims and RLS decide what they can touch.