Multi-tenant isolation belongs in the data layer, not in every query

I built a multi-tenant publishing platform: many customers, one database, shared tables. The feature list was never the hard part. Isolation was.

Consider the failure mode. Somebody adds an endpoint, writes a query, and forgets one clause:

SELECT * FROM posts WHERE status = 'draft';
-- and not: AND tenant_id = $1

That is not a bug report. That is one customer reading another customer's unpublished work, and you learning about it from the customer. There is no version of that conversation that goes well, and there is no amount of "we have fixed it" that gets the trust back.

Why code review does not solve this

The usual answer is discipline: always filter by tenant, catch it in review.

That fails for a reason that has nothing to do with how good the team is. Correctness depends on every author of every query, forever, including the one writing a hotfix at eleven at night. It is a rule with hundreds of enforcement points and no mechanism. The probability of getting it right every time is not high, and the cost of getting it wrong once is unbounded.

Any control with those properties belongs somewhere a person cannot forget it.

Move it under the query

The principle: a query that omits the tenant filter should be impossible to write, not merely discouraged.

Postgres row-level security is the strongest form. The policy lives on the table, the session carries the tenant, and the filter is applied by the database whether or not the query mentions it:

ALTER TABLE posts ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON posts
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

Now the forgotten WHERE clause returns nothing instead of everything. The failure mode inverts: a mistake shows a customer too little of their own data, which is a bug report, rather than too much of someone else's, which is an incident.

If RLS is not available, the next best thing is a data-access layer that takes the tenant as a required argument and is the only path to the database, with a lint rule banning raw client access outside it. Weaker, because it can be bypassed, but it still moves the decision from every query to one place.

What this costs

You have to set the session variable on every connection, which matters with a connection pool — a pooled connection carrying the previous request's tenant is the same bug with extra steps. Test that path specifically.

Some admin and analytics work legitimately spans tenants, so you need a deliberate, audited way to bypass the policy rather than developers quietly reaching for a superuser role.

Both are real costs. Both are one-time, in one place, done by someone thinking about exactly this problem. That is the trade: a fixed cost at the boundary instead of a recurring risk at every call site.

The wider point

When a system has a failure mode that is catastrophic and easy to reach, do not defend it with a convention. Push the guarantee down to the lowest layer that can enforce it, and let the layers above be wrong safely.