Composite keys: tenant isolation nobody has to remember

If one customer of your multi-customer system can see another customer’s data, that is not a bug report. It is the end of the commercial relationship, and possibly of the company. So where the separation is enforced is a decision worth more thought than it usually gets.

The usual answer is the application layer: every query carries a tenant filter, and code review catches the ones that do not. That answer has a failure rate, and the rate is not zero, because it depends on every developer remembering on every query for as long as the system lives. The aim in these two systems was to move the guarantee somewhere that does not depend on memory.

No incident is described here. This is design work done before there was one, and what makes it useful is that each mechanism’s limits were written down.

What was actually going on

Two systems took two routes, and both are worth knowing.

Composite foreign keys. Every parent table carries a redundant unique index on the pair (organisation, id), beside its ordinary primary key. It is redundant because id is already unique, but it exists so it can be the target of a foreign key. Every child table carries the organisation too, and points its foreign key at the pair rather than at id alone:

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

A child row then cannot reference a parent belonging to a different organisation. The pair does not exist in the parent’s index, so the database refuses the insert. It is refused on every write path, including ones written next year, migrations, and a statement typed by hand at three in the morning during an incident.

Row-level security. The other system sets the tenant on the connection at the start of each transaction. Every table has a policy restricting rows to that tenant. The application connects as a role the policies apply to, and a separate exempt role is used only for migrations and platform jobs.

What we changed

Both systems also recorded where their guarantee stops, which is the part that keeps it honest.

The composite key has a known gap: PostgreSQL skips the foreign key check entirely if any referenced column is NULL. A nullable participating column therefore has no enforcement on the rows where it is null. That is written as a comment on the constraint itself, not just in a migration file. A comment in a migration is read once. A comment on the constraint is visible at the moment somebody decides whether they can rely on it. A list headed “integrity not enforced by SQL alone” also names what the technique cannot express:

• junction tables with no tenant column of their own;
• a record that must belong to a location and to a department of that same location, which is a two-hop rule no single foreign key states;
• role assignments where system roles legitimately have no tenant.

The row-level security system recorded two operational traps. Views bypass row-level security by default, because a view runs with its definer’s rights, so every multi-tenant view must be created with invoker security, verified in the catalogue after each migration cycle up, down and up again. And several migrations had been written believing no default privileges existed. They did, so every new view silently inherited write access. That is invisible in the migration history and visible only in the catalogue.

Application-layer filters were kept too, described as the second line and not the only line.

What it did not fix

The two mechanisms cover different things. Composite keys stop bad data being written. They do nothing about a query that forgets its filter, which still returns other tenants’ rows. Row-level security covers reads and writes with one policy per table, but it needs the tenant set correctly on every transaction, an exempt role that application code never uses, and constant attention to views and grants.

That makes them complements, not alternatives. Neither one is total, and the gap between where a guarantee stops and where people assume it stops is where an incident would come from.

What to ask your own team or supplier

• What specific mechanism stops a developer who forgets the tenant filter, and what happens when they do? “Code review” is a process control on a correctness problem.
• If the answer is “the query returns other tenants’ rows and nothing complains”, how soon is the discovery date?
• Does the database refuse a cross-tenant reference on write, or does only the application check?
• Where does the guarantee stop, in writing? Nullable columns, views and default privileges are the usual places.
• Which database role does the application connect as, and do the isolation policies apply to it?

Where this ends up

Choosing where the guarantee sits, and writing down where it stops, is an architecture decision taken once and lived with for years, which is why it is one of the first things settled on a multi-tenant custom software build.