Uploaded image for project: 'Percona Monitoring and Management'
  1. Percona Monitoring and Management
  2. PMM-7888

Custom TLS certificates now allow SSL connections to PostgreSQL instances

Details

    • New Feature
    • Status: Done
    • Medium
    • Resolution: Fixed
    • None
    • 2.21.0
    • None
    • 2
    • 11 - Core
    • Yes
    • Yes
    • Yes
    • 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:

      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

      Attachments

        Issue Links

          Activity

            People

              Unassigned Unassigned
              jyoprakash Jyoti Prakash
              Votes:
              1 Vote for this issue
              Watchers:
              9 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Time Tracking

                  Estimated:
                  Original Estimate - Not Specified
                  Not Specified
                  Remaining:
                  Remaining Estimate - Not Specified
                  Not Specified
                  Logged:
                  Time Spent - 3 days
                  3d

                  Smart Checklist