How do I create a PostgreSQL user for Veeam Backup & Replication data collection?

Learn how to automatically or manually create a PostgreSQL user to connect Bocada Cloud to Veeam B&R

Connecting to Veeam B&R on a PostgreSQL database

The Bocada Cloud Data Collection Agent requires access to the Veeam Backup & Replication (B&R) database to collect data for monitoring and reporting. Customers using Veeam B&R on a PostgreSQL database must create a PostgreSQL user to complete the connection. Click here for step-by-step instructions to connect Bocada Cloud to Veeam B&R.

There are two ways to create the PostgreSQL user -- either through an automated approach using a PowerShell script, or manually. 

Automated user creation using a PowerShell Script

1) First, download the PowerShell script and copy it to the machine where Veeam B&R is installed:

https://backup-api.demo-cloud.bocada.com/Scripts/Script.For.Veeam.Postgres.UserCreation.ps1

2) On the machine where Veeam B&R is installed, open Windows PowerShell as an Administrator: 

3) Access the folder where you downloaded the PowerShell script and run it:

.\Script.For.Veeam.Postgres.UserCreation.ps1

Temporary Update to hba_pg.conf file

Prior to making any changes to the pg_hba.conf file, ensure you have made a backup copy, e.g., copy to pg_hba_<date>.conf.

First, add a temporary entry in the pg_hba.conf file to enable the addition of a user:

Reload pg_hba.conf (1st time)

pg_ctl.exe reload –D <Full_path_to_Veeam_DB_Data_Directory>

Create a new PostgreSQL user

Next, create the PostgreSQL Database User and password using the following command located the in Veeam DB Bin directory:

createuser.exe -p <port> -h <Veeam_DB_Host_IP> -U postgres –P –s <new username>

Notes for creating a user:

• The default port for PostgreSQL is 5432. Use this value unless a different port is being used.
• The username must be all lower case. No capital letters may be used.
• Use the IP address of your Veeam server.
• Once you run the command above, you will be asked to “Enter password for new role”, then “Enter it again” (see screenshot below).
  
  If the system asks for a password a third time, there is an error and you should review all prior steps. Possible reasons for an error are that the new entry in the pg_hba.conf is incorrect, or the pg_hba.conf has not been reloaded.

The following example command will add a user named bocada:

Reduce access in pg_hba.conf

Update the temporary  pg_hba.conf entry added above to reduce access to only the specific user added from the IP Address of the Bocada Cloud Data Collection Agent, for example:

Reload pg_hba.conf (2nd time)

Reload the pg_hba.conf file by running the following command from the Veeam DB Bin directory:

pg_ctl.exe reload –D <Full_path_to_Veeam_DB_Data_Directory>