PostgreSQL Client Receive Connection Timeout

The Incident (What does the error mean?)

Raw server log:

2024-03-15 03:17:42 UTC [28901]: FATAL:  could not receive data from client: Connection timed out
2024-03-15 03:17:42 UTC [28901]: DETAIL:  Client connection lost while waiting for data.

PostgreSQL's backend process called recv() on the client socket. The kernel waited, received no ACK or data, exhausted its retransmit window, and returned ETIMEDOUT. PostgreSQL treats this as unrecoverable: the backend exits immediately, the transaction is rolled back, any advisory locks are released, and the application gets a broken pipe on its next write. Under connection pooling (PgBouncer, RDS Proxy) this also poisons the pooled socket, triggering a pool drain cycle.

The Attack Vector / Blast Radius

This is a silent infrastructure mismatch, not a code bug, which makes it far more dangerous in production:

1. NAT/Load Balancer idle timeout is shorter than your longest query. AWS NLB default: 350 s. AWS ALB: 60 s. GCP Cloud SQL Proxy: 600 s. If a VACUUM ANALYZE, a bulk INSERT, or a reporting query runs longer, the LB silently drops the flow. PostgreSQL's backend has no idea until it tries to send the result — by then the client is gone.
2. Cascading pool exhaustion. PgBouncer holds the server-side connection open. The broken client socket is not detected until the next recv(). Meanwhile the pool slot is occupied. Under load, all pool slots fill with zombie connections → new queries queue → application latency spikes → timeouts cascade.
3. Data integrity risk. Any transaction that was mid-flight (multi-statement, COPY, two-phase commit) is rolled back. If the application lacks proper retry logic with idempotency keys, it may re-submit partial writes → duplicate rows or constraint violations.
4. Invisible in APM. The error surfaces in PostgreSQL logs, not in application error rates, because the TCP drop happens below the application layer. Teams spend hours blaming slow queries before checking pg_log.

Prevention in CI/CD

1. Checkov — Flag Missing Keepalive in RDS Parameter Groups

Write a custom Checkov check (CKV_CUSTOM_PG_KEEPALIVE) that asserts tcp_keepalives_idle is set and non-zero in any aws_db_parameter_group Terraform resource.

 resource "aws_db_parameter_group" "postgres" {
   family = "postgres15"
+  parameter {
+    name  = "tcp_keepalives_idle"
+    value = "60"
+  }
+  parameter {
+    name  = "tcp_keepalives_interval"
+    value = "10"
+  }
+  parameter {
+    name  = "tcp_keepalives_count"
+    value = "6"
+  }
 }

2. OPA / Conftest Policy — Enforce LB Idle Timeout

# policy/lb_timeout.rego
package terraform.aws.lb

deny[msg] {
  res := input.resource.aws_lb_target_group[name]
  not res.config.deregistration_delay
  msg := sprintf("LB target group '%v' missing explicit deregistration_delay — verify idle timeout alignment with PostgreSQL keepalive", [name])
}

3. GitHub Actions — Lint postgresql.conf in PRs

- name: Validate postgresql.conf keepalive settings
  run: |
    grep -E '^tcp_keepalives_idle\s*=\s*[1-9]' postgresql.conf || \
      (echo '::error::tcp_keepalives_idle not set' && exit 1)
    grep -E '^tcp_keepalives_interval\s*=\s*[1-9]' postgresql.conf || \
      (echo '::error::tcp_keepalives_interval not set' && exit 1)

4. Alerting — Detect the Error Before Users Do

# Prometheus log scrape rule (promtail / Loki)
- match:
    selector: '{job="postgresql"}'
    stages:
      - regex:
          expression: 'FATAL.*could not receive data from client.*Connection timed out'
      - metrics:
          pg_client_timeout_total:
            type: Counter
            description: PostgreSQL client receive timeout events

Alert on pg_client_timeout_total > 5 in a 5-minute window → page on-call before pool exhaustion cascades.