Multi-Tenant Data Boundaries in PostgreSQL
In a multi-tenant app, the company ID must never come from the request body. How I enforce company boundaries in queries and why it also makes changes easier.
In a multi-tenant application, the worst bug is one company seeing another company’s data. The simplest protection is a rule about where the company ID comes from.
The rule
The company ID on a request is never taken from the incoming payload. It comes from the currently signed-in user, who has already been validated as a member of that company.
A client can send anything in a request body. If the server trusts a company ID from there, a user can name another company and ask for its data.
Why it works
Because the ID comes from the authenticated user, the client has no way to ask for a different company. Every query then filters by that ID, so the boundary is applied in the same place every time.
It also makes company-wide changes easier. When the boundary lives in one predictable spot, an operation on an entire company has one clear definition of what belongs to it.
How it got there
RE/AMP did not start with a company ID on everything. One of the hardest database changes was adding it across the board, which is the multi-tenant change itself.
I wrote migration scripts and tested them repeatedly, then ran dry runs on staging. After that I tested on staging with live data before the production push, and the change went through without trouble.
Testing the boundary
A rule like this is only worth anything if tests prove it. A useful test signs in as a user from one company, sends a request that names a record from another company, and expects the record not to be found.
I also test that a payload carrying a company ID is ignored and not honored. Those two tests catch most of the mistakes people make when adding a new endpoint.
What I would watch for
New endpoints are where boundaries fail, because it is easy to write a query that forgets the filter. A shared helper that always applies the company ID beats remembering to type it.
Hiding a button in the interface is never protection. The server has to refuse the request.
Related
This pattern is part of the RE/AMP case study. For how messaging events attach to the right tenant, see Twilio Messaging With Per-Company Keys. The custom web applications service covers this kind of build.