Enable SSL encrypted communication between PuppetDB and PostgreSQL in Puppet 5.0 and later📍

By default, traffic between PuppetDB and its PostgreSQL backend database is not encrypted. To improve security, enable and configure SSL communication between the two databases using certificates signed by the Puppet certificate authority (CA). This example uses the certificate and private key of the master, as well as the Puppet CA certificate, but you can use certs issued by any valid CA.

Version and installation information

Puppet version: 5.0 and later
OS: Any
Installation type: Any

Solution

These steps assume that PuppetDB, PostgreSQL, and the master are all on the same node. These steps also work when PostgreSQL and PuppetDB are on different nodes; however, file locations and FQDNs might differ.

Enable and Configure SSL in PostgreSQL

Before you begin: Back up the following configuration files: postgresql.conf and database.ini.

1. Choose a location to copy your PostgreSQL certificate files to. On RHEL, the default PostgreSQL configuration directory is /var/lib/pgsql/<pg_version>/data/, and on Debian it is /etc/postgresql/<pg_version>/main/. To allow the certs to be accessed by PostgreSQL, change the file owner to the postgres user and set file permissions to 600.

   The following example is for an RHEL node running Postgres 9.6. A new directory is created to store the certificates, but you can use the PostgreSQL defaults for server.crt and server.key instead.

    puppet_cert_dir=/var/lib/pgsql/9.6/data/certs/
    mkdir "$puppet_cert_dir"
   
    cp $(puppet config print hostcert) "$puppet_cert_dir"/cert.pem
    cp $(puppet config print hostprivkey) "$puppet_cert_dir"/priv.pem
   
    chown -R postgres:postgres "$puppet_cert_dir"
    chmod 600 "$puppet_cert_dir"/*

2. To enable SSL using the certs you added in the last step, edit postgresql.conf to add the following:

    listen_addresses = <FQDN OF POSTGRESQL>
    ssl_cert_file = '<PATH_TO_CERT.PEM>'
    ssl_key_file = '<PATH_TO_PRIVATE_KEY.PEM>'

3. To require PostgreSQL clients to use a Puppet CA cert as a trusted certificate, edit postgresql.conf to set the ssl_ca_file option:

    ssl_ca_file = '/etc/puppetlabs/puppet/ssl/certs/ca.pem'

   Create or edit host-based authentication (HBA) rules as needed.

4. To allow PuppetDB to connect to PostgreSQL, in the pg_hba.conf hostssl entry, add clientcert=verify-ca or clientcert=verify-full. Learn more about clientcert from the PostgreSQL documentation.

   If you are using a local rule to allow this, you need to use hostssl instead.

   If you are using Puppet to manage HBA rules, such as with the postgresql module, update the rules so that your configuration is not reverted.

5. To use the new settings, restart PostgreSQL.

    systemctl restart postgresql-9.6

6. Confirm that your new settings are valid. Check the postgresql-\* log for errors.

   If SSL is not mandated by HBA rules, PuppetDB will continue to function. If SSL is mandated by your HBA rules, PuppetDB will not function until you update PuppetDB configuration to use SSL.

   In either case, complete the steps in the next section to configure SSL for PuppetDB.

Enable and configure SSL in PuppetDB

1. The JDBC driver used by PuppetDB requires a pk8 formatted copy of the master’s private key owned by the user puppetdb with permissions set to 600. In the following example, the key is converted to pk8 format in the default SSL directory for PuppetDB, the owner and permissions are set:

   openssl pkcs8 -topk8 -inform PEM -outform DER -in /etc/puppetlabs/puppetdb/ssl/private.pem -out /etc/puppetlabs/puppetdb/ssl/private.pk8 -nocrypt

   chown puppetdb:puppetdb /etc/puppetlabs/puppetdb/ssl/private.pk8

   chmod 600 /etc/puppetlabs/puppetdb/ssl/private.pk8

2. Edit /etc/puppetlabs/puppetdb/conf.d/database.ini to change the subname entry to use SSL. As with PostgreSQL, replace with the FQDN contained in the certificate.

   subname = //<FQDN OF POSTGRESQL>:5432/puppetdb?ssl=true&sslfactory=org.postgresql.ssl.jdbc4.LibPQFactory&sslmode=verify-full&sslrootcert=/etc/puppetlabs/puppet/ssl/certs/ca.pem&sslkey=/etc/puppetlabs/puppetdb/ssl/private.pk8 &sslcert=/etc/puppetlabs/puppetdb/ssl/public.pem

3. To use the new settings, restart PuppetDB:

   systemctl restart puppetdb

4. Confirm the new settings are valid by checking pg_stat_ssl and /var/log/puppetlabs/puppetdb/puppetdb.log.

Traffic between PuppetDB and PostgreSQL should now be SSL encrypted.