Most of the VMware products these days are standardised on PostgreSQL. Yes, you can still deploy vCenter for Windows, for instance, and use MS SQL or Oracle as a back-end database, but it’s now deprecated and vSphere 6.7 is the last release where it’s supported. Other products, like vRealize Automation are moving in the same direction.
VCSA, vRA, vRO are all distributed as appliances and shouldn’t be modified in any way by the end user. But I’ve had times before when I needed to directly connect to the PostgreSQL database to better understand certain parts of the product. One of the recent examples was encryption in vRO. I needed to ensure that the passwords I save in SecureString attributes (the ones shown as asterisks) in my workflows are not kept as plain text in vRO. So let’s see how I validated this assumption by looking at the vRO database.
vRO Database
I first SSH’ed into the appliance and connected to the database using PostgreSQL interactive terminal:
# psql vmware postgres
I then listed all database table names:
> SELECT * FROM pg_catalog.pg_tables;
When I found the table I was looking for, I listed its contents:
> SELECT * FROM vmo_workflowcontent;
And simply searched for my attribute name in the output, which was encrypted indeed.
Exporting the Database
You won’t always know what table you’re looking for, so the easiest way to go about it is to simply export the whole database in plain text and use search in a text file:
# su -m -c “/opt/vmware/vpostgres/current/bin/pg_dump -Fp vmware > /tmp/vmware.sql” postgres
“-Fp” here is for plain text (default is custom format, which is compressed), “vmware” is the database and “postgres” is the user.
VCSA and vRA Databases
You will find that database names aren’t the same for different products, for instance vCenter’s database name is “VCDB” (capital letters) and vRA is “vcac” (username is also “vcac”). So if you need to connect to VCSA database you will use the following syntax:
# psql VCDB postgres
For vRA it will look like this:
# psql vcac vcac
Then you can use the same approach demonstrated for vRO to read table data or simply export the whole database.
Conclusion
I hope it helps you with your tinkering adventures. Just make sure to use this only for research and not change anything in the database, unless specifically advised by GSS.
Niktips's Blog 