Setting Tenant Context for Row-Level Security Under Transaction Pooling

This guide is part of Transaction vs Statement Pooling Trade-offs. Multi-tenant PostgreSQL applications commonly enforce isolation with row-level security: every table carries a tenant_id, a policy compares it with a custom setting such as app.tenant_id, and the application sets that value when a request begins. With a dedicated connection per request this works. Behind PgBouncer in transaction mode, RDS Proxy or any multiplexing pooler, a session-level SET stays on a backend that the next transaction — belonging to a different tenant — may be given. The failure is either a security incident or an intermittent error, depending on how the policy was written:

ERROR:  invalid input syntax for type uuid: ""
CONTEXT:  SQL expression "(tenant_id = (current_setting('app.tenant_id'::text, true))::uuid)"

The first form is worse and quieter: a request for tenant B returns tenant A’s rows because the backend still carries A’s setting. The objective is to make tenant context transaction-scoped, fail closed when it is missing, and prove that no backend can carry it across clients.

Rapid incident diagnosis

Find how the setting is applied. Search the data access layer for session-level assignments. Any of these is unsafe behind a transaction pooler:

grep -rnE "SET (SESSION )?app\.|set_config\([^)]*,\s*false\)|SET search_path" src/ app/ lib/

A SET app.tenant_id = ... or set_config('app.tenant_id', $1, false) persists for the life of the backend session. SET LOCAL and set_config(..., true) last only until the end of the current transaction.

Check whether requests actually run in a transaction. SET LOCAL issued outside an explicit transaction applies to an implicit single-statement transaction and is discarded immediately; PostgreSQL emits WARNING: SET LOCAL can only be used in transaction blocks. The next query sees no tenant, and a fail-closed policy returns zero rows — which appears as intermittent empty pages rather than an error. Enable statement logging for one tenant’s requests in staging and look for BEGIN before the setting:

ALTER ROLE app_user SET log_statement = 'all';    -- staging only; revert afterwards

Detect leaked context on live backends. Custom settings are not visible in pg_stat_activity, but you can test a pooler for leakage directly. Through the pooler, in autocommit mode, read the setting with no transaction of your own. On a correctly configured system the result is always empty:

for i in $(seq 1 200); do
  psql "host=pgbouncer.internal port=6432 dbname=app user=app_user" -Atc \
    "SELECT coalesce(nullif(current_setting('app.tenant_id', true), ''), '<none>')"
done | sort | uniq -c

Any tenant identifier in the output is a backend carrying another client’s context.

Distinguish the error. invalid input syntax for type uuid: "" comes from casting an empty string. Once a custom setting has been assigned in a session — even with SET LOCAL — current_setting(name, true) returns an empty string rather than NULL after the transaction ends. A policy that casts it directly fails on the next transaction that did not set it.

Mathematical sizing or parameter formula

Transaction-scoped context has a cost: every request must now run inside an explicit transaction, and setting the context adds a statement. The pool effect follows from hold time. For a request that previously ran q autocommit queries with round-trip time rtt and execution time e:

hold_before = q × (rtt + e)                  -- connection returned between queries
hold_after  = (q + 3) × rtt + q × e          -- BEGIN, set_config, COMMIT added; held throughout

Behind a transaction pooler, the connection is held from BEGIN to COMMIT, so in-transaction application work between queries also counts. Worked example: 3 queries, 0.5 ms round trip, 2 ms execution, and 4 ms of application work between the queries. Before, the pooler held a backend for 3 × 2.5 = 7.5 ms spread across three short transactions. After, it holds one backend for 6 × 0.5 + 3 × 2 + 4 = 13 ms. At 800 requests per second the backend demand rises from 800 × 0.0075 = 6 to 800 × 0.013 = 10.4 connections.

Two optimisations recover most of it. Send BEGIN, set_config and the first query in one round trip — drivers that support pipelining or multi-statement simple queries allow it — and do the application work before BEGIN or after COMMIT wherever correctness allows. With both, the example drops to about 4 × 0.5 + 3 × 2 = 8 ms, close to the original.

A second cost is policy evaluation. current_setting() is marked stable, so wrapping it in a scalar subquery lets the planner evaluate it once per statement as an initplan rather than once per row:

USING (tenant_id = (SELECT NULLIF(current_setting('app.tenant_id', true), '')::uuid))

Exact remediation & configuration

Write the policy to fail closed. A missing or empty setting must match no rows and must not raise:

ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;
ALTER TABLE invoices FORCE ROW LEVEL SECURITY;       -- applies to the table owner too

CREATE POLICY tenant_isolation ON invoices
  USING      (tenant_id = (SELECT NULLIF(current_setting('app.tenant_id', true), '')::uuid))
  WITH CHECK (tenant_id = (SELECT NULLIF(current_setting('app.tenant_id', true), '')::uuid));

-- The application role must not bypass policies
ALTER ROLE app_user NOBYPASSRLS;

NULLIF(..., '') turns the post-transaction empty string into NULL, and tenant_id = NULL is never true, so a request without context sees nothing.

Set context with set_config(..., true) as the first statement of every transaction. It accepts a bind parameter, which SET LOCAL does not. In Node.js with pg:

async function withTenant(pool, tenantId, work) {
  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
const invoices = await withTenant(pool, req.tenantId, (c) =>
  c.query("SELECT id, total FROM invoices WHERE status = $1", ["open"]),
);

In SQLAlchemy, attach it to the start of every session transaction so no code path can forget it:

from sqlalchemy import event, text
from sqlalchemy.orm import Session

@event.listens_for(Session, "after_begin")
def set_tenant_context(session, transaction, connection):
    tenant_id = session.info.get("tenant_id")
    if tenant_id is None:
        raise RuntimeError("database transaction started without tenant context")
    connection.execute(text("SELECT set_config('app.tenant_id', :t, true)"), {"t": str(tenant_id)})

In Spring, run it inside the transaction before repository calls — for example in a @Transactional service method via a small component — never in a connection customiser that fires once per physical connection:

@Component
public class TenantContext {
    private final JdbcTemplate jdbc;
    public TenantContext(JdbcTemplate jdbc) { this.jdbc = jdbc; }

    @Transactional(propagation = Propagation.MANDATORY)   // fails if no transaction is active
    public void apply(UUID tenantId) {
        jdbc.queryForObject("SELECT set_config('app.tenant_id', ?, true)", String.class, tenantId.toString());
    }
}

Remove session-level assignments and connection-init hooks that set tenant values, such as HikariCP connectionInitSql, options=-c app.tenant_id=... in connection strings, or ORM “on connect” events. They run once per physical connection, which under a pooler is shared by every tenant.

Keep the pooler’s reset as a backstop, not the fix. PgBouncer’s server_reset_query = DISCARD ALL runs only in session mode unless server_reset_query_always = 1, and enabling it in transaction mode adds a round trip to every transaction and breaks prepared statement tracking. Transaction-scoped settings make it unnecessary.

Validation & verification

Leak test through the pooler. Run two tenants’ workloads concurrently through the pooler against a table where each tenant owns disjoint rows, and assert every result belongs to the requesting tenant:

import asyncio, asyncpg, uuid

TENANTS = [uuid.UUID(int=41), uuid.UUID(int=88)]

async def worker(pool, tenant):
    for _ in range(2000):
        async with pool.acquire() as conn, conn.transaction():
            await conn.execute("SELECT set_config('app.tenant_id', $1, true)", str(tenant))
            rows = await conn.fetch("SELECT tenant_id FROM invoices")
            assert all(r["tenant_id"] == tenant for r in rows), f"leak into {tenant}"

async def main():
    pool = await asyncpg.create_pool("postgresql://app_user@pgbouncer.internal:6432/app",
                                     min_size=20, max_size=20, statement_cache_size=0)
    await asyncio.gather(*(worker(pool, t) for t in TENANTS for _ in range(10)))

asyncio.run(main())

Fail-closed test. Through the pooler, without setting context, a query must return zero rows and no error:

SELECT count(*) FROM invoices;   -- expected: 0

Residue test. The loop from the diagnosis section, run after the load test, must print only <none>.

Role check. Confirm the application role cannot bypass policies and does not own the tables:

SELECT rolname, rolsuper, rolbypassrls FROM pg_roles WHERE rolname = 'app_user';
SELECT tablename, tableowner FROM pg_tables WHERE schemaname = 'public' AND tableowner = 'app_user';

Both rolsuper and rolbypassrls must be false, and the second query must return nothing.

Background jobs, migrations and administrative access

Request handlers are only one path into the database. Every other path must follow the same rule — context inside the transaction — or be deliberately exempt.

Background jobs. A job that processes one tenant’s data must set context at the start of each transaction it opens, exactly as a request does. A job that processes all tenants — a nightly aggregation — should loop over tenants with one transaction per tenant, not disable row-level security:

for tenant_id in tenant_ids:
    with Session(engine) as session:
        session.info["tenant_id"] = tenant_id          # picked up by the after_begin listener
        with session.begin():
            recompute_invoice_totals(session)

Migrations. Schema migrations run as the table owner or a dedicated migration role and must not be subject to tenant policies, which would hide rows from data migrations. Give the migration role BYPASSRLS, connect it directly to PostgreSQL rather than through the pooler, and never reuse it for application traffic.

Administrative and support access. Support tooling that must read across tenants should use a separate role with an explicit policy — for example, a policy that permits all rows when current_user = 'support_readonly' — and every such session should be audited. Avoid giving support staff the migration role.

CREATE ROLE support_readonly NOLOGIN;
CREATE POLICY support_read ON invoices FOR SELECT TO support_readonly USING (true);
GRANT SELECT ON invoices TO support_readonly;

Keeping these paths separate means a mistake in one — a job that forgets context — fails closed with zero rows rather than exposing data, and elevated access is always a different role that can be monitored.

Frequently Asked Questions

Related

• Transaction vs Statement Pooling Trade-offs — the parent guide on what each pooling mode costs in session state.
• Handling Session-Level Features Under Transaction Pooling — advisory locks, temporary tables, LISTEN and other session features.
• Prepared Statement Caching Under Transaction Pooling — the other common transaction-pooling incompatibility.
• How to Choose Between Transaction and Statement Pooling in PostgreSQL — selecting the pooling mode in the first place.