How to Fix PostgreSQL 'SSL Connection Closed Unexpectedly' on Long-Running Queries
Threat/Impact Level: HIGH | Downtime Risk: HIGH | Time to Fix: 15–30 mins
TL;DR
- What broke: PostgreSQL dropped an active SSL session mid-query because a network appliance (ALB, NAT Gateway, firewall) or the server itself killed the idle-looking TCP connection before the query returned results.
- How to fix it: Tune TCP keepalive settings at the OS and PostgreSQL level, increase or disable SSL renegotiation, and configure your load balancer idle timeout to exceed your longest expected query runtime.
- Fast path: Use our Client-Side Sandbox above to auto-refactor your
postgresql.confor connection string — paste your config and get patched output instantly.
▼ 🔍 Root Cause Breakdown & Kernel Diagnostics [Click to Expand Deep Dive]
The Incident (What Does the Error Mean?)
FATAL: SSL connection has been closed unexpectedly
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.This is not a query bug. The query was running fine. The TCP socket underneath the SSL session was silently torn down — by a firewall idle-timeout, an AWS ALB 60-second hard timeout, a NAT Gateway 350-second UDP/TCP timeout, or OpenSSL's built-in SSL renegotiation kicking in on long-lived connections. PostgreSQL sees the socket die and throws FATAL. Your transaction is rolled back. Any work done is lost.
▼ 🔍 Detailed Impact & Blast Radius Analysis [Click to Expand Deep Dive]
The Attack Vector / Blast Radius
This is a silent data integrity risk disguised as a network error.
- Long ETL jobs,
pg_dumpover SSL, bulkINSERT ... SELECT— all silently aborted. If your application doesn't check for this specific error and retry with proper transaction handling, you get partial writes with no alert. - Connection poolers (PgBouncer, RDS Proxy) make this worse. The pooler may hold the dead connection in its pool and hand it to the next client, causing a cascade of
SSL connection closed unexpectedlyerrors across unrelated application threads. - AWS RDS / Aurora specific: The RDS proxy and the underlying ALB both enforce a hard 60-second idle timeout by default. A query that runs for 90 seconds with no intermediate network traffic (no rows streamed back yet) looks "idle" to the load balancer. It tears down the TCP connection. PostgreSQL never knew it was coming.
- On-prem / GCP / Azure: Stateful firewalls with TCP idle timeouts of 30–300 seconds are the usual culprit. The query is CPU-bound on the server, producing no network traffic, so the firewall ages out the flow entry and sends a RST.
How to Fix It
Basic Fix — TCP Keepalive at the OS Level
On the PostgreSQL server host, reduce the TCP keepalive probe interval so the kernel sends keepalive packets before any firewall or load balancer times out the connection.
# /etc/sysctl.conf
- # net.ipv4.tcp_keepalive_time = 7200
- # net.ipv4.tcp_keepalive_intvl = 75
- # net.ipv4.tcp_keepalive_probes = 9
+ net.ipv4.tcp_keepalive_time = 60
+ net.ipv4.tcp_keepalive_intvl = 10
+ net.ipv4.tcp_keepalive_probes = 6Apply without reboot:
sysctl -pThis makes the kernel send a keepalive probe after 60 seconds of silence, every 10 seconds, for 6 attempts — totaling 120 seconds before the kernel declares the connection dead. This keeps the TCP flow alive through most firewalls.
Enterprise Best Practice — PostgreSQL + Application Layer
1. Enable keepalives in postgresql.conf:
# postgresql.conf
- #tcp_keepalives_idle = 0
- #tcp_keepalives_interval = 0
- #tcp_keepalives_count = 0
+ tcp_keepalives_idle = 60
+ tcp_keepalives_interval = 10
+ tcp_keepalives_count = 62. Disable SSL renegotiation (PostgreSQL < 14 only — this was the primary culprit in older versions):
# postgresql.conf (PostgreSQL 9.x–13.x)
- #ssl_renegotiation_limit = 512MB
+ ssl_renegotiation_limit = 0PostgreSQL 14+ removed ssl_renegotiation_limit entirely. If you're still on 12 or 13, set it to 0 to disable forced renegotiation that can interrupt long transfers.
3. Pass keepalive parameters in your connection string (application side):
# Python psycopg2 / libpq connection string
- postgresql://user:pass@host:5432/db
+ postgresql://user:pass@host:5432/db?keepalives=1&keepalives_idle=60&keepalives_interval=10&keepalives_count=6# JDBC (Java)
- jdbc:postgresql://host:5432/db
+ jdbc:postgresql://host:5432/db?tcpKeepAlive=true&socketTimeout=04. AWS RDS / Aurora — fix the load balancer timeout:
If you're behind an Application Load Balancer, the default idle timeout is 60 seconds. For long-running queries this is fatal.
# Terraform — aws_lb resource
resource "aws_lb" "main" {
name = "app-lb"
- idle_timeout = 60
+ idle_timeout = 4000
}For RDS Proxy, set ConnectionBorrowTimeout and ensure your proxy is not using MaxConnectionsPercent so aggressively that connections are recycled mid-query.
💡 Tired of pasting proprietary infrastructure configs into public LLMs? Generic AI interfaces log your company's ARNs, connection strings, and private keys. StackEngine enforces client-side zero-trust boundaries—the sandbox above executes 100% locally in your browser. Drop your broken manifest above to redact secrets locally and synthesize deterministic patch drafts with your own API key.
(For CI/CD pipeline automation, install via
npm i -g stackengine).
▼ 🔍 Continuous Pipeline Governance & CI/CD Guardrails [Click to Expand Deep Dive]
Prevention in CI/CD
1. Checkov — flag missing keepalive on RDS Proxy / ALB Terraform:
Write a custom Checkov check or use terraform-compliance to assert idle_timeout on aws_lb resources is never below your P99 query duration SLA.
# terraform-compliance feature file
Scenario: ALB idle timeout must support long-running queries
Given I have aws_lb defined
Then it must contain idle_timeout
And its value must be greater than 3002. pg_stat_activity alerting — catch long queries before they hit the timeout:
-- Alert if any query has been running > 5 minutes
SELECT pid, now() - query_start AS duration, query, state
FROM pg_stat_activity
WHERE state = 'active'
AND now() - query_start > interval '5 minutes';Pipe this into your Prometheus postgres_exporter custom query config and alert before the SSL timeout window closes.
3. statement_timeout as a safety net — never let runaway queries accumulate:
# postgresql.conf
- #statement_timeout = 0
+ statement_timeout = '30min'Set this at the role level for application users, not globally, to avoid killing legitimate long-running maintenance jobs:
ALTER ROLE app_user SET statement_timeout = '30min';
ALTER ROLE etl_user SET statement_timeout = '4h';4. Integration test — simulate the timeout in your pipeline:
Use tc (traffic control) in your CI environment to simulate a 60-second TCP idle drop and assert your application retries correctly with exponential backoff.
Official Grounding & Normative Specifications
The technical diagnostics, error recovery protocols, and prevention patterns in this specification are grounded in official vendor documentation and standards:
Official runtime configuration documentation governing lock acquisition timeouts, deadlock detection cycles, and wait logging.
Federal security guidelines for secret exfiltration prevention, automated integrity checks, and peer sign-offs.
Core memory subsystem controls, pressure stall information (PSI), and kernel OOM invocation mechanics.
Industry-standard specification for container execution boundaries and POSIX namespace isolation.