PostgreSQL Server Logs Show Failed Connection Attempts from Agents Using Non-SSL Modes (4380908)

PostgreSQL Server logs show repeated failed connection attempts from agents before successful SSL connections. These failures appear as: 

LOG:  connection received: host=<IPv6-Address-1> port=33408
LOG:  connection authenticated: identity="<UserA>" method=md5 (/datadrive/pg/data/pg_hba.conf:34)
LOG:  connection authorized: user=<UserA> database=<DatabaseA> SSL enabled (protocol=TLSv1.3, cipher=TLS_AES_256_GCM_SHA384, bits=256)
LOG:  connection received: host=<IPv6-Address-2> port=64136
FATAL:  no pg_hba.conf entry for host "<IPv6-Address-2>", user "<UserB>", database "postgres", no encryption
LOG:  connection received: host=<IPv6-Address-2> port=64137
LOG:  connection authenticated: identity="<UserB>" method=md5 (/datadrive/pg/data/pg_hba.conf:34)
LOG:  connection authorized: user=<UserB> database=postgres SSL enabled (protocol=TLSv1.3, cipher=TLS_AES_256_GCM_SHA384, bits=256)
LOG:  disconnection: session time: 0:00:00.020 user=<UserB> database=postgres host=<IPv6-Address-2> port=64137

• The first connection from <UserA> is successful using SSL.
• The <UserB> agent first attempts a non-SSL connection, which fails due to no matching pg_hba.conf rule.
• It then retries with SSL and connects successfully.

Agents using sslMode=Allow or sslMode=Prefer will attempt a non-SSL connection before falling back to SSL. PostgreSQL rejects the initial unencrypted attempt, which gets logged as a failure.

Example agent settings causing this:

• SSL Mode: Allow
• SSL Mode: Prefer

These modes do not enforce SSL-only connections, resulting in unnecessary log noise. 

To prevent failed connection attempts from appearing in the logs:

Update agent configuration: Set: SSL Mode to either Require, Verify-CA, or Verify-Full. 

All three options force the agent to connect securely without triggering failed connection log entries, keeping PostgreSQL logs clean and focused.

Verify-CA and Verify-Full also validate the server certificate, adding an extra layer of security.

Note: If the server requires SSL, then the agent must be able to establish a secure connection, which means:

• • Either the Java Truststore (sslFactory=1) must contain the server’s root cert.
  • Or the agent must be given:
    • sslRootCert
    • sslCert
    • sslKey (and optionally sslKeyPassword)

Some form of trust must be present for SSL to succeed if the server enforces it.

• Verify-CA and Verify-Full are stricter modes that require the agent to validate the server's certificate.
• These modes need either:
  • A valid sslRootCert path, or
  • Use Java Truststore enabled (sslFactory=1)

Note: No changes are needed in pg_hba.conf unless you want to explicitly reject non-SSL attempts from specific IPs.