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.
Procedure
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 172.168.100.5/32 md5 host vcloud vcloud 172.168.20.5/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 [3005] LOG: invalid connection type "The" 2020-02-13 21:10:27.186 UTC [3005] CONTEXT: line 97 of configuration file "/var/vmware/vpostgres/current/pgdata/pg_hba.conf" 2020-02-13 21:10:27.186 UTC [3005] LOG: pg_hba.conf was not reloaded 2020-02-13 21:11:23.924 UTC [6525] 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 [6526] 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 [3005] LOG: received SIGHUP, reloading configuration files
A very simple but useful fix!! Lesson learnt: Always read all the VMware documentation guide! 🙂