Multi-tenant isolation patterns in Supabase, compared
Tenant_id columns, membership joins, schema-per-tenant, separate projects - how each isolates tenants, what it costs, and the policies each requires.
Four architectures can isolate tenants on Supabase, and they trade isolation strength against operational cost very differently. This comparison shows the SQL each requires, the leaks each permits, and how to choose before your data model makes the choice for you.
Every multi-tenant app answers one question architecturally: where does tenant identity live? In a column, in a join table, in the schema name, or in an entirely separate database? Supabase supports all four, and each shapes what your policies look like, what mistakes are possible, and how expensive the next hundred customers become. The choice also tends to fossilize early — made by whichever pattern the first tables happened to use — so it deserves a deliberate look before growth cements it. That applies before launch too: design partners share real data under informal agreements, which is exactly the stage where a cross-tenant accident both happens and matters.
This article builds each pattern as runnable SQL, states what isolation it actually delivers — not marketing isolation, evaluated-by-the-database isolation — and ends with the decision factors that pick one for your product. The patterns are presented in increasing order of separation, which is also roughly increasing order of operational weight.
The four patterns at a glance
| Pattern | Isolation mechanism | Failure blast radius | Operational weight |
|---|---|---|---|
Shared tables + tenant_id | Policy predicate on every query | One missing clause exposes across tenants | Lowest |
| Shared tables + membership join | Predicate through a relationship table | Same class, plus join correctness matters | Low-moderate |
| Schema-per-tenant | Namespace separation + grants | Migration drift, cross-schema mistakes | High |
| Project-per-tenant | Physical database boundary | Essentially none within a project | Highest |
Read the last column honestly: most products should start at pattern one or two, and the main reason teams end up on three or four is compliance language or enterprise contracts rather than technical necessity. The sections below give you enough mechanics to make that call deliberately — including the failure modes each pattern inherits no matter how carefully it's implemented, because no architecture removes the need for correct policies so much as it relocates where correctness lives.
Pattern 1: the tenant_id column
Every row carries its tenant's identifier; every policy filters against the caller's tenancy:
create table projects (
id uuid primary key default gen_random_uuid(),
tenant_id uuid not null,
name text not null,
slug text not null,
created_by uuid not null
);
alter table projects enable row level security;
-- Tenancy source: a custom claim placed in the JWT at sign-in.
create or replace function current_tenant() returns uuid
language sql stable
as $$
select nullif(auth.jwt() ->> 'tenant_id', '')::uuid;
$$;
create policy "projects_tenant_read"
on projects for select
to authenticated
using (tenant_id = (select current_tenant()));
create policy "projects_tenant_write"
on projects for insert
to authenticated
with check (
tenant_id = (select current_tenant())
and created_by = (select auth.uid())
);
The current_tenant() helper stands in for wherever your tenancy comes from; the version above reads a custom claim populated at sign-in, which is the common choice. Because it is declared stable, (select current_tenant()) resolves once per statement exactly like (select auth.uid()) does. If tenancy equals user identity instead of an organization concept, substitute auth.uid() directly and compare against created_by; the structure is identical.
Strengths: simplest mental model; one schema to migrate; queries filter naturally; indexes do their job ((tenant_id, ...) composites); PostgREST, GraphQL, and Realtime all honor it uniformly.
Two implementation details separate working deployments from leaky ones. First, scope uniqueness by tenant — a slug or invoice number unique across the whole table leaks existence information and collides tenants; make it composite:
alter table projects
add constraint projects_tenant_slug_unique
unique (tenant_id, slug);
Second, writes need both identity and tenancy checks: the insert policy above requires the row to land in the caller's tenant and carry their user id as creator; update policies need using for the target row plus with check for the result, so a row can neither be edited from outside its tenant nor moved into another one. Delete mirrors update's using. Four clauses per command surface, all referencing server-verified values — that's the whole contract.
The failure mode is singular and famous: any query path that forgets the predicate crosses tenants. With correct policies this cannot happen through the API — the database adds the predicate whether the developer remembers or not. So the pattern's risk concentrates entirely into "are the policies right and still there?", which makes it a monitoring problem rather than an architecture problem. That's a good trade, and it's why the default recommendation for most SaaS starts here.
Costs: global analytics need deliberate aggregation paths; every sensitive table repeats the predicate (template it in migrations); and one fatuous policy edit widens everything at once — the blast radius column above is real.
Pattern 2: membership join for team semantics
When tenants have many users and users belong to many tenants, tenancy lives in a relationship table and access flows through a join:
create table workspace_members (
workspace_id uuid not null,
user_id uuid not null,
member_role text not null default 'member',
primary key (workspace_id, user_id)
);
alter table workspace_members enable row level security;
create policy "members_read_own"
on workspace_members for select
to authenticated
using (user_id = (select auth.uid()));
create table documents (
id uuid primary key default gen_random_uuid(),
workspace_id uuid not null,
title text not null,
owner_id uuid not null
);
alter table documents enable row level security;
create policy "documents_member_read"
on documents for select
to authenticated
using (exists (
select 1 from workspace_members m
where m.workspace_id = documents.workspace_id
and m.user_id = (select auth.uid())
));
create policy "documents_insert"
on documents for insert
to authenticated
with check (exists (
select 1 from workspace_members m
where m.workspace_id = documents.workspace_id
and m.user_id = (select auth.uid())
and m.member_role in ('member', 'admin')
));
Two details carry the whole pattern's safety, both verified repeatedly on this site. First, the membership table itself needs its own select policy — users read their own memberships — because policy subqueries evaluate under the caller's policies too; a deny-all members table silently neuters every EXISTS branch built on it, with no error anywhere. Second, role checks inside the subquery (member_role in (...)) let writes be stricter than reads: viewers see documents, only members and admins create them.
Guard the membership table's own writes with equal care, or the join becomes the leak: promotion to admin, self-invitation into arbitrary workspaces, and role forgery all arrive through INSERT/UPDATE on members. The pattern:
create policy "members_admin_manages"
on workspace_members for update
to authenticated
using (exists (
select 1 from workspace_members me
where me.workspace_id = workspace_members.workspace_id
and me.user_id = (select auth.uid())
and me.member_role = 'admin'
))
with check (
exists (
select 1 from workspace_members me
where me.workspace_id = workspace_members.workspace_id
and me.user_id = (select auth.uid())
and me.member_role = 'admin'
)
and member_role <> 'owner'
);
Only existing admins change memberships, and even they cannot mint the owner role through this path. Whatever your role vocabulary, write these clauses explicitly — role escalation is the multi-tenant equivalent of the ownership-transfer hole.
Strengths: models real org structures; roles ride along free; revoking membership revokes data access instantly, everywhere.
Costs: every sensitive-table policy repeats the join (or wraps it in a helper function, which then needs definer hygiene); the join runs on every row-group evaluation, so the membership table's primary key is load-bearing infrastructure; and the failure mode extends pattern one's — wrong predicates plus wrong joins both cross tenants. One more cost is behavioral: because membership changes take effect instantly, offboarding a fired employee mid-incident becomes a single DELETE — which is exactly what you want, provided you remember the row may still sit in caches, export files, and Realtime subscribers until their next authorization check.
Pattern 3: schema-per-tenant
Each tenant gets a namespace: acme.documents, globex.documents. Separation comes from Postgres namespace boundaries plus grants rather than predicates.
What it genuinely buys: a bug in one tenant's objects stays contained; per-tenant extensions or schema variants become possible; dropping a tenant is a clean namespace drop.
What it costs — and why it fights the platform: Supabase's API layer exposes configured schemas, so every new tenant schema means reconfiguring exposure; every migration now multiplies by tenant count (looping DDL, drift detection between schemas); pooled connections churn search_paths; and your application grows string-interpolated schema names, which is its own injection surface if names ever derive from input. Policies mostly disappear in this model — grants do the work — which trades readable rules for invisible ones.
The multiplication is the part teams underestimate. A single alter table becomes a loop over every tenant schema, each iteration able to succeed or fail independently; six months later, answering "do all tenants have the latest constraint?" requires its own audit tooling — you've reinvented drift management per namespace, manually. Grant reviews scale the same way: an API role's privileges must be correct in every schema simultaneously, so one over-broad grant replicated forty times is forty findings or one template fix, depending entirely on whether your tooling loops for you.
There are niches where it fits: dozens-to-hundreds of large enterprise tenants, contractual demands for logical separation, per-tenant customization requirements. For a typical self-serve SaaS with a tenant count that keeps climbing, it converts an operations problem you had into several you didn't.
Pattern 4: separate projects per tenant
The strongest boundary available: different physical databases, different credentials, different everything. Cross-tenant bugs require credentials from another tenant, which policies can't leak.
And the costs scale linearly with tenants: N projects means N migration pipelines, N sets of keys to rotate and monitor, N dashboards, and — usually fatally for product features — no cross-tenant query path for analytics, support tooling, or platform-wide features. This pattern fits extreme-isolation regimes: white-label deployments for giant customers, regulated workloads sold as dedicated instances, reseller models where the customer administers their own stack.
The honest signal that you belong here is contractual, not technical. When an enterprise agreement specifies dedicated infrastructure, pattern 4 is the product you're being asked to sell — price it accordingly, because every operational habit from this article (scans, tests, rotation drills) now runs once per deployment instead of once per company. Teams that arrive at pattern 4 to escape their own policy anxiety rather than to satisfy a contract almost always discover the anxiety was cheaper.
Choose it for business reasons, not fear of pattern-one bugs — those bugs are cheaper to prevent (monitoring, tests) than N-fold infrastructure is to operate.
Choosing, and moving later
Decision factors, weighted roughly by how often they decide:
| Factor | Points toward |
|---|---|
| Self-serve signup, many small tenants | Pattern 1 |
| Team/org semantics, roles, per-seat billing | Pattern 2 |
| Dozens of enterprise tenants demanding separation | Pattern 3 |
| Contractual physical isolation | Pattern 4 |
| Rich cross-tenant analytics | 1 or 2 (3 with effort, 4 never) |
Migration between patterns is possible but expensive — moving from shared tables to schema-per-tenant means rewriting every query and policy; moving from 1 to 2 is incremental (add the membership dimension alongside tenant_id). That asymmetry deserves a concrete sketch, because 1→2 happens in real products when solo accounts grow teams. Unlike the pattern blocks above, this one is illustrative rather than runnable as printed: it assumes you have already created the pattern-2 workspaces table carrying its own tenant_id.
-- Illustrative sketch. Assumes a `workspaces` table with a `tenant_id` column,
-- created earlier in the same migration.
alter table projects add column workspace_id uuid;
-- backfill: one workspace per existing tenant
update projects p set workspace_id = w.id
from workspaces w where w.tenant_id = p.tenant_id;
-- policies widen from tenant match to membership OR tenant-owner match
Existing policies keep working throughout — the tenancy predicate stays true while the membership branch extends it. This asymmetry is the real argument for starting at the light end: pattern 1 with correct policies scales further than most products ever need, and the leak-test playbook keeps it honest while you grow.
Whichever pattern you run, prove it the same way: two tenant fixtures, five adversarial probes, expected results stated in advance. For pattern 2, the expected matrix reads:
| Caller | Own workspace's docs | Other workspace's docs | Public/shared rows |
|---|---|---|---|
| Alice (member of A) | Full per policies | [] | Per shared-row policy |
| Bob (member of B) | [] for A's docs | Full per policies | Per shared-row policy |
| Anonymous | [] everywhere | [] everywhere | Nothing unless deliberately public |
Isolation claims are cheap; isolated demonstrations are nearly as cheap and worth infinitely more. The full probe set lives in the B2B tenant leak test.
Common questions
Can I mix patterns?
Yes, deliberately: pattern 2 for core collaboration data, pattern 4-style dedicated projects for one whale customer, pattern 3's namespace trick for staging isolation. Mixing accidentally is the danger — document which entities follow which pattern, because contributors will otherwise assume uniformity that doesn't exist.
Does tenant_id indexing really matter that much?
It's the difference between flat and linearly-degrading latency. Every policy predicate touches tenant_id on every query; unindexed, each scan reads the whole table. Composite indexes led by tenant_id serve both policies and ordinary list views — the indexing guide shows the plan shapes to confirm.
How do custom JWT claims fit in?
They're the cleanest way to carry tenancy for pattern 1: populate a claim at token issuance via the auth hook, read it in policies through a wrapper like current_tenant(), and tenants can never forge their claim because tokens are signed server-side. The trade-off versus joining a members table per query is speed versus freshness — claims change only at re-issue.
How do Realtime subscriptions interact with these patterns?
Postgres-changes subscriptions evaluate the table's SELECT policies per subscriber, so pattern 1 and 2 isolation extends to live updates automatically — Bob's client simply never receives Alice's rows. Verify it anyway: subscribe as both test tenants and confirm neither sees the other's inserts. The subtler Realtime risk isn't row data leaking, it's presence/metadata channels configured more openly than their underlying tables.
Can guests access shared links without accounts?
Yes, if designed: anonymous-read policies keyed to a share token (a random column on the shared row, matched against the request) let one row out without opening the tenant. The mistake is broadening the whole tenant's policy to make sharing work — share tokens isolate at row granularity, tenant_id-wide anon policies don't.
Not sure which pattern your current project actually implements? Run the free scan — paste your app URL, and check what the outside world sees of your tenancy boundaries today.
RowShield is an independent product and is not affiliated with, endorsed by, or sponsored by Supabase, Inc.