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.
1.0 Three isolation models
| Model | Benefit | Cost | Fits |
|---|---|---|---|
| Database per tenant | Strongest isolation; one tenant can be backed up, migrated or restored on its own | Migrations run N times; connections and operations grow with tenant count | Few tenants, or contracts that require physical separation |
| Shared database, schema per tenant | Strong isolation | Migrations and monitoring get harder as schemas multiply | A moderate number of tenants |
| Shared tables with a tenant column | Simplest to operate; cross-tenant reporting is easy | Isolation depends entirely on code and database policy | Many 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
- PostgreSQL, row security policies: https://www.postgresql.org/docs/current/ddl-rowsecurity.html
- OWASP, Authorization Cheat Sheet: https://cheatsheetseries.owasp.org/cheatsheets/Authorization_Cheat_Sheet.html
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.