01Why the database should enforce it
In a shared-schema SaaS, every table has a tenant_id column and every query must filter by it. Application code can do this, but it has to do it every time, in every query, written by every engineer. Row-level security (RLS) lets PostgreSQL apply the filter itself, so a forgotten clause returns nothing instead of returning someone else’s data.
02The minimal setup
Enable RLS on the table and create a policy that compares the row’s tenant with a setting defined for the current transaction.
<schema.sql
01ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;
02ALTER TABLE invoices FORCE ROW LEVEL SECURITY;
03
04CREATE POLICY tenant_isolation ON invoices
05 USING (tenant_id = current_setting('app.tenant_id')::uuid)
06 WITH CHECK (tenant_id = current_setting('app.tenant_id')::uuid);
USING filters what a query can read. WITH CHECK blocks inserts and updates that would write a row into another tenant. FORCE applies the policy to the table owner too.
03Setting the tenant per request
The API resolves the tenant from the authenticated user and sets it at the start of each transaction. The third argument of set_config makes the value local to the transaction, which matters when connections are pooled.
<TenantScope.java
01@Transactional
02public <T> T inTenant(UUID tenantId, Supplier<T> work) {
03 jdbc.queryForObject("SELECT set_config('app.tenant_id', ?, true)",
04 String.class, tenantId.toString());
05 return work.get();
06}
04Three mistakes that break isolation
<Connecting as a superuser or a role with BYPASSRLS. Policies are skipped without any error. Use a dedicated application role.
<Setting the tenant at session level with a connection pool. The next request on the same connection inherits the previous tenant.
<Forgetting new tables. Add a migration check that fails when a table with tenant_id has RLS disabled.
RLS is a second line of defence. Keep tenant filters in the application as well, and test isolation in CI with two tenants and the same queries.
05Testing it in CI
<isolation_test.sql
01BEGIN;
02SELECT set_config('app.tenant_id', '00000000-0000-0000-0000-0000000000a1', true);
03SELECT count(*) FROM invoices
04 WHERE tenant_id = '00000000-0000-0000-0000-0000000000b2';
05
06ROLLBACK;
We run this check for every table with a tenant column on every pull request. It takes seconds and catches the one mistake that would cost a customer.
[ PHOTO ]
ABOUT THE AUTHOR[ PLACEHOLDER · Name, role and one line of background ]
