跳到正文
DEV Community· Tomo·· 2 小時前AI 評分57

Supabase RLS 多租戶資料隔離：小型發票入口網站的真實登入測試

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

AI 導讀

Tomo 示範以 Supabase RLS 為小型發票入口網站設定多租戶資料隔離，並透過 SQL、API 與真實登入測試讀寫權限。測試中，陌生訪客看不到發票，Alice 和 Bob 各自只能查看 2 張，跨租戶寫入會回傳 42501。

正文

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

The invoice portal logged in as Acme: 3 invoices, a form to add one, and developer tests.

What you will build

The browser logs in with Supabase Auth, gets a token, and queries Postgres without a tenant filter; RLS policies add the filter.

  1. Three tables: tenants, memberships (which user belongs to which company) and invoices.
  2. RLS policies: you can read invoices of the companies you belong to, and add invoices only to those.
  3. Checks as a stranger, as Alice (Acme) and as Bob (Globex), and a write test.
  4. A one-file portal that logs people in and shows their company's invoices.

Before you start

  • Cost. Free plan, nothing billable. This uses logins and ordinary reads and writes. Free includes 50,000 monthly active users (Supabase docs, checked 2026-10-22). I built only the "all customers in one project" design; I did not build or price one project per customer.
  • Risk. The "before RLS" step deliberately leaves data readable. Do it only in a throwaway project with fake data.
  • Security. The portal uses only the publishable key. Never put a service_role or secret key in a page or a repo.
  • Not verified. Update and delete policies, admin or viewer roles, performance with many rows.

1. Users, schema and seed

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):

Query result: four invoices, two for Acme Inc. and two for Globex Corp.

2. Before RLS: what can a stranger see?

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;

Result: all four invoices of both companies are visible to anon.

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.

3. Turn RLS on and add policies 

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.

Query result: four policies, two on invoices, one on memberships and one on tenants.

4. Check as three kinds of people

The anon check, now with RLS on:

begin;
set local role anon;
select count(*) as visible_invoices from public.invoices;

Result: visible_invoices is 0.

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;

Result as Alice: only Acme's two invoices.

Change the email to bob@example.com and you get Globex's two:

Result as Bob: only Globex's two invoices.

(Remember rollback; after each of these.)

5. Writes, and a trap

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);

Error: 42501 new row violates row-level security policy for table

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:

Result: rows_inserted is 0.

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.

6. The invoice portal

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):

Result: Acme Inc. linked to demo-acme and Globex Corp. linked to demo-globex.

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:

Portal as Globex: 2 invoices, $5,300, Training workshop and Data migration.

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:

Developer tests: 3 rows came back; the insert was rejected with 42501.

7. What went wrong when I used it

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":

Before: the header says Acme Inc., yet the list includes Globex's Training workshop and Data migration; the count says 5 and the total $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

After: a company switcher in the header, and 3 invoices of Acme Inc. totalling $2,150.

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.

Security

  • config.js is git-ignored; only the publishable key belongs there.
  • Until RLS is on, the table is readable with the public key alone. Never run the "before" step on a project with real data.
  • Demo passwords never go in the repo.

Not verified

  • Update and delete policies, and roles such as admin and viewer.
  • Speed with many rows or many policies, and indexes on the policy columns.
  • Password reset and other auth flows.
  • One project per customer: not built, no numbers.
  • Browsers other than Chrome.

Cleanup

Run sql/00_reset.sql and delete the four demo users.

Recap

  • With no policies, a stranger saw all 4 invoices, through SQL and through the real API.
  • With RLS and four policies: 0 for a stranger, 2 for Alice, 2 for Bob, a 42501 error for a cross-company write.
  • A write test that raises no error may have done nothing. Put the forbidden value in explicitly.
  • Correct policies do not make a correct screen: test users with two companies.
  • Everything here was tested with fake data on a tiny table.

The code is in the repo: https://github.com/tomozilla/rls-multitenancy-demo-en. The plain-language version: https://medium.com/p/dd72e0e13bed.

來源：DEV Community · dev.to

#教學/實踐#部署/工程