Multi-tenant isolation in Postgres that fails closed

In a multi-tenant ERP, one leaked row is another business's customers and cash. The usual protection is a WHERE tenant_id = ? that every query has to remember. One forgotten clause, in one report, in one background job, and the isolation is gone. For the Commerce ERP I wanted the database itself to refuse, so a bug in application code cannot become a data leak.

Forced row-level security on every tenant table

Every tenant-owned table goes through one helper in a migration. It enables row-level security, forces it, and attaches the same policy:

ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
ALTER TABLE orders FORCE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON orders
  USING (tenant_id = app_tenant_id())
  WITH CHECK (tenant_id = app_tenant_id());

FORCE matters. Without it, the table owner bypasses the policy, and the owner is often the role your migrations and scripts run as. WITH CHECK matters too: it stops a write that tries to plant a row in someone else's tenant, not just a read.

A tenant id that fails closed

The policy compares against app_tenant_id(), which reads a setting scoped to the current transaction:

SELECT nullif(current_setting('app.tenant_id', true), '')::bigint;

The application sets it at the start of each transaction with set_config('app.tenant_id', ..., true). The true makes it local to that transaction, so a pooled connection cannot carry one merchant's id into the next request. And if nothing set it, the function returns NULL, and tenant_id = NULL matches nothing. A forgotten tenant does not see everything; it sees an empty result. That is the whole point.

Composite foreign keys block cross-tenant references

Row-level security filters what you can see, but a foreign key on id alone would still let a stock movement point at another tenant's product variant. So child tables reference (tenant_id, id):

FOREIGN KEY (tenant_id, variant_id)
  REFERENCES product_variants (tenant_id, id)

Now a cross-tenant link is a constraint violation, not a subtle bug.

The costs

This design is not free, and the tradeoffs are worth saying out loud.

• Every job must declare its tenant. Background jobs, webhooks, and scheduled tasks all have to open a transaction with the tenant set. That is the intent, but it is friction.
• Platform work needs a separate privileged role. Billing all merchants or running platform analytics crosses tenants by design, so it runs under a different role, kept out of the merchant request path.
• Migrations get stranger. The migration role is itself under forced security, so a plain backfill UPDATE silently touches zero rows. I added one helper that lifts FORCE on a single table inside the migration's transaction, runs the statement, and forces it again, and refuses any table that was not forced to begin with.
• Branch-level scoping stays in application code. One tenant id per transaction is clean; nesting branches inside that in the database would not be.

Test the boundary, not the happy path

The tests that matter here try to break isolation: read another tenant's order, insert a row with someone else's tenant id, run a query with no tenant set. Each must come back empty or fail. Across the ERP's 616 API operations, that boundary is the one thing I never wanted to rely on code review for.

What I'd tell someone starting

• Force RLS from the first migration; retrofitting it means auditing every query you already wrote.
• Make the missing-tenant case return nothing, never everything.
• Put tenant_id in your foreign keys, not just your policies.
• Write the attack tests before the feature tests.