PG_AUTO_FAILOVER: all nodes down after enable SSL due to ceritificate issue

Products

• VMware Tanzu Greenplum
• VMware vFabric Postgres 9.x Standard Edition
• VMware Tanzu for Postgres

Issue/Introduction

In pg_auto_failover, the user can enhance the network security by enabling the SSL (via command: # pg_autoctl enable ssl xxxx), refer to Encryption of network communications
In some cases, After enabling the SSL, the user might notice all data nodes been marked as unhealthy and in down state. 

In this article, we will discuss one of the reasons that may cause such an issue - caused by invalid permission of client certificate/key on monitor. 

Environment

Product Version: 14.5

Resolution

Troubleshooting steps:

When the data node has been marked as unhealthy, please check the Postgres logs of the data node, below is an example:

From the logs, we can see:

2024-04-04 14:37:37.332 +08LOG:   started streaming WAL from primary at B/3F000000 on timeline 1
2024-04-04 14:37:38.909 +08LOG:   could not accept SSL connection: EOF detected
2024-04-04 14:37:38.917 +08FATAL:  no pg_hba.conf entry for host "[redacted]5", user "pgautofailover_monitor", database "postgres", no encryption
2024-04-04 14:37:38.917 +08DETAIL:  Client IP address resolved to <IP address>, forward lookup not checked.

 

1. The monitor is trying to connect to the data node with SSL, but failed: "could not accept SSL connection: EOF detected" 
2. Then the monitor try to fall back to non-SSL connection, but since the customer requires high-level security so non-SSL is not allowed in pg_hba.conf, so the connection failed
3. As a result, the monitor can not get info from the data node, so marked the data node as unhealthy

To check why the SSL connection failed, we can use psql client to connect to the Data Node from the Monitor Node. 
- Noted that the client certificate and key by default is under ~/.postgresql/
- Run the below command from the Monitor Node:

psql -h <DataNode> -U pgautofailover_monitor "dbname=postgres sslmode=verify-full sslcert=<CLIENT CERT FILE> sslkey=<CLIENT KEY FILE> sslrootcert=<ROOT CERT>"

In this example, we get the below error:

[postgres@[redacted].postgresql]$ psql -h [redacted] -U pgautofailover_monitor -p 55432 "dbname=postgres sslmode=verify-full sslcert=/var/lib/pgsql/.postgresql/postgresql.crt sslkey=/var/lib/pgsql/.postgresql/postgresql.key sslrootcert=/u01/postgres/monitor/SSL/ca-cert.pem"

psql: error: connection to server at "[redacted]" ([redacted]), port 55432 failed: private key file "/var/lib/pgsql/.postgresql/postgresql.key" has group or world access; file must have permissions u=rw (0600) or less if owned by the current user, or permissions u=rw,g=r (0640) or less if owned by root

- So it is clear that the reason why the monitor can not connect to a data node is due to invalid permission of the client's certificate files under  ~/.postgresql/, once we fix the permission issue, the cluster is back to normal