Simple Scripts and Hacks: Connecting to a Jira Database

When it comes to the administration of systems and infrastructure, one should always be wary if they find themselves manually repeating the same process too many times. This is especially true when said manual process involves touching potentially security-related data like passwords. However, if you're connecting directly to a database, like the one your Jira application uses, you can gain an added layer of protection by wrapping the entire process in a script.

Ideally, this is tackled with a fully developed shortcut like a Python script with full input validation that can account for all of the gotchas that one has the foresight to imagine.

As many system admins are aware, though, it is often the case that the ideal world is something being moved toward rather than something already in place. In some cases, the limiting factor is time, and prioritizing it means that you must make the best of what you have until you can (eventually) circle back to building out your toolkit. Other times, the infrastructure you're working with could be locked in so that you do not have access to all of the bells and whistles that would allow you the best of all worlds.

As long as you understand the caveats you're working with as they pertain to scripting out reapeated processes, scripts can save countless minutes (which eventually add up to countless hours). They also remove the need to copy and paste a password, where there is only downside if something doesn't go as expected. ("Oops, that wasn't the gif I meant to paste into chat.") To that end, here is an example of something you could add to a .bashrc that will run in most linux environments* to allow you to jump directly into a postgres client.

(*There are far more powerful options in most modern systems, such as a stronger regex parser than basic sed, but the idea is to make this as portable as possible.)

jirapsqljump () {
    [ -z $1 ] && echo 'missing host' && return 0 || CFGHOST=$1
    [ -z $2 ] && CFGPATH=/var/atlassian/jira-data/dbconfig.xml || CFGPATH=$2
    CFG=$(ssh ${CFGHOST} "cat ${CFGPATH}")
    SQLP=$(sed 's/.*<password>\(.*\)<\/password>.*/\1/' <<< ${CFG})
    SQLU=$(sed 's/.*<username>\(.*\)<\/username>.*/\1/' <<< ${CFG})
    SQLH=$(sed 's/.*<url>[a-z:]\{1,\}\/\/\(.*\):\([0-9]\{1,\}\)\/\([A-Za-z0-9_]\{1,\}\).*<\/url>.*/\1/' <<< ${CFG})
    SQLN=$(sed 's/.*<url>[a-z:]\{1,\}\/\/\(.*\):\([0-9]\{1,\}\)\/\([A-Za-z0-9_]\{1,\}\).*<\/url>.*/\2/' <<< ${CFG})
    SQLD=$(sed 's/.*<url>[a-z:]\{1,\}\/\/\(.*\):\([0-9]\{1,\}\)\/\([A-Za-z0-9_]\{1,\}\).*<\/url>.*/\3/' <<< ${CFG})
    ssh -f -o ExitOnForwardFailure=yes -L ${SQLN}:${SQLH}:${SQLN} ${CFGHOST} sleep 10
    PGPASSWORD=${SQLP} psql -U ${SQLU} -h localhost -p ${SQLN} -d ${SQLD}
}

The only things that would serve as prerequisites for this are:

• The postgres client must be installed on the workspace you run this from.
• Your ssh connection must be configured and keyed properly so that you can normally connect to the application server with "ssh jirahostname".
• The user you connect with can either directly read the dbconfig.xml file or can do so via sudo (in which case, just make it "sudo cat" instead of "cat"!)

From there, the expected functionality is "jirasqljump hostname [path to dbconfig]" and line by line does the following:

1. Return if missing a host – change the return code to match your other functions accordingly.
2. Autofill the path so you can exclude it if you traditionally manage systems with the same data path for Jira applications – update CFGPATH accordingly.
3. Connect to the supplied host to slurp in the config file to a local variable.
4. Use sed to parse out the database password from said slurp – if your database password contains the string <password>, well, we're going to need something more like perl and less like sed for the regex parsing.
5. ...username...
6. ...hostname...
7. ...port...
8. ...database name...
9. Create a temporary tunnel through the host to the database port via a background ssh – if the port the host uses is also in use for you locally, simply update this (and the following connection line) to an available port (e.g. -L 9999:$SQLH:$SQLN).
10. Connect to the database via the tunnel (note: this will, in effect, hold the tunnel open so that instead of closing at the end of 'sleep 10' it will instead close immediately once the psql client connection closes).

If all is working correctly, it looks like this when put into action:

~$ jirapsqljump jira_dev_hostA
psql (13.2, server 10.14)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

jiradbdev=>