Row-Level Security, Done Properly

The least verifiable sentence in software

Every SaaS product says your data is isolated. Almost none of them say what the isolation is made of, which is a problem, because "isolated" describes an outcome and the outcome is only as good as the mechanism producing it.

There are broadly two mechanisms in use. Give each customer their own database, or keep everyone in one database and enforce a boundary inside it. The first sounds obviously safer. The second is what most platforms actually do, and the honest version of the argument is that the second is safe when a specific set of properties hold — and unsafe, in a quiet and hard-to-notice way, when they do not.

CashSheet uses both models, deliberately, in different products. This is an account of the one behind Workbooks: what it is, what it is not, and which four properties we test rather than assert.

Why "a tenant_id column" is a fair criticism

The naive version of shared-database multi-tenancy is a tenant_id column and a discipline: every query includes WHERE tenant_id = ?.

The criticism of this is well known and correct. The boundary lives in application code, so it holds only as long as every query is written correctly, forever, by everyone. One forgotten clause in one reporting endpoint, one ORM call that builds its own query, one admin panel written in a hurry, and a customer sees rows that are not theirs. The history of SaaS incidents is full of exactly this, and it is a bad failure because nothing errors — the query succeeds and returns too much.

If that were the whole story, the criticism would be decisive. It is not, because Postgres can enforce the boundary itself.

What row-level security actually changes

Row-level security moves the filter from the application into the database. A policy on the table says which rows a session may see and which it may write, and the database applies it to every query against that table — including queries nobody anticipated, written by tools nobody has audited.

The forgotten WHERE clause stops mattering, because the clause is no longer the thing doing the work.

That is the headline, and on its own it is not sufficient. RLS has three well-documented ways of being switched on and still not being on, and they are the difference between a real boundary and a comforting one.

1. Enabled is not the same as forced

ENABLE ROW LEVEL SECURITY does not apply to the table's owner. The owner of a table bypasses its policies by default, silently. Any process connecting as the owner — a migration runner, a maintenance script, an application that never bothered to separate roles — sees everything, and there is no error to notice.

Workbooks uses FORCE ROW LEVEL SECURITY, which applies policies to the owner as well. There is no role for which the policies are advisory.

2. A privileged role bypasses everything anyway

A superuser, or any role with BYPASSRLS, ignores policies entirely. If the application connects as such a role, every policy in the database is decoration.

The Workbooks runtime connects as a dedicated role, workbooks_app, created NOSUPERUSER NOBYPASSRLS. The application literally cannot exempt itself from the policies it runs under, because the credentials it holds do not carry that power. Migrations run as a separate owner role; the application never gets those credentials.

3. A new table is not protected until someone protects it

This is the one that erodes over time. RLS is per-table. Ship a feature, add a table, forget the policy, and you have a hole that no test notices — because the feature works.

So policy application is not a step a developer remembers. It is a build step. After every migration, a routine walks the schema and force-enables RLS on every table carrying a tenant_id column, on the convention that has a tenant_id ⇒ is tenant data ⇒ is protected. Tables that legitimately sit outside tenancy are named in an explicit exemption list, which means excluding a table is a visible, reviewable act rather than an omission.

The default for a new table is protected. You have to write something down to make it otherwise.

The connection pool problem

Here is the failure mode that catches teams who get everything above right.

RLS policies read the current tenant from session state. The obvious implementation is to set that state when a request arrives. But web applications do not use one connection per user — they use a pool, and the same physical connection serves thousands of requests from different tenants over its life.

Set tenant state at the session level and it persists on that connection after the request finishes. The next request to borrow that connection inherits it. Usually that means seeing nothing, because the tenants differ. Occasionally it means something much worse. And the bug is load-dependent and timing-dependent, which is to say it will not reproduce on your laptop.

Workbooks binds tenant scope with transaction-local settings rather than session-local ones. Every unit of work runs inside a transaction that sets its scope for the duration of that transaction only. When the transaction ends the setting is gone — not reset by cleanup code that might not run, but gone because that is what transaction-local means. A connection returning to the pool carries no scope, because scope cannot outlive a transaction.

Three helpers make this explicit in code, and naming them was itself a design decision: withTenant for ordinary work, withoutTenant for genuinely global reads, and withBypass for the small number of privileged operations that must cross the boundary. Bypass is a named, greppable, reviewable call site rather than an ambient capability.

Default-deny

The last property is the one that turns a mistake into a visible failure.

If no tenant is bound, Workbooks reads empty and rejects writes. Not "returns everything". Not "falls back to the last known tenant".

The direction of that default is the entire safety argument. A default-allow system fails open: forget to bind the scope and the query succeeds, returning every tenant's rows, and the only symptom is a page that looks unusually full. A default-deny system fails closed: forget to bind the scope and the feature is visibly broken in development, immediately, by the person writing it.

Writes are constrained the same way. Policies carry a WITH CHECK clause, so a row cannot be written with someone else's tenant id — the boundary applies to what goes in, not only to what comes out. Without that, a tenant could insert rows into another tenant's data while being unable to read them, which is a strange and genuinely dangerous half-boundary.

What we test, not what we claim

Everything above is a design. Designs decay. So the properties are asserted by a test suite that runs in CI against a real Postgres, connecting as both the owner role and the application role, and it checks four things:

• Cross-tenant invisibility — tenant A's session cannot read tenant B's rows.
• Default-deny — an unbound session reads nothing and cannot write.
• Write rejection — the WITH CHECK clause refuses a write carrying the wrong tenant.
• Pool scope containment — bypass scope cannot leak across pooled connections.

That fourth one exists because it is the failure we consider most likely. It is not a property you can eyeball in a code review; it is a property you either exercise or hope about.

Two models, and when each is right

Workbooks and CashSheet Back Office run row-based, on a shared database with the enforcement described here. CutSheet, our production planning system for processors, runs the other model: a dedicated database and application stack per tenant.

The reason is not that one is secure and the other is not. It is that they carry different costs and suit different shapes of customer. A per-tenant stack gives you an isolation boundary you can point at physically, and it costs provisioning, migration and operational overhead per customer — which is proportionate for a plant deployment scoped and priced individually, and disproportionate for a business that signed up this morning to count a drawer. A shared database with enforced RLS gives you a boundary made of policies rather than infrastructure, which is only as strong as the properties above and can serve a self-serve signup in seconds.

The honest summary is that the model matters less than whether anybody checked. A dedicated database with an application that connects as superuser and joins across schemas is not safer than a shared one with forced RLS, a non-bypassing role, transaction-local scope and a test suite. The question worth asking any vendor is not "which model do you use" but "which of these properties do you test, and how often".

Ours run on every build. If you are evaluating Workbooks for something that matters, ask us and we will walk you through the specifics.