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. We recommend installing the agent on the same server/machine where Veeam is installed. However, it can be installed on a different machine in the same environment that has access to the Veeam database, but you will need to create a PostgreSQL user to complete the connection.

If the agent is installed on the same machine as Veeam B&R

The advantage of installing the Bocada Cloud data collection agent on the same machine as the Veeam B&R database is that you can use the same user credentials (username and password) that were used during the setup of Veeam. By default, Veeam uses Windows Authentication. Alternately, you can create a new PostgreSQL user as described below.  

Creating a PostgreSQL user

If the Bocada Cloud data collection agent is installed on a different machine than the Veeam B&R database, you 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:  PowerShell Script PostgreSQL Bocada User
2. On the machine where Veeam B&R is installed, open Windows PowerShell as an Administrator:
   
   
3. PowerShell: Set-ExecutionPolicy
   This may be required depending on previous configurations. More information can be found in Microsoft Docs.
   1. Run the command "Set-ExecutionPolicy Unrestricted -Scope CurrentUser".
      
      
   2. Confirm policy change with a "Y"
      
      
4. Access the folder where you downloaded the PowerShell script and run it:
   .\Script.For.Veeam.Postgres.UserCreation.ps1
   
   
5. The introduction text will be displayed, and the script will ask you to enter the name of the new PostgreSQL user that will be used by Bocada Cloud to connect to Veeam’s PostgreSQL database. 
   
   
6. If you hit <Enter>, the name of the new user will be bocada
7. You will be asked to provide a password for the user, and then confirm it:
   
8. In the last step, the script will try to use the provided credentials to make a connection attempt to the PostgreSQL database: 
   
   

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>

Successful reload will display server signaled:

Create the 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>