Overview

Every now and then I see a question come up that prompts me to do a little digging/experimenting. This brief article will address such a question. Last week, I saw the following request posted by a colleague:

Has anyone managed to configure the SQL plug-in with the embedded vRO PostgreSQL database?

For those unfamiliar, this is regarding the VMware vRealize Orchestrator (vRO) product that I used to work with regularly back when I ran the vCOTeam.info website (now archived here: https://vcoteam.github.io )

The embedded Orchestrator in vRealize Automation uses a PostgreSQL Database to store all the workflow information as well as many other bits of information. On occasion, it can prove helpful to access this database directly. In earlier versions (prior to 8.x), accessing this database was a matter of running a CLI tool on the server to retrieve and decrypt the password in order to access the database. With 8.x, things get slightly more tricky since Orchestrator and the PostgreSQL Database are all running as Kubernetes pods within the Automation server.

Q: Why would you want a database added to the SQL plug-in? A: Once a database has been added to the SQL plug-in, you can leverage a library of workflows that allow you to query/update various tables. In this particular case, adding the embedded vRO database, you could potentially clean up workflow executions that appear hung, even though they are cancelled and no longer actually running.

In any case, let’s move on towards getting this figured out! Please note that the remainder of this article assumes a proficient knowledge of Orchestrator - this is NOT an introductory article!

Step 1: Obtain database password

In order to leverage the SQL plugin and jdbc url generator, you need the plain text passwrd for the “postgres” user. On version 8.x, this means we have to dig into kubernetes pods, find files, and extract the relevant details. In order to perform these steps, you must have administrative/root access to the server.

From a Linux system (probably MacOS too) with SSH access to the vRA server, you can retrieve the password using the following command:

1ssh -q root@VRA_SERVER_ADDRESS 'kubectl -n prelude exec -it postgres-0 -- /bin/bash -c "cat /run/populate_db.sql"' 2>/dev/null | grep 'ALTER ROLE \"postgres\"' | cut -d"'" -f2

What’s happening in the above command:

  1. You establish an SSH connection to the vRealize/Aria/VCF Automation server (yes, it has had many names. The current as of this article is VMware Cloud Foundation Automation )
  2. You pass in a kubectl command to execute a command INSIDE the postgres-0 pod (container running PostgreSQL)
  3. The command outputs the contents of the file that was used to initialize the database and setup the account for the postgres user
  4. The output of the command is searched for the line that sets the postgres user password and then returns only the unencrypted password

Alternatively, if you first SSH to the vRA server as root, then you can run the following command:

1kubectl -n prelude exec -it postgres-0 -- /bin/bash -c "cat /run/populate_db.sql | grep 'ALTER ROLE \"postgres\"' | cut -d\"'\" -f2"

Step 2: Add the Orchestrator database to the SQL Plugin

For the “Add a database” workflow:

Add a database

1Name: embedded-vro-pgsql
2Database type: PostgreSQL
3Connection URL: jdbc:postgresql://postgres:5432/vco-db
4
5username: postgres
6password: (plain text value returned from above command)
7sessionMode: Shared Session

You may set the name to whatever you prefer, I chose “embedded-vro-pgsql”

Add a database

When specifying “Session mode”, be sure to use “Shared Session”. This will always use the specified user name and password you enter in this form.

Now, when you explore your Orchestrator Inventory, you can see all the tables of the embedded database:

Orchestrator DB added to SQL Plugin

Step 3: Leverage the library and write your own workflows

You can now start using some of the library workflows to create your own solutions.

Orchestrator SQL Plugin library workflows

For example, the “Execute a custom query on a database” library workflow has been added to the following workflow and renamed to something more meaningful: Create record in vlpClasses. You can see that the first element of the workflow is a simple scriptable task named “build insert query”.

create-class-attendee-record workflow

Conclusion

The SQL Plug-in can be very useful in Orchestration. It provides you the ability to tie in Database record create, read, update, delete (CRUD) operations to other events and operations. Whether you have received new data from a webhook, another database, another plugin, or an API call to some other system, that data can then be parsed and applied to one or more databases using this plugin.

The use-case described earlier in this post is (accessing the embedded vRO database) is a less common case, but can prove handy in troubleshooting.

In any case, I hope that this article proves useful to someone. If nothing else, it will serve as a reference to myself and the person who originated the question!

My Gear

Please Note: As an Amazon Associate, I earn from qualifying purchases.

Workstation Build: