skip to content

Your service uses a connection pool, so every database session authenticates as one shared application account and the database has no idea which end user made the request. What are the consequences, and what techniques let you re-introduce per-user identity?

level: seniorimportance: should knowfreq 38%

answer

  1. pool = one identity, DB cannot see end users
  2. SET LOCAL context var - transaction-scoped, asserted not proven
  3. SET ROLE from a powerless connector role; RESET on return
  4. missed reset = next request inherits previous tenant
  5. transaction-pooling mode drops session state

basics

~20 s

The database sees only the pooled account, so its audit trail, row-level policies and per-user limits cannot distinguish end users - authorization must be enforced in the application. To restore identity you either set a per-transaction session variable the database reads, or switch identity with SET ROLE after checkout, and you must reset that state before returning the connection to the pool.

solid answer

~60 s

Consequences: the audit log attributes everything to `app_rw`; row-level policies and per-user resource limits have nothing to key on; and the whole authorization decision moves into application code, so a missing tenant predicate is a data leak with no backstop. Techniques, roughly in order of cost: 1. **Session context variables** - at the start of each transaction, set a variable carrying the user or tenant id; policies and audit triggers read it. Cheap, pool-friendly, but only as trustworthy as the code that sets it. 2. **SET ROLE after checkout** - the pool authenticates as a low-privilege connector role that is a member of many per-tenant roles, then switches to the right one per request and resets afterwards. The database now enforces the boundary, at the cost of managing many roles and plan-cache churn. 3. **Per-user connections** - true identity, but it destroys pooling economics; only viable for small internal user populations. The non-negotiable part is reset. Any state set on a pooled connection must be cleared on return, or request N+1 inherits request N's identity.

code

sql · 4 lines
sql
BEGIN;
SET LOCAL app.current_tenant = '42';
SELECT * FROM app.orders;   -- policy compares tenant_id to app.current_tenant
COMMIT;                     -- setting disappears with the transaction

go deeper

for a junior

Recognise that the database sees only the pooled account, so it cannot tell users apart and authorization is enforced by the application.

for a middle

Describe setting a transaction-scoped context variable the database reads, and explain why it must not persist on the pooled connection.

for a senior

Compare context variables, SET ROLE from a connector role, and per-user connections; cover reset discipline on error paths, plan-cache effects and pooler modes.

for a principal

Decide where the tenant boundary is enforced overall - application, database policies, or separate databases per tenant - and justify it against auditing needs, tenant count and operational cost.

## What pooling does to identity A connection pool exists because authenticating and establishing a database session is expensive relative to a query. So the pool opens a handful of sessions as one account and multiplexes thousands of end users over them. From the database's point of view there is exactly one client: `app_rw`. That costs three things: - **Attribution.** Every statement in the audit log and in activity views belongs to the shared account. Answering 'who read this customer record?' requires the application's own logs, correlated by timestamp. - **In-database authorization.** Row-level security policies and per-user grants are evaluated against the session identity. With one shared identity there is nothing to evaluate, so tenant isolation lives entirely in application predicates. - **Per-user resource control.** Connection limits, timeouts and quotas apply to the pooled account, not to individuals. ## Technique 1: session context variables The application sets a variable at the start of each transaction - conceptually `SET LOCAL app.current_user_id = '...'` - and database-side logic reads it. Row-level policies compare it to a column; audit triggers record it. Strengths: no extra roles, no reconnection, works with any pool, and `SET LOCAL` scopes the value to the transaction so it disappears at commit or rollback - which solves the leakage problem almost for free. Weakness: it is *asserted* identity. Anything that can run SQL on that connection can set the variable to another value, so this defends against application bugs (a forgotten WHERE clause is still caught by the policy) but not against an attacker who already controls statement text on that session. Use `SET LOCAL`, never a session-scoped `SET` on a pooled connection. ## Technique 2: SET ROLE Here the pool authenticates as a deliberately powerless *connector* role, which is a member of many per-tenant or per-role identities. After checking out a connection the application issues `SET ROLE tenant_42`; privilege checks and RLS then evaluate against that role. On return it issues `RESET ROLE`. Strengths: the database itself enforces the boundary. Grants and policies attach to real roles, so a missing application predicate is not automatically a cross-tenant read. Costs and traps: - **Role explosion.** One role per tenant does not scale to a million tenants; it fits tens or low hundreds of *classes* of access far better than per-end-user identity. - **Reset discipline.** If `RESET ROLE` is skipped on an error path, the next borrower of that connection runs as the previous tenant. Reset must be part of the pool's connection-return hook, not the application's happy path. - **Prepared statements and plan caching.** Plans and prepared statements are session-scoped; switching roles can invalidate assumptions, and with RLS the plan may legitimately differ per role, reducing reuse. - **`SET ROLE` is reversible.** A session that switched to a lower-privileged role can normally switch back, since the underlying login identity is unchanged. It is a scoping mechanism, not a sandbox against hostile SQL. ## Technique 3: real per-user connections Every end user gets a database login and their own session. Identity, auditing and privileges all work natively. This is normal for internal tools with tens of users, and unworkable for consumer-facing systems: pooling collapses, connection counts explode, and account lifecycle management becomes a second user-management system. A middle path some teams use: pooled connections for normal traffic, a separate per-human path for admin and analyst access, where attribution matters most and volume is lowest. ## The pooler in the middle External poolers add a wrinkle. In transaction-pooling mode a client's connection is reassigned between transactions, so *any* session-level state - `SET`, session variables, prepared statements, temp tables, advisory locks - may not survive or may leak. Under transaction pooling, `SET LOCAL` inside the transaction is safe; session-scoped state is not. Knowing your pooler's mode is a prerequisite for choosing between these techniques. ## Choosing If tenant isolation is the goal and you have a manageable number of access classes, `SET ROLE` plus policies gives real enforcement. If you mainly need attribution and defence against your own missing predicates, transaction-scoped context variables are far cheaper and usually sufficient. In both cases the pooled login should remain minimally privileged: whichever identity trick you use, the connector account itself should not be able to read everything on its own.

  • What breaks if RESET ROLE is only called on the success path?
    A request that throws after SET ROLE returns the connection still impersonating that tenant. The next borrower runs under the wrong identity, so it may read or write another tenant's rows while the application believes it is correctly scoped. The reset must live in the pool's connection-return or transaction-cleanup hook so it executes regardless of outcome.
  • Is a session variable set by the application a trustworthy identity for a row-level policy?
    It is trustworthy against application mistakes but not against an attacker who can execute arbitrary SQL on that session, since the same session can overwrite the variable. It is a strong second layer that catches forgotten predicates; where the threat model includes hostile statements on the connection, role switching gives the engine a value it derives itself rather than one the client asserts.

saying these in an interview costs you the question

  • Setting the user id with a session-scoped SET on a pooled connection instead of SET LOCAL, so it survives into the next request.
  • Believing SET ROLE is a one-way sandbox - the session can usually switch back, since the login identity never changed.
  • Proposing one database role per end user for a consumer-scale application.
  • Assuming session state such as prepared statements or temp tables survives when an external pooler runs in transaction-pooling mode.

context

resolving links…