Supabase RLS patterns for multi-tenant agencies

The multi-tenant problem, stated precisely

You're building one app that serves many agencies, and each agency's data must be invisible to every other. The naive version — filter by tenant in your application's queries — fails the first time you forget a WHERE clause, or a new endpoint queries the table directly, or someone connects a BI tool. The isolation has to live in the database, not in the discipline of whoever writes the next query. In Postgres that means Row Level Security: policies the database enforces on every read and write no matter who's asking or how.

Tenant key strategy: one column beats one schema

Two models exist. Schema-per-tenant gives each agency its own set of tables; a tenant column (an org_id on every row) keeps one shared set of tables and tags each row. For an agency SaaS the column wins almost always. Schema-per-tenant sounds cleaner but turns every migration into an N-times loop, makes cross-tenant admin queries painful, and scales badly past a few dozen tenants. The org_id column with RLS is simpler to migrate, simpler to reason about, and the isolation guarantee is identical when the policies are right. Reach for schemas only under a hard compliance requirement for physical separation.

The policy set — all four verbs, not just SELECT

The mistake that leaks data is writing a read policy and stopping. RLS defaults to deny once enabled, so a missing policy blocks access — but a policy that only covers SELECT while you rely on the service role for writes quietly moves your write-path isolation back into application code, which is the thing you were trying to escape. Cover every verb:

alter table contacts enable row level security;

-- read: only your org's rows
create policy tenant_select on contacts
  for select using (org_id = (auth.jwt() ->> 'org_id')::uuid);

-- insert: you may only create rows tagged with your org
create policy tenant_insert on contacts
  for insert with check (org_id = (auth.jwt() ->> 'org_id')::uuid);

-- update: USING gates which rows you can touch,
--         WITH CHECK stops you from reassigning a row to another org
create policy tenant_update on contacts
  for update using (org_id = (auth.jwt() ->> 'org_id')::uuid)
             with check (org_id = (auth.jwt() ->> 'org_id')::uuid);

-- delete: only your org's rows
create policy tenant_delete on contacts
  for delete using (org_id = (auth.jwt() ->> 'org_id')::uuid);

The USING clause decides which existing rows a statement can see or touch; WITH CHECK validates the row after a write. An UPDATE needs both — without WITH CHECK, a tenant can read only their own row but rewrite its org_id to someone else's, moving data across the boundary you built the policy to defend.

The auth.uid() → org mapping

The policies above read org_id straight from the JWT. That's the fast path — no join, the value is in the token — but it's only as trustworthy as whatever mints the token. The org_id must be written into the JWT by a server-side process at login (a Supabase Auth Hook or a custom claim set from a trusted memberships table), never by the client. If a user can belong to more than one agency, the token carries the currently-active org and switching orgs re-mints the token. When you can't put it in the JWT, join instead — org_id in (select org_id from memberships where user_id = auth.uid()) — which is bulletproof but pays a lookup on every row check, so index memberships(user_id).

The three holes that leak data anyway

The service-role key bypasses RLS entirely. It's designed to — it's for trusted backend work. But that means any Edge Function or server route using the service role is running with the guardrails off, and a tenant filter you forgot there is a full cross-tenant leak with no policy to catch it. Treat the service role as infrastructure, scope what it does narrowly, and never hand it a value that came from a client. This is the same caution the Edge Function guide raises from the function side.

Leaky joins. RLS protects the table it's on. If contacts has policies but notes doesn't, a join from a locked table into an unlocked one exposes the unlocked side. Every table reachable from a tenant's queries needs its own policies — isolation isn't inherited across a join.

Missing WITH CHECK on insert/update, already covered above, is the third — and the most common miss in code review, because everything appears to work until a tenant deliberately writes a foreign org_id.

Testing RLS like you mean it

RLS is security code, so test it adversarially: not "can tenant A read their row" but "can tenant A read tenant B's row, and does the write path reject a foreign org_id." In Postgres you can set local role authenticated and set local request.jwt.claims to impersonate a tenant inside a transaction, then assert that a cross-tenant SELECT returns zero rows and a cross-tenant UPDATE is rejected. Run those as automated tests against a seeded two-tenant fixture. The pattern that mirrors this from the sync side is in the GHL→Supabase contact sync guide, where Realtime also has to respect these same policies.

Performance

An RLS policy is a predicate added to every query, so it needs to be cheap and indexable. Reading org_id from the JWT keeps the predicate a constant comparison — fast. The moment a policy contains a subquery (the memberships join), that subquery runs per query, so index its lookup column and keep the policy expression simple. Avoid functions in policies that Postgres can't inline, and always index the org_id column itself — it's now in the filter of literally every query against the table.

Browse 4,900+ ready-to-import workflow templates →