Multi-tenancy: the query that eventually forgets the tenant
Every multi-tenant leak in history is one query missing one WHERE clause. The only durable fix is to make writing that query hard rather than to ask people to be careful, because over enough queries and enough years, somebody will not be.
Where it goes wrong
- A new endpoint written by someone who did not know about the convention.
- A report or export, written in a hurry, often against the database directly.
- A lookup by id. "It is a UUID, it is unguessable" - until it appears in a URL somebody shares.
- Search. The filter is applied to the results instead of to the query, and the count is wrong even when the rows are not.
- A background job, which has no request and therefore no obvious tenant.
Make it structural
Three approaches, in increasing order of guarantee:
| Approach | Guarantee | Cost |
|---|---|---|
| A convention plus review | None, statistically | Free until it fails |
| A base query every read starts from | Strong, if nothing bypasses it | Discipline about raw SQL |
| Row-level security in the database | The database refuses | Session variables, some complexity |
| A database per tenant | Total | Operational cost per tenant |
The strongest cheap option is the third. With row-level security, a query that forgets the tenant returns nothing rather than everything - which turns a data breach into a visible bug, and those are the good kind.
Test for it, not just around it
Write the test that creates two tenants, puts data in both, and asserts that every list endpoint returns only one tenant's rows. Generate it over the endpoints rather than writing it once, so a new endpoint is covered the day it exists rather than the day somebody remembers.
The operator exception
Support needs to see a customer's account, which means a deliberate hole in the isolation. Make it explicit, narrow, and loud: a different code path, a banner on screen saying whose account this is, and an audit entry every time. An operator who can see everything without leaving a trace is the same risk as a leak, arriving through the front door.
Questions people actually ask
How do I stop one customer seeing another's messages?
Make it impossible to write a query without the tenant filter, rather than requiring everybody to remember it. Row-level security or a session-scoped base query are both structural; a code review guideline is not.
Is a database per tenant safer?
It removes the class of bug entirely and adds operational cost that grows linearly. It is the right answer at a small number of large tenants and the wrong one at ten thousand small ones.
Njiwa
One inbox for WhatsApp, Facebook and Instagram, and an API that sends over either WhatsApp rail. 7 days free, no card.