ENABLE is not FORCE, and other things we got wrong about tenant isolation

Most software that serves many customers from one database separates them with a WHERE clause. Every query carries WHERE clinic_id = ?, and the separation holds exactly as long as every developer remembers to write it, forever, including on the query added at five o'clock on a Friday.

We did not want to hold that line by memory, so Orla separates clinics with PostgreSQL row-level security: a policy attached to every table that carries a clinic identifier, applied underneath every query whether or not anybody remembered. The policies are generated from the database catalogue rather than from a hand-kept list, so a new table cannot ship unprotected by being forgotten.

That is the design. This post is about the part of it we got wrong.

The gap between ENABLE and FORCE

ALTER TABLE … ENABLE ROW LEVEL SECURITY turns policies on. What it does not do — and this is documented, and we had read the documentation, and we still did not think about it hard enough — is apply them to the table's owner.

Our migration role owns all 92 tables. It has no BYPASSRLS attribute, so it looked safe on paper. It bypassed every policy anyway, purely by being the owner.

FORCE ROW LEVEL SECURITY removes that exemption. We measured it against a two-tenant fixture, with the owning role reading the patients table:

Two rows from two different clinics, returned to a connection that had not said which clinic it was acting for. That is the number that matters.

Why it was not a live breach

The application does not connect as the owning role. It connects as a separate, non-superuser role that has never been exempt, and every request runs inside a tenant scope. So the two-row read was reachable by migrations and by anyone holding the owner's credentials — not by the app, and not by a request arriving from the internet.

We are saying that plainly rather than either hiding it or dramatising it. It was a weaker guarantee than we had been describing, which is its own kind of problem: the claim on the website has to be the claim the database actually enforces.

What fixing it cost

FORCE is one line, in the same catalogue-derived loop that creates the policies. The line is correct. The estimate attached to it was not.

Turning it on means every piece of tooling that legitimately runs as the owner — migrations, the seed, fixtures, backfill scripts — stops being able to see across clinics, because that is the entire point. Each one has to be taught to declare which clinic it is acting for, and the few that genuinely operate across all clinics need an explicit, auditable way to say so.

We implemented it, measured it, and reverted it, because landing it half-done would have left the seed broken and the migrations mysterious. It is now a decision with a written cost behind it rather than a one-line item on a roadmap.

The general lesson

Two of them, really.

A guarantee you have not tried to break is a guess. We believed the isolation was total because the design said so. The design did say so. The measurement said something else, and the measurement is what a supervisory authority would be interested in.

A security control that breaks your own tooling is working. The temptation when FORCE broke the seed was to conclude it was too invasive. It was not being invasive. It was revealing how much of our tooling had quietly come to rely on an exemption we had been telling people did not exist.

If you are evaluating any clinical system — ours included — the question worth asking is not "do you have row-level security". It is "which roles are exempt from it, and how do you know". Ask us. We will show you the table above.

Related: the patient record describes what the isolation covers, and exporting your data describes what leaves with you.