Multi-tenant isolation with Postgres Row-Level Security

When many tenants share one database, the scariest bug is the one that leaks another tenant’s data. The usual defense is to add WHERE tenant_id = ? to every query. It works right up until the one query that forgets - a new endpoint, a reporting join, a quick fix under deadline - and now Tenant A can see Tenant B.

In Bildora I don’t trust application code to remember. The database enforces the boundary with Row-Level Security (RLS), so a forgotten clause fails closed instead of leaking.

The policy

Enable RLS on the table, then force it so even the table owner is subject to it:

ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
ALTER TABLE orders FORCE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON orders
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

Every read and write against orders is now silently filtered to the current tenant.

Setting the tenant per request

The policy reads a per-connection setting (a GUC). I set it at the start of each request’s transaction and let it reset automatically at commit:

SET LOCAL app.tenant_id = '2b1f...';

SET LOCAL scopes the value to the transaction, which matters when connections are pooled - the next request on that connection starts clean.

Fail closed

The important property: if app.tenant_id is never set, current_setting('app.tenant_id') raises instead of returning everything. An un-scoped query errors rather than dumping the whole table. The database refuses to guess.

Application-layer filtering is still there as the first line of defense. RLS is the last one - the safety net that catches the query you forgot to filter.