‘TLS connections’

Description

The TLS server is an alternative to using SSH for protocol connections to remote hosts. It is supported since the 2.37 release.

In this section, we will examine a configuration used to run pgBackRest from a dedicated repository host like described in the Use Case 2 , but using TLS rather than SSH connections.

We will set up a demo cluster (using EDBPostgresAdvancedServer version 14 on Rocky Linux 8) where the repository host will be called backup-srv and the 3 Postgres nodes in Streaming Replication: pg1-srv , pg2-srv , pg3-srv .

Create a Dedicated User on the Repository Host

The pgbackrest user is created to own the backups and archives repository. Any user can own the repository, but it is recommended not to use postgres or enterprisedb to avoid confusion.

Create the user and the repository location on backup-srv :

$ sudo groupadd pgbackrest
$ sudo adduser -g pgbackrest -n pgbackrest
$ sudo chown pgbackrest: /var/log/pgbackrest

The repository should be reachable from the backup server. It can be located on the various supported types described in the first use case .

For example, let us create /backup_space to store our backups and archives locally:

$ sudo mkdir /backup_space
$ sudo chown pgbackrest: /backup_space

Certificates generation

The TLS server implementation relies on certificates.

In this example, we will: * create our own root certificate authority (CA), * generate keys and certificates for the Postgres nodes and for the backup server.

$ mkdir certs && cd certs
$ openssl req -new -x509 -days 365 -nodes -out ca.crt -keyout ca.key -subj "/CN=root-ca"
$ openssl req -new -nodes -out backup-srv.csr -keyout backup-srv.key -subj "/CN=backup-srv"
$ openssl req -new -nodes -out pg1-srv.csr -keyout pg1-srv.key -subj "/CN=pg1-srv"
$ openssl req -new -nodes -out pg2-srv.csr -keyout pg2-srv.key -subj "/CN=pg2-srv"
$ openssl req -new -nodes -out pg3-srv.csr -keyout pg3-srv.key -subj "/CN=pg3-srv"
$ openssl x509 -req -in backup-srv.csr -days 365 -CA ca.crt -CAkey ca.key -CAcreateserial -out backup-srv.crt
$ openssl x509 -req -in pg1-srv.csr -days 365 -CA ca.crt -CAkey ca.key -CAcreateserial -out pg1-srv.crt
$ openssl x509 -req -in pg2-srv.csr -days 365 -CA ca.crt -CAkey ca.key -CAcreateserial -out pg2-srv.crt
$ openssl x509 -req -in pg3-srv.csr -days 365 -CA ca.crt -CAkey ca.key -CAcreateserial -out pg3-srv.crt
$ rm *.csr

Then, you have to deploy it on each server (ca.crt + server_name.crt + server_name.key ).

Here is an example using a shared folder between the hosts:

#  on backup-srv

$ sudo -u pgbackrest mkdir ~pgbackrest/certs
$ sudo cp `hostname`.* ~pgbackrest/certs
$ sudo cp ca.crt ~pgbackrest/certs
$ sudo chown -R pgbackrest: ~pgbackrest/certs
$ sudo chmod -R og-rwx ~pgbackrest/certs

#  on pg1-srv, pg2-srv and pg3-srv

$ sudo -u enterprisedb mkdir ~enterprisedb/certs
$ sudo cp `hostname`.* ~enterprisedb/certs
$ sudo cp ca.crt ~enterprisedb/certs
$ sudo chown -R enterprisedb: ~enterprisedb/certs
$ sudo chmod -R og-rwx ~enterprisedb/certs

Configuration

We will now prepare the configuration for our stanza called demo .

Update the backup-srv pgBackRest configuration file:

#  /etc/pgbackrest.conf

[global]
#  repo details

repo1-path=/backup_space
repo1-retention-full=2

#  general options

process-max=2
log-level-console=info
log-level-file=debug
start-fast=y
delta=y

#  tls server options

tls-server-address=*
tls-server-cert-file=/home/pgbackrest/certs/backup-srv.crt
tls-server-key-file=/home/pgbackrest/certs/backup-srv.key
tls-server-ca-file=/home/pgbackrest/certs/ca.crt
tls-server-auth=pg1-srv=demo
tls-server-auth=pg2-srv=demo
tls-server-auth=pg3-srv=demo

[demo]
pg1-host=pg1-srv
pg1-host-user=enterprisedb
pg1-path=/var/lib/edb/as14/data
pg1-host-type=tls
pg1-host-cert-file=certs/backup-srv.crt
pg1-host-key-file=certs/backup-srv.key
pg1-host-ca-file=certs/ca.crt

Update the pg1-srv , pg2-srv and pg3-srv pgBackRest configuration file:

#  /etc/pgbackrest.conf

[global]
repo1-host=backup-srv
repo1-host-user=pgbackrest
repo1-host-type=tls
repo1-host-cert-file=/var/lib/edb/certs/pg1-srv.crt
repo1-host-key-file=/var/lib/edb/certs/pg1-srv.key
repo1-host-ca-file=/var/lib/edb/certs/ca.crt

#  general options

process-max=2
log-level-console=info
log-level-file=debug

#  tls server options

tls-server-address=*
tls-server-cert-file=/var/lib/edb/certs/pg1-srv.crt
tls-server-key-file=/var/lib/edb/certs/pg1-srv.key
tls-server-ca-file=/var/lib/edb/certs/ca.crt
tls-server-auth=backup-srv=demo

[demo]
pg1-path=/var/lib/edb/as14/data
pg1-user=enterprisedb
pg1-port=5444

( Remark: obviously, adjust pg1-srv by pg2-srv on pg2-srv and pg3-srv on pg3-srv . )

Clients are authorized on the server by verifying their certificate and checking the certificate CN (Common Name) against a list on the server configured with the tls-server-auth . A CN can be authorized for as many stanzas as needed by repeating the tls-server-auth option, or for all stanzas by specifying tls-server-auth=* .

The idea is to have a TLS server running on each host to serve the request coming from the other. In example, the backup command running on the repository host will act as client for the TLS server running on the Postgres node. The archive-push command running on the Postgres node will act as client for the TLS server running on the repository host.

The EDB Postgres Advanced Server command can be used to start the TLS server and will run until terminated by the SIGINT signal (Control+C).

If not done by the PGDG package already, create a service file on the backup-srv server:

#  /etc/systemd/system/pgbackrest.service

[Unit]
Description=pgBackRest Server
After=network.target
StartLimitIntervalSec=0

[Service]
Type=simple
User=pgbackrest
Restart=always
RestartSec=1
ExecStart=/usr/bin/pgbackrest server
ExecStartPost=/bin/sleep 3
ExecStartPost=/bin/bash -c "[ ! -z $MAINPID ]"
ExecReload=/bin/kill -HUP $MAINPID

[Install]
WantedBy=multi-user.target

Start the server and check it’s running:

$ sudo systemctl daemon-reload
$ sudo systemctl enable pgbackrest
$ sudo systemctl start pgbackrest
$ pgbackrest server-ping

The server-ping command serves as an aliveness check only since no authentication is attempted.

Don’t forget to terminate/restart the process when the tls-* configuration is changed.

Now, apply the same steps (service configuration and start) on the on pg1-srv , pg2-srv and pg3-srv using this configuration:

#  /etc/systemd/system/pgbackrest.service

[Unit]
Description=pgBackRest Server
After=network.target
StartLimitIntervalSec=0

[Service]
Type=simple
User=enterprisedb
Restart=always
RestartSec=1
ExecStart=/usr/bin/pgbackrest server
ExecStartPost=/bin/sleep 3
ExecStartPost=/bin/bash -c "[ ! -z $MAINPID ]"
ExecReload=/bin/kill -HUP $MAINPID

[Install]
WantedBy=multi-user.target

Now, let us configure Postgres archiving in the postgresql.conf file (on pg1-srv ):

listen_addresses = *
archive_mode = on
archive_command = pgbackrest --stanza=demo archive-push %p

The Postgres instance must be restarted after making these changes and before performing a backup.

Let us finally create the stanza and check the configuration on backup-srv :

$ sudo -iu pgbackrest pgbackrest --stanza=demo stanza-create
...
P00   INFO: stanza-create command end: completed successfully

$ sudo -iu pgbackrest pgbackrest --stanza=demo check
...
P00   INFO: check command end: completed successfully

Perform a backup

If everything is working correctly, we can now take our first backup:

$ sudo -iu pgbackrest pgbackrest --stanza=demo --type=full backup
P00   INFO: backup command begin 2.38: ..
P00   INFO: execute non-exclusive pg_start_backup(): backup begins after the requested
                                                              immediate checkpoint completes
P00   INFO: backup start archive = 000000010000000000000004, lsn = 0/4000060
P00   INFO: check archive for prior segment 000000010000000000000003
P00   INFO: execute non-exclusive pg_stop_backup() and wait for all WAL segments to archive
P00   INFO: backup stop archive = 000000010000000000000004, lsn = 0/4000138
P00   INFO: check archive for segment(s) 000000010000000000000004:000000010000000000000004
P00   INFO: new backup label = 20220308-143110F
P00   INFO: full backup size = 56.5MB, file total = 1691
P00   INFO: backup command end: completed successfully

The info command can then be executed from any server where pgBackRest is correctly configured:

#  From the Postgres node

$ sudo -iu enterprisedb pgbackrest --stanza=demo info
stanza: demo
    status: ok
    cipher: none

    db (current)
        wal archive min/max (14): 000000010000000000000002/000000010000000000000004

        full backup: 20220308-143110F
            timestamp start/stop: 2022-03-08 14:31:10 / 2022-03-08 14:31:20
            wal start/stop: 000000010000000000000004 / 000000010000000000000004
            database size: 56.5MB, database backup size: 56.5MB
            repo1: backup set size: 9.1MB, backup size: 9.1MB

#  From the backup server

$ sudo -iu pgbackrest pgbackrest --stanza=demo info
stanza: demo
    status: ok
    cipher: none

    db (current)
        wal archive min/max (14): 000000010000000000000002/000000010000000000000004

        full backup: 20220308-143110F
            timestamp start/stop: 2022-03-08 14:31:10 / 2022-03-08 14:31:20
            wal start/stop: 000000010000000000000004 / 000000010000000000000004
            database size: 56.5MB, database backup size: 56.5MB
            repo1: backup set size: 9.1MB, backup size: 9.1MB

Prepare the servers for Streaming Replication

On pg1-srv , create a specific user for the replication:

$ sudo -iu enterprisedb psql -d postgres
postgres=# CREATE ROLE replic_user WITH LOGIN REPLICATION PASSWORD mypwd;

Configure pg_hba.conf :

host   replication   replic_user   pg2-srv   scram-sha-256
host   replication   replic_user   pg3-srv   scram-sha-256

Reload configuration:

$ sudo systemctl reload edb-as-14.service

Configure ~enterprisedb/.pgpass on pg2-srv and pg3-srv :

$ sudo -iu enterprisedb
$ echo "*:*:replication:replic_user:mypwd" >> ~enterprisedb/.pgpass
$ chown enterprisedb: ~enterprisedb/.pgpass
$ chmod 0600 ~enterprisedb/.pgpass

Setup the standby servers

Adjust /etc/pgbackrest.conf on pg2-srv and pg3-srv to add the recovery-option . The idea is to automatically configure the Streaming Replication connection string with the restore command.

recovery-option=primary_conninfo=host=pg1-srv user=replic_user

Then, make sure the configuration is correct by executing the info command. It should print the same output as above.

Restore the backup taken from the pg1-srv server on pg2-srv and pg3-srv :

$ sudo -iu enterprisedb pgbackrest --stanza=demo --type=standby restore
P00   INFO: restore command begin 2.38: ...
P00   INFO: repo1: restore backup set 20220308-143110F, recovery will start at ...
P00   INFO: write updated /var/lib/edb/as14/data/postgresql.auto.conf
P00   INFO: restore global/pg_control
(performed last to ensure aborted restores cannot be started)
P00   INFO: restore size = 56.5MB, file total = 1691
P00   INFO: restore command end: completed successfully

The restore will add extra information to the postgresql.auto.conf file:

#  Recovery settings generated by pgBackRest restore on ...

primary_conninfo = host=pg1-srv user=replic_user
restore_command = pgbackrest --stanza=demo archive-get %f "%p"

The --type=standby option creates the standby.signal needed for Postgres to start in standby mode. All we have to do now is to start the Postgres instances:

$ sudo systemctl enable edb-as-14.service
$ sudo systemctl start edb-as-14.service

If the replication setup is correct, you should see those processes on the pg1-srv server:

$ sudo -u enterprisedb ps -o pid,cmd fx
    PID CMD
  30594 /usr/edb/as14/bin/edb-postmaster -D /var/lib/edb/as14/data
  ...
  31436  \_ postgres: walsender replic_user ... streaming 0/5001FB0
  31439  \_ postgres: walsender replic_user ... streaming 0/5001FB0
  30222 /usr/bin/pgbackrest server

We now have a 3-nodes cluster working with Streaming Replication and archives recovery as safety net.

Take backups from the standby servers

Add the following settings to the pgBackRest configuration file on backup-srv , in the [demo] stanza section:

pg2-host=pg2-srv
pg2-host-user=enterprisedb
pg2-path=/var/lib/edb/as14/data
pg2-host-type=tls
pg2-host-cert-file=certs/backup-srv.crt
pg2-host-key-file=certs/backup-srv.key
pg2-host-ca-file=certs/ca.crt
pg3-host=pg3-srv
pg3-host-user=enterprisedb
pg3-path=/var/lib/edb/as14/data
pg3-host-type=tls
pg3-host-cert-file=certs/backup-srv.crt
pg3-host-key-file=certs/backup-srv.key
pg3-host-ca-file=certs/ca.crt
backup-standby=y

Now, perform a backup fetching the data from the first standby server found in the configuration:

$  sudo -iu pgbackrest pgbackrest --stanza=demo --type=full backup
P00   INFO: backup command begin 2.38: ...
P00   INFO: execute non-exclusive pg_start_backup(): backup begins after the requested
                                                             immediate checkpoint completes
P00   INFO: backup start archive = 000000010000000000000006, lsn = 0/6000028
P00   INFO: wait for replay on the standby to reach 0/6000028
P00   INFO: replay on the standby reached 0/6000028
P00   INFO: check archive for prior segment 000000010000000000000005
P00   INFO: execute non-exclusive pg_stop_backup() and wait for all WAL segments to archive
P00   INFO: backup stop archive = 000000010000000000000006, lsn = 0/6000138
P00   INFO: check archive for segment(s) 000000010000000000000006:000000010000000000000006
P00   INFO: new backup label = 20220308-145104F
P00   INFO: full backup size = 56.5MB, file total = 1691
P00   INFO: backup command end: completed successfully

Conclusion

The TLS server provides an alternative to SSH for remote operations. This new feature will fit perfectly in a containerized world where it can significantly improve performance and user experience.