paulserban.eu

Writing Edition

Paul Serban

Writing, snippets & book notes

Vol. 2026

February 08, 2025

Row-Level Security and Connection Pooling: Closing Postgres's Multi-Tenant Leak Vector

Why session-scoped RLS context and PgBouncer transaction mode are a dangerous combination, and how to guarantee tenant isolation anyway

Introduction

Postgres Row-Level Security is one of the most elegant tools available for building multi-tenant systems. Instead of scattering WHERE tenant_id = ? clauses across every query in your codebase, you push the isolation logic into the database itself, where it cannot be forgotten by a tired engineer at 11 PM. A policy defined once applies everywhere: ORMs, ad-hoc scripts, analytics queries, and future code you haven't written yet all inherit the same guarantee. For teams that have been burned by a missing WHERE clause leaking one customer's invoices into another's dashboard, RLS feels like the fix that finally lets you sleep.

But RLS has a quiet dependency that most teams discover the hard way: it typically relies on session state, usually a custom GUC like app.tenant_id set via current_setting(). Session state belongs to a database connection, not to a request. The moment you introduce a connection pooler sitting between your application and Postgres - which is standard practice at any meaningful scale - that assumption breaks. This article walks through exactly how the interaction between RLS and pooling fails, why it fails in PgBouncer's transaction mode specifically, and the concrete patterns that let you keep both the operational benefits of pooling and the security guarantees of RLS.

The Problem: Shared Connections, Private Context

Connection pooling exists because establishing a new Postgres connection is expensive. Each connection forks a backend process, allocates memory, and negotiates authentication - work that adds up fast under load. Poolers like PgBouncer, PgCat, or Supavisor solve this by maintaining a small set of long-lived physical connections to Postgres and multiplexing many client requests over them. Your application thinks it has its own connection; in reality, it is borrowing one from a shared pool for a brief window and then handing it back.

This works beautifully for stateless SQL. It works terribly for anything that depends on connection-local state, because connection-local state is exactly what the pool reuses across unrelated callers. Postgres session variables set with SET app.tenant_id = 'tenant-a' persist on that physical connection until it is reset or the session ends. If your application sets this value at the start of a request and never explicitly clears it, the setting rides along on the connection after it goes back to the pool. The next request to borrow that connection - quite possibly belonging to a different tenant - inherits whatever context the previous occupant left behind.

The severity here is easy to underestimate because in development, with one connection per process and low concurrency, everything appears to work. The bug is invisible until you're at production scale with a pool of 20 connections serving hundreds of concurrent tenants, at which point it becomes a statistics game: the more requests per minute, the more likely two different tenants land on the same physical connection back-to-back, and the more likely a cache-warm response accidentally serves tenant B's rows to tenant A. This is not a theoretical edge case; it's the default failure mode of combining session-based RLS with any pooler running in anything other than session mode.

How It Breaks in PgBouncer Transaction Mode

PgBouncer offers three pooling modes, and the choice matters enormously here. Session mode assigns a client its own backend connection for the lifetime of that client's session - closest to no pooling at all, and the safest with respect to session state, but it defeats much of the purpose of pooling since idle clients still hold a connection. Transaction mode, the configuration almost everyone actually wants for web application workloads, returns the backend connection to the pool as soon as a transaction commits or rolls back. Statement mode goes further and can multiplex at the statement level, which is incompatible with most transactional application code and rarely used.

Transaction mode is where the RLS interaction turns genuinely dangerous. A SET statement executed outside of an explicit transaction block is not guaranteed to run on the same backend connection as the query that follows it - PgBouncer may route your next statement to an entirely different server connection, meaning your session setting never reaches the query that needed it, and the policy either silently uses stale data from whatever connection you did land on, or finds no setting at all. Worse, if you do manage to set it directly on a borrowed connection without wrapping it in a transaction, that setting outlives your request entirely: it sits on the connection as a lingering side effect, waiting for the next unrelated caller to inherit it. This is precisely the stale-context scenario described above, and transaction mode makes it the common case rather than the rare one.

Implementation Patterns That Actually Work

The fix is to stop treating RLS context as session state and start treating it as transaction state. Postgres gives you exactly the primitive you need: set_config(setting_name, value, is_local). When is_local is true, the setting behaves like SET LOCAL - it applies only for the remainder of the current transaction and is automatically discarded on COMMIT or ROLLBACK, regardless of which physical connection the transaction happens to run on. Because the reset is automatic and guaranteed by Postgres itself, not by application discipline, this closes the leak at its root rather than papering over it.

BEGIN;
SELECT set_config('app.tenant_id', $1, true);
-- all queries in this transaction are now scoped to the tenant
SELECT * FROM invoices WHERE tenant_id = current_setting('app.tenant_id')::uuid;
COMMIT;

The reason to prefer set_config() over the SET LOCAL app.tenant_id = 'value' syntax is practical rather than semantic: set_config() is a regular function call, which means it accepts bind parameters. SET LOCAL requires a literal in the SQL text, which either forces string interpolation (a SQL injection risk for a value you may not fully control) or forces you to special-case this one statement in your query builder. set_config() lets you pass the tenant ID the same way you'd pass any other bound parameter.

In practice, you don't want every call site in your codebase remembering to open a transaction and call set_config correctly. You want a single chokepoint that every request passes through. Here's a Node.js example using pg that wraps this as a reusable helper around a pooled client:

import { Pool, PoolClient } from "pg";

const pool = new Pool({ connectionString: process.env.DATABASE_URL });

export async function withTenantContext<T>(
  tenantId: string,
  work: (client: PoolClient) => Promise<T>
): Promise<T> {
  const client = await pool.connect();
  try {
    await client.query("BEGIN");
    await client.query("SELECT set_config('app.tenant_id', $1, true)", [tenantId]);
    const result = await work(client);
    await client.query("COMMIT");
    return result;
  } catch (err) {
    await client.query("ROLLBACK");
    throw err;
  } finally {
    client.release();
  }
}

// usage in a request handler
app.get("/invoices", async (req, res) => {
  const invoices = await withTenantContext(req.tenantId, (client) =>
    client.query("SELECT * FROM invoices").then((r) => r.rows)
  );
  res.json(invoices);
});

Because work() can only run queries inside the open transaction on the client that was just configured, there is no code path that lets a query execute without the tenant context attached, and no code path that lets the context survive past COMMIT. If a future contributor adds a new query, they're forced through this helper or they don't get a connection at all - the safe path is also the path of least resistance.

For applications built on Supabase or implementing a similar JWT-claims-driven authorization model, the same transaction-scoped principle extends to role switching. You can set both the active role and the claims payload within the same local scope:

BEGIN;
SET LOCAL ROLE authenticated;
SELECT set_config('request.jwt.claims', $1, true);
-- policies read current_setting('request.jwt.claims', true)::json ->> 'sub'
COMMIT;

This pattern is worth adopting even outside Supabase-style stacks, because it generalizes: any claim-based or role-based authorization scheme benefits from being scoped to the transaction rather than the session, for exactly the same reasons tenant IDs do.

Trade-offs and Pitfalls Beyond the Obvious

Transaction-scoped context solves the stale-setting problem, but it introduces its own discipline requirements that teams routinely get wrong. The most common mistake is writing RLS policies that fail open rather than fail closed. A policy like USING (tenant_id = current_setting('app.tenant_id', true)::uuid) looks correct, but current_setting with the "missing is OK" flag set to true returns an empty string when the setting isn't present, and casting an empty string to uuid throws an error rather than quietly returning no rows - which sounds safe until you remember that some application code wraps database calls in broad try/catch blocks that swallow the error and return an empty list to the caller, masking a misconfiguration as "no data" rather than surfacing it as the bug it is. The correct policy explicitly handles the missing case with nullif(), forcing a NULL comparison that evaluates to no matching rows rather than an error:

CREATE POLICY tenant_isolation ON invoices
  USING (tenant_id = nullif(current_setting('app.tenant_id', true), '')::uuid);

The second pitfall is role configuration, and it's arguably more dangerous because it fails silently in the opposite direction - not by throwing errors, but by having RLS do nothing at all. Table owners bypass RLS by default, as do any roles granted the BYPASSRLS attribute, and it's extremely common for application connection strings to use the same role that ran the migrations, which is typically the owner. If your pooled application connects as the table owner, every policy you've carefully written is simply inert; psql will show you the policy exists, EXPLAIN will show no filter being applied, and nobody will notice until an audit or an incident. The fix is organizational as much as technical: migrations run as an owner role with elevated privileges, and the application runs as a separate, non-owner role with NOBYPASSRLS, with tables additionally marked FORCE ROW LEVEL SECURITY so that even the owner is subject to policies when connecting through the application's credentials.

Best Practices for Production Systems

Beyond the core transaction-scoping pattern, a handful of operational practices separate teams that have genuinely solved this problem from teams that have a fix that works until it doesn't. First, if you cannot avoid session-level pooling for some workload - certain long-running analytical connections, for instance, or a legacy service that predates this architecture - configure PgBouncer's server_reset_query to run DISCARD ALL whenever a connection is released back to the pool. This resets session-level GUCs, prepared statements, and temporary tables, eliminating cross-session contamination. It's worth being explicit that this setting does not run automatically in transaction mode by default and is not a substitute for transaction-scoped context there; treat it as a safety net for session-mode pooling specifically, not as your primary defense.

Second, keep your policies cheap, because RLS predicates run on every row touched by every query against a protected table, and an expensive policy becomes an expensive multiplier across your entire workload. Index the column you filter on - tenant_id should almost always have a btree index, often as the leading column in a composite index matching your common query patterns. Avoid subqueries inside policy definitions where possible; a policy that does tenant_id IN (SELECT tenant_id FROM memberships WHERE user_id = current_setting('app.user_id')::uuid) forces Postgres to evaluate that subquery per row in the worst case. Wrapping context lookups in STABLE SQL functions helps the planner recognize that the value won't change within a single query execution and can be evaluated once rather than repeatedly.

Third, treat your RLS policies as part of your schema's test surface, not as a background assumption. Policies are code; code regresses when nobody is watching it. A migration that adds a new table and forgets FORCE ROW LEVEL SECURITY, or a new policy with an off-by-one in its tenant comparison, should fail CI the same way a broken unit test would - which leads directly into how you actually verify isolation holds.

Testing for Leaks, Not Just Trusting the Design

The only convincing evidence that your RLS and pooling setup is safe is a test that tries to break it and fails to. The most direct test constrains the pool to a single connection and runs two different tenants' requests through it in sequence, asserting that the second tenant never sees rows belonging to the first. This directly simulates the worst case - a pool under load where every connection gets reused constantly - without needing to wait for production concurrency to surface the bug by accident.

import psycopg2.pool
import pytest

@pytest.fixture
def single_conn_pool():
    pool = psycopg2.pool.ThreadedConnectionPool(1, 1, dsn=TEST_DSN)
    yield pool
    pool.closeall()

def run_as_tenant(pool, tenant_id):
    conn = pool.getconn()
    try:
        with conn.cursor() as cur:
            cur.execute("BEGIN")
            cur.execute("SELECT set_config('app.tenant_id', %s, true)", (tenant_id,))
            cur.execute("SELECT id, tenant_id FROM invoices")
            rows = cur.fetchall()
            cur.execute("COMMIT")
        return rows
    finally:
        pool.putconn(conn)

def test_no_cross_tenant_leak_on_shared_connection(single_conn_pool):
    rows_a = run_as_tenant(single_conn_pool, TENANT_A_ID)
    rows_b = run_as_tenant(single_conn_pool, TENANT_B_ID)
    assert all(r[1] == TENANT_A_ID for r in rows_a)
    assert all(r[1] == TENANT_B_ID for r in rows_b)
    assert set(r[0] for r in rows_a).isdisjoint(set(r[0] for r in rows_b))

A second test should run a query with no tenant context set at all and assert it returns zero rows, catching any policy that fails open rather than closed. A third should connect explicitly as the application's runtime role - never the owner - and confirm that FORCE ROW LEVEL SECURITY is actually in effect, for instance by checking pg_tables.rowsecurity and pg_class.relforcerowsecurity programmatically as part of a schema-validation step in CI. None of these tests are exotic; they're a few dozen lines each, and together they catch the overwhelming majority of real-world RLS misconfigurations before they reach production.

Key Takeaways

If you only change five things after reading this, make them these: set tenant and claims context with set_config(..., true) inside an explicit transaction, never with a bare session-level SET. Write every RLS policy to fail closed on a missing setting, using nullif() to turn an absent context into zero rows rather than an error or an open gate. Run your application as a dedicated, non-owner role with NOBYPASSRLS, and apply FORCE ROW LEVEL SECURITY on every protected table so even privileged connections can't accidentally skip policies. Build a single, mandatory code chokepoint - a middleware, decorator, or helper function - that every database-touching request must pass through, so the safe pattern is also the only available pattern. And add the single-connection-pool leak test to your CI suite now, before an incident forces you to write it under pressure.

Second, keep the distinction between session mode and transaction mode visible in your team's architecture documentation, not just in a PgBouncer config file nobody rereads. The choice of pooling mode is a security-relevant decision, not merely a performance tuning knob, and new engineers joining the team should understand why SET is banned in application code before they write their first query against a multi-tenant table.

Analogies and Mental Models

It helps to think of a pooled Postgres connection the way you'd think of a shared hotel room rather than a private apartment. A session-level setting is like leaving your luggage in the room when you check out - the next guest, even though they have no relationship to you, inherits whatever you left behind until housekeeping explicitly clears it. DISCARD ALL is that housekeeping pass, and it's a reasonable safety net, but you shouldn't rely on it as your primary security control any more than a hotel chain relies on "hope the previous guest didn't leave anything dangerous" as its security model.

Transaction-scoped context, by contrast, is closer to a locker you rent for the duration of a single visit to the gym: it's yours for exactly as long as you're using it, and it empties itself automatically the moment you walk out, with no dependency on you remembering to clean it or on the facility running a nightly reset. The guarantee comes from the mechanism's design, not from anyone's diligence - which is exactly the property you want from a security control that every request in your system depends on.

The 80/20 Insight

Of everything covered here, two decisions produce almost all of the real-world security benefit. The first is the blanket rule: never call SET on a connection-context value outside of an explicit transaction, full stop, enforced through a single shared helper that the rest of the application cannot bypass. This one discipline eliminates the entire class of stale-session leaks that this article opened with, and it's a change you can make without touching your RLS policies at all.

The second is the fail-closed policy pattern using nullif() around current_setting(). It costs nothing in query performance and takes one line per policy, but it's the difference between a misconfiguration producing an obvious error (or an empty, clearly-wrong result set that gets caught in testing) versus a misconfiguration silently serving another tenant's data. Everything else in this article - role separation, FORCE ROW LEVEL SECURITY, index tuning, DISCARD ALL, the test suite - meaningfully reduces residual risk and is worth doing, but if you're triaging limited engineering time against a hard deadline, these two changes are where the leverage actually is.

Schema-per-tenant or database-per-tenant architectures sidestep this entire category of problem by construction, since there's no shared row space for a stale setting to misdirect a query into. That's a legitimate answer for organizations with strict regulatory isolation requirements, but it trades away the operational simplicity of a single schema and the ability to run cross-tenant analytics without federation, so it should be a deliberate architectural choice rather than a default reached for out of fear of the pooling problem.

Conclusion

Row-Level Security and connection pooling are not fundamentally incompatible, but they share an assumption that is easy to violate without realizing it: that connection state belongs to a request. Once you accept that it doesn't - that a physical connection is a shared resource passed between unrelated callers - the correct architecture follows fairly directly. Scope every security-relevant setting to the transaction, never the session. Write policies that fail closed. Run as a role that cannot bypass RLS, and force the issue even for roles that technically can.

None of these individual changes are exotic or difficult to implement; the set_config(..., true) pattern is a few lines of SQL, and the nullif() guard is a one-line policy change. What makes this problem dangerous in practice isn't technical difficulty, it's that the failure mode is invisible in development and under light load, and only manifests as actual cross-tenant data exposure once you're running a production pool under real concurrency - exactly the environment where you have the least appetite for discovering it the hard way. Treat the pattern described here as a baseline requirement for any multi-tenant Postgres deployment behind a pooler, write the leak test before you need it, and you convert a category of incident that has ended careers at other companies into a non-event at yours.

References