Skip to content
Back home

Article — SaaS

Multi-tenant SaaS: the isolation mistakes that leak customer data

How multi-tenant SaaS apps leak customer data, and how Postgres row-level security, transaction-scoped tenant context and two-tenant tests prevent it.
Reading time
9 min
Topics
SaaS · Multi-tenancy · PostgreSQL · Row-level security
Published
3 November 2026
Go deeper
SaaS Launch Kit skill pack

In a B2B SaaS product, one customer seeing another customer's data is not a bug, it is an incident. It ends deals, triggers breach notifications and is very hard to explain away. Almost every leak I have seen came from an ordinary code path that simply forgot whose data it was handling.

This article covers the tenancy models, the places isolation usually breaks, and how to make the database enforce it so a single forgotten filter is not enough to leak data.

The three tenancy models

There are three common ways to separate tenants in a relational database. Each is a legitimate choice, and each moves the risk somewhere different.

Shared schema with a tenant_id column

Every tenant-owned table has a tenant_id column and all tenants share the same tables.

  • For: cheapest to run, one migration path, simple cross-tenant reporting for your own operations, scales to many small tenants.
  • Against: isolation depends on every query being correct. Noisy neighbours share the same tables, indexes and buffer cache. Per-tenant backup and restore is awkward.

Schema per tenant

Each tenant gets its own schema with identical tables. The application selects the schema per request, often through search_path.

  • For: a missing WHERE clause cannot reach another tenant's tables, and per-tenant restore is easier.
  • Against: every migration runs once per tenant, so deployments slow down and can partially fail. Thousands of schemas mean a very large system catalogue. And search_path is session state, which has the same pooling hazard described later for tenant context.

Database per tenant

Each tenant has its own database, sometimes its own cluster.

  • For: the strongest isolation, per-tenant encryption keys, data residency and maintenance windows, and clean deletion.
  • Against: the highest operational cost. Connection counts, monitoring, migrations and upgrades all multiply. It is usually reserved for large or regulated customers.

Many products end up hybrid: shared schema for most tenants, dedicated databases for the few who pay for it. Whatever you choose, record the decision and the reasons, because changing it later is a migration of every row you own.

The rest of this article focuses on the shared schema model, because it is the most common and the most leak-prone.

Where isolation actually breaks

A forgotten tenant filter

The classic. Ninety-nine queries include WHERE tenant_id = $1 and one, usually added under pressure, does not. Common hiding places are admin screens, search endpoints, raw SQL in a reporting module, and UPDATE or DELETE statements keyed only on id. ORMs help with default scopes until someone calls the unscoped variant.

Trusting a tenant ID from the request

If the client sends tenantId in the body, a query string or a custom header and the server uses it, any authenticated user can try someone else's. The tenant should be derived on the server from something the user cannot forge: their authenticated session, a verified token claim, or a subdomain checked against the user's memberships. When a user belongs to several tenants and picks one, verify the membership on every request, not just at login.

Caches and search indexes keyed without the tenant

A cache key of report:monthly or user:42:dashboard works perfectly in development with one tenant. In production the first tenant to warm the cache serves their data to everyone. Include the tenant in every cache key, every CDN cache key for authenticated responses, and every search index query. For search, either filter on a tenant field enforced server-side, or use a separate index per tenant.

Background jobs and exports that lose context

Request handlers have the tenant from the session. A job picked up from a queue five minutes later has whatever was put in its payload. If the job payload carries only reportId, and the worker loads it with a query that does not scope by tenant, you have a cross-tenant path that no request-level test will catch. Put the tenant ID in every job payload, set tenant context at the start of every job, and fail loudly if it is missing.

Guessable object IDs

Sequential integer IDs let users enumerate. On its own that only leaks counts, but combined with a missing tenant check it turns /invoices/1041 into /invoices/1040. Random identifiers such as UUIDs reduce enumeration, but they are not access control: always check ownership. When an object belongs to another tenant, return 404 rather than 403 so you do not confirm it exists.

File storage paths

Object storage keys like uploads/avatar-123.png or user-controlled filenames make it easy to fetch or overwrite another tenant's files. Prefix keys with the tenant ID, never build paths from untrusted input, keep buckets private, and hand out short-lived signed URLs after checking access.

Logs, analytics and error trackers

Logs and analytics pipelines often contain customer data and are queried by people and tools that bypass the application. Tag every event with the tenant, scrub personal data before it leaves the application, and be careful with customer-facing analytics built on a shared warehouse: the same missing filter problem reappears there.

Defence in depth with Postgres row-level security

Application checks are necessary but not sufficient. PostgreSQL row-level security (RLS) lets the database filter rows by policy, so a query that forgets its tenant filter returns nothing from other tenants instead of everything.

ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;
ALTER TABLE invoices FORCE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON invoices
  USING (tenant_id = nullif(current_setting('app.tenant_id', true), '')::uuid)
  WITH CHECK (tenant_id = nullif(current_setting('app.tenant_id', true), '')::uuid);

How this works, per the PostgreSQL documentation:

  • Once RLS is enabled on a table, normal access must be allowed by a policy. If no policy applies, the default is deny.
  • USING filters the rows a query can see, update or delete. WITH CHECK validates new or updated rows, so a tenant cannot write a row tagged with another tenant's ID. For an ALL policy, USING doubles as the check if you omit WITH CHECK, but stating it explicitly is clearer.
  • If the expression evaluates to null, the row is not visible. With current_setting(name, true), a missing setting returns NULL instead of raising an error, and the nullif treats an empty value the same way, so no tenant context means no rows.
  • app.tenant_id is a custom setting. Postgres accepts any two-part name containing a dot.

The roles matter more than the policy

RLS has documented bypasses that catch teams out:

  • Superusers and roles with BYPASSRLS always bypass row security. If your application connects as postgres or an admin role, your policies do nothing.
  • Table owners normally bypass row security. If the migration role that created the tables is also the role the application uses, the policies are skipped. FORCE ROW LEVEL SECURITY makes policies apply to the owner too, as described in the ALTER TABLE docs.

So the application should connect as a dedicated role that does not own the tables, is not a superuser and does not have BYPASSRLS, with only the grants it needs. Keep FORCE on as a second layer. Use a separate, tightly controlled role for migrations and genuine cross-tenant jobs.

Also note the documented exception: referential integrity checks such as unique constraints and foreign keys bypass RLS. A unique constraint on email alone lets a tenant discover that an address exists in another tenant by trying to insert it. Scope uniqueness by tenant where that is the intended rule.

Setting tenant context safely

The policy reads app.tenant_id, so each request must set it. How you set it is where the subtle bug lives.

A plain SET lasts for the rest of the session. With a connection pooler in transaction mode, such as PgBouncer, a server connection is returned to the pool when your transaction ends and handed to a different client for their next transaction. PgBouncer's own feature table lists SET/RESET as incompatible with transaction pooling. A session-level tenant setting can therefore survive into another request, and that request then runs with the previous tenant's context.

SET LOCAL lasts only until the end of the current transaction, committed or not, which is exactly the scope you want. Two caveats from the docs: issued outside a transaction block, SET LOCAL emits a warning and has no effect, so it must run inside an explicit transaction. And because SET does not accept bind parameters, use the equivalent set_config function with is_local set to true.

BEGIN;
SELECT set_config('app.tenant_id', $1, true);  -- transaction-scoped
SELECT id, total FROM invoices WHERE status = 'open';
COMMIT;

Wrap this in one place in your data access layer, so every unit of work opens a transaction, sets the tenant, runs its queries and commits. Never let request code acquire a raw connection.

Indexes and keys that lead with tenant_id

In a shared schema almost every query filters by tenant first, so indexes should start with tenant_id: an index on (tenant_id, created_at) serves "this tenant's recent invoices" far better than one on created_at alone. RLS adds the tenant predicate to every query, which makes this even more important.

Consider composite keys as well. If child tables reference parents by (tenant_id, id) rather than id alone, the database itself refuses a row that links one tenant's project to another tenant's task.

CREATE TABLE projects (
  tenant_id uuid NOT NULL,
  id uuid NOT NULL DEFAULT gen_random_uuid(),
  name text NOT NULL,
  PRIMARY KEY (tenant_id, id)
);

CREATE TABLE tasks (
  tenant_id uuid NOT NULL,
  id uuid NOT NULL DEFAULT gen_random_uuid(),
  project_id uuid NOT NULL,
  PRIMARY KEY (tenant_id, id),
  FOREIGN KEY (tenant_id, project_id) REFERENCES projects (tenant_id, id)
);

Test isolation with two tenants

Most test suites create one tenant and one user, so cross-tenant bugs are structurally invisible. Make two tenants the default fixture and write tests that try to cross the line:

it('does not expose another tenant\'s invoice', async () => {
  const invoice = await createInvoice(tenantB);
  const res = await api.as(userInTenantA).get(`/invoices/${invoice.id}`);
  expect(res.status).toBe(404);
});

Cover reads, updates, deletes, list endpoints, search, exports and background jobs. Run database-level tests as the same role the application uses: tests running as a superuser will pass even when RLS is broken, because superusers bypass it. A cheap extra guard is a CI query against the system catalogue, pg_class, that fails if any table with a tenant_id column does not have RLS enabled and forced.

Tenant-aware rate limits

Isolation is also about performance. One tenant running a bulk import or a misbehaving integration can degrade everyone on shared infrastructure. Rate limit per tenant as well as per user and per IP address, give heavy operations such as exports and imports their own queues with per-tenant concurrency caps, and track per-tenant usage so you can spot the noisy neighbour before customers report slowness.

Export and deletion

Enterprise customers will ask for a full export of their data, and data protection law such as the UK GDPR gives individuals rights of access and erasure. Build for both early:

  • Keep an inventory of every place tenant data lives: tables, object storage, search indexes, caches, analytics, backups and third-party processors.
  • Generate exports as a background job under the tenant's own context, so isolation rules apply to the export too.
  • Make deletion a tracked, repeatable process across every store, and document how long data persists in backups.

With a tenant_id on every row and every storage key, both become a matter of iteration rather than archaeology.

Closing

Tenant isolation fails at the edges: one query, one cache key, one job payload, one pooled connection. The fix is layers. Derive the tenant on the server, carry it explicitly into every job and key, let Postgres enforce it with RLS under a non-owner role, scope the context to the transaction, and prove it with two-tenant tests that try to break it.

Tenancy decisions, row-level security and pooler-safe tenant context are covered in depth in the SaaS Launch Kit pack.

More articles