PMM client is not able to connect: kubectl -n pgo -c pmm-client logs cluster2-6b4984cc99-brghd INFO[2023-02-21T17:14:50.853+00:00] time="2023-02-21T17:14:50Z" level=error msg="Error opening connection to database (postgres://postgres:PASSWORD_REMOVED@cluster2-6b4984cc99-brghd:5432/postgres?connect_timeout=1&sslmode=disable): \"pq: no pg_hba.conf entry for host \\\"10.42.2.10\\\", user \\\"postgres\\\", database \\\"postgres\\\", no encryption\": too many connection retries" source="postgres_exporter.go:1661" agentID=/agent_id/8a255b2a-cf9f-4b7d-8c72-2fcce88052a3 component=agent-process type=postgres_exporter
Solution: a) create localhost record allowing insecure connections and connect to 127.0.0.1 instead of hostname:
pg_hba:
- local all postgres peer
- host all all 127.0.0.1/32 md5
pmm-admin add postgresql --tls-skip-verify --skip-connection-check --metrics-mode=push --username=postgres --password=$DB_PASS --service-name=$PMM_AGENT_SETUP_NODE_NAME --host=127.0.0.1 --port=5432 --query-source=pgstatmonitor
b) handle ssl connection properly and use certificates for the pmm connection
Environment
None
AFFECTED CS IDs
CS0032841
Activity
Show:
Slava Sarzhan March 22, 2023 at 10:46 AM
The issue was fixed. This fix will be available in the next PGv1 release.
Jobin Augustine March 14, 2023 at 2:54 AM
Hi Team,
Customer is awaiting a fix.
Regards,
Jobin.
Nickolay Ihalainen February 21, 2023 at 7:48 PM
2.0.0 is also affected due to:
cat pg_hba.conf
# Do not edit this file manually!
# It will be overwritten by Patroni!
local all "postgres" trust
hostssl replication "_crunchyrepl" all cert
hostssl "postgres" "_crunchyrepl" all cert
host all "_crunchyrepl" all reject
hostssl all "_crunchypgbouncer" all scram-sha-256
host all "_crunchypgbouncer" all reject
hostssl all all all md5
Steps to reproduce:
Create a cluster with SSL support
+ sslCA: cluster2-ssl-ca
+ sslSecretName: cluster2-ssl-keypair
+ sslReplicationSecretName: cluster2-ssl-keypair
upgradeOptions:
versionServiceEndpoint: https://check.percona.com
apply: disabled
@@ -17,109 +17,109 @@ spec:
port: "5432"
user: pguser
disableAutofail: false
tlsOnly: false
+ tlsOnly: true
https://docs.percona.com/percona-operator-for-postgresql/TLS.html#installation-of-the-cert-manager
PMM client is not able to connect:
kubectl -n pgo -c pmm-client logs cluster2-6b4984cc99-brghd
INFO[2023-02-21T17:14:50.853+00:00] time="2023-02-21T17:14:50Z" level=error msg="Error opening connection to database (postgres://postgres:PASSWORD_REMOVED@cluster2-6b4984cc99-brghd:5432/postgres?connect_timeout=1&sslmode=disable): \"pq: no pg_hba.conf entry for host \\\"10.42.2.10\\\", user \\\"postgres\\\", database \\\"postgres\\\", no encryption\": too many connection retries" source="postgres_exporter.go:1661" agentID=/agent_id/8a255b2a-cf9f-4b7d-8c72-2fcce88052a3 component=agent-process type=postgres_exporter
Solution:
a) create localhost record allowing insecure connections and connect to 127.0.0.1 instead of hostname:
pg_hba: - local all postgres peer - host all all 127.0.0.1/32 md5 pmm-admin add postgresql --tls-skip-verify --skip-connection-check --metrics-mode=push --username=postgres --password=$DB_PASS --service-name=$PMM_AGENT_SETUP_NODE_NAME --host=127.0.0.1 --port=5432 --query-source=pgstatmonitorb) handle ssl connection properly and use certificates for the pmm connection