Multi-tenant data isolation that fails closed

There is a version of this bug in almost every multi-tenant product, and it always looks the same in the postmortem: one query, written once, that forgot its tenant filter.

It is never the query anyone would have reviewed carefully. It is a reporting endpoint added in month nine by someone who joined in month seven, copied from a query that was already missing the filter.

Application filters are a discipline, not a control

The usual approach is a WHERE tenant_id = @tenantId on every query, enforced by code review and good intentions. That works until it does not, and the failure is silent: the query returns more rows than it should, the page renders, nothing errors, and a customer eventually sees a name that is not theirs.

A control that depends on every developer remembering it forever is not a control. It is a habit with a deadline.

Put it in the database

PostgreSQL row-level security moves the filter from the query to the table.

ALTER TABLE student ENABLE ROW LEVEL SECURITY;
ALTER TABLE student FORCE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON student
  USING      (school_id = NULLIF(current_setting('app.current_school_id', true), '')::uuid)
  WITH CHECK (school_id = NULLIF(current_setting('app.current_school_id', true), '')::uuid);

Three things in there are doing real work.

FORCE, not just ENABLE. Without FORCE, the table owner bypasses the policy. If your migrations and your application happen to run as the same role, and they often do early on, you have enabled a policy that never applies.

WITH CHECK as well as USING. USING filters what you can read. WITH CHECK constrains what you can write. Without it, a tenant can insert a row stamped with somebody else’s tenant id, which is a quieter and nastier version of the same bug.

The two-argument current_setting and the NULLIF wrapper. This is the part that decides whether you fail closed, and it is worth being slow about.

The detail that decides everything

current_setting('app.current_school_id') with one argument throws if the setting was never set. With true as the second argument it returns NULL instead.

But there is a second state. A setting that was set and then reverted, which is what happens at the end of a transaction that used SET LOCAL, comes back as an empty string, not NULL. And ''::uuid does not evaluate to NULL; it raises invalid input syntax for type uuid, SQLSTATE 22P02.

So on a connection returned to the pool and handed to the next request, the naive policy does not fail closed. It throws. Your monitoring fills with cast errors from a completely unrelated endpoint and nobody connects it to tenancy for a day and a half.

NULLIF(current_setting('app.current_school_id', true), '')::uuid maps both the unset and the reverted case to NULL. And school_id = NULL is NULL, which is not TRUE, so the row is not visible.

No context means zero rows. Never every row. That is the whole objective, and it is two functions wide.

Set the context inside the transaction

SELECT set_config('app.current_school_id', $1, true);

That third argument is is_local. It scopes the setting to the current transaction, so it is reverted on commit or rollback and cannot survive into whatever request borrows the connection next.

Use SET LOCAL, or set_config(..., true). Never the session-scoped versions. With connection pooling, a session-scoped tenant id is not a bug you find in testing, it appears under concurrency, in production, intermittently.

Two more rules that follow from this:

• The tenant id comes from a validated token claim. Never from a request body, a query string, or a header the client controls. If the client can name the tenant, the policy is decorative.
• The application connects as a non-owner role with NOBYPASSRLS. Migrations run as a different role that owns the schema. A superuser connection bypasses every policy on the system, so no service path may use one.

Make it impossible to forget on the next table

None of the above helps on table forty-one if somebody creates it without a policy. So the guard is a test, not a checklist:

• Sweep the migrated schema for every table with a tenant column, and fail the build if any of them lacks RLS enabled, forced, and a policy.
• Seed two tenants and assert, endpoint by endpoint, that tenant A cannot read, count, update or delete tenant B’s rows, and that the response is a not-found rather than a leak or a 500.
• Assert that a query with no tenant context returns nothing.

The sweep catches a forgotten policy. The per-endpoint tests catch the service layer mapping invisibility to the wrong result, which is a different bug and just as visible to a customer.

What this costs

Row-level security is not free. Policies are applied to every query and complex ones can defeat index usage, so keep them simple, an equality check on an indexed column, which is what the example above is. Put the tenant column first in your composite indexes. And measure, because the failure mode of a clever policy is a slow product rather than an insecure one.

That is a good trade. Slow is a problem you can see.