Multi-Tenant Isolation in PostgreSQL

The bug I found in my own live product

I run a multi-creator subscription platform where every creator is a tenant, all sharing one Postgres database. Separation between them is enforced in the database rather than in application code, because the database is the one layer every request has to pass through.

During a review I found that any signed-in user could upgrade their own account to a paid plan by sending a single update from the browser console. No credential compromise. No server bug. The request was well formed, correctly authenticated, and satisfied every authorization policy I had written.

The same class of write also let a user grant themselves admin rights, reset their usage counter, and rewrite which tenant they belonged to.

Why it happened

Row-level security decides whether you may act on a row. It has no vocabulary for columns.

My policy said a user may update the row whose id matches their own. An update that sets the plan column on that same row satisfies that completely. The row genuinely belongs to the caller. The check genuinely passes. The result is a free upgrade.

The mechanism was not broken. It was doing exactly what it promises, and the promise was narrower than the assumption I had built on top of it.

The fix, in three layers

Row scope. RLS policies on every tenant-scoped table, with FORCE ROW LEVEL SECURITY enabled. Postgres exempts a table's owner from its own policies unless you force it, which means a system can pass review and still be wide open, with no error and no warning.

Privileged columns. A before-update trigger that rejects changes to plan, admin status, usage counters, and tenant association unless the caller is an authorized internal writer. It compares old and new values rather than detecting the column in the statement, so ordinary saves that write back unchanged fields keep working.

Metering. Usage limits moved into security-definer functions that do the check and the increment together, so the caller supplies no count and cannot reach the counter.

Two details that decide whether any of it is real

The authorization flag lives inside the transaction that needs it and does not survive past it. Under connection pooling, a flag that persists gets inherited by whatever unrelated request reuses that connection next, and a privilege leak that only appears under load is close to undiagnosable.

Billing events are keyed by the provider's event id, so a webhook delivered twice cannot grant a second entitlement. Payment providers retry by design, which makes duplicate delivery expected rather than exceptional.

Proving it stays fixed

Most authorization test suites confirm that permitted actions succeed. That is the less useful half, because a policy that is too permissive passes every happy-path test.

I wrote fifteen integration tests the other way around. Each one performs an attack and asserts the database refuses it. They run against real Postgres in Docker, from migrations applied to an empty schema, connecting as a role that owns no tables. That last detail is what makes the results mean anything: a suite that connects as the table owner bypasses the very policies it is testing, and passes while production is open.

What I took from it

Any security model inherits the limits of the primitive underneath it, and those limits are worth writing down rather than leaving to be inferred.

Detection is also not a substitute for enforcement. A self-service plan upgrade produces the same database write as a legitimate one, so no monitoring would have separated them. I found this by reading the schema and asking what an update statement could reach.

I extracted the whole pattern into a public repo so it is reusable rather than trapped inside one product.