Configure pgAdmin to connect to a vRealize Automation instance

Products

• VMware Aria Suite

Issue/Introduction

Provide detailed instructions on setting up external connections to vRealize Automation to allow remote pgAdmin connections to the embedded vPostgres database.

Environment

VMware vRealize Automation 7.x

Resolution

Setting up pgAdmin

1. Download & Install the pgAdmin Tool:  https://www.pgadmin.org/download/
2. SSH into primary appliance
3. Retrieve the vPostgres password:

xpath /etc/vcac/server.xml "//@password" 2>&1 | grep password | cut -d'"' -f2
The result will look similar to:
s2enc~jczzANwNk35PNxcUuRjlGUIe9uF6y/ZkQfbm41j2Ln8=

4. Decrypt the password:

vcac-config prop-util -d --p 's2enc~jczzANwNk35PNxcUuRjlGUIe9uF6y/ZkQfbm41j2Ln8='

4. The result will look similar to:

EbjQVLiOypXHO3Zl

5. The password can be saved in the PGPASSWORD environment variable:

export PGPASSWORD="EbjQVLiOypXHO3Zl"

6. Log into vPostgres with the password:

/opt/vmware/vpostgres/current/bin/psql -h localhost vcac vcac

 

NOTE: This should connect to the database without asking for a password.
NOTE: Additional Tip: Add these two lines to the logged in user .profile in the vRealize Automation appliance so that it is executed at login:

pg_password=$(xpath /etc/vcac/server.xml "//@password" 2>&1 | grep password | cut -d'"' -f2)
export PGPASSWORD=$(vcac-config prop-util -d --p ${pg_password})

7. Update pg_hba.conf with new trust settings for external connections:

echo "host all all 0.0.0.0/0 trust" >> /var/vmware/vpostgres/current/pgdata/pg_hba.conf

8. Edit pg_hba.conf changing md5 to trust for vcac:

vi /var/vmware/vpostgres/current/pgdata/pg_hba.conf

9. Reload vPostgres configuration:

service vpostgres reload

10. Open the pgAdmin tool and create a server with hostname (vRAApplianceFQDN or ipAddress) and the Password from step 2.  The database connection should be successful
11. Use pgAdmin to interact with the database as necessary.
12. Revert the pg_hba.conf changes when pgAdmin access is no longer needed.

Additional Information

Impact/Risks:
This knowledge article includes directions on how to query a live vPostgres database embedded on the vRealize Automation appliance.  If manual updates to the database are expected, ensure a valid backup is generated before any changes.