Designing Multi‑tenant Isolation with Supabase Row Level Security

The problem: scattered tenant checks

When building a SaaS application that serves many organizations, it is tempting to add a WHERE tenant_id = current_tenant() clause to every query. This approach works, but it couples data‑access logic to every service, repository, or raw SQL string you write. A missed clause or a typo can leak data across tenants, and auditing that no query bypasses the filter becomes a maintenance burden.

Thesis: let Postgres enforce isolation once

Supabase gives you a managed Postgres database where Row Level Security (RLS) is available out of the box. By enabling RLS on a table and writing policies that reference the JWT claims issued by Supabase Auth, the database itself filters rows on every SELECT, INSERT, UPDATE, or DELETE. Application code can then focus on business logic, trusting that the data layer guarantees tenant isolation.

How Supabase Auth feeds RLS

When a user logs in, Supabase Auth returns a JSON Web Token (JWT). Inside Postgres, the helper functions auth.uid() and auth.jwt() expose the user’s UUID and any custom claims you added (e.g., tenant_id). Policies can reference these functions directly, eliminating the need for an extra round‑trip to fetch tenant information.

Typical multi‑tenant schema

-- run in the Supabase SQL editor (requires owner or superuser rights)
create table projects (
    id uuid primary key default gen_random_uuid(),
    tenant_id uuid not null,
    name text not null,
    created_at timestamptz default now()
);
-- enable RLS on the table
alter table projects enable row level security;

The tenant_id column stores the organization to which each project belongs. Indexing this column helps the planner use an index scan when policies filter on it.

Policy patterns

• Ownership‑based: auth.uid() = owner_id – useful when each row belongs to a single user.
• Tenant‑scoped: auth.jwt() ->> 'tenant_id' = tenant_id::text – isolates data per organization.
• Hybrid: combine ownership and tenant checks for extra safety.

Worked example: tenant‑scoped projects table

Below is a minimal, end‑to‑end illustration you can copy into the Supabase SQL editor. Replace placeholders with your actual values.

-- 1. Create a table that stores tenant_id
create table projects (
    id uuid primary key default gen_random_uuid(),
    tenant_id uuid not null,
    name text not null
);

-- 2. Turn on RLS
alter table projects enable row level security;

-- 3. Create a policy that limits SELECT to the tenant encoded in the JWT
create policy tenant_select on projects
    for select
    using ( auth.jwt() ->> 'tenant_id' = tenant_id::text );

-- 4. Insert policy: only allow rows with the caller’s tenant_id
create policy tenant_insert on projects
    for insert
    with check ( auth.jwt() ->> 'tenant_id' = tenant_id::text );

-- 5. (Optional) Update/DELETE policies mirror the SELECT rule
create policy tenant_update on projects
    for update
    using ( auth.jwt() ->> 'tenant_id' = tenant_id::text )
    with check ( auth.jwt() ->> 'tenant_id' = tenant_id::text );

create policy tenant_delete on projects
    for delete
    using ( auth.jwt() ->> 'tenant_id' = tenant_id::text );

Where to run: The SQL editor in the Supabase dashboard (or via psql with the postgres role). You need owner privileges to alter the table and create policies.

Expected check: After inserting a few rows with different tenant_id values, log in as two distinct users whose JWTs contain different tenant_id claims. Run select * from projects; under each session; each user should see only the rows matching their tenant.

Verifying the plan

To confirm the policy uses an index, examine the query plan:

explain (analyze, buffers)
select * from projects where tenant_id = '00000000-0000-0000-0000-000000000001';

Look for an Index Scan on projects_tenant_id_idx (create the index if missing: create index on projects(tenant_id);). If you see a Seq Scan, the policy expression may be preventing index usage—consider making the expression immutable and indexed.

Trade‑offs and limitations

• Service role bypass: The Supabase service role key disables RLS entirely. Any backend code that uses this key must enforce its own tenant checks or wrap queries in SECURITY INVOKER functions to avoid accidental data exposure.
• Planning stability: Policies that call volatile functions (e.g., now()) or contain subqueries can inhibit plan caching, leading to higher latency under load. Keep predicates simple and deterministic.
• Schema change lock: Altering or dropping a policy acquires a short‑term exclusive lock on the table, which can queue concurrent INSERT/UPDATE/DROP statements during deployments. Plan migrations during low‑traffic windows or use concurrent validation where possible.
• No protection against privileged functions: A SECURITY DEFINER function runs with the owner’s privileges, bypassing RLS unless explicitly marked SECURITY INVOKER. Audit such functions carefully.

Actionable closing steps

1. Add a tenant_id column to every table that needs isolation.
2. Create a matching index (create index on table(tenant_id);).
3. Enable RLS and write a select policy that compares auth.jwt() ->> 'tenant_id' to the column.
4. Mirror the policy for INSERT/UPDATE/DELETE as needed.
5. Test with two users belonging to different tenants; verify row visibility diverges.
6. Run EXPLAIN on representative queries to confirm index usage.
7. Review any backend code that uses the service role key; ensure it does not unintentionally bypass tenant checks.

By letting Postgres handle the filter, you centralize the isolation logic, reduce the chance of leaks, and keep your application code focused on what it does best—delivering features to your users.