Multi-Tenant Data Isolation with Supabase RLS, Tested Through Real Logins in a Small Invoice Portal

A developer built a demo invoice portal on Supabase that enforces multi-tenant data isolation entirely through Row Level Security (RLS), with no tenant filter in the application page itself. Testing with the publishable API key showed that before RLS was enabled, all four invoices from both seeded companies were readable without any login; after enabling RLS and adding membership-based policies on the tenants, memberships, and invoices tables, access was restricted to each user's own tenant. The demo, published on GitHub, walks through the SQL policies and verifies them through real logins.

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 https://github.com/tomozilla/rls-multitenancy-demo-en Plain-language companion no code, no product names : https://medium.com/p/dd72e0e13bed 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: js 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 https://github.com/tomozilla/rls-multitenancy-demo-en . The plain-language version: https://medium.com/p/dd72e0e13bed https://medium.com/p/dd72e0e13bed .