We were attempting to access vCD 9.7 appliance database from a server running PGAdmin without success until we followed some of the steps described in VMware Article: https://docs.vmware.com/en/vCloud-Director/9.7/com.vmware.vcloud.install.doc/GUID-3A3BB3A9-F6F3-47BA-A785-2B99882A692B.html
During a migration to the vCloud Director appliance, or if you plan to use a third party database backup solution (in our case PGAdmin), you might want to enable external access to the embedded vCloud Director database.
1.Log in directly or SSH to the primary cell as root.
2.Navigate to the database directory, /opt/vmware/appliance/etc/pg_hba.d/.
Note: You will find an existing file call README with the following content:
3. Create a text file containing entries for the target external IP addresses similar to:
#TYPE DATABASE USER ADDRESS METHOD host vcloud vcloud 18.104.22.168/32 md5 host vcloud vcloud 22.214.171.124/32 md5
New entries are appended to the dynamically updated pg_hba.conf file (file location: /var/vmware/vpostgres/10/pgdata/pg_hba.conf).
PGadmin & Postgres Errors
If pg_hba.conf file is not properly configured you will see the following error while trying to connect vcloud database from PGAdmin
vPostgres log file:
vcd-appliance@root: /var/vmware/vpostgres/10/pgdata/log 2020-02-13 21:10:27.186 UTC  LOG: invalid connection type "The" 2020-02-13 21:10:27.186 UTC  CONTEXT: line 97 of configuration file "/var/vmware/vpostgres/current/pgdata/pg_hba.conf" 2020-02-13 21:10:27.186 UTC  LOG: pg_hba.conf was not reloaded 2020-02-13 21:11:23.924 UTC  FATAL: no pg_hba.conf entry for host "10.10.10.2", user "vcloud", database "vcloud", SSL on 2020-02-13 21:11:23.926 UTC  FATAL: no pg_hba.conf entry for host "0.10.10.2", user "vcloud", database "vcloud", SSL off 2020-02-13 21:11:28.442 UTC  LOG: received SIGHUP, reloading configuration files
A very simple but useful fix!! Lesson learnt: Always read all the VMware documentation guide! 🙂