Multi-Tenant SaaS: Data Isolation and Reseller Permission Hierarchies

Direct answer. The incident every multi-tenant system fears is tenant A seeing tenant B’s data. A shared database with a tenant column is a reasonable starting point for most B2B SaaS, provided tenant filtering does not depend on every developer remembering it. Tenant context is set only at authentication, the data access layer injects the tenant filter everywhere, the database backs it up with row-level security, the platform-reseller-sub-account hierarchy is modelled explicitly, and access-control tests are written before feature tests.

Multi-tenant diagram. Top: the request path, where a request with a token or session passes through authentication middleware that resolves tenant, role and scope, the data access layer injects a tenant filter, and the database enforces row-level security; the tenant id comes only from authentication. Bottom: an organisation tree with a platform operator, two resellers and two sub-accounts under one reseller, with a dashed area marking what that reseller can see.
Figure 1. Request path and organisation tree. A reseller sees its own subtree, no more and no less.

1.0 Three isolation models

ModelBenefitCostFits
Database per tenantStrongest isolation; one tenant can be backed up, migrated or restored on its ownMigrations run N times; connections and operations grow with tenant countFew tenants, or contracts that require physical separation
Shared database, schema per tenantStrong isolationMigrations and monitoring get harder as schemas multiplyA moderate number of tenants
Shared tables with a tenant columnSimplest to operate; cross-tenant reporting is easyIsolation depends entirely on code and database policyMany tenants with modest data each

They can be mixed, with large customers in their own database and everyone else shared, as long as the data access layer hides where a tenant’s data lives and business code never has to care.

2.0 Where tenant context comes from

The tenant id may only come from authentication, meaning a claim in the token or the server-side session. It must never come from a request parameter or a header the client can set freely. Middleware resolves tenant, role and data scope on each request and puts them in the request context, and everything downstream reads them from there.

Background jobs and message consumers need tenant context as well, and this is where it most often goes missing. A scheduled job has no request and no middleware, and it is easy to end up operating on the whole table. We require every job to declare which tenants it processes; a job that does not declare it does not run.

3.0 Filtering cannot rely on discipline

  • The ORM adds the tenant condition centrally through a global filter or a repository base class. Business code cannot reach another tenant’s data by default, and crossing tenants has to be explicit.
  • On PostgreSQL, row-level security (RLS) is a second line of defence: even if a hand-written query forgets the condition, the database refuses. Watch how session variables are set and reset with connection pooling, or one request’s tenant leaks into the next.
  • Operations that genuinely span tenants, such as platform reporting or data repair, use a separate role and code path and write an audit log.
  • Unique constraints and indexes include the tenant column, otherwise two tenants using the same reference number collide.

4.0 Reseller hierarchies

Many products are not a flat list of tenants but platform, then reseller, then sub-account or end customer. What usually works:

  • Store organisations as a tree, and let a reseller see data within its own subtree. Query subtrees with a closure table or materialised path instead of recursive queries on every request.
  • Model roles and data scope separately. The role says which operations are allowed, the scope says on which organisations. Combine the two rather than creating a new role for every combination, or the role table gets out of hand quickly.
  • Each level often has its own front end, such as a platform console, a reseller console and mobile pages for sub-accounts, but the backend has one permission model. Hiding a button is not access control; the API checks on every call.

5.0 Tests to write first

  • An access matrix: for every role and level, access to its own, sibling and parent data, with the expected results in a table that runs automatically.
  • ID substitution: replace the resource id in a request with another tenant’s, and the API must answer “forbidden” or “not found”, never with data.
  • Background jobs: run each job on data mixing several tenants and check that it touched only the intended ones.

These tests deserve to come before feature tests. A feature bug is a usability problem; an access-control bug is an incident.

For a new SaaS back office, or to add tenant isolation to an existing system, see Web, Cloud and Device Management, or discuss a project with us.

References

Frequently asked questions

We start with shared tables. Can a large customer still get its own database later?

That depends on whether the data access layer routes by tenant from the start instead of handing out one shared connection everywhere. If it does, moving a tenant out means exporting its data, switching the route and verifying the result, and most of the cost is in rehearsing it.

Does row-level security slow queries down?

A policy is effectively an extra condition on each query, so the cost depends on whether that condition can use an index. With the tenant column leading the composite indexes the impact is usually small. Check the query plans on realistic data volumes before go-live rather than assuming.

Can resellers share data with each other?

Model it as an explicit grant: one organisation grants a specific kind of data to another, and the grant is recorded and revocable. Nothing is shared by default, so nobody sees someone else's data as a side effect of the hierarchy.

When a sub-account user leaves, who owns their data?

The organisation owns the data, not the personal account. When the account is disabled the data stays with the organisation and a manager reassigns it. Decide this when you design the model; retrofitting it touches many tables.