Setting up pgAdmin
- Download & Install the pgAdmin Tool: https://www.pgadmin.org/download/
- SSH into primary appliance
- 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=
- Decrypt the password:
vcac-config prop-util -d --p 's2enc~jczzANwNk35PNxcUuRjlGUIe9uF6y/ZkQfbm41j2Ln8='
- The result will look similar to:
EbjQVLiOypXHO3Zl
- The password can be saved in the PGPASSWORD environment variable:
export PGPASSWORD="<HASH>"
- 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})
- 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
- Edit pg_hba.conf changing md5 to trust for vcac:
vi /var/vmware/vpostgres/current/pgdata/pg_hba.conf
- Reload vPostgres configuration:
service vpostgres reload
- 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
- Use pgAdmin to interact with the database as necessary.
- Revert the pg_hba.conf changes when pgAdmin access is no longer needed.