Blog · · 8 min read
Multi-tenant Postgres without the footguns
Row-level security, tenant-scoped connection pools, and the three queries that nearly leaked data between tenants.
Every multi-tenant application eventually asks the same question: do tenants share tables, or get their own schema? I have shipped both. Shared tables with row-level security has won every time, but only because of a handful of decisions made early.
Put the tenant in the connection, not the query
The failure mode of tenant-scoped queries is not the query you wrote carefully. It is the one written six months later, in a background job, by someone in a hurry. If the tenant boundary lives in a WHERE clause, one forgotten clause is a data leak.
Setting a session variable on checkout from the pool, and letting Postgres row-level security enforce it, moves the boundary below the application. A missing clause then returns nothing instead of everything.
Background jobs are the dangerous part
Jobs run without a request context, so nothing reminds you which tenant you are acting for. Every job payload should carry a tenant id, and the worker should refuse to start without one.
What I would do differently
Add the cross-tenant leak test on day one. A test that logs in as tenant A and enumerates every endpoint looking for tenant B's ids is tedious to write and pays for itself the first time someone adds a report.