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:

In the Agent Status Properties for the PostgreSQL and PostgreSQL PI agents: 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.

Troubleshooting steps

1). Ensure the PostgreSQL agent is configured with: SSL Mode = Require, Verify-CA, or Verify-Full

2). Ensure the PostgreSQL PI agent is configured with: SSL Mode = Require, Verify-CA, or Verify-Full

3). Stop the agents.

4). Wait 2–5 minutes, then check the PostgreSQL database log for any new connections from the FglAM. If the agent is truly stopped, these should no longer appear.

5). Restart the agents 

6). Wait a few minutes for the agents to reconnect and begin collecting data.

7). Check the agent log files to confirm it connects successfully.

8). Reopen the PostgreSQL log file and collect a fresh copy.

9). Review the log to confirm that the FATAL: no pg_hba.conf entry ... no encryption messages are no longer appearing.