When application servers share pooled database connections, how do you tell the database which tenant the current request belongs to so row policies can use it, and what can go wrong with that mechanism?

## The problem A row policy is only as good as the identity it reads. In a two-tier design where each end user has their own database login, the policy can just use the current role. In a modern service, dozens of requests for different tenants share a handful of pooled connections owned by one application role. The database sees one identity; the policy needs the *request's* identity. ## The session-variable pattern The standard answer is a namespaced runtime setting: the application writes `app.tenant_id` into the session, and the policy reads it back with a `current_setting`-style function. The namespace prefix matters — engines only allow custom settings under a dotted namespace, and it keeps the key from colliding with server parameters. The decisive detail is **scope**: - *Session-scoped* values persist until the connection closes or something resets them. On a pooled connection that is exactly the leak: request A sets tenant 7, request B forgets to set anything, and B's queries run as tenant 7. - *Transaction-scoped* values (a `set_config(key, value, true)`-style call, or the `SET LOCAL` form) are reverted at commit **and** rollback. The connection can never carry a stale tenant back into the pool. So the rule is: begin transaction, set the context transaction-scoped, do all the work inside that transaction, commit. Anything running outside a transaction (autocommit health checks, a stray `SELECT` before `BEGIN`) sees no context and, because policies fail closed, sees nothing — a safe failure, and a good reason not to make the missing-context case error out into a wide-open default. ## Where teams get it wrong 1. **Setting once at checkout, resetting on release.** Workable, but the window between checkout and release must be leak-free: an exception path that returns the connection without the reset, or a pool that hands out the same connection to a background job, poisons the next user. Transaction scope removes the whole class. 2. **Connection proxies in transaction-pooling mode.** These multiplex several clients over one backend and may switch backends between statements. Any session-level state, including your tenant setting, becomes unreliable. Either use session pooling, or keep everything inside one transaction so the proxy pins the backend for its duration. 3. **Trusting client input.** The tenant id must come from the server-side authenticated principal — the validated token claim, resolved and authorized — not from a request header or query parameter passed through. RLS enforces the predicate; it does not vouch for the value you fed it. 4. **Injecting it.** Building the context statement by string-concatenating the id invites injection into a security-critical statement. Use a parameterized `set_config(...)` call, never `SET app.tenant_id = '<interpolated>'`. 5. **Letting the application change it.** If the same role that runs ordinary queries can also call `set_config`, then any code (or injection) can switch tenants at will. RLS then protects only against *accidents*, not attackers. Hardening options: set the context through a `SECURITY DEFINER`-style function that validates the tenant against a membership table, put the setting behind a proxy/connection-init the app cannot reach, or use a per-tenant role and `SET ROLE` with `NOSET`-style restrictions. 6. **Forgetting the value's type and null case.** `current_setting('app.tenant_id')` on an unset key errors in some engines and returns empty in others; policies should use the missing-value-tolerant variant so an unset context means "no rows", not "query aborts" or, worse, "predicate evaluates oddly". ## The stronger alternative Where the tenant maps to a real database role, `SET ROLE` (or a dedicated login per tenant) gives the policy an identity the application cannot forge without privilege to assume that role. Costs: role explosion, catalog bloat, and pool fragmentation because a connection is now tenant-specific. Most teams take the setting-based approach and compensate with strict connection-lifecycle discipline and a hardened setter. ## Testing it The test that catches real bugs is the *reuse* test: run request A for tenant 1 on a pool of size one, then run request B for tenant 2 without setting context, and assert B sees zero rows rather than tenant 1's rows. Also test rollback: after a failed transaction, the next borrower must not inherit anything.