PostgreSQL Idle Transaction Session Timeout

The Incident (What Does the Error Mean?)

Raw error output from PostgreSQL logs:

LOG:  could not receive data from client: Connection reset by peer
LOG:  unexpected EOF on client connection with an open transaction
FATAL:  terminating connection due to idle-in-transaction timeout

PostgreSQL enforces idle_in_transaction_session_timeout (introduced in PG 9.6). When a session opens a transaction (BEGIN) and then goes silent — no queries, no commit, no rollback — for longer than the configured interval, the server forcibly terminates the backend process. The OS resets the TCP socket. The client library sees Connection reset by peer.

Immediate consequences:

• The open transaction is rolled back entirely. Any uncommitted writes are lost.
• The application connection is dead. If your pool doesn't detect this, the next query on that connection throws a broken pipe or similar error.
• Under high concurrency, this cascades: multiple connections die simultaneously, the pool exhausts, and the application starts refusing requests.

The Attack Vector / Blast Radius

This is a cascading availability failure, not a security exploit — but the blast radius is severe in production:

1. Connection pool poisoning. PgBouncer, HikariCP, and SQLAlchemy pools cache connections. A server-side reset is not always detected immediately. The pool hands out a dead connection to the next thread. That thread throws an exception. If retry logic is absent or naive, the request fails silently.

2. Lock amplification. The killed session was likely holding row-level or table-level locks. While PG releases locks on transaction termination, any dependent queries were blocked for the full timeout duration before that. Under load, this queues up hundreds of waiting backends.

3. Data integrity risk. If the application assumes a transaction committed (e.g., it already returned HTTP 200 to the user), but the timeout killed it mid-flight, you have a phantom write — the user believes the operation succeeded, the DB has no record of it.

4. Root cause is almost always application-layer: a background job that opens a transaction, calls an external HTTP API mid-transaction, and waits. The HTTP call stalls. The transaction sits idle. PG kills it.

Prevention in CI/CD

1. Lint postgresql.conf in Terraform/Ansible:

- # No timeout set — silent misconfiguration
- resource "aws_db_parameter_group" "pg" {
-   parameter { name = "idle_in_transaction_session_timeout" value = "0" }
- }

+ resource "aws_db_parameter_group" "pg" {
+   parameter { name = "idle_in_transaction_session_timeout" value = "30000" apply_method = "immediate" }
+   parameter { name = "idle_session_timeout"                value = "600000" apply_method = "immediate" }
+ }

2. Checkov policy — flag disabled timeouts:

Add a custom Checkov check or use conftest with OPA:

deny[msg] {
  input.resource.aws_db_parameter_group[_].parameter[p]
  p.name == "idle_in_transaction_session_timeout"
  p.value == "0"
  msg := "idle_in_transaction_session_timeout must not be 0 (disabled). Set to 30000ms or lower."
}

3. Integration test gate: Add a test that opens a transaction, sleeps past your configured timeout, and asserts the pool correctly recovers (reconnects) rather than serving a dead connection. Run this in your staging pipeline on every deploy.

4. Observability: Alert on pg_stat_activity where state = 'idle in transaction' and now() - state_change > interval '10 seconds'. This fires before PG kills the session and gives you time to identify the offending query pattern.

SELECT pid, usename, application_name, state, now() - state_change AS idle_duration, query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
  AND now() - state_change > interval '10 seconds'
ORDER BY idle_duration DESC;

Set this as a Datadog, Prometheus, or CloudWatch custom metric. Page on it.