How to manually edit vRealize Automation vPostgres database

Published: August 23, 2017 How-to vRealize-Automation vRealize-Suite

Purpose:

Did you ever have the need to manually edit the vRealize Automation vPostgres database? This post covers exactly that topic. It provides detailed steps on how you can access the database and modify it. So without further ado start reading the post.

Introduction:

There are times when we are forced to modify the inbuilt database of vRA server. Whereas very little documentation is available around it. This is for a good reason though. Modifying the database manually is very dangerous. It can render the entire environment useless. So DO NOT do it or at least avoid doing it. Unless you are hundred percent sure of what you are doing. I will consider you are warned enough. So, now I will go ahead and explain exactly how you can do it.

Steps to modify vRealize Automation vPostgres Database:

Follow the below steps in sequence to modify the database.

Before I explain the procedure, let me explain the use case first. I had the following problem:

Issue: I had one custom resource type item (SQL:Database) in vRA. I had mistakenly deleted the original database from DB. Also, removed the Database entry from vCO SQL Databases list. So, now the item existed only in vRA DB.

Action Taken: I created a Remove Database custom resource action in vRA and applied it on the item. It did not help.

So the only option left was to manually remove the entry from vPostgres database. If you are stuck in a similar situation. Then this will be helpful for you.

The logical steps are:

First, enable access to vPostgres database
Then find out the password of the postgres user
Connect to the database and complete the tasks

In my case, I wanted to connect to the database remotely from another machine. Use pgAdmin as a GUI tool and do the task from there. In your case, you may want to just log into the database locally and edit the database from SQL prompt. I chose GUI tool as I wanted to visually explore the database.

Enabling access to vPostgres Database:

First, log into the vRA appliance or SSH to it using root account.

Next edit pg_hba.conf file

vi /storage/db/pgdata/pg_hba.conf

Check for the presence of the line

local all postgres trust

What this enables, is using the local machine you can use vcac user to login to the database. Since I wanted to access it remotely, I added the following line:

host all all 10.110.23.148/24 trust

The above line allows any user from the machine 10.110.23.148 to connect to the database.

In the next step, edit the postgresql.conf file.

vi /storage/db/pgdata/postgresql.conf

In that file add the following line

#listen_addresses = 'localhost' # what IP address(es) to listen on;listen_addresses = '*'

What I am doing here, is listening to everything.

The above steps will let me connect to the database from my machine at 10.110.23.148.

Get the vcac user password:

In the next stage, we need to get the password for the vcac user. Detailed steps for doing so is provided in this VMware KB article.

Run the following command

cat /etc/vcac/server.xml |grep password="*"

You get an output similar to:

<Resource abandonWhenPercentageFull="0" auth="Container" driverClassName="org.postgresql.Driver"     factory="com.vmware.vcac.tools.tomcat.db.CafeEncryptedDataSourceFactory" fairQueue="false" initialSize="10"     jdbcInterceptors="ResetAbandonedTimer" jmxEnabled="true" logAbandoned="true" logValidationErrors="true" maxActive="100"     maxIdle="20" maxWait="120000" minEvictableIdleTimeMillis="60000" minIdle="10" name="jdbc/cafe"     password="s2enc~646fCCJWPaSYJe91XKBpkrc1E+3N83n7ZIF3a+PGEaI=" removeAbandoned="true" removeAbandonedOnBorrow="true"     removeAbandonedOnMaintenance="true" removeAbandonedTimeout="240" suspectTimeout="60" testOnBorrow="true"     testOnReturn="false" testWhileIdle="true" timeBetweenEvictionRunsMillis="30000" type="javax.sql.DataSource"     url="jdbc:postgresql://127.0.0.1:5433/vcac" username="vcac" validationInterval="30000" validationQuery="select 1"     validationQueryTimeout="2"/>

Note the encrypted value after the password field. In this case the value is "s2enc~646fCCJWPaSYJe91XKBpkrc1E+3N83n7ZIF3a+PGEaI=". In the next step we will find the actual password.

/usr/lib/vcac/tools/config/bin/vcac-config prop-util -d --p s2enc~646fCCJWPaSYJe91XKBpkrc1E+3N83n7ZIF3a+PGEaI= | less

This will give you the password.

Connecting to vPostgres using pgAdmin:

Now all that is left is to connect to the database using the user id and password. Create a new connection to the vPostgres database and connect to it. Post connection it looks like the following:

Note the expanded view on the left. Next, we will explore the tables:

Modifying the Database:

Note the vRA items screen. It shows the information.

Since I have resources which are custom resources. So the table I am interested in is asd_resource. This will have a list of items for custom resource. Now one of deleting is object by object. We need to follow this if say we have 10 items and we want to delete one or two of them.

SELECT * from asd_resource WHERE name = 'sajalpgdb';

It will show the information with the resource id as well. Note the id. Try to delete the item by running the following command:

DELETE from asd_resource WHERE name = 'sajalpgdb';

In all probability, it is going to fail showing a list of other tables where the item exists with the same name. For example, the table containing information about requests has reference to this. For this reason, the entry can not be deleted. So, you need to delete those references first and then, at last, delete the reference from this table.

For example, provided below a list of possible places:

SELECT * from cat_resource WHERE name = 'sajalpgdb';SELECT * from cat_resource_owners WHERE resource_id = 'c7ae9023-da0c-4960-b279-ae4dea6d0e8d';DELETE from cat_resource_owners WHERE resource_id = 'c7ae9023-da0c-4960-b279-ae4dea6d0e8d';DELETE from cat_request WHERE resource_id = 'c7ae9023-da0c-4960-b279-ae4dea6d0e8d';

Likewise, it will go one, until the time we clear last of them.

Since I have a lab environment which does not have much information. So I used another command which is very helpful:

TRUNCATE asd_resource CASCADE;TRUNCATE cat_resource CASCADE;

What this does (TRUNCATE), it will clear the table from all information. Also, it will clear all the other references to these data (CASCADE).

Post the operation my vRA items list is blank:

Conclusion:

This is an easy and quick hack to tweak the inbuilt database. But please remember, this is destructive and strictly forbidden. Take responsibility and then perform these operations. Only do these as last resort and at that time also let VMware Support team do the work. Only do yourself if you are 200% sure of what you are doing.

Hope after all that warning you still found this to be useful :).