Multi-Tenant Data Isolation in PostgreSQL: Getting Row Scoping Right

Multi-tenant means several customer organizations share the same infrastructure while their data stays strictly separated. The sharing is the point: one deployment, one schema, one set of background jobs serving everybody. The separation is the part that has to be engineered, because nothing about a shared table enforces it on its own.

We maintain and extend a US lone worker safety SaaS platform on PostgreSQL that we inherited as a live production codebase. Part of that work was identifying a structural issue in how the inherited code isolated data between tenant companies, and correcting the isolation model so each company’s safety and account data stays properly separated on shared infrastructure. That experience is the basis for most of what follows.

The model: shared tables, scoped rows

In the row-based approach, every tenant’s records live in the same tables, and each row carries the company it belongs to. A read is correct only if it filters on that company.

The appeal is operational. One schema to migrate, one set of indexes to tune, one job scheduler. Adding a tenant is inserting a row, not provisioning a database. At a few hundred tenants that difference is the difference between a product and a hosting operation.

The cost is that correctness now depends on every query. A schema-per-tenant design makes a mistake impossible to express, because the wrong data is not in scope. Row isolation makes the mistake easy to express and invisible when you make it.

That trade-off should be a decision rather than a default. Row-level isolation across shared tables, or a schema per tenant, chosen for the specific product rather than assumed.

Every query carries the tenant, with no exceptions

The rule sounds simple and the failures are always in the gaps.

A query written against a single tenant during development, where the filter was not needed to get the right answer. A join that scopes the primary table and not the joined one, so the filter looks present and is not complete. A background job running outside a user session, where there is no current tenant to scope by and somebody passed the whole table instead. An aggregate or report built straight against the tables for speed. A lookup by primary key, where the ID looked unique enough that filtering seemed redundant.

The last one is the most common and the most instructive. Fetching a record by its ID without also constraining the tenant works perfectly until someone passes an ID belonging to another company, and then it returns their data with no error at all.

The defence is structural rather than disciplinary. The tenant should be impossible to omit: scoping applied at the data access layer rather than remembered per query, so a developer has to actively bypass it rather than actively remember it. Reviewing for “did you add the filter” does not scale, because it only catches the queries somebody reads.

Separate the domains before writing queries

An isolation model is easier to keep correct when the schema has clear seams. Distinct areas of the system get their own tables and rules before a query is written, so tenant boundaries are enforced structurally rather than by convention.

On a safety platform those seams are reasonably obvious once you look for them. Safety and location data, the check-ins, GPS positions and SOS alerts, is one domain. Organization and account data, the companies, employees and subscription state, is another. Alerting and notification, the escalation that reaches a person through push, SMS or a phone call, is a third.

Those domains have genuinely different access patterns and lifetimes. Keeping them separate means you can reason about who may read what, instead of reasoning about one undifferentiated pile of tables with a company column on each.

Keep flexible structures for configuration only

PostgreSQL’s JSONB is useful and it is also a trap in a multi-tenant schema.

The legitimate use is per-tenant configuration: the settings, thresholds and rules that genuinely differ between companies and that you do not want to model as columns because every tenant uses a different subset. Company-specific behaviour handled through configuration data rather than a forked codebase per customer is exactly the right instinct.

The misuse is letting business data drift into it. Once a field you need to filter, join or aggregate on lives inside a JSON document, your tenant scoping has to reach inside that document too, and it is much easier to get wrong there than in a column with an index on it. The test is simple: if you will ever query by it, it is a column.

Test the isolation, not the feature

This is the part that decides whether an isolation bug survives for years, and it is the reason these defects are so common in inherited systems.

A test suite built around one tenant cannot detect a missing tenant filter. Every query returns the right answer, because there is no other company’s data for it to leak. The suite passes, the feature works, and the defect ships.

The fix is to make multiple tenants the default in test data. Seed at least two companies with overlapping-looking records, then assert the negative: reading as company A returns nothing belonging to company B. Do that for every read path, including the reports and the background jobs, because those are the paths with no user session to scope by.

It is also worth auditing an existing platform’s actual isolation model for structural flaws before adding new capability on top of it. Building on an isolation model you have not verified means every new feature inherits whatever is wrong with it.

If you are taking over a multi-tenant system

Read the data access layer before the features. How a query gets its tenant, and whether it can be written without one, tells you more about the system’s safety than any amount of reading the business logic.

Then check the test data. If it contains one tenant, you have found the reason any isolation problem has gone unnoticed.

We do this work as multi-tenant SaaS development and the wider product engineering as SaaS product development. The inherited platform described here is set out in our lone worker safety SaaS case study. If you are about to build features on top of an isolation model nobody has verified, get in touch and answer one question first: how many tenants does your test data contain?

Common questions