Tenant isolation with PostgreSQL Row-Level Security

A shared SaaS database often starts with a tenant_id condition in application queries. The weak point is the next report or background job that forgets that condition. A stronger design combines database enforcement, trusted request context and tests for every route that can expose data. The example below is an architectural starting point, not a production-ready security configuration.

Choose the isolation model first

Shared tables can simplify central operations when tenants use a similar schema. Separate databases offer different backup, isolation and maintenance options, at the cost of more connections and coordinated migrations. Decide from operational requirements and customer needs; tenant count alone is not a sufficient reason to choose either approach.

Understand the enforcement boundary

PostgreSQL Row-Level Security policies control which rows an applicable role may access or change. Once enabled, missing applicable policies produce default denial for roles subject to RLS. Owners, superusers and roles with BYPASSRLS require particular attention. The application should not routinely connect with a privileged migration role.

Set trusted context per transaction

Resolve the tenant from authenticated membership rather than accepting an arbitrary browser identifier. With pooled connections, avoid leaving tenant state attached to a connection for the next request. A transaction-local setting provides a bounded context. The following example assumes UUID tenant identifiers and requires a properly configured application role.

ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;
ALTER TABLE invoices FORCE ROW LEVEL SECURITY;
CREATE POLICY invoice_tenant ON invoices
USING (tenant_id = nullif(current_setting('app.tenant_id', true), '')::uuid)
WITH CHECK (tenant_id = nullif(current_setting('app.tenant_id', true), '')::uuid);
BEGIN;
SELECT set_config('app.tenant_id', :trusted_tenant_id, true);
SELECT id, total FROM invoices ORDER BY id LIMIT 20;
COMMIT;

Carry isolation beyond the database

Consider a correctly authorized invoice query followed by a Redis write to invoice:42. If identifiers overlap across tenants, that cache can return another tenant’s response without touching the protected table. Include tenant identity in cache keys, jobs and file paths. Consider composite relational constraints where a record must reference another record belonging to the same tenant.

Test denial using the real role

Create two tenants and exercise reads, inserts, updates and deletes using the application database role. Test missing context, reused connections, exports and scheduled jobs. Give administrative access a separate, explicit path with an audit trail. A passing list-page test says little about a forgotten export endpoint.

An implementation sequence

Inventory data paths, protect one table, verify the actual connection-pool behavior, then expand. Record every privileged exception. RLS can enforce a valuable database boundary, but membership verification, object storage and caches remain part of the application’s responsibility.

Scenario: exporting a tenant’s invoices

Consider an administrator requesting an invoice export and signing out before the worker starts. Do not make execution depend on a browser session that may expire. Persist the tenant and requesting actor, then apply the operation’s authorization policy when it runs. Establish tenant context for database reads and write the output into the correct storage scope. At download time, verify access again rather than assuming that a previously issued link remains appropriate indefinitely.

Pre-release review

Test permission removal between export creation and download, context changes on reused connections, and simultaneous exports for different tenants. Inspect actual records, not just the tenant identifier printed in logs. Include a test that detects any foreign row and another that denies access after permission is withdrawn. Support access should be explicit, bounded and audited instead of disabling the normal isolation mechanism.

Prepared by: Noor Yasser