Setting up SSL for PostgreSQL

If you don't have a signed Puppet certificate on your PostgreSQL server, see the ssl documentaion for alternate SSL connection options.

Using Puppet certificates to secure your PostgreSQL server has the following benefits:

• Because you are using PuppetDB, we can presume that you are using Puppet on each server. This means you can reuse the local Puppet agent certificate for PostgreSQL.

• Because your local Puppet agent's certificate must be signed for Puppet to work, you likely have an established workflow for getting these signed.

• We also recommend this methodology for securing the HTTPS interface for PuppetDB.

• You can remove the plaintext password from your PuppetDB config files if you also configure database authorization using agent certificates

To begin, configure your PostgreSQL server to use the host's Puppet server certificate and key. The location of these files can be found by using the following commands:

# Certificate
puppet config print hostcert
# Key
puppet config print hostprivkey

Copy these files to the relevant directories as specified by the PostgreSQL configuration items ssl_cert_file and ssl_key_file. This process is explained in detail here.

Make sure that ssl is set to on in postgresql.conf, and then restart PostgreSQL.

After this is complete, modify the database JDBC connection URL in your PuppetDB configuration as follows:

[database]
subname = //<HOST>:<PORT>/<DATABASE>?ssl=true&sslfactory=org.postgresql.ssl.LibPQFactory&sslmode=verify-full&sslrootcert=/etc/puppetlabs/puppetdb/ssl/ca.pem
username = <USERNAME>
password = <PASSWORD>

Restart PuppetDB and monitor your logs for errors. Your connection should now be SSL (but you will still be authorizing your database connection using a plaintext password).

To use Puppet's signed certificates to authenticate PuppetDB's database connection (instead of using a password in the database config section), you need to follow the above instructions for setting up an SSL connection between PuppetDB and Postgres and then change the pg_hba.conf and pg_ident.conf settings to allow your PuppetDB service to access the puppetdb database using its certificate. Also, your PostgreSQL server will need to be able to validate the certificate of your PuppetDB server, so copy the ca.pem (which can be found with puppet config print localcacert over to the directory of your ssl_cert_file and ssl_key_file, and set the ssl_ca_file in your postgresql.conf.

In pg_hba.conf you need to add one hostssl entry for each database user configured. Starting in PostgreSQL 14 the clientcert=1 option isn't supported and is instead replaced with clientcert=verify-full. The verify-full option is first available in PostgreSQL 12.

# Allow certificate mapped connections to puppetdb as puppetdb (ipv6)
hostssl puppetdb     puppetdb     ::/0    cert    map=puppetdb-puppetdb-map clientcert=1

# Allow certificate mapped connections to puppetdb as puppetdb_migrator (ipv6)
hostssl puppetdb     puppetdb_migrator    ::/0    cert    map=puppetdb-puppetdb-migrator-map clientcert=1

# Allow certificate mapped connections to puppetdb as puppetdb_read (ipv6)
hostssl puppetdb     puppetdb_read        ::/0    cert    map=puppetdb-puppetdb-read-map clientcert=1

Then, in pg_ident.conf configure the certificate map from your certificate name to the map names used in pg_hba.conf

puppetdb-puppetdb-map HOSTNAME puppetdb
puppetdb-puppetdb-migrator-map HOSTNAME puppetdb_migrator
puppetdb-puppetdb-read-map HOSTNAME puppetdb_read

Finally, configure PuppetDB's subname to include its private key and certificate.

subname = //<HOST>:<PORT>/<DATABASE>?ssl=true&sslfactory=org.postgresql.ssl.LibPQFactory&sslmode=verify-full&sslrootcert=/etc/puppetlabs/puppetdb/ssl/ca.pem&sslkey=/tmp/private_key.pk8&sslcert=/etc/puppetlabs/puppetdb/ssl/public.pem

First, obtain your signed certificate using the process required by your commercial Certificate Authority.

Note: If you don't want to pay for individual certificates for each server in your enterprise, you can probably get away with using wildcards in the subject or CN for your certificate. Note, however, that while at the moment DNS resolution based on CN isn't tested using the default SSLSocketFactory, we do not know if this will change going forward, and therefore if wildcard support will be included.

Follow the documentation for secure TCP/IP connections with SSL, which explains in detail how to configure SSL on the server. Make sure you place your signed certificate and private key in the locations specified by the ssl_cert_file and ssl_key_file locations, and that you change the ssl setting to on in your postgresql.conf. Don't forget to give the correct permissions for each file (such as chmod 0600 file). Otherwise, PostgreSQL will reject the key and cert files.

Because the JDBC PostgreSQL driver utilizes the Java's system KeyStore, and because the system KeyStore usually contains all public CAs, there should be no trust issues with the client configuration. Simply modify the JDBC URL as provided in the database configuration section for PuppetDB.

For example:

[database]
subname = //<HOST>:<PORT>/<DATABASE>?ssl=true&sslmode=verify-full&sslfactory=org.postgresql.ssl.DefaultJavaSSLFactory
username = <USERNAME>
password = <PASSWORD>

Restart PuppetDB and monitor your logs for errors. Your connection should now be SSL.

Warning: This is not recommended. SSL connections offer a higher level of security. Disabling SSL verification effectively removes the ability for the SSL client to detect man-in-the-middle attacks.

However, if you wish to disable SSL verification, you can do so by simply modifying your JDBC URL in the database configuration section of PuppetDB as follows:

[database]
subname = //<HOST>:<PORT>/<DATABASE>?ssl=true&sslfactory=org.postgresql.ssl.NonValidatingFactory
username = <USERNAME>
password = <PASSWORD>

Restart PuppetDB and monitor your logs for errors. Your connection should now be SSL, with validation disabled.