BDR3, BDR2 and BDR1 all support SSL and also SSL client certificates. So does pglogical.
Note: for simplicity and readability this article frequently uses "BDR" to mean "BDR3, BDR2, BDR1, or pglogical". Where a specific product or version applies, it's mentioned specifically, e.g. BDR3.
All support for SSL in BDR and pglogical is built on the facilities in PostgreSQL and libpq, so you should be familiar with how they work and with SSL configuration in PostgreSQL. See:
To set libpq connection parameters in BDR or pglogical, you may use any of the following methods:
- Directly set the connection parameter(s) in the node dsn (connection string);
- Set the connection parameters for all outbound connections from a node by adding them in
postgresql.conf: - BDR3 and pglogical: add to the
pglogical.extra_connection_optionsoption - BDR2 and BDR1: add to the
bdr.extra_apply_connection_optionsoption - Specify a
service=nameparameter using either of the above methods, then create a.pg_service.conffile in the PostgreSQL server user's home directory that specifies the additional options required.
BDR and pglogical are often used in globally distributed systems where traffic goes over the Internet. Frequently the traffic travels via a WAN with VPN facilities, providing encryption and protection at the network layer, so BDR does not need to be configured for SSL. However, it's possible to deploy BDR with connections directly Internet-exposed, or on a WAN that's used for many other purposes with varying levels of trust. In these cases it is strongly desirable to configure SSL for transport layer security in BDR.
To configure BDR or pglogical to require SSL when connecting to the server, configure each node to accept SSL connections as normal. Then add sslmode=require to the BDR connection string to enforce SSL, just like any other libpq client.
A typical configuration is like any other PostgreSQL SSL configuration:
ssl=on
ssl_cert_file='server.crt'
ssl_key_file='server.key'
# If you want to validate client certificates
ssl_ca_file='root.crt'
and in pg_hba.conf:
# to require SSL:
hostssl mybdrdb mybdruser md5
# or if you want client certificate verification too:
hostssl mybdrdb mybdruser md5 clientcert=1
See "Encrypted keys" below if you need to store the server key passphrase-protected.
To guard against MiTM attacks, it's not very practical to use sslmode=verify-full unless you have proper DNS configured and either a wildcard certificate or certificate management automation. So it's generally preferable to use sslmode=verify-ca and specify the path to the CA certificate you require the server cert to be signed with in the sslrootcert libpq option.
The sslrootcert path is relative to the PostgreSQL data directory and the file must be readable by the user PostgreSQL runs as.
All BDR and pglogical versions support SSL client certificates. The sslcert and sslkey connection parameters are used for this, like in any other libpq application. They may be configured in BDR with any of the methods given above.
The certificate and key paths are relative to the PostgreSQL data directory. The certificate file must be readable by the user PostgreSQL runs as. The key file must have permissions mode 0600 (u=rx,g=,o=) and be owned by the user PostgreSQL runs as.
See "Encrypted keys" below if you need to store the client key passphrase-protected.
To require client certificate validation on the server side add the client_cert=1 option to the relevant pg_hba.conf line(s) and configure the ssl_ca_file the client certificates must be signed by in postgresql.conf.
Warning: The client certificate and key file(s) used by BDR must be readable by the PostgreSQL server. So any PostgreSQL superuser can read and extract them, as can anyone with rights to access the unix user account the PostgreSQL server runs as.
Note: An unprivileged user can also potentially use the client certificate and key via postgres_fdw, but per the fixes for CVE-2018-10915 they cannot use them (or a postgres-server pgpass or pg_service.conf) as a sole and sufficient means of authentication to a remote postgres server. postgres_fdw requires that a password be specified in the user mapping for non-superusers and actually be used by the authenticating server for non-superuser connections. (2ndQPostgres 11 r1.3 lifts that restriction slightly with the password_required option in postgres_fdw user mappings; see the documentation in 2ndQPostgres for details).
It is typical to store the server SSL keys in clear-text and rely on protection of the server data directory. The assumption here is that a compromise of the keys implies a compromise of access to the server data directory, which means you have already lost.
However, organisational rules or policies may require that keys be stored encrypted.
On PostgreSQL 11 and higher, PostgreSQL supports use of an encrypted server certificate via the ssl_passphrase_command option.
On older versions an interactive prompt is presented at server start-up time; if this prompt cannot be displayed due to lack of an available TTY, server startup will fail.
On 2ndQPostgres 11 r1.3 and higher, 2ndQPostgres supports use of an encrypted client key for SSL client certificate authentication. This is managed by the sslpassword option in libpq (a 2ndQPostgres extension). This may be used by BDR3 by adding sslpassword to your connection string using any of the methods described above for the other SSL options.
There is no built-in support for calling out to an askpass executable in 2ndQPostgres's libpq, it only supports a static password in the libpq connection string. However, you can add your own extension to PostgreSQL that injects an askpass capability if you require it, registering your extension in shared_preload_libraries or local_preload_libraries and passing your own askpass callback to the new PQsetSSLKeyPassHook libpq function during _PG_init . This will work for postgres_fdw, BDR, and anything else that makes libpq connections from within the PostgreSQL server.
On older PostgreSQL versions and on community PostgreSQL 11, attempts to use an encrypted client certificate file will either fail with a decrypt error if no TTY is available. If a TTY is available to the postgres executable, it will prompt interactively for the password instead.