PostgreSQL MCP connection pooling: reset session state before reuse

A PostgreSQL MCP tool finishes one tenant's query and returns the connection to the pool.

The next request borrows the same physical session. If the first operation changed a role, search_path, tenant variable, timeout, prepared statement, or temporary object and did not clean it up, the second request may inherit state it never asked for.

Connection pooling improves throughput. It also turns session hygiene into a security boundary.

Know what the pool is reusing

A logical request is not necessarily a new PostgreSQL session. Application pools and proxies can reuse a backend connection across many users and AI tool calls.

Document the pooling mode:

• session pooling: one client keeps one backend session for its lifetime;
• transaction pooling: the backend may change after each transaction;
• statement pooling: even multi-statement transaction assumptions may not hold.

The MCP server must not depend on session semantics that the selected pool mode does not preserve.

Inventory every piece of mutable session state

The obvious risks are not the only ones. Review:

• SET ROLE and authorization changes;
• search_path and object resolution;
• custom GUCs used for tenant or policy context;
• statement_timeout, lock timeout, timezone, and locale;
• prepared statements and cached query plans;
• temporary tables, sequences, and schemas;
• advisory locks and open cursors;
• listen/notify registrations;
• aborted or accidentally open transactions.

A clean-looking result does not prove the session is clean. State can influence a later query without appearing in the first response.

Prefer transaction-local context

Put identity, tenant, role, timeout, and query execution inside one short transaction on one physical connection. Use transaction-local settings where possible so PostgreSQL rolls them back automatically.

BEGIN READ ONLY;
SET LOCAL statement_timeout = '5s';
SELECT set_config('app.tenant_id', :trusted_tenant, true);
SELECT ... FROM approved_reporting_view WHERE ...;
COMMIT;

The tenant value must come from authenticated server context, not a model-provided argument. The true flag makes the setting local to the transaction.

If row-level security reads a custom setting, verify that the transaction, setting, and query stay on the same backend connection. A transaction-pooling proxy can support that pattern only when the client holds the transaction correctly.

For the wider boundary, see scoped database access for AI agents.

Reset on every exit path

The connection must return to a known state after success, validation failure, timeout, cancellation, network error, and application exception.

Always finish or roll back the transaction before releasing the connection. Treat a failed rollback or uncertain session state as a reason to discard that connection rather than return it to the pool.

Some stacks use RESET ALL, DISCARD ALL, proxy-level reset queries, or driver-specific cleanup hooks. They are not interchangeable:

• RESET ALL resets configurable parameters but does not remove every kind of session state;
• DISCARD ALL is broader, cannot run inside a transaction, and may add cost by dropping prepared state;
• a pooler's reset policy may differ by mode and version;
• application cleanup can be skipped when exception paths are incomplete.

Choose a reset contract deliberately and test the exact driver, pooler, and deployment configuration you operate.

Do not rely on search_path for isolation

A request-specific search_path can simplify SQL, but it also changes which object a name resolves to. If it leaks across requests or includes a writable schema, an unqualified function or table reference may resolve somewhere unexpected.

Use fully qualified objects for security-sensitive operations, remove untrusted writable schemas from resolution paths, and keep tenant isolation in roles, row-level policy, trusted views, or explicit predicates—not merely in naming convention.

Bind cancellation to the borrowed connection

When an AI client times out, the PostgreSQL query may continue. Cancellation must target the correct in-flight operation, then the server must confirm that the transaction ended before the connection is reused.

Do not release a connection while query execution is still ambiguous. Mark it unhealthy or close it if the client cannot prove cleanup.

See the PostgreSQL query-cancellation test for a deeper failure checklist.

Concrete example: two tenants, one pool

Request A borrows connection 17, sets app.tenant_id to tenant A, runs an approved summary, and encounters a timeout during result serialization.

Request B later receives connection 17 for tenant B. If A's transaction remained open or its setting was session-scoped, B can see the wrong policy context even though B's prompt and token are valid.

The safe sequence is: begin, apply trusted local context, query, consume or cancel, commit or roll back, verify clean state, and only then release. If any step is uncertain, destroy connection 17.

Test pool reuse adversarially

1. Alternate tenants across a pool smaller than the worker count.
2. Randomize success, SQL error, timeout, client cancellation, and network disconnect.
3. Set a role, tenant GUC, timezone, and search path in one request.
4. Create temporary state and prepared statements where the stack allows them.
5. Verify backend PID reuse and inspect state before the next query.
6. Run under the actual session or transaction pooling mode.
7. Kill a worker between query execution and cleanup.
8. Assert that every subsequent request begins from the documented baseline.

Include cross-tenant aggregates and indirect joins, not only direct record lookups. The test should prove isolation when physical sessions are reused under pressure.

Observe hygiene without logging data

Record pool wait time, backend reference, transaction outcome, reset outcome, cancellation status, effective role, policy version, tenant reference, and whether a connection was discarded. Avoid copying raw query results or secrets into pool diagnostics.

A rising discard rate may reveal a cleanup or cancellation bug before it becomes a data-boundary incident.

Use the MCP server for Postgres production checklist for complementary limits and monitoring.

Where Conexor fits

Conexor provides MCP infrastructure for connecting AI clients to PostgreSQL and other data sources through governed tools. A documented pooling and reset contract helps keep identity, tenant scope, timeouts, and query execution bound to one request even when physical database sessions are reused.

Explore connecting PostgreSQL to Claude