Tenant isolation with Postgres row-level security: how we do it

In a multi-tenant product, one missing WHERE tenant_id = … is enough to leak a customer's data. We did not want isolation to depend on every developer remembering that clause, so Keel enforces it in the database.

Row-level security on every tenant table

Every table that holds workspace data has a tenant_id column and a Postgres row-level security policy that only returns rows where tenant_id matches the current request's tenant.

One tenant per request

Each request runs inside a transaction as a restricted database role. At the start of the transaction we set the tenant for that request:

SET LOCAL app.tenant_id = '…';

Because it is SET LOCAL, the setting ends with the transaction and cannot leak into the next request on the same connection. The policies read it, so queries simply cannot see other workspaces' rows.

No bypass for the app

The role the application uses has no superuser rights and cannot bypass row-level security. Migrations run as a separate role. A bug in a query can return wrong data for the right tenant, but not data from another tenant.

A lint that fails the build

A migration check fails any new table that has a tenant_id column but no row-level security policy. Forgetting isolation on a new table breaks CI instead of shipping.

Next: tests with many tenants

The next step on our build plan is an isolation suite that seeds several tenants and asserts, for every route and search endpoint, that tenant A cannot read or write tenant B's data, on every change.

The same walls everywhere

Isolation is not only for tables. As we add caches, job queues, live update channels, search, and AI retrieval, each one is designed to be keyed by tenant and to filter by project access before anything is ranked.

Things that tripped us up

• Connection pooling. A plain SET would survive on a pooled connection and leak into the next request. SET LOCAL inside a transaction avoids that.
• Table owners. Row-level security does not apply to the table owner by default, which is one more reason the app must not connect as the role that owns the tables.
• Missing tenant setting. Policies should fail closed: if no tenant is set, a query returns nothing rather than everything.

Is row-level security enough?

It is a strong floor, not the whole building. Permission checks per role and per project still live in the application, and everything that is not a table, like caches and queues, needs the same discipline. But putting the tenant wall in the database means a forgotten clause becomes an empty result instead of a data leak.

Read more about how Keel keeps workspaces apart.