How to safely create users for Barman in PostgreSQL

This short article guides you through the creation of users required by Barman to work with PostgreSQL. It requires you to have already setup a Barman installation using our packages.

Barman needs to communicate with PostgreSQL in order to perform backup operations and coordinate activities. This is thoroughly described in the Barman documentation, specifically in the "Setup of a new server in Barman" section (please, make sure you have read the documentation before proceeding here).

In this article we will go through two steps:

1. Setup of the primary connection between Barman and PostgreSQL (mandatory)
2. Setup of the streaming replication connection between Barman and PostgreSQL (optional)

For security reasons, we will use different users, with different passwords (randomly generated). Most importantly, we will also use .pgpass to safely store passwords, so that you do not have to define them in the connection strings.

Barman needs to be installed in a separate server (for simplicity called backup, with IP address 10.0.0.250) using RPM or Deb packages (as supported by 2ndQuadrant). Similarly, PostgreSQL is installed in another server (for simplicity called pg, with IP address 10.0.0.251), and listening on port 5432.

NOTE: please replace the above hostnames and IP addresses with the ones used in your environment throughout the document.

Barman needs a user with superuser privileges to be created in the PostgreSQL server in order to coordinate activities. This is mandatory. While you could simply use the postgres user, we recommend not to do it and use a separate user, specific for Barman. Let's proceed with it.

First select a strong and random password for the user. You can choose a tool like pwgen for example:

pwgen 20 1

In the backup server, as the barman user, create the ~barman/.pgpass file (if it does not exist) and add the following line:

pg:5432:postgres:barman:PASSWORD

NOTE: Make sure you substitute PASSWORD with the randomly generated one.

Make sure the file has the right permissions:

chmod 600 ~barman/.pgpass

In a separate terminal, connect to the PostgreSQL server via SSH and, as postgres create the barman user as follows:

createuser -s -P barman

When prompted, enter the random password that you have generated previously.

Make sure that pg_hba.conf in the PostgreSQL server allows access as barman user to the postgres database and only from the Barman server (10.0.0.250):

hostssl postgres barman 10.0.0.250/32 md5

IMPORTANT: We recommend that PostgreSQL is configured to accept encrypted connections. If it isn't you need to change hostssl into host. Also, make sure you use the right IP address for your environment.

As the postgres user reload the PostgreSQL server configuration:

psql -c 'SELECT pg_reload_conf()'

You can now verify that the barman user can connect to PostgreSQL from the backup server, as follows:

psql -c 'SELECT version()' -U barman -h pg postgres

You should receive an output similar to this:

version
PostgreSQL 9.6.10 on x86_64-pc-linux-gnu (Ubuntu 9.6.10-1.pgdg18.04+1), compiled by gcc (Ubuntu 7.3.0-16ubuntu3) 7.3.0, 64-bit
(1 row)

In case you plan to take advantage of streaming replication for the backups with pg_basebackup and/or WAL streaming with pg_receivewal/pg_receivexlog to reach best recovery point objectives (RPO), you need to create a user with replication privileges. As per the documentation, we will call it streaming_barman.

Similarly to the step above, generate another password with pwgen. In the the backup server, as the barman user, add the following line to the ~barman/.pgpass file:

pg:5432:*:streaming_barman:PASSWORD

NOTE: Make sure you substitute PASSWORD with the randomly generated one.

In a separate terminal, connect to the PostgreSQL server via SSH and, as postgres create the streaming_barman user as follows:

createuser -P --replication streaming_barman

Enter the randomly generated password when prompted.

Make sure that pg_hba.conf in the PostgreSQL server allows access via streaming replication as the streaming_barman user only from the Barman server (10.0.0.250):

hostssl replication streaming_barman 10.0.0.250/32 md5

IMPORTANT: We recommend that PostgreSQL is configured to accept encrypted connections. If it isn't you need to change hostssl into host. Also, make sure you use the right IP address for your environment.

As the postgres user reload the PostgreSQL server configuration to make changes effective:

psql -c 'SELECT pg_reload_conf()'

You can now verify that the streaming_barman user can connect to PostgreSQL via streaming replication from the backup server, as follows:

psql -U streaming_barman -h pg \
-c "IDENTIFY_SYSTEM" \
replication=1

You should receive an output similar to this:

systemid | timeline | xlogpos | dbname
6478545250863176024 | 19 | 188/CB114380 |
(1 row)

The above best practices will allow you to setup a more robust and safe backup environment for your PostgreSQL servers using Barman. Make sure that:

• you rely on the .pgpass file for storing passwords, rather than adding them to the configuration options;
• properly set client access in pg_hba.conf, preferably with in transit encryption.