Making a cross-tenant reference physically impossible with composite foreign keys

In a multi-tenant system, a cross-tenant data leak is the failure that ends the product. Not a bug report — the end of the commercial relationship, and possibly of the company. So the question of where isolation is enforced deserves more thought than it usually gets.

The default answer is the application layer: every query carries a tenant filter, and code review catches the ones that do not. That answer has a known failure rate, and the rate is not zero, because it depends on every developer remembering every time on every query forever. The interesting work is in moving the guarantee somewhere that does not depend on memory.

Two approaches, both used, both worth knowing.

Approach one: composite foreign keys

The technique is simple to state and does something surprisingly strong.

Every parent table carries a redundant unique index on the pair of columns (organisation_id, id), alongside its ordinary primary key on id. That index is redundant in the sense that id is already unique — but it exists so it can be the target of a foreign key.

Every child table then denormalises organisation_id onto itself and declares its foreign key against the pair rather than against id alone:

FOREIGN KEY (organisation_id, staff_id) REFERENCES staff (organisation_id, id)

The effect is that a child row physically cannot reference a parent belonging to a different tenant. If the child’s organisation is A and the referenced staff record’s organisation is B, the pair does not exist in the parent’s unique index and the insert is rejected by the database.

Not rejected by a service-layer check somebody might forget. Not rejected by a code review. Rejected by a constraint, on every write path, including the ones written next year, including migrations, including someone running an ad-hoc statement at three in the morning during an incident.

The cost is a denormalised tenant column on every child table and one extra index per parent. In exchange, an entire class of bug becomes unrepresentable. That is a good trade, and it is the same trade that makes people prefer non-nullable columns and enumerated types: shrink the set of states the system can be in, so that the invalid ones cannot be reached rather than merely being avoided.

The hole, which is documented rather than hoped away

Composite foreign keys have a specific and non-obvious gap: PostgreSQL skips the foreign key check entirely if any referenced column is NULL. Not partially — the whole constraint is not evaluated.

So a child table with a nullable participating column has no enforcement on the rows where that column is null. This is standard behaviour, it is easy to forget, and it converts a guarantee into an assumption without any warning.

The handling that impressed me was writing it into the schema as a comment on the constraint itself, naming the exact condition under which the constraint does nothing. A comment in the migration file gets read once. A comment on the constraint is visible to anyone inspecting the table, which is exactly the moment somebody is deciding whether they can rely on it.

The same discipline was applied to what the technique cannot express at all, listed under a heading that says precisely that:

• Junction tables that have no tenant column of their own, so there is no pair to constrain.
• A record that must belong to both a location and a department, where the requirement is that the department belongs to that location — a two-hop constraint no single foreign key expresses.
• Role assignments where system-level roles legitimately have a null tenant, so the constraint cannot apply.

These are enumerated as “integrity not enforced by SQL alone”, which is the right posture. A partial guarantee is genuinely useful, and it is dangerous the moment anyone believes it is total. Writing down the boundary is what keeps it useful.

Approach two: row-level security

The other system took the database-enforced route differently. The tenant identifier is set on the connection at the start of each transaction, every table has a policy restricting rows to the current tenant, and the application connects as a role that the policies actually apply to — with a separate, exempt role used only for migrations and platform-level jobs.

Two operational details from that implementation are worth carrying, because both are the sort of thing that quietly voids the whole mechanism.

Views bypass row-level security by default. A normal view executes with the privileges of the user who defined it, not the user querying it — so a view over a protected table serves every tenant’s rows to anybody who can select from it. The rule adopted was that every multi-tenant view must be created with invoker security, and that this is verified in the catalogue after every migration cycle up, down and up again. A migration that recreates a view and forgets the option reintroduces the leak silently, and the only thing that catches it is checking the catalogue rather than the migration file.

Default privileges grant more than the migrations think they do. Several migrations were written on the belief that no default privileges existed, so a newly created object would have no grants until explicitly given. Default privileges did exist, meaning every new view silently inherited write access. Worth checking on any inherited database, because it is invisible in the migration history and visible only in the catalogue.

Application-layer tenant filters were kept as well — described as “the second line, not the only line”. That is the right framing. Defence in depth here is not paranoia; it is recognition that the database mechanism has holes you have enumerated and probably some you have not.

How to choose

Row-level security gives you blanket coverage with one policy per table, and it protects reads as well as writes. It costs you a connection-level context that must be set correctly on every transaction, an exempt role that must never be used by application code, and constant vigilance about views and grants.

Composite foreign keys give you a narrower but harder guarantee: no relationship can cross tenants. They do nothing about a query that forgets to filter — a missing tenant filter still returns other tenants’ rows. They only stop bad data being written, not bad data being read.

Which suggests they are complements rather than alternatives, and that the honest way to describe each is by what it does not cover.

The question worth asking

Whatever you choose, the useful test is this: name the specific mechanism that would stop a developer who forgot the tenant filter, and describe what happens when they forget it.

If the answer is “code review”, you have a process control on a correctness problem, and process controls have a failure rate that scales with team size and time pressure.

If the answer is “the query returns rows from other tenants and nothing complains”, you have a disclosed vulnerability with a discovery date rather than a design.

If the answer is “the database refuses the write” or “the policy returns zero rows”, you have a guarantee — and your remaining job is to write down precisely where that guarantee stops. Because it does stop somewhere, and the gap between where it actually stops and where people assume it stops is where the incident will come from.