Two customer companies, one Supabase project, one invoices table. How do you make sure each company sees only its own rows? This post walks through Row Level Security (RLS) from "no rules" to "tested through real logins": SQL, the checks that prove it, and a small invoice portal where the page has no tenant filter at all, because the database does the filtering.
AI disclosure: I used Claude to help draft this post. I ran every step in my own project, checked the results, and edited the text. The mistakes are mine.
Repo: https://github.com/tomozilla/rls-multitenancy-demo-en
Plain-language companion (no code, no product names): https://medium.com/p/dd72e0e13bed
tenants, memberships (which user belongs to which company) and invoices. service_role or secret key in a page or a repo.
In Authentication, Users, Add user, Create new user (tick Auto Confirm User), create alice@example.com and bob@example.com. Choose any passwords; the SQL checks below do not need them. For the portal you will also create demo-acme@example.com and demo-globex@example.com later.
create table public.tenants (
id uuid primary key default gen_random_uuid(),
name text not null
);
create table public.memberships (
tenant_id uuid not null references public.tenants(id),
user_id uuid not null references auth.users(id),
primary key (tenant_id, user_id)
);
create table public.invoices (
id bigint primary key generated always as identity,
tenant_id uuid not null references public.tenants(id),
title text not null,
amount_usd int not null
);
The SQL Editor warns that you are creating tables without RLS. For this demo choose Run without RLS; the point is to see what happens without it.
Seed two companies, link Alice to Acme and Bob to Globex, and add 4 invoices (the full file is sql/02_seed.sql):
In the SQL Editor, pretend to be someone who is not logged in (anon):
begin;
set local role anon;
select t.name as tenant, i.title, i.amount_usd
from public.invoices i join public.tenants t on t.id = i.tenant_id
order by i.id;
-- afterwards: rollback;
All 4 rows. That is the SQL Editor pretending, so I also asked the real API with only the publishable key, the one every app ships with:
curl -s "https://<PROJECT_REF>.supabase.co/rest/v1/invoices?select=title,amount_usd&order=id" \
-H "apikey: <publishable key>" -H "Authorization: Bearer <publishable key>"
[{"title":"Support plan","amount_usd":300},
{"title":"Website renewal","amount_usd":1200},
{"title":"Training workshop","amount_usd":800},
{"title":"Data migration","amount_usd":4500}]
Both companies' invoices, no login. In my project the table in the exposed schema was readable until RLS was on.
alter table public.tenants enable row level security;
alter table public.memberships enable row level security;
alter table public.invoices enable row level security;
With RLS on and no policies, nobody (anon or logged in) can read anything. Now the policies:
-- You can see only your own membership rows
create policy "members see their own memberships" on public.memberships
for select to authenticated
using (user_id = (select auth.uid()));
-- You can see only the tenants you belong to
create policy "members see their own tenants" on public.tenants
for select to authenticated
using (id in (select tenant_id from public.memberships
where user_id = (select auth.uid())));
-- You can see only the invoices of the tenants you belong to
create policy "members see their tenant's invoices" on public.invoices
for select to authenticated
using (tenant_id in (select tenant_id from public.memberships
where user_id = (select auth.uid())));
-- You can add invoices only to the tenants you belong to
create policy "members add invoices to their tenant" on public.invoices
for insert to authenticated
with check (tenant_id in (select tenant_id from public.memberships
where user_id = (select auth.uid())));
Two things worth knowing. to authenticated means a stranger matches no policy, so gets nothing. And (select auth.uid()) is wrapped in a select on purpose: the Supabase docs recommend that form for performance. I did not measure the difference.
The anon check, now with RLS on:
begin;
set local role anon;
select count(*) as visible_invoices from public.invoices;
The real API now answers [] for the same curl as before. Then Alice, by faking her login claims in the SQL Editor:
begin;
select set_config('request.jwt.claims',
json_build_object('sub', (select id from auth.users where email = 'alice@example.com'),
'role', 'authenticated')::text, true);
set local role authenticated;
select t.name as tenant, i.title, i.amount_usd
from public.invoices i join public.tenants t on t.id = i.tenant_id
order by i.id;
Change the email to bob@example.com and you get Globex's two:
(Remember rollback; after each of these.)
Can Alice add an invoice to Globex? Write the other company's id out explicitly:
begin;
select set_config('app.globex_id',
(select id::text from public.tenants where name = 'Globex Corp.'), true);
select set_config('request.jwt.claims',
json_build_object('sub', (select id from auth.users where email = 'alice@example.com'),
'role', 'authenticated')::text, true);
set local role authenticated;
insert into public.invoices (tenant_id, title, amount_usd)
values (current_setting('app.globex_id')::uuid, 'Sneaky invoice', 1);
That is the policy working. Now the trap. My first version of this test looked up Globex inside the insert:
insert into public.invoices (tenant_id, title, amount_usd)
select id, 'Sneaky invoice', 1 from public.tenants where name = 'Globex Corp.';
No error. I counted the inserted rows:
Zero. Alice cannot see Globex's tenant row, so the select returned nothing, so nothing was inserted. It looks like success and proves nothing. To test a write policy, put the forbidden value in explicitly and look for the error.
Now through a real login. Create demo-acme@example.com and demo-globex@example.com (random passwords, Auto Confirm), then link them to tenants (sql/app/01_portal_users.sql):
The page is one HTML file using supabase-js from a CDN. The part that matters is this:
// NOTE: there is no "filter by tenant_id" condition here. The database (RLS) decides which rows are visible.
const { data: all, error: e2 } = await supabase.from('invoices').select('id, tenant_id, title, amount_usd').order('id')
Run it from a copy that has your own config.js (project URL and publishable key):
cd app
cp config.example.js config.js
python3 -m http.server 8765 --bind 127.0.0.1 # open http://127.0.0.1:8765/index.html
Logged in as Acme, the page shows 2 invoices and $1,500. I added a "Hosting fee" of $650 and it became 3 invoices, $2,150 (the screenshot at the top). Logging out and in as Globex:
The two "Developer tests" buttons prove the point from the browser: "Read everything, with no filter" returns only your company's rows, and "Try to add an invoice with another tenant's ID" is rejected with 42501:
The page lied about a user with two companies. I linked demo-acme to Globex as well, to see what happens. RLS did exactly the right thing: that user may see both companies' invoices. But my page took the first membership as "the company", so it showed "Acme Inc." above a list that mixed both, with "Invoices of your company: 5" and "$7,450":
The database was right and the screen was wrong. The fix is a company switcher, plus filtering the display to the selected company:
const tenants = mem.map((m) => ({ id: m.tenant_id, name: m.tenants.name }))
const tenant = tenants.find((t) => t.id === currentTenantId) || tenants[0]
// ...
const invoices = all.filter((r) => r.tenant_id === tenant.id) // display only: RLS already limited `all` to my companies
That filter is for display, not for security. The protection is still RLS.
Failed inserts use up invoice numbers. After my failed write tests, the invoice I added in the portal got id 6, not 5. Identity numbers are consumed even when the insert is rejected. If you need gapless invoice numbers, do not use the identity column as the invoice number.
The silent no-op from section 5 is the third one, and the one I would most want you to remember.
config.js is git-ignored; only the publishable key belongs there.
Run sql/00_reset.sql and delete the four demo users.
The code is in the repo: https://github.com/tomozilla/rls-multitenancy-demo-en. The plain-language version: https://medium.com/p/dd72e0e13bed.