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.
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.
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:
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.
First, log into the vRA appliance or SSH to it using root account. Next edit pg_hba.conf file
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.
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.
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"> </resource>
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.
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:
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:
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 :).