Security settings for pg_auto_failover

In order to be able to orchestrate fully automated failovers, pg_auto_failover needs to be able to establish the following Postgres connections:

from the monitor node to each Postgres node to check the node's “health”

from each Postgres node to the monitor to implement our node_active protocol and fetch the current assigned state for this node

from the secondary node to the primary node for Postgres streaming replication.

Postgres Client authentication is controlled by a configuration file: pg_hba.conf. This file contains a list of rules where each rule may allow or reject a connection attempt.

For pg_auto_failover to work as intended, some HBA rules need to be added to each node configuration. You can choose to provision the pg_hba.conf file yourself thanks to pg_autoctl options' --skip-pg-hba, or you can use the following options to control which kind of rules are going to be added for you.

Postgres HBA rules

For your application to be able to connect to the current Postgres primary servers, some application specific HBA rules have to be added to pg_hba.conf. There is no provision for doing that in pg_auto_failover.

In other words, it is expected that you have to edit pg_hba.conf to open connections for your application needs.

The trust security model

As its name suggests the trust security model is not enabling any kind of security validation. This setting is popular for testing deployments though, as it makes it very easy to verify that everything works as intended before putting security restrictions in place.

To enable a “trust” security model with pg_auto_failover, use the pg_autoctl option --auth trust when creating nodes:

$ pg_autoctl create monitor --auth trust ...
$ pg_autoctl create postgres --auth trust ...
$ pg_autoctl create postgres --auth trust ...

When using --auth trust pg_autoctl adds new HBA rules in the monitor and the Postgres nodes to enable connections as seen above.

Authentication with passwords

To setup pg_auto_failover with password for connections, you can use one of the password based authentication methods supported by Postgres, such as password or scram-sha-256. We recommend the latter, as in the following example:

$ pg_autoctl create monitor --auth scram-sha-256 ...

The pg_autoctl does not set the password for you. The first step is to set the database user password in the monitor database thanks to the following command:

$ psql postgres://monitor.host/pg_auto_failover
> alter user autoctl_node password 'h4ckm3';

Now that the monitor is ready with our password set for the autoctl_node user, we can use the password in the monitor connection string used when creating Postgres nodes.

On the primary node, we can create the Postgres setup as usual, and then set our replication password, that we will use if we are demoted and then re-join as a standby:

$ pg_autoctl create postgres       \
       --auth scram-sha-256        \
       ...                         \
       --monitor postgres://autoctl_node:h4ckm3@monitor.host/pg_auto_failover

$ pg_autoctl config set replication.password h4ckm3m0r3

The second Postgres node is going to be initialized as a secondary and pg_autoctl then calls pg_basebackup at create time. We need to have the replication password already set at this time, and we can achieve that the following way:

$ export PGPASSWORD=h4ckm3m0r3
$ pg_autoctl create postgres       \
       --auth scram-sha-256        \
       ...                         \
       --monitor postgres://autoctl_node:h4ckm3@monitor.host/pg_auto_failover

$ pg_autoctl config set replication.password h4ckm3m0r3

Note that you can use The Password File mechanism as discussed in the Postgres documentation in order to maintain your passwords in a separate file, not in your main pg_auto_failover configuration file. This also avoids using passwords in the environment and in command lines.

Encryption of network communications

Postgres knows how to use SSL to enable network encryption of all communications, including authentication with passwords and the whole data set when streaming replication is used.

To enable SSL on the server an SSL certificate is needed. It could be as simple as a self-signed certificate, and pg_autoctl creates such a certificate for you when using --ssl-self-signed command line option:

$ pg_autoctl create monitor --ssl-self-signed ...      \
                            --auth scram-sha-256 ...   \
                            --ssl-mode require         \
                            ...

$ pg_autoctl create postgres --ssl-self-signed ...      \
                             --auth scram-sha-256 ...   \
                             ...

$ pg_autoctl create postgres --ssl-self-signed ...      \
                             --auth scram-sha-256 ...   \
                             ...

In that example we setup SSL connections to encrypt the network traffic, and we still have to setup an authentication mechanism exactly as in the previous sections of this document. Here scram-sha-256 has been selected, and the password will be sent over an encrypted channel.

When using the --ssl-self-signed option, pg_autoctl creates a self-signed certificate, as per the Postgres documentation at the Creating Certificates page.

The certificate subject CN defaults to the --hostname parameter, which can be given explicitly or computed by pg_autoctl as either your hostname when you have proper DNS resolution, or your current IP address.

Self-signed certificates provide protection against eavesdropping; this setup does NOT protect against Man-In-The-Middle attacks nor Impersonation attacks. See PostgreSQL documentation page SSL Support for details.

Using your own SSL certificates

In many cases you will want to install certificates provided by your local security department and signed by a trusted Certificate Authority. In that case one solution is to use --skip-pg-hba and do the whole setup yourself.

It is still possible to give the certificates to pg_auto_failover and have it handle the Postgres setup for you:

$ pg_autoctl create monitor --ssl-ca-file root.crt   \
                            --ssl-crl-file root.crl  \
                            --server-cert server.crt  \
                            --server-key server.key  \
                            --ssl-mode verify-full \
                            ...

$ pg_autoctl create postgres --ssl-ca-file root.crt   \
                             --server-cert server.crt  \
                             --server-key server.key  \
                             --ssl-mode verify-full \
                             ...

$ pg_autoctl create postgres --ssl-ca-file root.crt   \
                             --server-cert server.crt  \
                             --server-key server.key  \
                             --ssl-mode verify-full \
                             ...

The option --ssl-mode can be used to force connection strings used by pg_autoctl to contain your preferred ssl mode. It defaults to require when using --ssl-self-signed and to allow when --no-ssl is used. Here, we set --ssl-mode to verify-full which requires SSL Certificates Authentication, covered next.

The default --ssl-mode when providing your own certificates (signed by your trusted CA) is then verify-full. This setup applies to the client connection where the server identity is going to be checked against the root certificate provided with --ssl-ca-file and the revocation list optionally provided with the --ssl-crl-file. Both those files are used as the respective parameters sslrootcert and sslcrl in pg_autoctl connection strings to both the monitor and the streaming replication primary server.

SSL Certificates Authentication

Given those files, it is then possible to use certificate based authentication of client connections. For that, it is necessary to prepare client certificates signed by your root certificate private key and using the target user name as its CN, as per Postgres documentation for Certificate Authentication:

The cn (Common Name) attribute of the certificate will be compared to the requested database user name, and if they match the login will be allowed

For enabling the cert authentication method with pg_auto_failover, you need to prepare a Client Certificate for the user postgres and used by pg_autoctl when connecting to the monitor, to place in ~/.postgresql/postgresql.crt along with its key ~/.postgresql/postgresql.key, in the home directory of the user that runs the pg_autoctl service (which defaults to postgres).

Then you need to create a user name map as documented in Postgres page User Name Maps so that your certificate can be used to authenticate pg_autoctl users.

The ident map in pg_ident.conf on the pg_auto_failover monitor should then have the following entry, to allow postgres to connect as the autoctl_node user for pg_autoctl operations:

# MAPNAME       SYSTEM-USERNAME         PG-USERNAME

# pg_autoctl runs as postgres and connects to the monitor autoctl_node user
pgautofailover   postgres               autoctl_node

To enable streaming replication, the pg_ident.conf file on each Postgres node should now allow the postgres user in the client certificate to connect as the pgautofailover_replicator database user:

# MAPNAME       SYSTEM-USERNAME         PG-USERNAME

# pg_autoctl runs as postgres and connects to the monitor autoctl_node user
pgautofailover  postgres                pgautofailover_replicator

Given that user name map, you can then use the cert authentication method. As with the pg_ident.conf provisioning, it is best to now provision the HBA rules yourself, using the --skip-pg-hba option:

$ pg_autoctl create postgres --skip-pg-hba --ssl-ca-file ...

The HBA rule will use the authentication method cert with a map option, and might then look like the following on the monitor:

# allow certificate based authentication to the monitor
hostssl pg_auto_failover autoctl_node 10.0.0.0/8 cert map=pgautofailover

Then your pg_auto_failover nodes on the 10.0.0.0 network are allowed to connect to the monitor with the user autoctl_node used by pg_autoctl, assuming they have a valid and trusted client certificate.

The HBA rule to use on the Postgres nodes to allow for Postgres streaming replication connections looks like the following:

# allow streaming replication for pg_auto_failover nodes
hostssl replication pgautofailover_replicator 10.0.0.0/8 cert map=pgautofailover

Because the Postgres server runs as the postgres system user, the connection to the primary node can be made with SSL enabled and will then use the client certificates installed in the postgres home directory in ~/.postgresql/postgresql.{key,cert} locations.

Postgres HBA provisioning

While pg_auto_failover knows how to manage the Postgres HBA rules that are necessary for your stream replication needs and for its monitor protocol, it will not manage the Postgres HBA rules that are needed for your applications.

If you have your own HBA provisioning solution, you can include the rules needed for pg_auto_failover and then use the --skip-pg-hba option to the pg_autoctl create commands.

Enable SSL connections on an existing setup

Whether you upgrade pg_auto_failover from a previous version that did not have support for the SSL features, or when you started with --no-ssl and later change your mind, it is possible with pg_auto_failover to add SSL settings on system that has already been setup without explicit SSL support.

In this section we detail how to upgrade to SSL settings.

Installing Self-Signed certificates on-top of an already existing pg_auto_failover setup is done with one of the following pg_autoctl command variants, depending if you want self-signed certificates or fully verified ssl certificates:

$ pg_autoctl enable ssl --ssl-self-signed --ssl-mode required

$ pg_autoctl enable ssl --ssl-ca-file root.crt   \
                        --ssl-crl-file root.crl  \
                        --server-cert server.crt  \
                        --server-key server.key  \
                        --ssl-mode verify-full

The pg_autoctl enable ssl command edits the postgresql-auto-failover.conf Postgres configuration file to match the command line arguments given and enable SSL as instructed, and then updates the pg_autoctl configuration.

The connection string to connect to the monitor is also automatically updated by the pg_autoctl enable ssl command. You can verify your new configuration with:

$ pg_autoctl config get pg_autoctl.monitor

Note that an already running pg_autoctl daemon will try to reload its configuration after pg_autoctl enable ssl has finished. In some cases this is not possible to do without a restart. So be sure to check the logs from a running daemon to confirm that the reload succeeded. If it did not you may need to restart the daemon to ensure the new connection string is used.

The HBA settings are not edited, irrespective of the --skip-pg-hba that has been used at creation time. That's because the host records match either SSL or non-SSL connection attempts in Postgres HBA file, so the pre-existing setup will continue to work. To enhance the SSL setup, you can manually edit the HBA files and change the existing lines from host to hostssl to disallow unencrypted connections at the server side.

In summary, to upgrade an existing pg_auto_failover setup to enable SSL:

run the pg_autoctl enable ssl command on your monitor and then all the Postgres nodes,

on the Postgres nodes, review your pg_autoctl logs to make sure that the reload operation has been effective, and review your Postgres settings to verify that you have the expected result,

review your HBA rules setup to change the pg_auto_failover rules from host to hostssl to disallow insecure connections.