Multi-tenant scoping in PostgreSQL that survives a rewrite
Every tenant table carries the organisation, every query goes through one layer that puts it there, and a build-time check fails the build if application code ever reaches the database directly. The discipline is the design.
/Updated /6 min read
The short answer: put the organisation id on every tenant-owned row, let one repository layer add it to every query from the signed-in session (never from the caller), make a build-time test fail if any code bypasses that layer, and move the rule into PostgreSQL row-level security when the data is regulated or several organisations really share tables in production.
A single-tenant application that might one day serve several organisations is one of the most expensive things you can build, because the day it has to change, the change touches every query you have written.
The alternative is not complicated. It is just unforgiving about discipline, so the discipline has to be mechanical.
Every tenant table carries the organisation
Not a join away. On the row.
create table deal (
id serial primary key,
organisation_id integer not null references organisation(id),
title varchar(180) not null
);
create index deal_organisation_idx on deal (organisation_id);
Even when the product has exactly one organisation and will for a year. The column costs four bytes and an index; retrofitting it costs a migration across every table plus an audit of every query that ever ran.
There are always a few exemptions — the organisation table itself, roles, currencies, exchange rates, sessions. Write them down. An undocumented exemption is indistinguishable from a bug.
Shared tables, a schema per tenant, or a database per tenant?
This article is about the first option, because it is the one most business products should start with:
| Approach | Isolation | Operational cost | Usually fits |
|---|---|---|---|
| Shared tables with an organisation column | Enforced by your code, or by row-level security | Lowest: one schema, one migration run | Most B2B products and internal systems |
| A schema per tenant | Separate tables in one database | Every migration runs once per tenant | A modest number of tenants that need visible separation |
| A database per tenant | Strongest | Provisioning, backups and migrations per tenant | A few large or regulated tenants, or data-residency rules |
Moving from shared tables to separate schemas later is a data migration. Moving the other way, after the code assumed one tenant per database, is a rewrite.
Keep keys and constraints inside the tenant
The organisation column alone does not stop one tenant's row pointing at another's. A deal in organisation 1 can reference a contact in organisation 2, because a single-column foreign key only checks that the contact exists. Make the reference carry the organisation too:
alter table contact add constraint contact_org_id_key unique (organisation_id, id);
alter table deal
add constraint deal_contact_same_org
foreign key (organisation_id, contact_id) references contact (organisation_id, id);
The same goes for uniqueness. unique (slug) lets one tenant's choice block another's, and the error message tells the second tenant the first one exists. unique (organisation_id, slug) is what the business means. Lead indexes with organisation_id too, since nearly every query filters on it first.
One place puts the organisation into the query
The important part is not the column. It is that application code never chooses the organisation.
export function createRepository(ctx: Context) {
const organisationId = ctx.session.organisationId; // from the session, never the caller
return {
async listDeals(filter: DealFilter) {
const { organisationId: _ignored, ...safe } = filter; // strip anything supplied
return db.select().from(deal).where(and(eq(deal.organisationId, organisationId), …));
},
};
}
Two rules inside that layer, both learned the hard way:
Strip a caller-supplied organisation id rather than trusting it. If a filter object can carry one, eventually something will pass one through from a request body.
Return not-found, not forbidden, for a cross-tenant id. A "you are not allowed to see this" response confirms the record exists. Not-found tells an attacker nothing.
Make the leak impossible to commit
Discipline decays. The only version of this that holds is the one a machine checks.
In this site's codebase there is exactly one module that imports the database client, and a test walks the source tree and fails the build if anything outside the repository layer imports it:
it('only the repository layer reaches the database client', () => {
const offenders = sources
.filter((file) => !allowed.some((prefix) => file.startsWith(prefix)))
.filter((file) => /from '@\/db\/client'/.test(read(file)));
expect(offenders).toEqual([]);
});
That test has already earned its place. Building the admin screens, I wrote queries directly in a server action because it was two lines and obviously fine. The build went red, I moved the queries into the repository, and the rule stayed true. Without the test, the exception would have shipped, and the second exception would have been easier than the first.
Row-level security is the stronger version, at a price
PostgreSQL can enforce this itself: policies on every table, the tenant taken from a session setting, and no way for a query to see another tenant's rows even if the application asks.
It is genuinely stronger. It is also harder to debug, slower, and it changes how migrations and background jobs have to be written — a job that runs without a tenant context now sees nothing at all.
In outline:
alter table deal enable row level security;
alter table deal force row level security; -- the table owner is bound by it too
create policy deal_tenant_isolation on deal
using (organisation_id = current_setting('app.organisation_id')::integer)
with check (organisation_id = current_setting('app.organisation_id')::integer);
and, for each unit of work:
begin;
select set_config('app.organisation_id', '42', true); -- true: this transaction only
-- every query here sees organisation 42's rows and nothing else
commit;
Three details decide whether this actually holds. The application must not connect as a superuser or a role with bypassrls, because those skip policies entirely. Without force, the table's owner skips them too. And the setting must be transaction-local: behind a connection pooler, a session-level setting outlives the request and is inherited by whichever request gets that connection next.
My rule of thumb: application-layer scoping with a build-time guard for internal systems and single-organisation products; database-enforced policies when the data is regulated or when more than one organisation's data really does sit in the same tables in production.
Two things worth doing on day one
Write down the exemptions. The list of tables that are deliberately not tenant-scoped is a design document, and it belongs next to the schema.
Decide what a missing organisation means. In my systems, a query without one is a programming error that throws — not a query that quietly returns everything. The default has to be nothing, because the failure mode of the other default is a support call you will remember for a long time.
Where this is used
The property and investment CRM enforces its access rules in PostgreSQL row-level security, and how I structure role-based access covers the permission model that sits on top of tenant scoping. Products built this way are described on custom business software.
Common questions
What is multi-tenant scoping?
Multi-tenant scoping means every query in a shared application is limited to the organisation the signed-in user belongs to, so customers who share the same tables never see each other's data.
Should a single-customer product still have an organisation column?
Yes, if there is any chance of a second customer. The column and its index cost almost nothing today; adding them later means a migration on every table and a review of every query.
Is row-level security enough on its own?
It is a strong last line, not the only one. Keep the repository layer as well: it gives clear errors, keeps queries readable, and the database policy catches whatever the application gets wrong.
Does row-level security slow PostgreSQL down?
It adds a condition to every query on the table. With an index that starts with organisation_id, that is usually a small cost; policies that call functions or run subqueries per row are where it gets expensive.