Row Level Security in Practice: Multi-Tenant Isolation on Supabase

A two-person AI SaaS came to me with a problem that's becoming a pattern. They'd built a document-analysis product on top of an LLM, customers were signing up, and every customer's documents lived in the same Postgres database, separated by nothing but a workspace_id column and the discipline of whoever wrote the last query. One forgotten WHERE clause away from showing customer A's contracts to customer B.

They had no backend team and no budget for one. They were already on Supabase for auth and storage. So instead of building an authorization layer in application code, we pushed tenant isolation down into the database with Row Level Security, where a forgotten WHERE clause stops being a data breach and starts being an empty result set.

The Mental Shift

RLS inverts the usual model. Normally your API decides what rows a user can see, and the database trusts the API. With RLS enabled, the database itself refuses to return rows the current user isn't entitled to, no matter what the query says.

alter table documents enable row level security;
 
create policy "members read their workspace documents"
on documents for select
using (
  workspace_id in (
    select workspace_id from workspace_members
    where user_id = auth.uid()
  )
);

auth.uid() is the Supabase helper that reads the user ID out of the verified JWT. Every query through the client hits this policy. A buggy frontend query, a missing filter in a new endpoint, even a curious user poking the auto-generated REST API directly: all of them get filtered to the workspaces that user belongs to. The floor is isolation, not exposure.

The Policy Set That Worked

The naive version is one permissive policy per table and done. The version that survived review separates read and write paths, because membership and authorship are different questions:

-- Reads: any member of the workspace
create policy "read" on documents for select
using (workspace_id in (select workspace_id from my_workspaces()));
 
-- Inserts: members, and the row must claim their workspace
create policy "insert" on documents for insert
with check (workspace_id in (select workspace_id from my_workspaces()));
 
-- Deletes: only workspace admins
create policy "delete" on documents for delete
using (
  workspace_id in (
    select workspace_id from workspace_members
    where user_id = auth.uid() and role = 'admin'
  )
);

Two things in there took me a while to appreciate. with check on inserts matters as much as using on reads, because without it a member of workspace A can insert rows stamped with workspace B's ID. And my_workspaces() is a security definer SQL function wrapping the membership lookup, which exists for performance reasons that deserve their own section.

Mistake One: Policies That Tank Performance

Our first policies inlined the membership subquery everywhere, and Postgres re-evaluated it per row. On a table with a few hundred thousand documents, list queries went from 40ms to over 2 seconds. The fix was twofold: wrap the membership lookup in a security definer function so Postgres treats it as a stable initplan instead of a correlated subquery, and index (workspace_id, user_id) on the membership table.

The lesson generalizes: write your policies, then run explain analyze on your hottest queries as an actual tenant user. RLS performance problems are invisible when you test as the service role.

Mistake Two: The Service Role Bypasses Everything

Supabase gives you two kinds of keys. The anon/publishable key respects RLS. The service-role key does not; it walks straight past every policy. That's by design, for migrations and admin jobs.

The near-miss: an early background job (the LLM pipeline that processes uploaded documents) used the service-role client and wrote results back with a workspace_id taken from the job payload. A malformed payload during testing wrote one tenant's analysis into another tenant's workspace. RLS never had a chance to object, because the service role had quietly opted out of the entire security model.

The rule we adopted: service-role code never trusts identifiers from a payload. It re-derives the workspace from the source row it's processing, and we wrapped the service client in a module that requires an explicit, greppable bypassRls: true flag so reviewers can see every escape hatch in one search.

Mistake Three: Believing Policies Without Testing Them

Policies are code, and untested code is folklore. We added a pgTAP suite that runs in CI against a seeded database: create two workspaces, two users, sign JWTs for each, and assert the matrix. User A reads their document, fine. User A reads user B's document, zero rows. User A inserts into workspace B, error.

select results_eq(
  $$ select count(*) from documents $$,
  $$ values (3::bigint) $$,
  'tenant A sees only their three documents'
);

The suite caught a regression within the first month, when a new table shipped with RLS enabled but no policies at all. That fails closed (nobody could read anything), which is the good direction, but it's still an outage. Enabled-without-policies is the most common RLS bug I've seen since.

Where Supabase Earned Its Keep

Beyond RLS itself, the surrounding pieces mattered. Auth issued the JWTs that policies key on, with no token plumbing on our side. Storage respects the same policy model, so tenant files got the same isolation as tenant rows. And pgvector runs natively, which meant the document embeddings for the RAG features lived in the same database, under the same policies, instead of in a separate vector store with its own half-baked auth story. One security model across rows, files, and vectors is worth a lot when your whole team is two people.

When I'd Do It Differently

For a product with heavyweight compliance needs (per-tenant encryption keys, data residency), schema-per-tenant or database-per-tenant still wins, and RLS becomes a defense layer rather than the whole strategy. And if your access rules are genuinely complicated (delegation, time-boxed grants, approval chains), policies turn into unreadable SQL and you want a real authorization service in front.

But for the common case, a small team shipping a multi-tenant SaaS on Postgres, RLS on Supabase is the highest ratio of isolation-per-engineering-hour I've found. The database becomes the one place where tenant boundaries are defined, enforced, and tested, and the application code above it gets to be ordinary and a little careless without anyone's documents leaking.