Details
-
New Feature
-
Status: Done
-
Medium
-
Resolution: Fixed
-
None
-
None
-
2
-
Yes
-
Yes
-
Yes
-
[obsolete] C/S Core
Description
User story:
As a PMM user, I want to be able to pass custom SSL keys to be able to connect to my PostgreSQL server, so that PMM can monitor it
UI/UX:
- >pmm-admin add postgresql .. --ssl...
- An additional fields on Add Remote PG page
Acceptance criteria
- user able to pass PG related SSL keys:
- sslcert
- sslkey
- sslrootcert
- User able to pass the content of the certificates on the UI page for remote PG monitoring
- Documentation:
- Documentation reflects the new flags in the security-related section
- the reference manual is updated
- TBD
Out of scope:
Suggested implementation:
TBD
How to test:
- create certs
openssl req -new -x509 -extensions v3_ca -days 9999 -nodes -out ca.crt -keyout ca.key -subj "/CN=pmm"
cat ca.key.pem ca.req > $CA_PEM
openssl req -new -nodes -keyout server.key -out server.csr -subj "/CN=postgres-tls"
openssl x509 -sha256 -CA ca.crt -CAkey ca.key -CAcreateserial -req -days 9999 -in server.csr -out server.crt -extfile <(printf "subjectAltName=DNS:postgres-tls")
cat server.key server.crt > server.pem
rm server.csr
openssl req -new -nodes -keyout client.key -out client.csr -subj "/CN=user"
openssl x509 -sha256 -CA ca.crt -CAkey ca.key -CAcreateserial -req -days 9999 -in client.csr -out client.crt
cat client.key client.crt > client.pem
rm client.csr
openssl verify -CAfile ca.crt server.crt client.crt - up docker-compose (in attachment) : docker-compose -f docker-compose-pg-tls.yaml up -d
- fix permitions
docker exec postgres-tls bash -c "sed -i -r 's/host all all all md5/hostssl all all all cert/' /var/lib/postgresql/data/pg_hba.conf && su postgres -c 'pg_ctl reload'"
- copy clients certs
docker cp client.key pmm-client:/tmp
docker cp client.crt pmm-client:/tmp
docker cp ca.crt pmm-client:/tmp
5. connect to PG from inside pmm-client conainer pmm-admin add postgresql --tls --tls-ca-file=/tmp/ca.crt --tls-cert-file=/tmp/client.crt --tls-key-file=/tmp/client.key --host=postgres-tls --port=5432 --username=user --service-name=MyPGTLS
or psql "host=postgres-tls port=5432 user=user dbname=mydb sslmode=verify-full sslcert=client.crt sslkey=client.key sslrootcert=ca.crt"
5. enable extentions:
mydb=# \c postgres
postgres=# ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_monitor';
ALTER SYSTEM
postgres=# CREATE EXTENSION pg_stat_monitor;
CREATE EXTENSION
postgres=# select name,description from pg_stat_monitor_settings;
postgres=# CREATE EXTENSION pg_stat_statements;
Original report:
Can we include certificate based login for postgres monitoring? In UI can we add option to select sslmode and path to certificates and keys? we have requirement where all postgres will be ssl enabled and key and certificates will be used for authenetication
Sample connection string:
DATA_SOURCE_NAME=“postgresql://postgres_exporter:[email protected]:5432/postgres?sslmode=verify-ca&sslrootcert=/etc/ssl/certs/server-ca.pem&sslcert=/etc/ssl/certs/client-cert.pem&sslkey=/etc/ssl/certs/client-key.pem”
Sample connection setting from grafana where you can see the certificate related entries
Attachments
Issue Links
- blocks
-
PMM-7903 [UI] Enabling PMM UI to connect to Postgres using client certificates (for Remote Add)
-
- Done
-
-
PMM-7937 [API, CLI] Enabling PMM UI to connect to Postgres using client certificates (for Remote Add)
-
- Done
-
- is cloned by
-
PMM-8558 [DOCS] Add support for TLS certificates for PostgreSQL
-
- Done
-
- relates to
-
PMM-8851 Can't monitor GCP Cloud SQL or other PostgreSQL with custom SSL certificates
-
- Done
-