One forgotten WHERE clause: how we keep SaaS tenants apart

One forgotten WHERE tenant_id = ... and a customer is looking at someone else’s data. That is the core risk of a multi-tenant SaaS, where a single codebase and a single infrastructure serve many independent customers. The load problems from the previous post gain another layer here. The system has to be fast and secure, and it also has to guarantee that no tenant can see or slow down another. The decisions below come from last year’s SaaS projects.

Three models

There are three basic ways to separate tenant data, and each has its place.

Shared schema with a tenant_id column. Every table carries a tenant identifier and every query filters on it. It is the cheapest to operate, migrations are simple, and a backup covers one database. The risk: one forgotten WHERE tenant_id = ... and you have a data leak.

Schema per tenant. Within one PostgreSQL database, each tenant gets its own schema. Isolation is stronger, and with search_path set the queries stay the same. The cost: migrations have to run per tenant, and beyond a few hundred schemas catalogue operations slow down noticeably.

Database per tenant. Full isolation, per-tenant backup and restore, optionally on separate servers. It is the most expensive option and only justified by contractual or regulatory requirements.

Most of our projects choose the shared schema. The risk of a forgotten filter is handled by a database-level mechanism, so it does not depend on developer discipline.

Let the database filter

With PostgreSQL row-level security (RLS), the database itself enforces the filtering, even when the application gets it wrong. Our pattern:

• ENABLE ROW LEVEL SECURITY on every tenant-scoped table, plus a policy that filters on current_setting('app.tenant_id'),
• the application sets the tenant at the start of the request, inside the transaction, with SET LOCAL app.tenant_id = ...,
• the application’s database user does not own the table, so the policy applies to it too.

A missing WHERE clause then returns an empty result set. That is far easier to spot than a data leak, and far less painful. The price is slightly more complex query plans, and the fact that with PgBouncer in transaction mode SET LOCAL is the only safe form.

Settings live in a table

We paid for this lesson more than once. Tenant-specific settings (limits, enabled features, integration keys, branding) belong in the database, in a versioned tenant_settings-style table. We keep them out of environment variables and configuration files. The reasons:

• onboarding a new tenant needs no deployment,
• changes to settings can be logged and rolled back,
• the admin interface sees exactly the same data as the application.

Secrets (API keys, passwords) are stored encrypted, with the key read from the application’s environment. Settings are cached in Redis with a short TTL, because every request needs them.

Migrations and the noisy neighbour

The big advantage of a shared schema is that a migration runs once. But altering a large table hits every tenant at the same time, so we follow a few rules: only additive changes during production hours, columns dropped in two steps (code first, then schema), and CREATE INDEX CONCURRENTLY for every index.

Against the “noisy neighbour”, one tenant’s load slowing down everyone else, we defend on several levels:

• per-tenant rate limits in Redis, on top of the global one,
• heavy operations (exports, bulk imports, reports) go to a background queue with a per-tenant concurrency limit,
• queries run with a statement_timeout, so a single bad query cannot hold a connection for minutes,
• the tenant ID is a label on every metric, so a problem tenant shows up immediately.

Billing and backups

In a SaaS, billing is an architectural question, and hard to bolt on later. Two things go in from day one:

Usage events. Every billable operation (API call, message sent, record stored) writes an event to an append-only table, with tenant ID and timestamp. The end-of-period summary is built from it, and in a dispute it is the record you go back to.

State transition hooks. The tenant lifecycle (trial, active, payment overdue, suspended, deleted) is an explicit state machine. Each transition runs its hooks: notification, feature restriction, starting the data-retention timer. “What happens if they stop paying” has a documented answer.

The weak spot of the shared schema is backup: restoring a single tenant is not trivial. So alongside the daily full backup we produce a per-tenant logical export (pg_dump with filtered data, or an application-level export), and we rehearse the restore procedure regularly, in practice as well as on paper.

Multi-tenant architecture comes down to a dozen smaller, interlocking decisions. A shared schema with RLS, tenant configuration in the database and per-tenant limits strike a good balance between operational simplicity and safety in most cases. If a tenant demands full isolation for contractual reasons, it gets a separate database. For us, that remains the exception.

We build and operate this kind of architecture for clients too (SaaS development and operations). Our own product, Abigel.ai, shows how it works in production in a case study.