Custom TLS certificates now allow SSL connections to PostgreSQL instances

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:

1. >pmm-admin add postgresql .. --ssl...
2. 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:

1. 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
2.  up docker-compose (in attachment) : docker-compose -f docker-compose-pg-tls.yaml up -d
3. 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'"
    
4. 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