.. _security: 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. __ https://www.postgresql.org/docs/current/libpq-pgpass.html 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. __ https://www.postgresql.org/docs/current/ssl-tcp.html#SSL-CERTIFICATE-CREATION 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. __ https://www.postgresql.org/docs/current/libpq-ssl.html 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 __ https://www.postgresql.org/docs/current/auth-cert.html 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``). __ https://www.postgresql.org/docs/current/libpq-ssl.html#LIBPQ-SSL-CLIENTCERT 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. __ https://www.postgresql.org/docs/current/auth-username-maps.html 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: 1. run the ``pg_autoctl enable ssl`` command on your monitor and then all the Postgres nodes, 2. 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, 3. review your HBA rules setup to change the pg_auto_failover rules from ``host`` to ``hostssl`` to disallow insecure connections.