Can't monitor GCP Cloud SQL or other PostgreSQL with custom SSL certificates

General

Escalation

General

Escalation

Description

User Impact:
PG server is not able to monitor with custom SSL certificates

Test Case:

Example: sslmode=verify-ca

Example: sslmode=verify-full

Original report:
-------------
Hi,

After implementation of , I tried adding one of our GCP postgres cloud SQL instance and it did not work.

Few info:

1- We do not use hostname, all we have is IP address, when I add I get this error:

Connection check failed: x509: cannot validate certificate for 10.49.208.198 because it doesn't contain any IP SANs.

2- Then I checked "skip TLS certificate and hostname validation" : This let me add the instance but I don't see any metrics being captured. Not sure how to debug this?

How to test

If you want to generate your own certs (optional)

Install certstrap from https://github.com/square/certstrap

Build certstrap to generate certs

Make CA file

Make Server certificate

Sign server cert with ca

(end certs generation)

Set up Postgres with SSL.

Get you localhost IP address in the lan

Start docker pmm and mount the certificates file inside the server container.

Add the node using the IP address you got before

How to test

None

How to document

None

Linked work items

blocks

PMM-8874

Exporters 2.23.0

relates to

PMM-7888

Custom TLS certificates now allow SSL connections to PostgreSQL instances

PMM-8999

tls connection/options not working for mysql with custom certificates and TLS verification method

Smart Checklist

Activity

Show:

Jyoti Prakash November 1, 2021 at 8:16 PM

I see that connection is working with SSL, however no PG metrics or very limited metrics are being captured.

Also, I am adding the instance using UI (remote add)

Lalit Choudhary October 25, 2021 at 1:58 PM

Can you try without ssl connection and check if PG monitoring works fine, showing PG data on the dashboard?

The above test is to verify if the issue is only when connecting using SSL. Also when you add PG instance using pmm-admin why do you get as output? please share pmm-admin command and its output.

Jyoti Prakash October 25, 2021 at 1:40 PM

, ,

[31mERRO[0m[2021-10-25T04:13:00.312+00:00] pq: relation "pg_stat_statements" does not exist [31magentID[0m=/agent_id/0d47e2c6-1039-4ea1-ac67-75383e3e0b65 [31mcomponent[0m=agent-builtin [31mtype[0m=qan_postgresql_pgstatements_agent
[36mINFO[0m[2021-10-25T04:13:00.312+00:00] Sending status: WAITING. [36magentID[0m=/agent_id/0d47e2c6-1039-4ea1-ac67-75383e3e0b65 [36mcomponent[0m=agent-builtin [36mtype[0m=qan_postgresql_pgstatements_agent
[36mINFO[0m[2021-10-25T04:13:10.868+00:00] time="2021-10-25T04:13:10Z" level=error msg="Error querying databases (postgres://Grafana_user:PASSWORD_REMOVED@10.49.208.198:5432/postgres?connect_timeout=1&sslcert=/tmp/postgres_exporter/agent_id/0a71e6fe-6dd7-4a65-ac76-7f3dd28ea47b/certificateFilePlaceholder&sslkey=/tmp/postgres_exporter/agent_id/0a71e6fe-6dd7-4a65-ac76-7f3dd28ea47b/certificateKeyFilePlaceholder&sslmode=verify-ca&sslrootcert=/tmp/postgres_exporter/agent_id/0a71e6fe-6dd7-4a65-ac76-7f3dd28ea47b/caFilePlaceholder): error retrieving databases: read tcp 172.17.0.2:45668->10.49.208.198:5432: i/o timeout" source="postgres_exporter.go:1651" [36magentID[0m=/agent_id/0a71e6fe-6dd7-4a65-ac76-7f3dd28ea47b [36mcomponent[0m=agent-process [36mtype[0m=postgres_exporter

Jyoti Prakash October 25, 2021 at 4:18 AM

- can you please check?

Jyoti Prakash October 25, 2021 at 4:16 AM

It does not work. I just tested with 2.23.DO not see the metrics

[36mINFO[0m[2021-10-25T04:12:17.204+00:00] Sending status: STARTING (port 42008). [36magentID[0m=/agent_id/0a71e6fe-6dd7-4a65-ac76-7f3dd28ea47b [36mcomponent[0m=agent-process [36mtype[0m=postgres_exporter
[36mINFO[0m[2021-10-25T04:12:17.204+00:00] Sending status: STARTING. [36magentID[0m=/agent_id/0d47e2c6-1039-4ea1-ac67-75383e3e0b65 [36mcomponent[0m=agent-builtin [36mtype[0m=qan_postgresql_pgstatements_agent
[36mINFO[0m[2021-10-25T04:12:17.215+00:00] time="2021-10-25T04:12:17Z" level=info msg="Starting postgres_exporter (version=, branch=, revision=)" source="postgres_exporter.go:1759" [36magentID[0m=/agent_id/0a71e6fe-6dd7-4a65-ac76-7f3dd28ea47b [36mcomponent[0m=agent-process [36mtype[0m=postgres_exporter
[36mINFO[0m[2021-10-25T04:12:17.215+00:00] time="2021-10-25T04:12:17Z" level=info msg="Build context (go=go1.16.4, user=, date=)" source="postgres_exporter.go:1760" [36magentID[0m=/agent_id/0a71e6fe-6dd7-4a65-ac76-7f3dd28ea47b [36mcomponent[0m=agent-process [36mtype[0m=postgres_exporter
[36mINFO[0m[2021-10-25T04:12:17.215+00:00] time="2021-10-25T04:12:17Z" level=info msg="Established new database connection to \"10.49.208.198:5432\"." source="postgres_exporter.go:913" [36magentID[0m=/agent_id/0a71e6fe-6dd7-4a65-ac76-7f3dd28ea47b [36mcomponent[0m=agent-process [36mtype[0m=postgres_exporter
[31mERRO[0m[2021-10-25T04:12:18.046+00:00] pq: relation "pg_stat_statements" does not exist [31magentID[0m=/agent_id/0d47e2c6-1039-4ea1-ac67-75383e3e0b65 [31mcomponent[0m=agent-builtin [31mtype[0m=qan_postgresql_pgstatements_agent
[36mINFO[0m[2021-10-25T04:12:18.046+00:00] Sending status: WAITING. [36magentID[0m=/agent_id/0d47e2c6-1039-4ea1-ac67-75383e3e0b65 [36mcomponent[0m=agent-builtin [36mtype[0m=qan_postgresql_pgstatements_agent
[36mINFO[0m[2021-10-25T04:12:18.205+00:00] Sending status: RUNNING (port 42008). [36magentID[0m=/agent_id/0a71e6fe-6dd7-4a65-ac76-7f3dd28ea47b [36mcomponent[0m=agent-process [36mtype[0m=postgres_exporter
[36mINFO[0m[2021-10-25T04:12:19.616+00:00] time="2021-10-25T04:12:19Z" level=info msg="Semantic Version Changed on \"10.49.208.198:5432\": 0.0.0 -> 13.3.0" source="postgres_exporter.go:1497" [36magentID[0m=/agent_id/0a71e6fe-6dd7-4a65-ac76-7f3dd28ea47b [36mcomponent[0m=agent-process [36mtype[0m=postgres_exporter
[36mINFO[0m[2021-10-25T04:12:25.273+00:00] time="2021-10-25T04:12:25Z" level=info msg="Established new database connection to \"10.49.208.198:5432\"." source="postgres_exporter.go:913" [36magentID[0m=/agent_id/0a71e6fe-6dd7-4a65-ac76-7f3dd28ea47b [36mcomponent[0m=agent-process [36mtype[0m=postgres_exporter
[36mINFO[0m[2021-10-25T04:12:26.459+00:00] time="2021-10-25T04:12:26Z" level=info msg="Semantic Version Changed on \"10.49.208.198:5432\": 0.0.0 -> 13.3.0" source="postgres_exporter.go:1497" [36magentID[0m=/agent_id/0a71e6fe-6dd7-4a65-ac76-7f3dd28ea47b [36mcomponent[0m=agent-process [36mtype[0m=postgres_exporter
[36mINFO[0m[2021-10-25T04:12:35.675+00:00] time="2021-10-25T04:12:35Z" level=info msg="HTTP Basic authentication is enabled." source="basic_auth.go:91" [36magentID[0m=/agent_id/0a71e6fe-6dd7-4a65-ac76-7f3dd28ea47b [36mcomponent[0m=agent-process [36mtype[0m=postgres_exporter
[36mINFO[0m[2021-10-25T04:12:35.675+00:00] time="2021-10-25T04:12:35Z" level=info msg="Starting HTTP server for http://:42008/metrics ..." source="server.go:140" [36magentID[0m=/agent_id/0a71e6fe-6dd7-4a65-ac76-7f3dd28ea47b [36mcomponent[0m=agent-process [36mtype[0m=postgres_exporter
[36mINFO[0m[2021-10-25T04:12:49.641+00:00] time="2021-10-25T04:12:49Z" level=info msg="Established new database connection to \"10.49.208.198:5432\"." source="postgres_exporter.go:913" [36magentID[0m=/agent_id/0a71e6fe-6dd7-4a65-ac76-7f3dd28ea47b [36mcomponent[0m=agent-process [36mtype[0m=postgres_exporter
[36mINFO[0m[2021-10-25T04:12:50.826+00:00] time="2021-10-25T04:12:50Z" level=info msg="Semantic Version Changed on \"10.49.208.198:5432\": 0.0.0 -> 13.3.0" source="postgres_exporter.go:1497" [36magentID[0m=/agent_id/0a71e6fe-6dd7-4a65-ac76-7f3dd28ea47b [36mcomponent[0m=agent-process [36mtype[0m=postgres_exporter
[36mINFO[0m[2021-10-25T04:12:59.965+00:00] Sending 35 buckets. [36magentID[0m=/agent_id/56c5a7dd-81b0-42dd-86da-a58c3c2ee6b6 [36mcomponent[0m=agent-builtin [36mtype[0m=qan_postgresql_pgstatements_agent
[36mINFO[0m[2021-10-25T04:12:59.979+00:00] Sending status: STARTING. [36magentID[0m=/agent_id/0d47e2c6-1039-4ea1-ac67-75383e3e0b65 [36mcomponent[0m=agent-builtin [36mtype[0m=qan_postgresql_pgstatements_agent
[36mINFO[0m[2021-10-25T04:13:00.002+00:00] Sending 11 buckets. [36magentID[0m=/agent_id/56c5a7dd-81b0-42dd-86da-a58c3c2ee6b6 [36mcomponent[0m=agent-builtin [36mtype[0m=qan_postgresql_pgstatements_agent
[31mERRO[0m[2021-10-25T04:13:00.312+00:00] pq: relation "pg_stat_statements" does not exist [31magentID[0m=/agent_id/0d47e2c6-1039-4ea1-ac67-75383e3e0b65 [31mcomponent[0m=agent-builtin [31mtype[0m=qan_postgresql_pgstatements_agent
[36mINFO[0m[2021-10-25T04:13:00.312+00:00] Sending status: WAITING. [36magentID[0m=/agent_id/0d47e2c6-1039-4ea1-ac67-75383e3e0b65 [36mcomponent[0m=agent-builtin [36mtype[0m=qan_postgresql_pgstatements_agent
[36mINFO[0m[2021-10-25T04:13:10.868+00:00] time="2021-10-25T04:13:10Z" level=error msg="Error querying databases (postgres://Grafana_user:PASSWORD_REMOVED@10.49.208.198:5432/postgres?connect_timeout=1&sslcert=/tmp/postgres_exporter/agent_id/0a71e6fe-6dd7-4a65-ac76-7f3dd28ea47b/certificateFilePlaceholder&sslkey=/tmp/postgres_exporter/agent_id/0a71e6fe-6dd7-4a65-ac76-7f3dd28ea47b/certificateKeyFilePlaceholder&sslmode=verify-ca&sslrootcert=/tmp/postgres_exporter/agent_id/0a71e6fe-6dd7-4a65-ac76-7f3dd28ea47b/caFilePlaceholder): error retrieving databases: read tcp 172.17.0.2:45668->10.49.208.198:5432: i/o timeout" source="postgres_exporter.go:1651" [36magentID[0m=/agent_id/0a71e6fe-6dd7-4a65-ac76-7f3dd28ea47b [36mcomponent[0m=agent-process [36mtype[0m=postgres_exporter

Resize issue view side panel

Done

Assignee

Unassigned

Reporter

Jyoti Prakash

Priority

High

Components

Labels

pmmframework/monitor&alertregressiontech/PostgreSQL

Needs QA

Yes

Fix versions

2.23.0

Story Points

Affects versions

2.21.0

Created September 22, 2021 at 3:42 PM

Updated March 6, 2024 at 2:08 AM

Resolved October 14, 2021 at 2:15 PM

Can't monitor GCP Cloud SQL or other PostgreSQL with custom SSL certificates

Description

How to test

If you want to generate your own certs (optional)

Install certstrap from https://github.com/square/certstrap

Build certstrap to generate certs

Make CA file

Make Server certificate

Sign server cert with ca

Set up Postgres with SSL.

Get you localhost IP address in the lan

Start docker pmm and mount the certificates file inside the server container.

How to test

How to document

Linked work items

blocks

relates to

Smart Checklist

Activity

Jyoti Prakash November 1, 2021 at 8:16 PM

Lalit Choudhary October 25, 2021 at 1:58 PM

Jyoti Prakash October 25, 2021 at 1:40 PM

Jyoti Prakash October 25, 2021 at 4:18 AM

Jyoti Prakash October 25, 2021 at 4:16 AM

Details

Assignee

Reporter

Priority

Components

Labels

Needs QA

Fix versions

Story Points

Affects versions

Smart Checklist