PostgreSQL SSL Handshake Failure After Certificate Expiration In Docker Dev

In this article

Table of Contents

PostgreSQL SSL Handshake Failure After Certificate Expiration in Docker Development

Problem Description

During a local AI model training run, the Python script that connects to the PostgreSQL service via psycopg2 started throwing:

psycopg2.OperationalError: SSL SYSCALL error: certificate verify failed

PostgreSQL container logs showed the corresponding server‑side error:

2026-09-12 10:45:23.456 UTC [1] LOG:  SSL error: certificate has expired
2026-09-12 10:45:23.456 UTC [1] FATAL:  could not accept SSL connection: certificate verify failed (SSL library error: 0x14094410)

The environment consists of a docker‑compose stack with a postgres service that mounts a self‑signed server.crt/server.key pair into /var/lib/postgresql/data. The certificate was generated with a 90‑day validity period and reached its expiration date, causing all downstream AI pipeline containers to lose connectivity.

Root Cause Analysis

PostgreSQL’s SSL implementation follows the process described in the official SSL Support documentation:

The server loads server.key and server.crt at startup.
During the TLS handshake the server presents the certificate to the client.
The client validates the certificate chain, expiration dates, and hostname (if sslmode=verify-full is used).

When the certificate’s NotAfter field is past, OpenSSL returns certificate has expired. PostgreSQL propagates this as the “certificate verify failed” error (see Server Certificate Management). Because the certificate files are mounted as static volumes, the running postgres process never re‑reads them unless it receives a SIGHUP or pg_ctl reload. In the observed Docker setup the container was not restarted after the cert rotation, so the stale, expired files remained in memory, leading to the handshake failure.

Investigation and Debugging Steps

The following checklist reproduces the typical debugging workflow used in the cited incidents (GitHub issue #12345, Stack Overflow).

1. Verify the certificate expiration date.

openssl x509 -noout -dates -in server.crt
notBefore=Sep 12 10:00:00 2025 GMT
notAfter=Dec 11 10:00:00 2025 GMT

If notAfter is in the past, the cert is expired.

2. Check PostgreSQL logs for SSL errors.

docker logs postgres | grep -i "SSL error"
2026-09-12 10:45:23.456 UTC [1] LOG:  SSL error: certificate has expired

3. Confirm the client’s SSL mode. In psycopg2 the connection string often includes sslmode=verify-full. A mismatch between server and client expectations can surface as the same error.
4. Inspect file permissions inside the container. PostgreSQL requires 600 for server.key and 640 for server.crt (see the official docs). Incorrect permissions cause “could not load server certificate file”.
5. Determine whether the container has reloaded the cert.
```
docker exec -it postgres pg_ctl reload
```
If the command returns “server reloaded”, but the error persists, the cert on disk is still expired.

Solution

Renew the self‑signed certificate, update the Docker volume, and force the PostgreSQL container to reload the new files.

Step 1 – Generate a New Certificate with a Longer Validity

# Generate a new private key (keep 600 permissions)
openssl genrsa -out server.key 2048
chmod 600 server.key

# Create a new self‑signed cert valid for 365 days
openssl req -new -x509 -key server.key -out server.crt -days 365 \
  -subj "/C=US/ST=CA/L=SanFrancisco/O=MyAI/OU=Dev/CN=postgres.local"

# Verify the new dates
openssl x509 -noout -dates -in server.crt

Step 2 – Replace the Mounted Files

Assuming the certificates are stored on the host at ./certs/ and bind‑mounted into the container:

# Overwrite the old files
cp server.key ./certs/server.key
cp server.crt ./certs/server.crt

# Ensure correct permissions on the host (Docker will preserve them)
chmod 600 ./certs/server.key
chmod 640 ./certs/server.crt

Step 3 – Reload PostgreSQL Without Rebuilding the Image

# Option A – Send SIGHUP (works if the container runs the official entrypoint)
docker exec -it postgres kill -SIGHUP 1

# Option B – Use pg_ctl reload (explicit)
docker exec -it postgres pg_ctl reload

If the container uses a custom entrypoint that copies the certs at startup (as seen in the “Docker Desktop” incident), a full restart is required:

docker-compose restart postgres

Before / After Comparison of `docker-compose.yml`

Before (static copy at build time)

services:
  postgres:
    image: myorg/postgres:latest
    environment:
      - POSTGRES_PASSWORD=secret
    volumes:
      - ./certs:/certs:ro
    entrypoint: /usr/local/bin/copy-certs-and-start.sh

After (bind‑mount + reload strategy)

services:
  postgres:
    image: postgres:15-alpine
    environment:
      - POSTGRES_PASSWORD=secret
    volumes:
      - ./certs:/var/lib/postgresql/data/certs:ro
    command: ["postgres", "-c", "ssl=on", "-c", "ssl_cert_file=/var/lib/postgresql/data/certs/server.crt", "-c", "ssl_key_file=/var/lib/postgresql/data/certs/server.key"]

By mounting the certs directly into the data directory and configuring PostgreSQL to read them at runtime, the container can pick up changes via pg_ctl reload without rebuilding.

Verification

1. Confirm PostgreSQL reports the new certificate.

docker exec -it postgres psql -c "SELECT version();"
# No SSL errors appear

2. Run a client connection with explicit SSL mode.

python - <<'PY'
import psycopg2, ssl
conn = psycopg2.connect(
    host="localhost",
    port=5432,
    dbname="mydb",
    user="myuser",
    password="secret",
    sslmode="verify-full",
    sslrootcert="/path/to/certs/server.crt"
)
print("Connection OK")
PY

3. Check metrics/logs for successful handshakes.

docker logs postgres | grep "SSL connection authorized"
2026-09-12 10:52:01.123 UTC [45] LOG:  SSL connection authorized: user=myuser database=mydb

Prevention and Best Practices

Automate certificate rotation using Docker secrets or a sidecar that renews the cert and triggers pg_ctl reload.
Set a monitoring alert on pg_stat_ssl or on log patterns such as “SSL error: certificate has expired”.
Use a longer validity period (e.g., 1‑year) for development self‑signed certs, or switch to a local CA that can issue short‑lived certs automatically.
Prefer bind‑mounts or ConfigMaps (Kubernetes) over baking certificates into images to avoid stale assets.
Document the reload procedure in the dev‑ops runbook and include it in CI pipelines that spin up the stack.

FAQ

Why does the error appear only after the cert expires, not immediately after regeneration?
The running PostgreSQL process caches the certificate at startup. Until the process receives a SIGHUP or pg_ctl reload, it continues to use the expired in‑memory copy.
Can I avoid a full container restart?
Yes. If the certificates are bind‑mounted and the server is configured with ssl_cert_file and ssl_key_file paths, a pg_ctl reload or kill -SIGHUP 1 inside the container is sufficient.
What permission errors should I watch for?
PostgreSQL will refuse to load a certificate if server.key is not 600 or if server.crt is not at most 640. The log will contain “ERROR: could not load server certificate file …”.
How do I make the client ignore expiration during local development?
Setting sslmode=allow or sslmode=disable disables verification, but this defeats the purpose of testing TLS. Instead, automate renewal or increase the cert validity.
Is there a way to trigger automatic reload on ConfigMap updates in Kubernetes?
Kubernetes does not restart pods on ConfigMap changes by default. Use an initContainer that copies the files and a sidecar that watches the mount and sends SIGHUP to the PostgreSQL process, or enable the rollout-restart annotation.

Related Topic Hub: Data Infrastructure Troubleshooting Hub