Juju 4.0 shortcut to get the DQLite repl from the command line

Below is a script that I have been using to quickly get to the DQLite repl from the command line while testing Juju 4.0. Any suggestions for improvements are welcome!

#!/bin/bash

# First argument is the machine on the controller model to connect to.
# The default is machine 0
MACHINE=${1:-0}
# Second argument is the database to connect to, this is either "controller"
# for the controller database or the model UUID for a model specific database.
# The model UUID can be found with `juju show-model`.
DB_NAME=${2:-controller}
# Third argument indicates if you are on Kubernetes. If this argument is not
# blank then sudo is not prepended in from of the commands.
CAAS=${3}

if [[ -z "${CAAS}" ]] then
    # Don't use sudo on Kubernetes.
    SUDOSTRING="sudo"
fi

CMDS=$(cat << EOF
$SUDOSTRING DEBIAN_FRONTEND=noninteractive apt -q -y update 
# Install the add-apt-repository command.
$SUDOSTRING DEBIAN_FRONTEND=noninteractive apt -q install -y software-properties-common 
# Add and install DQLite.
$SUDOSTRING DEBIAN_FRONTEND=noninteractive add-apt-repository -y ppa:dqlite/dev 
$SUDOSTRING DEBIAN_FRONTEND=noninteractive apt -q install -y dqlite-tools libdqlite-dev
# Extract the controller cert and key from agent conf
$SUDOSTRING awk '/^controllercert:/{l=1;next;print;next} /^\S/{l=0} l' /var/lib/juju/agents/machine-$MACHINE/agent.conf | sed "s/^ *//" > dqlite.cert
$SUDOSTRING awk '/^controllerkey:/{l=1;next;print;next} /^\S/{l=0} l' /var/lib/juju/agents/machine-$MACHINE/agent.conf | sed "s/^ *//" > dqlite.key
echo "-------------------------------------------------------------------------"
echo ""
echo "                             WARNING!"
echo ""
echo "You're attached to the live database. There currently is no audit trail"
echo "when running any commands. You could end up corrupting the database."
echo "Ensure you make a backup before running any commands."
echo ""
echo "--------------------------------------------------------------------------"
echo ""
$SUDOSTRING dqlite -s file:///var/lib/juju/dqlite/cluster.yaml -c ./dqlite.cert -k ./dqlite.key $DB_NAME
EOF
)

echo "Connecting to controller and installing dependencies..."
juju ssh -m controller "${MACHINE}" "${CMDS}"

This script will connect to the controller database by default.

I have this script aliased as juju sql, you can do this by saving the script as juju-sql and putting it on your path: e.g. sudo ln -s <path to script>/juju-sql /bin/juju-sql.

4 Likes

I really wanted to get something like this in jhack! you could contribute it if you like! bonus points if you can also put in there something equivalent for juju 3

For getting into mongo in juju 3 there is this post that has a similar script for that

There’s almost definitely a way to parse yaml files without having to install an extra dependency.

it’d defo save a lot of time running this script first time if this could be replaced

grep and awk maybe? Something like this (disclaimer I sawk at awk)

sudo grep 'controllercert:' /var/lib/juju/agents/machine-$MACHINE/agent.conf | awk -F': ' '{print $2}' | xargs -I% echo % > dqlite.cert

This won’t do because the cert and key are multi-line strings.

An alternative would be to use the python stdlib.

Something like

$ sudo python3 -c '
import yaml
with open("/var/lib/juju/agents/machine-0/agent.conf") as ifh:
  print(yaml.safe_load(ifh)["controllercert"])
'

would probably work. It would make the script a little cumbersome, but imo worth it to save the dep

So the full script could look like:

#!/bin/bash

MACHINE=${1:-0}
DB_NAME=${2:-controller}

CMDS=$(cat << EOF
sudo DEBIAN_FRONTEND=noninteractive add-apt-repository -y ppa:dqlite/dev
sudo DEBIAN_FRONTEND=noninteractive apt install -y dqlite-tools libdqlite-dev

sudo python3 -c "
import yaml
with open(\"/var/lib/juju/agents/machine-$MACHINE/agent.conf\") as ifh:
  print(yaml.safe_load(ifh)['controllercert'])
" | xargs -I% echo % > dqlite.cert

sudo python3 -c "
import yaml
with open(\"/var/lib/juju/agents/machine-$MACHINE/agent.conf\") as ifh:
  print(yaml.safe_load(ifh)['controllerkey'])
" | xargs -I% echo % > dqlite.key

sudo dqlite -s file:///var/lib/juju/dqlite/cluster.yaml -c ./dqlite.cert -k ./dqlite.key $DB_NAME
EOF
)

echo "Connecting to controller and installing dependencies..."
juju ssh -m controller "${MACHINE}" "${CMDS}"

(if you’re happy ignoring a bit of nasty code repetition)

What do you think @aflynn

Nice, this looks good, though to get this working on kubernetes we’re going to have to come up with some incantation that works with the limited toolset included in the controller container. I know this includes cat, grep, awk and sed so at least we have something. I’ve got a version of the script where the rest of it works in k8s, just not that yet.

If you go back to one of my posts, awk can do the multiline work pretty well. I don’t remember the magic, but you can write an awk script that finds a keyword, then prints out lines until it finds the next keyword. That isn’t a yaml parser, but it does let you do multiline.

has this snippet:

awk '/^cacert:/{l=1;next;print;next} /^\S/{l=0} l' agent.conf | sed "s/^ *//" > /tmp/ca.cert

(Look for a line that starts with cacert: and print out all lines until you find one that doesn’t start with whitespace)

Solid, added to the script

The script currently doesn’t work on Kubernetes. This is because in 4.0 with juju ssh the user does not have root. I’m currently looking into this.

Separately to that, if anyone has an idea how to detect in the script if we are in Kubernetes automatically that would be great. Right now you need to pass a third argument which is a little janky.

I think a way to do it is to not rely on the machine container to run dqlite client.

What do you need are the file cluster.yaml and certs extracted from agent.conf. So you only needs both of those file.

Both file are readable without any particular right, so we can extract those from kubectl command.

I see also that the dqlite cluster (in cluster.yaml) listen on a local adress (127.0.0.1), but I doubt it doesn’t expose any sort of port because it’s a cluster (so it has non local node).

Maybe a way to access to the db in K8s would be, from our laptop (or even a docker image with the right tools)

  • install dqlite client / yq / whatever
  • run some kubectl/juju ssh commands to get required information (like endpoint, certs)
  • connect from our laptop to the dqlite cluster.

If we rely only on juju ssh and not on kubectl, we may be provide a script witch works with any juju deployment: the challenge is to find the right endpoint.

thought: Why does the user have root on machines controller and not on k8s controller ? Is it expected ? Because, if I look on the issue from my point, I would see that user shouldn’t have root in any case or in all case.

@aflynn

Can I suggest moving MACHINE to the 2nd or 3rd param? MACHINE is very unlikely to change, much less than DB_NAME or CAAS.

Having to provide a 0 to change DB_NAME to a specific model is just an annoying paper cut

It may also be useful to change DB_NAME to MODEL_NAME, and lookup the db name with juju show-model if it’s provided. What do you think?