Module: postgresql client certificate & general libpq ssl communication should be easily done in the module

Currently the module doesn't support switching on PostgreSQL SSL support using the more configurable libpq factory for users who want it. This ticket tracks the changes required to make that easy for users and so the $subname can be set automatically as a part of this also.

Right now, we have an option for database_ssl and read_database_ssl in the puppetdb::server::database_ini class but these only turn on ssl=true in the JDBC subname in the database.ini today: https://github.com/puppetlabs/puppetlabs-puppetdb/blob/master/manifests/server/database_ini.pp

While this is interesting, it implies the CA for postgresql is the same as Puppet. It also doesn't allow for client based certificate authentication. This is because the factory that is used is basic.

What we want to see is the usage of the LibPQ parameters as defined in our documentation, for example see the sample line here: https://docs.puppetlabs.com/puppetdb/2.2/postgres_ssl.html#using-your-own-self-signed-ca

Full documentation for using a new factory is here: http://jdbc.postgresql.org/documentation/93/ssl-factory.html which is very sparse, but the idea would be to use the libpq jdbc factory baked into the JDBC driver now in the later 9.x versions.

The options that are available to libpq are as follows:

• sslmode (verify-ca & verify-full)
• sslrootcert (a path to the root CA file)
• sslcert (path to the client cert file)
• sslkey (path to the client key)
• sslpassword (password the the client key)

Our desire would be to have the subname string be auto-created from these parameters in the module, so you ended up with a configuration line like this:

subname = //<HOST>:<PORT>/<DATABASE>?ssl=true&sslfactory=org.postgresql.ssl.jdbc4.LibPQFactory&sslmode=verify-full&sslrootcert=/etc/puppetdb/ssl/ca.pem

This could be a top level set of parameters for the class, or perhaps a hash that is passed into a single parameter database_ssl_options, and broken into arguments? Not sure . Then we would simply proxy these configuration parameters via puppetdb::server for completeness.

Source code is the best authoritative 'documentation' for libpqfactor for jdbc today. If you pick through it, you can see the parameters I've provided in the list above: https://github.com/pgjdbc/pgjdbc/blob/master/org/postgresql/ssl/jdbc4/LibPQFactory.java

Combined with this we could also allow proxied configuration via the puppetdb class puppetdb::database::postgresql for configuration of SSL for the PostgreSQL module: https://github.com/puppetlabs/puppetlabs-puppetdb/blob/master/manifests/database/postgresql.pp. That would allow full end-to-end configuration for SSL potentially, via the puppetdb class, whereby the necessary postgresql and puppetdb configuration could be provided up front and configured at once.

The design issue I haven't worked out is whether to require client certs up front, or somehow use Puppet's if they are around, but I'm presuming for an MVP we could just ask users to provide these initially.

I'm uncertain what work might be required on the postgresql module to enable these capabilities end-to-end, but most (if not all) configuration should already be exposed, so the changes hopefully should be small (and if done generically would help that module anyway).

Original report:

I've managed to use SSL for communication between PuppetDB and Postgres, but I can't effectively manage the 'subname' setting in database.ini while using the puppetlabs-puppetdb module. What ends up happening is that I'll change the subname with my own ini_setting resource, but then the puppetdb module will change it back on the next puppet run. Then my resource changes it again, and so it goes back and forth.

I looked through the module source and it doesn't look like there's any configuration option I can set to prevent this behavior.

Here's a gist of my example site.pp.

Edit: here's another gist with all the steps I took to get to where I am.