Keep each customer's data apart

How to do it

Add a NOT NULL account column to every shared table, and include it in keys and indexes. In PostgreSQL, turn on row-level security with a policy that compares that column with a setting the app sets per request. Set it with SET LOCAL inside the transaction, so a pooled connection never carries it to the next request. Connect as a role that does not own the tables and has no BYPASSRLS, because owners and superusers skip the policies. FORCE ROW LEVEL SECURITY applies the policies to the owner too. When only your server queries the database, app checks are the first defence, as the object access rule says. When the browser queries it with a public key, as with Supabase's anon key, these policies are the only defence. Turn on row-level security for every table shows the policies for that case. Supabase's service_role key connects as a role with BYPASSRLS, so it skips every policy. Use it only in server code that checks the account itself.

Examples

SQL example

ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;
ALTER TABLE invoices FORCE ROW LEVEL SECURITY;
CREATE POLICY invoices_by_account ON invoices
  USING (account_id = current_setting('app.account_id')::bigint);
-- Per request, inside the transaction
SET LOCAL app.account_id = '42';
-- Check: roles that skip every policy. Expect admin and server roles only.
SELECT rolname FROM pg_roles WHERE rolbypassrls OR rolsuper;

How to check

Connect as the app's role with the setting for account A and select from every shared table. No row from account B comes back. With a browser client, do the same with the public key, signed in as a user of account A. The query of roles with BYPASSRLS lists only admin and server roles, never the app's or the browser's role.

Sources

• OWASP Multi-Tenant Security Cheat Sheet

Related rules

• DAT-67 Choose the isolation model before the first customer in Data and databases
• ACC-77 Check ownership on every record in Accounts and identity
• DAT-83 Turn on row-level security for every table in Data and databases
• INF-33 Force encryption on every managed service in Infrastructure and environments
• ACC-37 Give roles inside an organisation in Accounts and identity
• ACC-41 Let organisations own the shared data in Accounts and identity
• DEN-31 Filter shared dashboards by customer inside the warehouse in Data engineering
• SRH-10 Filter search results by access inside the query in Search