Case Study 2 — The Forgotten WHERE That Leaked Every Tenant's Data

In a multi-tenant app, one missing WHERE tenant_id = ... exposed every customer's data to every other customer. The bug was a single line of application code — and the fix that made it impossible was Row-Level Security, which moves tenant isolation into the database where it can't be forgotten.

Background

A SaaS product served hundreds of business customers ("tenants") from a shared-schema database: every table had a tenant_id column, and every query was supposed to filter by the current tenant (Chapter 21's shared-schema multi-tenancy). The discipline was "always add WHERE tenant_id = :current_tenant," enforced by code review and convention.

It worked — until a new feature shipped an endpoint whose query forgot the filter:

# ❌ a new "recent invoices" endpoint — the tenant filter was omitted
cur.execute("SELECT * FROM invoices ORDER BY created_at DESC LIMIT 50")

The endpoint returned the 50 most recent invoices across all tenants — so any customer hitting it saw other companies' invoices: amounts, customer names, line items. A catastrophic cross-tenant data leak, from one missing WHERE. It was caught when a customer reported seeing a competitor's invoice, by which point the exposure had been live for days.

What went wrong: isolation enforced only in application code

The architecture relied on every single query, written by every developer, forever remembering to filter by tenant_id. That's an impossible standard at scale — hundreds of queries, many developers, new features under deadline. One omission breaks isolation completely, and there's no safety net: the database happily returns all tenants' rows because it doesn't know about tenant boundaries. Tenant isolation was a convention, not an enforced rule — and conventions get forgotten.

This is exactly the risk Chapter 21 flagged for shared-schema multi-tenancy: it's the simplest and densest model, but without a backstop, a forgotten filter leaks data across tenants.

The fix: Row-Level Security

The team moved tenant isolation into the database with Row-Level Security, so the filter is applied automatically to every query against the table — whether or not the application remembers:

-- Enable RLS and define the isolation policy
ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON invoices
    USING (tenant_id = current_setting('app.tenant_id')::int);

-- The app sets the current tenant once per request/connection:
--   SET app.tenant_id = '42';

Now PostgreSQL automatically appends the tenant_id restriction to every query on invoices. The forgotten-WHERE endpoint, re-run under RLS, returns only the current tenant's invoices — because the database enforces the boundary the application code failed to. The leak became structurally impossible: even a query with no WHERE at all is silently restricted to the right tenant. They applied the same policy to every tenant-scoped table.

As defense in depth, they also kept the application-level filters (belt and suspenders) and added a test that asserts cross-tenant queries return nothing.

The analysis

  1. Don't rely on application code to enforce isolation. "Every query must remember to filter" is a standard no team meets perfectly. One forgotten WHERE — in a new endpoint, a quick fix, a report — breaks it completely, with no safety net. Security boundaries enforced only by convention will eventually be crossed.

  2. Row-Level Security moves the boundary into the database. With RLS, the tenant filter is applied automatically to every query, so a developer cannot forget it. The database — which sees every query — enforces the rule universally, exactly as constraints enforce data rules (Chapter 3). It's the same philosophy: make the unsafe state unrepresentable, don't rely on care.

  3. This is the shared-schema multi-tenancy backstop (Chapter 21). Shared-schema + tenant_id is the cheapest, densest model — but it needs RLS (or equivalent) to be safe. Without the backstop, it's a leak waiting to happen; with it, it's both dense and secure.

  4. Defense in depth still applies. RLS is the enforcement layer; keeping app-level filters and cross-tenant tests adds redundancy. If RLS were misconfigured on one table, the app filter might still catch it (and vice versa).

  5. The cost of the leak vs. the cost of RLS. A cross-tenant leak is among the most damaging incidents a SaaS can have (trust, contracts, regulators). RLS is a one-time setup per table. The asymmetry makes RLS (or equivalent) essentially mandatory for shared-schema multi-tenancy.

Discussion questions

  1. How did a single missing WHERE expose every tenant's data?
  2. Why is "every query must filter by tenant_id" an unreliable security model at scale?
  3. How does RLS make the leak structurally impossible, even for a query with no WHERE?
  4. Why keep application-level filters and RLS (defense in depth)?
  5. ⭐ Compare RLS (shared schema) to schema-per-tenant and database-per-tenant (Chapter 21) for isolation strength vs. operational cost. When would you choose each?