Backend
PostgreSQL Row-Level Security for Multi-Tenant SaaS
July 202610 min read
Photo by MattHurst on flickr
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.
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.
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.
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.
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.
Photo by MattHurst on flickr
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.
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.
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.
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;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.
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.
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.
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.
-- 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 errorOnce 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.
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.