Multi-tenant Supabase: what row level security does not cover

Row level security is the reason I keep choosing Postgres for multi-tenant products. Isolation lives in the database, so a missing WHERE clause in application code is not a data breach.

That is a genuinely strong guarantee. It is also narrower than people assume, and I have watched three separate teams discover the same gaps.

The baseline that works

Tenant id on every table, one policy per table, resolved from the JWT:

create policy tenant_isolation on invoices
  for all
  using (tenant_id = (auth.jwt() ->> 'tenant_id')::uuid);

Put the tenant id in a custom JWT claim at login rather than joining through a memberships table in every policy. A join inside a policy runs on every row access and it will show up in your slow query log faster than you expect.

Gap 1: the service role bypasses everything

The service key ignores RLS entirely. That is the point — background jobs need it. The failure mode is that someone reaches for the service client in a request handler because a policy is inconveniently in the way, and now that endpoint has no tenant isolation at all.

I keep the service client in one module that refuses to be imported from route handlers, and every function in it takes an explicit tenantId. Not because that is airtight, but because it makes the dangerous thing visible in review.

Gap 2: storage is a separate system

This one has bitten every team I have worked with. You carefully lock down your tables, then upload tenant files to a bucket with a policy that says “authenticated users can read.”

Storage policies are their own thing and they do not inherit table RLS. Path structure has to carry the tenant, and the policy has to check it:

create policy tenant_files on storage.objects
  for select using (
    bucket_id = 'documents'
    and (storage.foldername(name))[1] = (auth.jwt() ->> 'tenant_id')
  );

Gap 3: functions run as their definer

A security definer function runs with the privileges of whoever created it, which is usually a superuser, which means RLS does not apply inside it. That is often exactly why you wrote it.

If it takes any parameter that reaches a query, validate the tenant explicitly inside the function body. A security definer function taking an invoice_id and not checking ownership is an enumeration endpoint with extra steps.

Gap 4: realtime is opt-in

Realtime respects RLS on the tables it is enabled for — but enabling replication on a table and forgetting the policy broadcasts every insert to every subscribed client. I have seen a staging environment stream other tenants’ rows into a browser console this way.

The performance trap

The subtle one. This policy looks fine:

using (tenant_id = (select tenant_id from members where user_id = auth.uid()))

Postgres may re-evaluate that subquery per row. On a 50,000-row table, sequential scan, and a query that took 4ms in development takes 900ms in production.

Two fixes. Wrap volatile calls so the planner treats them as constant — (select auth.uid()) rather than a bare auth.uid() — and put tenant_id first in your composite indexes, since every query filters on it.

Always explain analyze with RLS enabled and as a real user. Running as the table owner silently skips policies and tells you nothing about production.

Testing it

The test I actually trust: create two tenants with data, authenticate as one, and assert that every endpoint returns zero rows belonging to the other. Run it in CI. It catches the missing-policy-on-new-table mistake, which is the one that will genuinely happen, because someone will add a table at 6pm on a Friday and forget the alter table ... enable row level security.

Postgres does not enable it by default. A new table is wide open until you say otherwise. I have made that mistake myself.