Security and multi-tenant isolation

Why could one kitchen read another kitchen’s orders?

Forty partner kitchens now use the staff portal. Each kitchen’s staff log in, see their own orders, and print receipts for the delivery bags.

One Monday morning an email arrives from the manager of a partner kitchen in Nashik. She mistyped one digit in a receipt link and got an order from a kitchen in Pune: customer name, address, phone. She closed it, and thought the team should know.

A test account reproduces it. The login works, as it should. The receipt page, added last week, fetches the order by its number and nothing else. Every other page checks that the order belongs to the logged-in kitchen. This one does not.

The test also found a second hole. The portal sends the kitchen’s id in a header with every request, and one older endpoint trusts that header. By changing it, a user can act as any kitchen.

This manager told the team. The next person may sell the phone numbers. Before reading on, guess what would have caught the missing check. The portal has thirty endpoints that take an order number. Twenty-nine check ownership in their own code, written by hand by whoever wrote the endpoint. One was forgotten, and nothing noticed.

The first proposal is to give every kitchen its own database, so nobody can see anybody. That is forty databases to back up, upgrade and watch, and the next kitchen joins on Thursday.

The login told the server who she is. On one page out of thirty, nothing told it which orders are hers. Knowing who is asking and deciding what they may see are two separate decisions. The second must be made on every request, close to the data, by a mechanism that does not depend on each developer remembering it.

Pause and predict The receipt page fetched an order by number alone, and one endpoint trusted a kitchen id from a header. Where should “this order belongs to your kitchen” be checked?

Logged in does not mean allowed

Two different questions hide behind the word “security” in an API, and the portal answered only the first.

Authentication versus authorisation

Authentication answers “who is this?”. Authorisation answers “may this person do this to this thing?”. A valid login settles the first and says nothing about the second.

For example: The session proves the caller is staff 311 of kitchen 7. Whether she may read order 48217 depends on which kitchen owns order 48217.

Pause and predict Every one of the manager’s requests carried a valid login. She still read a Pune kitchen’s order. What was missing?

• A forgotten check. The rule lived in each handler, written by hand. Twenty-nine handlers remembered; one did not. A rule that depends on every developer remembering it on every endpoint will, eventually, be forgotten.
• A trusted claim. The kitchen id came from a request header. Anything in a request, its body, its headers, its URL, is chosen by the caller. The tenant must come from the server’s own record of the session.

Hiding other kitchens’ orders in the portal’s screens did nothing. The screens are one client of the API; the manager’s address bar was another. Anything the API will answer, someone can ask for directly.

A mental model

A railway station with a staff locker room faces the same two questions.

The rest of the design depends on two ideas. Least privilege: every person, service and tenant gets only the access it needs. And defence in depth: the check that matters most is enforced in more than one layer, so one forgotten line is not a breach.

Tenant and tenancy models

A tenant is a customer whose data must stay separate from everyone else’s: here, a partner kitchen. The tenancy model is how the data is split.

For example: Pool: all kitchens in the same tables, with a kitchen_id column. Silo: a database per kitchen. Bridge: a mix; one common form, and the one in the simulator, keeps one database with a schema per kitchen.

What each choice buys

The fifth row is the easiest mistake to make. PostgreSQL’s documentation is explicit: table owners normally bypass row security, as do superusers and roles with BYPASSRLS. If the app connects as the role that created the tables, the policy exists and silently does nothing. The fix is two roles: one that owns the schema and runs migrations, and a smaller one the app uses, or ALTER TABLE … FORCE ROW LEVEL SECURITY.

ALTER TABLE orders ENABLE ROW LEVEL SECURITY; CREATE POLICY kitchen_isolation ON orders USING (kitchen_id = current_setting('app.kitchen_id')::int); -- per request, inside the transaction, from the session (never the request): SET LOCAL app.kitchen_id = '7';

The noisy neighbour is the other cost of sharing. In the simulator every database runs at 60% on a normal day, so a query takes 5 / (1 − 0.6) = 12.5 ms. One kitchen’s month-end export at 20 times its normal load pushes the shared database to 88.5%, and every other kitchen’s query to about 43 ms. At 30 times it is overloaded. A per-kitchen query limit (twice the normal load) keeps everyone else at 13 ms. A silo keeps them at 12.5 ms, and the exporting kitchen overloads only itself.

The silo’s price is operational: 40 databases to run, and 40 migrations on every release, against one. The bridge saves nothing on noise (its schemas share one database) and still costs 40 migrations.

Pool with a second lock, or a database per kitchen?

The receipt page and the header are fixed. The question is what will stop the next missing check. Two goals compete: having only one database to run, and having the strongest separation to show an auditor.

Both stop the probes. Neither survives a tenant taken from the request; that rule is not optional in either path. The difference is cost and blast radius. A silo is easier to explain to a partner who demands one, and the SaaS Lens describes running a mix: pooled tables for most tenants and a silo for those who require it. For forty kitchens of similar size, forty databases is a large operational bill for a protection that two locks already give.

What to remember

1. Authentication says who is asking; authorisation decides what they may touch. A valid login is not permission to read an object.
2. Take the tenant from the server’s own record of the session, never from the request. If the tenant comes from the request, every layer that uses it can be fooled at once.
3. Check ownership on every request that takes an object id, and enforce it twice: in shared middleware, and in the database with row-level security, run by a role that cannot bypass it.
4. Pool, bridge and silo trade cost against isolation. Separate data isolation from load isolation: per-tenant limits handle most noisy neighbours.
5. Least privilege applies to programs too: one role and one rotated secret per service, nothing more than it needs.

A valid login is not permission: check ownership on every request, in two layers, with the tenant taken from the session.

In the real world

The simulator checks one kind of object, one kind of user and three layers. Real systems add roles inside a tenant (a kitchen’s manager may refund; a packer may not), admin tools that legitimately cross tenants and need their own audit trail, background jobs that run with no user at all, and caches and search indexes that must carry the tenant too, or they leak around the database’s policy. Authorisation logic often moves into a shared library or a policy service so that it is written once and tested once.

Isolation of load connects back to lessons 1.6 and 7.1: a per-tenant quota is a rate limiter, and a silo is one way to limit how far one tenant’s problem can spread. The next lesson, 8.4, is about writing decisions like this one down, so that the next engineer knows why the app’s database role does not own its tables.