Securing inter-node connections v6.3.1

PGD nodes connect directly to each other over the network to replicate changes. Without SSL, replication traffic is unencrypted and unauthenticated, leaving it exposed to interception and allowing unauthenticated peers to participate in replication. Configure SSL on each node to encrypt replication traffic and prevent unauthorized nodes from joining the cluster.

Setting up an authentication method

PGD supports two approaches for authenticating inter-node connections.

As a best practice, use a dedicated database role for inter-node connections rather than the postgres superuser. Before joining nodes to the cluster, run the following on each node to create the role:

CREATE ROLE pgd_replication LOGIN IN ROLE bdr_superuser;

Use this role in the connection strings and pg_hba.conf rules shown in the following sections.

We recommend certificate authentication as it provides mutual authentication alongside encryption. If certificates aren't available in your environment, SCRAM-SHA-256 with Certificate Authority (CA) verification provides password-based authentication while still encrypting the connection.

Authenticating with certificates

Certificate authentication is the recommended approach. Client certificates provide both encryption and mutual authentication. Each node presents a certificate to prove its identity, and all traffic is encrypted in transit.

Provisioning certificates

Because each PGD node both accepts incoming replication connections and initiates outgoing ones, every node acts as both a server and a client. Each node needs server-side certificate files to accept connections and client-side certificate files to initiate them.

Make sure the following files are present in the data directory on each node. See SSL certificate setup in the PostgreSQL documentation for guidance on generating them.

• server.crt and server.key — the node's server certificate and private key.
• bdr_client.crt — the client certificate for the node.
• bdr_client.key — the private key for the client certificate. This file must not be world-readable.
• root.crt — the CA certificate used to verify both client and server certificates.

The certificates must also meet these requirements:

• The server's SSL certificate must be directly or indirectly signed by the CA in root.crt.
• The hostname or IP address in the connection must match the certificate's subject alternative name (SAN) or common name (CN).
• The CN of the client certificate must match the Postgres user making the PGD connection.

Configuring certificate authentication

1. Enable SSL in postgresql.conf on each node:

   → WrapCopy

   ssl = on
   ssl_cert_file = 'server.crt'
   ssl_key_file = 'server.key'

   Restart Postgres on each node to apply the change:

   → WrapCopy

   pg_ctl restart -D "$PGDATA"

2. Check the current connection string for each node:

   → WrapCopy

   SELECT node_name, interface_connstr FROM bdr.node_summary;

3. Use bdr.alter_node_interface() to update each node's connection string with the SSL parameters. The function replaces the entire connection string, so include all existing parameters alongside the new SSL ones. Run it on every node in the group, including the node being changed. For example:

   → WrapCopy

   SELECT bdr.alter_node_interface(
     'node-1',
     'host=host-1 port=5432 dbname=pgddb user=pgd_replication sslmode=verify-full sslcert=bdr_client.crt sslkey=bdr_client.key sslrootcert=root.crt'
   );

4. Add entries to each node's pg_hba.conf to permit inter-node connections:

   → WrapCopy

   hostssl all         pgd_replication <node_subnet>/24 cert
   hostssl replication pgd_replication <node_subnet>/24 cert

   Replace <node_subnet> with the actual subnet or IP range of your PGD nodes.

5. Reload Postgres on each node to apply the pg_hba.conf changes:

   → WrapCopy

   pg_ctl reload -D "$PGDATA"

Authenticating with SCRAM-SHA-256 and CA verification

If client certificates aren't available, use password authentication while still encrypting the connection and verifying the server's certificate against a CA.

Provisioning certificates and credentials

Make sure the following are in place on each node:

• server.crt and server.key in the data directory — the node's server certificate and private key. See SSL certificate setup in the PostgreSQL documentation for guidance on generating them.
• root.crt in the data directory — the CA certificate used to verify the server's SSL certificate.
• A .pgpass file in the home directory of the Postgres process user, containing the correct password for each node. The file must have permissions 0600 or stricter. If permissions are too open, the file is silently ignored.

Configuring SCRAM-SHA-256 authentication

1. Enable SSL in postgresql.conf on each node:

   → WrapCopy

   ssl = on
   ssl_cert_file = 'server.crt'
   ssl_key_file = 'server.key'

   Restart Postgres on each node to apply the change:

   → WrapCopy

   pg_ctl restart -D "$PGDATA"

2. Use bdr.alter_node_interface() to update each node's connection string. The function replaces the entire connection string, so include all existing parameters alongside the new SSL ones. Run it on every node in the group, including the node being changed.

   → WrapCopy

   SELECT bdr.alter_node_interface(
     'node-1',
     'host=host-1 port=5432 dbname=pgddb user=pgd_replication sslmode=verify-ca sslrootcert=root.crt'
   );

3. Add entries to each node's pg_hba.conf for inter-node connections:

   → WrapCopy

   hostssl all         pgd_replication <node_subnet>/24 scram-sha-256
   hostssl replication pgd_replication <node_subnet>/24 scram-sha-256

   Replace <node_subnet> with the actual subnet or IP range of your PGD nodes.

4. Reload Postgres on each node to apply the pg_hba.conf changes:

   → WrapCopy

   pg_ctl reload -D "$PGDATA"

Verifying the configuration

After configuring SSL, confirm that replication connections are encrypted and that all nodes are active. Run these queries on any node in the cluster.

• Verify that replication connections are using SSL:

  → WrapCopy

  SELECT pid, ssl, version, cipher, bits
  FROM pg_stat_ssl
  JOIN pg_stat_replication USING (pid);

  Output

  pid | ssl | version |         cipher         | bits 
  -----+-----+---------+------------------------+------
  51 | t   | TLSv1.3 | TLS_AES_256_GCM_SHA384 |  256
  50 | t   | TLSv1.3 | TLS_AES_256_GCM_SHA384 |  256
  (2 rows)

  A successful result shows one row per replication connection, with ssl set to t. This output confirms encryption is active, but doesn't show which authentication method is in use.

• Confirm the authentication method configured in pg_hba.conf:

  → WrapCopy

  SELECT type, database, user_name, address, auth_method
  FROM pg_hba_file_rules
  WHERE auth_method IN ('cert', 'scram-sha-256');

  Output

  type   |   database    | user_name  |  address   |  auth_method  
  ---------+---------------+------------+------------+---------------
  hostssl | {all}         | {pgd_replication} | 172.20.0.0 | scram-sha-256
  hostssl | {replication} | {pgd_replication} | 172.20.0.0 | scram-sha-256
  (2 rows)

  The rows you added appear with the auth_method you configured, for example cert or scram-sha-256.

• Confirm all nodes are still replicating normally:

  → WrapCopy

  SELECT node_name, peer_state_name, peer_target_state_name 
  FROM bdr.node_summary;

  Output

  node_name | peer_state_name | peer_target_state_name 
  -----------+-----------------+------------------------
  node-1    | ACTIVE          | ACTIVE
  node-3    | ACTIVE          | ACTIVE
  node-2    | ACTIVE          | ACTIVE
  (3 rows)

  All nodes appear as ACTIVE in both state columns.