Enforcing Tenant Isolation with Supabase Row Level Security

Problem: accidental data bleed in a multi‑tenant SaaS

When you build a SaaS where each customer (tenant) owns its own set of records, a single mistaken query can expose another tenant’s data. Relying only on application‑level checks is fragile; a bug or a direct database call can bypass them.

Thesis: let Postgres Row Level Security (RLS) do the filtering for you

Supabase exposes Postgres’ RLS feature through its dashboard and SQL API. By tying a policy to a JWT claim (e.g., tenant_id), the database automatically adds a WHERE clause to every query, insert, update, or delete that matches the current user’s tenant. This gives you a defense‑in‑depth layer that works even if the application code is compromised.

Enabling RLS and mapping the JWT claim

1. Enable RLS on the table you want to protect:

alter table documents enable row level security;

1. Make the JWT claim available as a Postgres session variable. Supabase Auth already sets a custom claim tenant_id; you can expose it with:

-- run once per session (Supabase client does this automatically)
select set_config('app.tenant_id', current_setting('request.jwt.claim.tenant_id'), false);

If you prefer to keep the variable name short, you can create a helper function that runs on connection:

create or replace function set_tenant_from_jwt()
returns trigger language plpgsql as $$
begin
    perform set_config('app.tenant_id', current_setting('request.jwt.claim.tenant_id'), false);
    return null;
end
$$
;

create event trigger set_tenant_on_connect
on sql_drop
when tag in ('CONNECT')
execute procedure set_tenant_from_jwt();

(Supabase’s Auth flow already injects the claim into the session, so the explicit set_config call is often unnecessary; the example shows how you could do it manually.)

Creating a tenant‑isolated policy

A typical policy for the documents table looks like this:

create policy tenant_isolation
on documents
for all
using (tenant_id = current_setting('app.tenant_id')::uuid)
with check (tenant_id = current_setting('app.tenant_id')::uuid);

The using expression filters rows for SELECT, UPDATE, and DELETE. The with check expression ensures that any new or updated row carries the correct tenant_id.

Worked example: two tenants, isolated data

Imagine two users:

• User A belongs to tenant aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa
• User B belongs to tenant bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbbb

Both users sign in via Supabase Auth; their JWTs contain the respective tenant_id claim.

1. User A inserts a document:

-- executed via supabase-js from User A's client
supabase.from('documents').insert({ title: 'Notes A', tenant_id: 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa' });

1. User B inserts a document:

supabase.from('documents').insert({ title: 'Notes B', tenant_id: 'bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbbb' });

1. Each user queries the table:

supabase.from('documents').select('*');

User A receives only the row with tenant_id = 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa'. User B receives only the row with tenant_id = 'bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbbb'. No row leaks across tenants.

Trade‑offs and limitations

• Planning overhead: Every query now includes the policy expression; if the expression is not indexed, you may see a sequential scan. Keep the filtered column (tenant_id) indexed.
• Policy order matters: Policies are evaluated in the order they were created. A permissive policy placed before a restrictive one can unintentionally grant access. Test with a deny‑by‑default stance (no policies) then add the restrictive policy first.
• Superuser bypass: If you connect as postgres or a role with bypassrls, RLS is ignored. Ensure your application never uses such credentials for normal operations.
• Debugging complexity: When a query returns zero rows, the cause could be a missing policy, a mismatched claim, or a data issue. Enable log_statement = 'all' temporarily in Postgres to see the rewritten query.

Verification steps you can run today

1. Create a test table and enable RLS:

alter table test_docs enable row level security;

1. Add the isolation policy (as shown above).

1. Sign in two users via Supabase Auth, each with a distinct tenant_id claim in their JWT.

1. Insert a row for each tenant using the respective user’s client.

1. Query the table as each user; confirm each sees only their own row.

1. Optionally, simulate the claim directly in SQL:

set app.tenant_id = 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa';
select * from test_docs;
-- should return only rows where tenant_id matches the UUID above

If the results match expectations, the RLS layer is working. If not, check the policy order, the session variable value, and whether any bypass role is in use.

Actionable closing

Row Level Security gives you a database‑level guarantee that tenants cannot see each other’s data, complementing your application logic. Start by enabling RLS on a single table, add a simple tenant_id policy, and verify with two test accounts. Once confident, extend the pattern to all tables that store tenant‑scoped data, index the tenant column, and monitor query plans for any performance impact. This approach reduces the surface area for data‑leak bugs and lets you focus on building features rather than reinventing access controls.