Tenant isolation in a multi-tenant LIMS.

Why a list query has to prove its own tenant from its own filters instead of trusting the caller, why a policy that reads a field the query never filters on is a leak that returns a clean 200, and what a shared patient record does to the whole argument.

A multi-tenant laboratory system holds several laboratories’ data in one database, and the promise it makes is that one laboratory never sees another’s. That promise is easy to state, easy to test badly, and easy to break in a way that produces no error anywhere.

This is written from a Postgres and row-level-security perspective, because that is what LabFlow uses, but the failure modes are not specific to it. A Firestore rule and an RLS policy get the same class of thing wrong for the same reason.

The three shapes, briefly#

Database per tenant. Strongest isolation, and it moves the whole problem into operations: migrations across N databases, connection pooling across N databases, and a cross-tenant query that is now a distributed one. Real, and expensive at small scale.

Schema per tenant. A middle ground that mostly buys you a naming convention. Migrations are still N-way, the connection pool is shared, and the isolation is enforced by a `search_path` that is one bug away from being wrong.

Shared tables with a tenant column. One schema, one migration, one pool, and the isolation moves into the database’s own policy layer. This is what most systems at this size choose, and it is the one where the interesting mistakes live.

What row-level security actually does#

A policy attached to a table says which rows a given caller may see or write. In Postgres it is a boolean expression evaluated per row, with the caller’s identity available inside it.

create policy results_select on results
  for select to authenticated
  using (
    tenant_id in (
      select tenant_id from memberships
      where user_id = auth.uid() and status = 'active'
    )
  );

This is correct and it is not sufficient, for a reason that has nothing to do with the policy.

A list query must prove its tenant from its own filters#

Here is the failure, and it is worth reading twice because it is invisible in review.

Row-level security FILTERS. It does not refuse. A query for every row in `results` does not fail for a caller who may only see one tenant’s — it succeeds, and returns that tenant’s rows. Which is exactly right, and exactly why a system that leans on it for correctness rather than for defence develops a habit that is wrong somewhere else.

The habit is writing the client query without the tenant filter, because "the policy handles it". Then the query runs somewhere the policy does not: a background job on a service key, a reporting view, an aggregate, a function marked `security definer`. Every one of those is a plane where `auth.uid()` is null or bypassed, and the filter that was supposed to be redundant turns out to have been the only one.

• Write the tenant filter in the query, every time, even where the policy already covers it.
• Treat the policy as the thing that stops a mistake, not as the thing that implements the rule.
• Where the two disagree, the query is wrong. A query the policy has to correct is a query somebody will copy into a context without one.

A probe against an empty table proves nothing#

The natural way to check isolation is to call the endpoint as a user from tenant A and confirm that tenant B’s data is not returned. If tenant B has no data, that check passes vacuously, and it will keep passing until the day it matters.

The same is true of a write. A forbidden `update` under RLS does not fail: it matches no rows and reports success with a count of zero. A test asserting "no error" is asserting nothing. A test must assert the row is unchanged.

• Seed both tenants before probing, and delete the seed afterwards.
• Assert on rows returned and rows changed, never on status alone.
• Run the probe as the roles that actually exist — anonymous, an ordinary member, an administrator — and not as the one with the most privileges.

That last point deserves its own sentence. Verifying a user-facing flow while signed in as an administrator is the single most common way a permissions bug survives testing, because the administrator branch short-circuits the rule you were trying to check.

Grants are a separate boundary, and they are not optional#

Row-level security decides which rows. Table and column privileges decide whether the caller may touch the table at all, and they are a different mechanism with different defaults.

Postgres grants `execute` on every newly created function to `public`, and `public` includes the anonymous role. A `security definer` function that nobody granted anything to is therefore reachable by an unauthenticated caller unless it was explicitly revoked. The revoke is load-bearing; it is not hygiene.

revoke all on function admin_reassign(uuid, uuid) from public;
grant execute on function admin_reassign(uuid, uuid) to authenticated;

Two more that are easy to miss. `truncate` is a table privilege and row-level security does not police it, so a role holding it can empty a table the policies would never let it delete a row from. And a column-level grant layered on top of a table-level one is a silent no-op — the broader grant already permits the column, so the narrower one changes nothing while looking like it does.

The second call plane#

A `security definer` function runs with its owner’s privileges rather than its caller’s. That is genuinely useful — it is how you let a member do one privileged thing without giving them the privilege — and it is a second route into your data that bypasses everything your route handler does.

Whatever the HTTP layer enforces, the function does not: not the rate limit, not the step-up authentication, not the audit row, not the tenant filter. If those matter, they have to be inside the function as well.

There is a related trap on the other side. A backend using a service-role key has no JWT, so `auth.uid()` inside any function it calls is null. A function gated on `auth.uid()` will therefore refuse that backend unconditionally — the function is correct, the backend is correct, and the pair never works. The fix is to pass the actor explicitly, pin it, and fall back only where that is intended.

create function reassign(p_actor uuid, p_case uuid)
  returns void
  language plpgsql
  security definer
  set search_path = public
as $$
declare v_actor uuid := coalesce(auth.uid(), p_actor);
begin
  -- every check the route makes belongs here as well
  perform assert_member_of_case(v_actor, p_case);
  ...
end $$;

Isolation and the read budget are the same conversation#

A query that fetches everything and filters in the client is a correctness problem before it is a performance problem, because the filtering happens after the data has already crossed the boundary.

Every list read is paginated and every list read carries its own filters. That is not a performance rule that happens to help security; it is the same rule seen from two sides. A page of twenty rows scoped to a tenant cannot leak a thousand rows from another one, and a query with no limit is a query whose blast radius is the size of the table.

Counting is the same. A count computed by fetching rows and taking the length is a full read wearing a disguise. The database counts; the client displays.

The shared patient, which undoes half of the above#

Everything so far assumes data belongs to exactly one tenant. In a clinical laboratory that assumption breaks on the first patient who uses two laboratories.

A person’s haemoglobin from Meridian and their haemoglobin from Northgate are the same person’s history. A patient portal that shows only one of them is not showing a history; it is showing an excerpt. But a result produced by Meridian is Meridian’s record — its accession, its instrument, its validating scientist, its liability — and Northgate has no business reading it.

The resolution is that there are two different subjects with two different owners, and conflating them is what makes this hard.

• The RESULT belongs to the laboratory that produced it. Tenant isolation applies to it unchanged.
• The PERSON belongs to themselves. Their identity is not a tenant-scoped record, and their view across laboratories is authorised by them rather than by either laboratory.
• The bridge is an explicit grant with a scope and an expiry, granted by the patient, revocable by the patient, and recorded as an event in both directions.

That means a third access plane, and it must be modelled as one rather than as a special case in the tenant policy. A patient reading their own results is not a member of the laboratory’s tenant and never becomes one.

How to actually check it#

A checklist that has caught real problems, in the order worth running it.

• Enumerate every table in the schema and assert each one has row-level security enabled. A table added without it is the most common single failure, and it is invisible because the table works perfectly.
• Assert every policy carries a `using` or `with check` clause. A select policy with no `using` is `using (true)`.
• Compare the policies in your migrations against the catalog, not against your migrations. A file that was edited after being applied is not the database.
• Enumerate function privileges and assert nothing unintended is executable by the anonymous role.
• Seed two tenants and probe every list endpoint as a member of one. Assert the other’s rows are absent, not merely that no error occurred.
• Probe writes and assert the row is unchanged, because a refused write is a success with zero rows.

The first four are static and can run in a test suite. The last two need a live database and a seed, which is why they tend not to get written — and why they are the two that find things.

Where LabFlow stands#

Shared tables with a tenant column, row-level security on every table, and the tenant filter written into every query regardless. The role a person holds lives on their membership row rather than on their user record, because a person can belong to two laboratories with different roles in each.

The patient view is a separate plane with its own grant model, scoped by category and expiring by default, and the revocation is itself an event with a time and an actor. You can operate that model on the home page.

What is not claimed: this is a design that has been reasoned about and tested, not one that has survived years of production traffic in dozens of laboratories. That is a real difference and the comparison pages say so.