Connecting to PostgreSQL Database Backing VMware Products

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.

Advertisement

Unable to Delete vCenter Endpoint in vRealize Automation

vRealize Automation Error

More than once in my experience I’ve had a need to delete an endpoint in vRealize Automation. Maybe configuration has changed or you simply made a typo in vCenter hostname or credentials. Once you’ve specified vCenter address and saved the endpoint you can no longer change it (only delete and re-add).

But even when you try to delete it, you will get an error something along the lines of:

You cannot delete this endpoint because 1 compute resources and 0 storage paths use it.

CloudClient Error

There is a KB article that walks you through the process of how to do that using a special tool called CloudClient: Error “This endpoint is being used by # compute resources and # storage paths and cannot be deleted” when you attempt to delete an endpoint in vRA 7.x (2150548)

But even that approach not always work. When you run this command from the KB article “vra computeresource inactive list” you may get the following error:

Error: Something went wrong while processing your request. Please check the application logs for details.

Solution

There is almost no mention of this second error on the Internet and I can see how someone can keep banging his head trying to solve it, so I thought I’d share a solution here. And it’s simple – open a GSS ticket. They can delete the endpoint for you. If you see this error, there’s no other way that I know of to solve this problem without involving GSS.

Clean-up

You can see an error similar to the following in vRA logs if you didn’t stop proxy agents before deleting the endpoint:

Error processing ping response
Error occurred while executing stored proc usp_InsertUpdateHost The INSERT statement conflicted with the FOREIGN KEY constraint “FK_ManagementEndpoint_Host”. The conflict occurred in database “vRa_IaaS”, table “dbo.ManagementEndpoints”, column ‘ManagementEndpointID’.
The statement has been terminated.
Inner Exception: The INSERT statement conflicted with the FOREIGN KEY constraint “FK_ManagementEndpoint_Host”. The conflict occurred in database “vRa_IaaS”, table “dbo.ManagementEndpoints”, column ‘ManagementEndpointID’.
The statement has been terminated.

All you need to do to get rid of it is restart your proxy agents.

Conclusion

Hope this post saves someone the hassle of hours searching for the answer in blogs and forums.