Abuse and Multi-Tenancy

Tenant isolation models, quotas that stop a noisy neighbour, and the tenant predicate that is one missing WHERE clause from a breach.

SyntaxCREATE POLICY tenant_isolation ON invoices USING (tenant_id = current_setting('app.tenant_id', true)::uuid);

Multi-tenancy means one deployment serving many customers' data. It is the default shape of a SaaS product, and it creates two risks an interviewer will push on: one tenant reading another's rows, and one tenant consuming capacity everyone shares.

Isolation models

Shared tables with a tenant_id column is cheapest and scales to many small tenants. A schema per tenant gives cleaner separation and migrations that get painful somewhere in the thousands. A database or cluster per tenant is what you sell to the enterprise buyer who insists, and what you give the one account large enough to be its own shard. Most real systems are shared tables with an escape hatch for the top few accounts — say that, rather than pretending one model fits everything.

The tenant predicate

In the shared model, every read and write carries tenant_id = ?. Correctness then rests on hundreds of query sites, one of which is a reporting job written in a hurry on a Friday. Push the predicate below the application: PostgreSQL row-level security, available since 9.5, attaches it to the table, so a forgotten clause returns zero rows instead of someone else's. Set the tenant per transaction, never per connection, or a pooled connection serves the previous request's tenant.

Quotas

Isolating data is not isolating capacity. Meter per tenant as well as globally: request rate, concurrent in-flight work, storage, queue share. A per-tenant concurrency cap is the cheapest effective control — it stops one customer's bulk import from holding every worker while everyone else queues, which autoscaling will not fix fast enough to matter.

Example

Example · javascript
// tenant-db.js — every query runs inside a transaction that names its tenant.
//
// Schema side (PostgreSQL 9.5+ for RLS, 9.6+ for the policy as written):
//
//   ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;
//   ALTER TABLE invoices FORCE  ROW LEVEL SECURITY;  -- owners bypass policies without this
//   CREATE POLICY tenant_isolation ON invoices USING (
//     tenant_id = current_setting('app.tenant_id', true)::uuid
//   );
//   -- the two-argument current_setting() arrived in PostgreSQL 9.6; the second
//   -- argument `true` means "return NULL if unset" instead of raising,
//   -- so a query with no tenant set matches nothing rather than erroring out.

async function withTenant(tenantId, fn) {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    // set_config(..., is_local = true) scopes the setting to THIS transaction.
    // With `false` it persists on the pooled connection and the next request —
    // a different customer — inherits this tenant id. That is the whole bug.
    await client.query("SELECT set_config('app.tenant_id', $1, true)", [tenantId]);
    const out = await fn(client);
    await client.query('COMMIT');
    return out;
  } catch (e) {
    await client.query('ROLLBACK');
    throw e;
  } finally {
    client.release();
  }
}

// SHORTHAND: the policy appends the predicate. Forgetting it is not possible,
// including in the ad-hoc query someone adds next quarter.
const viaPolicy = await withTenant(req.user.tenant, c =>
  c.query('SELECT id, total FROM invoices WHERE status = $1', ['open']));

// LONGHAND: the same rows. These two are equivalent — but this version is only
// correct while the line is present at all 400 query sites, forever.
const viaWhere = await pool.query(
  'SELECT id, total FROM invoices WHERE status = $1 AND tenant_id = $2',
  ['open', req.user.tenant]);

// Index for it: the tenant column leads every composite index on a shared table,
//   CREATE INDEX ON invoices (tenant_id, status, created_at DESC);
// otherwise the policy's predicate is applied after a scan you did not want.

When to use it

  • Choosing between shared tables, schema-per-tenant and database-per-tenant for a new SaaS product.
  • Stopping one customer's bulk import from exhausting the worker pool for everyone else.
  • Retrofitting row-level security onto an application that currently relies on a WHERE clause in every query.
  • Writing the test that fails the build when a route can be made to return another tenant's rows.

More examples

A per-tenant token bucket for quotas

The script is atomic, so two app servers cannot both spend the last token. Keying by tenant rather than by IP is what makes it a fairness control rather than a bot defence. Weight `cost` by how expensive the call is — a report export is not one request's worth of work — and return the remaining budget in a response header so the customer can back off on purpose.

Example · javascript
// A global rate limit does not stop a noisy neighbour; it only tells you
// afterwards that there was one. Meter per tenant.
// Redis 5.0+: scripts may call TIME, because replication is by effect.
// HSET with several field/value pairs needs Redis 4.0+.
const BUCKET = `
local cap    = tonumber(ARGV[1])
local rate   = tonumber(ARGV[2])            -- tokens refilled per second
local cost   = tonumber(ARGV[3])
local now    = tonumber(redis.call('TIME')[1])   -- server clock, one source of truth
local b      = redis.call('HMGET', KEYS[1], 'tokens', 'ts')
local tokens = tonumber(b[1]) or cap        -- an unseen tenant starts full
local ts     = tonumber(b[2]) or now
tokens = math.min(cap, tokens + (now - ts) * rate)
local allowed = 0
if tokens >= cost then tokens = tokens - cost; allowed = 1 end
redis.call('HSET', KEYS[1], 'tokens', tokens, 'ts', now)
redis.call('EXPIRE', KEYS[1], math.ceil(cap / rate) * 2)  -- idle buckets expire full
return { allowed, math.floor(tokens) }`;

const sha = await redis.script('LOAD', BUCKET);  // load once at boot, EVALSHA per call

async function allow(tenantId, plan, cost = 1) {
  const { burst, perSecond } = PLANS[plan];      // the quota is a pricing decision
  const [ok, left] = await redis.evalsha(sha, 1, `q:${tenantId}`, burst, perSecond, cost);
  return { ok: ok === 1, remaining: left };
}

A concurrency cap, the control that actually protects the pool

Use this on a shared worker pool or any route that fans out to a slow dependency. It is worth saying out loud in a design review that this is per process, so the real cluster limit is the cap times the replica count, and that a tenant-aware queue (one subqueue per tenant, served round-robin) is the asynchronous equivalent for background jobs.

Example · javascript
// Rate limits bound arrivals. This bounds how much of the fleet one tenant
// can hold at once, which is the thing that starves everybody else.
const MAX_PER_TENANT = 4;       // cluster-wide ceiling is this times your replica count
const inFlight = new Map();

async function runForTenant(tenantId, job) {
  const n = inFlight.get(tenantId) ?? 0;   // `?? 0` only defaults on null/undefined, unlike `|| 0`
  if (n >= MAX_PER_TENANT) throw new Shed('tenant at concurrency cap'); // 429, with Retry-After
  inFlight.set(tenantId, n + 1);
  try {
    return await job();
  } finally {
    const left = inFlight.get(tenantId) - 1;
    // Delete at zero, or the map grows by one entry per tenant you ever saw.
    if (left === 0) inFlight.delete(tenantId); else inFlight.set(tenantId, left);
  }
}

Tests that prove the predicate instead of trusting it

The first test is table-driven so that adding a route means adding a case, which is the only version of this that survives a year. The second is the one people miss: it checks that the safety net exists, so a developer who forgets the predicate gets an empty result in staging rather than another customer's data in production.

Example · javascript
// Cross-tenant isolation is the one invariant worth asserting in CI, because
// the failure is a breach rather than a bug report.
test.each(TENANT_SCOPED_ROUTES)('%s does not leak across tenants', async (route) => {
  const acme   = await seedTenant('acme');
  const globex = await seedTenant('globex');

  const res = await request(app)
    .get(route.replace(':id', acme.invoiceId))
    .set('Authorization', `Bearer ${tokenFor(globex.user)}`);

  // 404, not 403: a 403 tells the caller the id exists, which is already a leak.
  expect(res.status).toBe(404);
});

test('a query with no tenant set returns nothing, not everything', async () => {
  await withTenant(ACME, c => c.query("INSERT INTO invoices (tenant_id, total) VALUES ($1, 1)", [ACME]));

  // appPool connects as a NON-OWNER role, so the policy applies. No set_config
  // here, so current_setting('app.tenant_id', true) is NULL, the comparison is
  // NULL, and zero rows come back. Run this exact test as the owner and it
  // returns the row — which is how you catch a missing FORCE ROW LEVEL SECURITY.
  const { rows } = await appPool.query('SELECT * FROM invoices');
  expect(rows).toHaveLength(0);
});

Discussion

  • Be the first to comment on this lesson.