What is PostgreSQL row-level security and why use it for multi-tenant SaaS

Row-level security is a PostgreSQL feature that lets you attach policies directly to a table so the database itself filters which rows a given connection can see or modify. For multi-tenant SaaS, it means tenant isolation is enforced at the data layer instead of relying only on application code remembering to add a tenant_id filter to every query. Even a raw SQL script or a buggy background job is subject to the same policy.

Why do I need FORCE ROW LEVEL SECURITY instead of just ENABLE ROW LEVEL SECURITY

By default, PostgreSQL exempts the table owner from its own row-level security policies, which matters because most application database roles are the table owner or have owner-equivalent privileges. FORCE ROW LEVEL SECURITY removes that exemption so the owning role is subject to the same tenant filtering as everyone else. Without FORCE, a policy can look correct in testing yet silently do nothing in production if the app connects as the owner role.

How do session variables work for passing the current tenant into a policy

A session variable is a custom configuration parameter, typically namespaced like app.current_tenant, that the application sets at the start of each request and the policy reads back with current_setting. The policy compares the tenant_id column against that value, so no query code needs to include a manual tenant filter. The value must be scoped correctly with SET LOCAL inside a transaction, otherwise it can leak into unrelated requests.

Can connection pooling break PostgreSQL row-level security policies

Yes, transaction-mode connection pooling, the default in tools like PgBouncer, recycles a physical connection between transactions and does not preserve session-level SET state across that recycling. If the tenant session variable is set with plain SET instead of SET LOCAL inside the same transaction as the query, that value can carry over to a completely different tenant's request on the next transaction. Always scope the tenant variable with SET LOCAL inside an explicit transaction boundary when pooling is in play.

How do you test that row-level security isolation actually holds

Write negative tests that run as a non-superuser, non-BYPASSRLS role mirroring the application's real database privileges, set one tenant's context, insert data, switch to a second tenant's context, and assert that a matching query returns zero rows rather than an error. Run the same suite through the same connection pooling mode used in production, since a policy that passes on a direct connection is not guaranteed to survive pooling. Put these tests in CI so a future change that weakens a policy or role privilege is caught automatically.

PostgreSQL Row-Level Security for Multi-Tenant SaaS

How to enforce tenant isolation inside the database itself with PostgreSQL row-level security, session variables, and tests that actually prove isolation holds.

Every multi-tenant SaaS eventually asks the same question: what happens the day an application bug forgets a WHERE tenant_id clause. If tenant isolation lives only in application code, that single missing filter leaks one customer's invoices, tickets, or messages straight into another customer's dashboard. Row-level security moves that guarantee down into PostgreSQL itself, so the database refuses to return or write rows that do not belong to the caller, no matter what the application layer forgot to do.

This post walks through a production-shaped setup: enabling row-level security with FORCE so even the table owner is not exempt, writing policies that key off a session variable set per request, the specific ways connection pooling can quietly break that session variable, and a testing approach that proves isolation holds rather than just hoping it does.

Why application-layer tenant filtering is not enough

Most multi-tenant applications isolate tenants by adding a tenant_id column and filtering every query in the ORM or repository layer. That works until it does not. Common failure modes include a new engineer adding a raw query that skips the filter, a background job or admin script that queries the table directly, a caching layer that stores results keyed incorrectly across tenants, or a refactor that silently drops a WHERE clause during a migration. Each of these is invisible in code review because the missing filter looks identical to a normal query.

A single forgotten filter in one endpoint exposes every tenant's data through that endpoint, not just the affected one.
Application-layer isolation must be re-verified in every new code path, every ORM version bump, and every ad hoc script forever.
Database-enforced isolation fails closed by default: a policy that returns no matching rows is safer than a missing filter that returns everyone's rows.

Treat row-level security as a second, independent layer of defense underneath application-layer filtering, not a replacement for it. Defense in depth means a bug in one layer still gets caught by the other.

Enabling row-level security and writing the tenant policy

Row-level security is enabled per table and is off by default even for tables where policies exist. The FORCE variant matters for multi-tenant setups because, without it, the table owner role bypasses every policy, which is exactly the role most application connections use.

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

CREATE POLICY tenant_isolation ON invoices
  USING (tenant_id = current_setting('app.current_tenant')::uuid)
  WITH CHECK (tenant_id = current_setting('app.current_tenant')::uuid);

The policy itself compares a tenant_id column against a PostgreSQL session variable read through current_setting. The application sets that variable once per request or per transaction, and every subsequent query automatically inherits the same filter with zero changes to query code.

BEGIN;
SET LOCAL app.current_tenant = '3f2504e0-4f89-11d3-9a0c-0305e82c3301';
SELECT * FROM invoices WHERE status = 'overdue';
COMMIT;

How the session variable actually gets set

The variable is set with SET LOCAL inside the same transaction that runs the tenant's queries, which scopes it to that transaction only. Using SET without LOCAL persists the value for the rest of the physical connection, which is dangerous the moment that connection is reused for a different tenant.

Command	Scope	Multi-tenant risk
SET app.current_tenant	Rest of the session or pooled connection	High: value leaks to the next request served from the same connection
SET LOCAL app.current_tenant	Current transaction only	Low: automatically clears on commit or rollback
current_setting with missing_ok true	Read at query time	Prevents a hard error if the variable was never set, so a missing tenant context fails the policy instead of crashing the query

Transaction-mode connection pooling, the default in tools like PgBouncer, recycles a physical connection between transactions. SET and RESET session state do not persist across that recycling by design, which is exactly why SET LOCAL inside the transaction boundary is not optional here, it is the only setting that survives correctly under pooling.

What pooling actually breaks and how to work around it

Session pooling keeps one physical connection per client for the whole session, so session-scoped SET works fine but you lose most of the resource efficiency pooling exists for. Transaction pooling reclaims the connection as soon as a transaction ends, which is where teams get burned: a session-level SET app.current_tenant silently carries over to whatever transaction the next unrelated request happens to land on.

Always set the tenant context with SET LOCAL and always do it as the first statement inside an explicit transaction, never rely on autocommit-per-statement mode to scope it correctly.
If the ORM wraps every request in its own transaction already, hook the tenant SET LOCAL into that same transaction boundary rather than issuing it as a separate statement outside it.
Avoid PREPARE-based statement caching across pooled connections unless the pooler explicitly supports it, since a cached plan tied to one tenant's session state can be reused for another tenant's query.

In practice this means auditing every place a connection is checked out from the pool and confirming the tenant SET LOCAL happens inside the same transaction as the query that needs it, not before the transaction starts and not in a separate round trip. Most ORMs and query builders expose a callback or middleware hook that wraps the entire request in one transaction, and that hook is the correct place to issue the tenant SET LOCAL exactly once, rather than scattering it across individual repository methods where it is easy to miss a code path.

Testing that isolation actually holds

A policy that looks correct on paper can still leak data through an edge case: NULL tenant IDs, a role with BYPASSRLS accidentally granted, or a superuser connection used for what should be a scoped tenant query. The only reliable way to catch these is a negative test suite that runs as a non-superuser role and asserts zero rows come back for another tenant's data, rather than trusting that the policy syntax alone is sufficient.

Create a dedicated, non-superuser, non-BYPASSRLS test role that mirrors exactly what the application's runtime role can do.
For every table with a tenant policy, write a test that sets tenant A's context, inserts rows, switches to tenant B's context, and asserts a matching SELECT returns zero rows.
Run the same suite against a connection pooled through the same pooling mode used in production, since a passing test on a direct connection does not guarantee the policy survives pooling.

-- Negative isolation test: assert tenant B cannot see tenant A rows
SET LOCAL app.current_tenant = '<tenant_b_uuid>';
SELECT count(*) FROM invoices WHERE tenant_id = '<tenant_a_uuid>';
-- expected: 0 rows, not an error

Once these negative tests are in CI, a future refactor that accidentally weakens a policy, changes a role's BYPASSRLS attribute, or drops FORCE ROW LEVEL SECURITY on a table gets caught automatically before it reaches production, instead of waiting for a customer to notice their data mixed with someone else's.

Rollout notes for an existing multi-tenant schema

Enabling row-level security on tables that already hold production data is safe to do incrementally: enable it without FORCE first, verify application behavior in staging with the tenant variable wired through every code path, then flip FORCE on once you are confident the table owner role is never relied upon to bypass policies. Keep row_security set to off temporarily during backup and migration scripts so administrative tooling does not silently filter rows it needs to see, then turn it back on immediately afterward. Roll the change out table by table rather than schema-wide in one migration, starting with the highest-risk tables that hold customer-facing data, so a mistake in one policy is easy to isolate and roll back without touching every other table at the same time.

Index the tenant_id column that every policy filters on. A policy adds a WHERE clause to every query PostgreSQL plans, and an unindexed tenant_id turns that clause into a sequential scan across the whole table for every single request.

Sources