How we isolate every agency's data with Row-Level Security

Audaitly is multi-tenant SaaS. Many agencies share one platform, one database, one API, and every one of them is trusting us with their clients’ sites, audit findings, and screenshots. Multi-tenant data isolation isn’t a feature we list on a pricing tier. If it fails once, the product is over. So we enforce it with Row-Level Security, in the database itself, underneath every line of application code. This post is about how, and about the parts that bit us on the way.

Why aren’t application-level WHERE clauses enough?

Application-level scoping fails because it depends on every engineer adding a tenant filter to every query, forever. One forgotten clause in one background job is enough to expose another tenant’s rows, and single-tenant dev databases mean your tests will probably never catch it.

That filter is the standard approach: every query gets a WHERE tenant_id = ... clause, added by hand or by an ORM helper, and everyone on the team promises to never, ever forget.

It works. Until it doesn’t. The failure isn’t hypothetical for us: during development, well before launch, a background job that aggregated audit statistics was written without the tenant filter. It passed review. Two of us read that diff. It ran fine in every test because our dev database had exactly one tenant, which is also true of almost everyone’s dev database. Nothing leaked, because a pre-launch check caught it, but the shape of the near-miss was clarifying. Our most important guarantee depended on every engineer remembering one clause in every query, forever. That’s not a guarantee. That’s a streak.

Streaks end. A new endpoint, a tired reviewer, an ORM call that looks scoped but isn’t, a raw SQL script pasted during an incident. Row-Level Security exists precisely so the streak doesn’t have to hold.

What does Row-Level Security actually do?

Row-Level Security lets you attach policies to a database table: predicates the database applies to every row of every query, no matter what the SQL says or which layer sent it. A query that forgets its tenant scope doesn’t leak; the database filters the rows anyway.

The shape of ours is simple to describe. Every tenant-scoped table carries a policy that says a row is visible only when it belongs to the tenant currently on the line, and the API binds that tenant identity at the start of every request, after authenticating the user and resolving their organisation. The policy is the wall; the request’s tenant context is the key that opens exactly one door in it.

Two details carry most of the weight. First, the application connects to the database as a deliberately unprivileged role, one with no path around the policies, not even the accidental paths the database grants generously by default. Getting that role’s privileges exactly right took longer than writing the policies themselves, and one of the sharper lessons arrived by watching a policy silently not apply in an early test. Second, the tenant identity is scoped to the request that set it, so it evaporates the moment the request finishes. That sounds pedantic right up until shared infrastructure enters the picture. More on that scar below.

In application code, all of this hides behind a single wrapper. Every tenant-scoped read on the web tier goes through one function that establishes the tenant context, runs the query, and cleans up after itself. Routing every existing read through it was a full afternoon of tedious, mechanical edits, and it was worth every minute. There’s now exactly one place where context binding can be wrong, and it’s the most heavily reviewed twenty lines in the codebase.

Why not a database per tenant?

Database-per-tenant gives the strongest isolation there is, but the operational cost sinks a small team: every migration runs once per tenant, backups and monitoring multiply by tenant count, and cross-tenant product analytics becomes a data engineering project. Shared tables with RLS kept most of the isolation at a fraction of the operational surface.

Fair question, and we did sit with it for a while. Schema-per-tenant is a respectable middle ground, and it falls apart the same way for a team our size the moment you think about operations: connection limits, backup schedules, monitoring dashboards. We’re a small team shipping fast; a hundred databases to babysit is how small teams stop shipping fast.

One schema, one migration path, one thing to monitor, and the database still enforces the boundary on every row. We accepted the tradeoff that the wall is a policy rather than a physical database, and then spent our energy making that policy very hard to get around, which is a better deal than spending the same energy on database janitorial work.

Fail closed, or the bug becomes a breach

The question that matters most is what happens when the tenant context is missing. If your policy treats “no setting” as “no filter”, a bug becomes a breach. Ours reads missing context as match nothing. Absent context can’t belong to any organisation, so the query returns zero rows. A forgotten context bind produces an empty dashboard and a bug report, not a leak. Annoying. Wonderfully, boringly annoying.

The one place that can’t have tenant context yet is sign-in, because you don’t know the organisation until you know the user. That path runs through a separate, narrowly scoped access route that can read exactly what authentication requires and nothing else, and it gets its own tests, because “the special path” is where isolation schemes historically rot.

The parts that bit us

Connection reuse

Database connections are precious, so like everyone we reuse them, and a reused connection serves many tenants over its lifetime. Bind the tenant identity carelessly and it can outlive the request that set it, sitting on the connection and waiting for the next request, possibly another agency’s, to inherit it. That is the nightmare scenario, and it’s why the wrapper scopes tenant context so tightly: the moment a request finishes, its context dies with it, and the next request starts from nothing. A little ceremony on every read, cheap insurance.

Migrations and new tables

Schema changes run with more privilege than the application ever gets, which means every new tenant-scoped table has to remember to put its isolation policy in place. “Remember” being the exact failure mode this design exists to remove, we added a guard: an automated check inspects the schema and fails the build if any tenant-scoped table is missing its policy. The database enforces the wall. The build enforces that the wall gets built in the first place.

Background workers

Audits run in background jobs, not just API requests, and a worker crawling one agency’s site must not see another agency’s rows either. So the worker binds tenant context per job, the same way the API binds it per request, through the same wrapper. One code path for setting context, used everywhere, is a lot easier to trust than three.

Performance

The policy predicate runs on every query, so the tenant column is indexed everywhere it appears. In practice the planner treats the policy like any other WHERE clause, and the overhead has been noise for us: equality checks on indexed columns. RLS picked up a reputation for being slow mostly from policies that run subqueries per row. Keep the policy to an indexed comparison and you’ll likely never find it on a flame graph.

Testing the wall

A wall you don’t test is decoration. Our suite connects as the actual application role, binds tenant A’s context, and tries to read tenant B’s rows through every tenant-scoped table. Expected result: zero rows, every time, forever. We also run negative tests around the sign-in path, and a pre-cutover check that replayed realistic traffic against the policies before enforcement went live. We cut RLS in gradually, organisations and users first, then the rest of the tenant-scoped tables, and those checks are what let us do it without holding our breath. The whole suite runs in CI on every commit, against a real database rather than a mock, because mocked databases will cheerfully pass tests that production would fail. This is the one subsystem where that trade is unacceptable.

If you’re doing something similar, one habit worth stealing: write the cross-tenant test before you write the policy. Watching it fail open first is what makes you trust it when it finally fails closed.

What RLS doesn’t solve

Worth being honest about the edges. RLS draws the line between organisations; it says nothing about what happens inside one. Whether a member can see what an admin sees, whether a suspended user can still export reports, all of that is application-level authorisation, and it needs its own design and its own tests. RLS also can’t save you from stolen credentials: if someone signs in as a legitimate user of tenant A, the database will happily and correctly show them tenant A’s data. Different threat, different defences.

And not everything lives in the database. Audit screenshots sit in object storage, so the same discipline applies there through a different mechanism: keys are namespaced per organisation and access goes through the API, which has already bound tenant context before it signs a single URL. The lesson generalises. The database wall is the strongest one we have, but every store you add, object storage, queues, caches, logs, has to answer the same question: what stops tenant A from reading tenant B here? If the answer is “the application is careful”, keep designing.

Boring on purpose

Application-level scoping didn’t go away. Every query is still written scoped, the API still checks membership, sessions still resolve organisations the careful way. RLS sits underneath all of it as the layer that doesn’t care whether the code above made a mistake today. Defence in depth is unglamorous, and that’s the point. The security you want in a load-bearing wall is the boring kind, the kind that’s still standing when someone ships a bad query on a Friday.

For a platform whose entire premise is “point us at your clients’ websites”, the difference between hoping isolation holds and knowing the database enforces it is the whole game. The commitments we make around this are written into our Data Processing Agreement, and the rest of the picture, encryption, sessions, access control, lives on the security page. And if you’re building multi-tenant SaaS on a shared database and putting RLS off: the migration is smaller than you think, and the sleep is better than you remember.