Multi-tenant isolation that survives a forgotten WHERE clause

A multi-tenant platform has one bug class that is categorically worse than the others. Not downtime, not data loss, not a billing error. It is one customer seeing another customer's data.

It is worse because it is unrecoverable in a way outages are not. You can apologise for being down. You cannot un-show a competitor's client list, and the customer who saw it and the customer who was seen both now have a story about your product that no amount of subsequent reliability corrects.

The mechanism is almost always mundane. Somebody wrote a query and did not include the tenant predicate. The query ran without error, returned rows, and the application rendered them. Nothing failed. That is the whole point: this is a silent fault, and silent faults are not caught by the things that catch loud ones.

The usual approach is to add the tenant condition to every query, sometimes helped by a base repository or an ORM global filter. It works, in the sense that it is correct when it is applied.

The problem is the shape of the risk. Every query is an independent opportunity to forget, and the number of queries only grows. A raw SQL string written under deadline, a reporting endpoint, a background job, a migration script, a new developer's first pull request: each is a place where the predicate can be absent and nothing will complain.

Code review catches most of them. Most is not a security posture for a fault whose consequence is losing the customer. The question is not whether your team is careful; it is whether the system is safe when somebody is not, because eventually somebody will not be.

The useful test of any isolation design: if a developer writes SELECT * FROM invoice with no WHERE clause, what happens? If the answer is other tenants' rows, the design depends on nobody ever making a mistake.

Three models, and the choice is mostly about operational cost against blast radius.

Database per tenant

Strongest isolation, and the easiest to explain to a security reviewer. Per-tenant restore is trivial. The cost is operational: migrations, connection management, monitoring and backups now scale with customer count, and a hundred tenants means a hundred of everything.

Schema per tenant

A middle position. One database, one connection pool, isolation by search path. Migrations must run per schema, which is manageable at tens of tenants and unpleasant at hundreds.

Shared schema, tenant column

One database, one schema, a tenant identifier on every row. Cheapest to operate, simplest to migrate, and the model most products should choose. It is also the only one where isolation is a property you have to actively build rather than one you get from the topology.

The rest of this guide is about making the third model safe, because it is the one most teams pick and the one where picking it carelessly is expensive.

Row-level security moves the tenant predicate from every query into the table definition. The database appends it whether the application remembered to or not, which converts a fault of omission into something that cannot be omitted.

A policy needs both halves. USING governs which existing rows are visible to reads, updates and deletes. WITH CHECK governs which rows may be written. Without the second, a developer can still insert a row belonging to somebody else's tenant.

ALTER TABLE invoice ENABLE ROW LEVEL SECURITY;
ALTER TABLE invoice FORCE  ROW LEVEL SECURITY;   -- see section 05

CREATE POLICY tenant_isolation ON invoice
  USING      (tenant_id = current_setting('app.tenant_id')::uuid)
  WITH CHECK (tenant_id = current_setting('app.tenant_id')::uuid);

-- USING      : which rows can be seen, updated, deleted
-- WITH CHECK : which rows may be written. Omit it and a caller can
--              still insert a row into another tenant.

Now the answer to SELECT * FROM invoice with no WHERE clause is the current tenant's invoices. The careless query became a correct one.

Row-level security has two defaults that will silently do nothing useful, and both are easy to miss because the policy exists, the syntax is right, and no error is raised.

ENABLE without FORCE

A table's owner bypasses its policies. Applications very commonly connect as the role that owns the tables, which means RLS is enabled, the policy is present, and every policy is ignored. FORCE ROW LEVEL SECURITY applies policies to the owner as well. Treat it as part of enabling, not an option.

BYPASSRLS on the application role

A role with the BYPASSRLS attribute skips row security entirely, as do superusers. Reserve it for migration tooling that genuinely needs to cross tenants, and never give it to the role the application connects with. Where an administrative surface needs to see everything, write an explicit policy for it rather than handing out the attribute.

Verify rather than assume. Connect as the application role, select from a table with no tenant context set, and confirm you get zero rows rather than everything. Do this in CI, not once by hand.

This is the subtlest failure in the whole design, and it produces exactly the breach the rest of the work was meant to prevent.

The tenant context is set as a database setting. Set it with plain SET and it persists for the life of the connection. Connections come from a pool. The next request to borrow that connection inherits the previous request's tenant, and now one customer is reading another's data through a policy that is working perfectly.

The fix is to scope the setting to the transaction, so it is discarded when the transaction ends and cannot outlive the request.

• Use SET LOCAL, or set_config with the local flag. Never plain SET
• Do all tenant-scoped work inside an explicit transaction, so there is a scope for the setting to be local to
• Statement-mode pooling discards session state between statements and breaks this entirely. Use session or transaction mode
• Set the context in one place, in middleware or a connection wrapper, not at each call site

BEGIN;
  -- third argument true = local to this transaction.
  -- Pass false and the value survives into the next request that
  -- borrows this pooled connection.
  SELECT set_config('app.tenant_id', $1, true);

  -- ... all tenant-scoped work happens inside this transaction
COMMIT;   -- context is gone

If the context is set anywhere other than a single chokepoint, there is a path through your application that reaches the database without it.

Policies protect rows. They do not stop a row in one tenant from referencing a row in another, which is how a line item ends up attached to somebody else's invoice.

Put the tenant in the key. If the foreign key includes the tenant identifier, the database itself refuses a reference that crosses a boundary, and no policy or application check is involved.

-- Parent carries a composite candidate key.
ALTER TABLE invoice ADD CONSTRAINT invoice_tenant_id_key
  UNIQUE (tenant_id, id);

-- Child references the tenant as part of the key, so a row can only
-- point at a parent inside its own tenant. Crossing is now a
-- constraint violation rather than a subtle data fault.
ALTER TABLE invoice_line ADD CONSTRAINT invoice_line_invoice_fk
  FOREIGN KEY (tenant_id, invoice_id)
  REFERENCES invoice (tenant_id, id);

It costs a wider index and removes an entire class of bug that is otherwise found by a customer.

A correct database is necessary and not sufficient. These are the paths that run outside the request context, or outside the database entirely, and each one has to be considered separately.

Background jobs

A scheduled task has no incoming request and therefore no tenant context. It either runs with elevated rights, in which case it can see everything, or it must set the context explicitly per tenant. Make it iterate tenants deliberately rather than querying across them.

Reports and aggregates

Reporting queries are the most likely place for hand-written SQL, and the most likely place for a missing predicate. They are also often run against a replica or a warehouse where the policies were never created.

Caches

A cache key without a tenant identifier serves one tenant's data to another, and the database never sees the request. This is the leak that survives a perfect RLS implementation.

File and object storage

Uploads usually live outside the database. Paths must be tenant-scoped and access checked on read, or a guessable URL is an enumeration attack.

Search indexes

A separate search engine has its own notion of documents and its own filtering. Tenant must be a filter on every query, enforced where the query is built rather than passed in by the caller.

Identifiers

Sequential integers let a curious user guess neighbouring records. Even with policies denying access, the error difference between not found and not permitted leaks the existence of other tenants' data.

Logs and error messages

An exception that includes a row, a query or a payload can put one tenant's data into a log another tenant's support ticket quotes.

Every platform eventually needs a surface that legitimately sees everything: support answering a ticket, an operator checking why a job failed, billing reconciling an account.

That surface is a security boundary, and the single most important rule is that it must not be the same code path as tenant access with a flag turned off. A boolean that disables the tenant predicate is one bug away from being set on a customer-facing request.

• Separate route, separate role, separate policy. Not a parameter on the normal path
• Every cross-tenant read is written to an audit log with actor, tenant, record and time, and that log is not editable from the console
• Access is granted to named people and reviewed, not held permanently by everyone with a staff account
• Where possible the console shows what it needs rather than everything: a support view that resolves one record beats a query tool over the whole table

Assume the audit log will be read during an incident by somebody deciding whether to notify customers. Write it so it can answer the question who saw what, and when.

Isolation is a property you can assert automatically, so assert it. Not for the happy path, which will pass anyway, but for every table, so a new table added without a policy fails the build rather than shipping quietly.

for every table that carries a tenant column:
    given tenant A and tenant B
    insert a row owned by A

    in B's context:
        assert select        returns 0 rows
        assert update        affects 0 rows
        assert delete        affects 0 rows
        assert insert as A   is rejected      # WITH CHECK

    with no tenant context set:
        assert select        returns 0 rows   # not everything

# Enumerate tables from the catalogue, not from a hand-written list,
# so a new table without a policy fails rather than being forgotten.

Deriving the table list from the database catalogue rather than a fixture is what makes this hold over time. A hand-maintained list stops being maintained.

Retrofitting is harder than starting with it, mostly because the existing code assumes it can see everything and you cannot tell which assumptions are load-bearing until they break.

Do it in an order where each step is separately reversible, and where the database is telling you about the gaps rather than your customers.

• Add the tenant column and backfill it, with the column still nullable and nothing enforcing anything
• Make it not null once the backfill is verified, which surfaces every write path that does not set it
• Add policies in permissive mode or against a staging copy first, and log what would have been denied. That log is your list of untenanted queries
• Fix the paths the log exposes: reports, jobs, admin tooling, and whatever else appears
• Enable and force the policies, then add the composite foreign keys
• Turn on the automated isolation test before, not after, so you can see it go from failing to passing

The common objection is that a policy on every table costs speed. In practice a properly indexed policy is not measurably slower than the equivalent predicate written by hand, because it is the same predicate: the planner sees a condition on tenant_id either way.

What does cost you is an index that does not lead with the tenant column. The policy adds a condition to every query, so every index that supports a tenant-scoped query should have the tenant identifier first. Get that wrong and you will blame row-level security for a problem that is a missing index.

Measure before deciding it is too slow. The usual finding is that the policy is free and the schema was already missing the index the application needed.