Postgres Multi-Tenancy: Row-Level Security, tenant_id Filters, or a Schema per Tenant?

When it comes to multi-tenancy in PostgreSQL, there are three main approaches to consider: row-level security (RLS) with a tenant_id column, schema-per-tenant, and database-per-tenant. The most common starting point is using RLS and a tenant_id column, which allows for shared schema and migration path. However, this method can lead to data leaks if not implemented carefully, as RLS can fail silently in both directions.

To enforce RLS properly, you need to enable it with the FORCE clause and use WITH CHECK to validate rows being written. Additionally, set the tenant information inside the transaction rather than as a session-level setting. This ensures that RLS policies are applied correctly to each query.

While RLS with a tenant_id column is a good default for most B2B SaaS applications, schema-per-tenant becomes worthwhile when you have a small number of large tenants with genuinely different needs, such as per-tenant restores or data residency requirements. Database-per-tenant is an operations decision, used when tenants need independent backups, moves, or deletions.

When setting up RLS, make sure to include two statements per table: ALTER TABLE ... ENABLE ROW LEVEL SECURITY; and ALTER TABLE ... FORCE ROW LEVEL SECURITY; along with a policy that uses the tenant_id column and the current setting of the app's current tenant. Remember to enable RLS with the FORCE clause and use WITH CHECK to validate rows being written. Omitting WITH CHECK can allow a tenant to insert rows with someone else's tenant_id, compromising isolation.

In CI pipelines, assert that every table in the schema has RLS enabled and not missing the tenant_id column. This helps catch data leaks early in the development process. Additionally, be aware of how connection pooling affects RLS, as session-level settings may survive across different tenants if not properly reset using SET LOCAL or set_config. Test isolation with real queries under real tenant roles to ensure the policies are truly enforced.