Multi-Tenant Data Isolation in Supabase with Row Level Security

You've built a SaaS app on Supabase. Every table has an org_id column, and every query in your API layer filters by the caller's organization. Then someone adds a new endpoint and forgets the filter. One missed WHERE clause and tenant A is reading tenant B's invoices. Row Level Security (RLS) exists to make that mistake structurally impossible: the authorization check moves from your application code, where it can be forgotten, into PostgreSQL, where it runs on every query no matter which client issued it.

The thesis of this piece is simple: for multi-tenant Supabase apps, RLS should be your enforcement layer and your application filters should be a convenience, not a control.

How RLS actually works in Supabase

PostgreSQL lets you attach policies to a table. A policy is a boolean expression evaluated per row; rows that fail the check are invisible to SELECT and rejected for writes. Supabase wires this to its auth layer: when a request arrives with a user's JWT, the database role is set to authenticated and the function auth.uid() returns that user's ID from the token. Anonymous requests use the anon role, where auth.uid() returns NULL.

Two details matter more than the syntax:

• Enabling RLS is a separate step. ALTER TABLE ... ENABLE ROW LEVEL SECURITY turns it on. Until you do, policies you wrote do nothing. Once enabled with no policies, the default is deny — which is the safe direction, but verify your table state rather than assuming it.
• The service role bypasses RLS entirely. The service_role key is for trusted server-side code only. If it ever ships to a browser or mobile client, every policy you've written is decorative.

A worked example: org-scoped projects

The common pattern is a membership table that maps users to organizations, then policies that check membership. Run this in the Supabase SQL editor (it requires database owner privileges, which the dashboard SQL editor has):

create table org_members (
  org_id uuid references organizations(id),
  user_id uuid references auth.users(id),
  primary key (org_id, user_id)
);

create table projects (
  id uuid primary key default gen_random_uuid(),
  org_id uuid not null references organizations(id),
  name text not null
);

alter table org_members enable row level security;
alter table projects enable row level security;

create policy "members read own org projects"
on projects for select
to authenticated
using (
  org_id in (
    select org_id from org_members
    where user_id = auth.uid()
  )
);

create policy "members insert into own org"
on projects for insert
to authenticated
with check (
  org_id in (
    select org_id from org_members
    where user_id = auth.uid()
  )
);

Note the two clauses: USING filters which existing rows are visible, and WITH CHECK validates rows being written. For INSERT you need WITH CHECK; without it, a user could insert a row into someone else's org even though they can't read it. UPDATE and DELETE need their own policies — a SELECT policy alone does not grant write access.

With this in place, the client query is just supabase.from('projects').select(). No filter needed; the database applies the org boundary. If a malicious user tampers with the request, the worst they can do is still bounded by their own membership rows.

The performance trade-off you should plan for

That subquery against org_members runs for every row PostgreSQL considers. On small tables you won't notice. On a million-row table with a policy that joins or subqueries, you can turn a 5 ms query into a 500 ms one. Practical mitigations, in rough order of effort:

1. Index the columns the policy touches — here, org_members(user_id) and projects(org_id).
2. Wrap auth.uid() in a select: (select auth.uid()). This lets PostgreSQL treat it as a constant for the query instead of re-evaluating it per row. This is a well-known optimization in the Supabase community, but confirm the behavior on your Postgres version with EXPLAIN ANALYZE.
3. If membership checks stay hot, consider storing org_id in a JWT custom claim so the policy reads from the token instead of hitting a table. That adds token-refresh complexity, so treat it as a later optimization, not a starting point.

Check the real cost with EXPLAIN ANALYZE on a representative query while impersonating an authenticated role — don't benchmark as the superuser, because RLS is bypassed for table owners by default and your numbers will lie to you.

Verify it before you trust it

Policies are code, and untested code is broken code. A minimal verification loop:

1. Query the table with the anon key and no JWT. Expect zero rows or a permission error — never data.
2. Sign in as a test user in org A and confirm you see only org A's rows.
3. Attempt an INSERT with org B's org_id while authenticated as the org A user. Expect a row-level security violation.

For regression safety, encode these as pgtap tests (Supabase ships a testing helper for this) so a future schema change can't silently reopen a hole.

Closing

RLS won't design your tenancy model for you — you still decide whether isolation is per-user, per-org, or something messier — but it guarantees the model is enforced at the one layer every query must pass through. Start with the membership-table pattern above, index what the policies touch, and write the three verification checks before you ship. Your future self, reviewing an incident that didn't happen, will appreciate it.