Simple containerised MariaDB setup

 

 Quite often I might want to try out some little network-connected tool or technology and to do this I try to run them in a container for the benefit of their config isolation. In most cases I want to do something quick and easy and I start-off by hunting-down a container image on DockerHub and then fire that up under Podman (would you like one of my pamphlets about why Podman is a superior religion to Docker?).

    More often than I'd like to admit, my heart will sink when I see in the documentation for these random container images something like "just set the db-name/user/password variables and it'll work". I figure that whatever it was I was looking at is more complicated than I'd hope and I give-up and try something else (my other religion is "You probably don't need an RDBMS").

    However in the back of my mind there's a little voice saying "You lazy sod! Why don't you just have a scratch MariaDB server ready to go that can be trashed and re-created easily?". So that's what this post is about.

Here be (security) dragons:

    The venerable history of SQL RDBMS systems is littered with the shipwrecks of disasters past. The script below skips a lot of "good security" with the intent of being a simple scratch/test system. Some obvious sins are:

• The user and root-user passwords are the same
• There's a simple baked-in default password (that you're encouraged to override if you want to increase security)
• The passwords are specified as command line parameters multiple times (They'll show-up in "ps" listings)
• The password will be printed out in clear-text during the script's execution (this is a deliberate design choice, not a "bug")
  

    Note however that this is not an exhaustive list of security issues (I expect there are emergent issues I'm not aware of at this time). Nevertheless, the fact it only runs on a Podman bridge network gives you a little more security than if you were running it on your server's external interface. Running it on a shared system with untrusted users puts your DB data at risk and you should probably never connect the results of this script to The Internet in general (unless you've done your own research/improvements and take responsibility for security).

    In other words "No warranty (implied or otherwise)", etc.

The script:

#!/bin/bash
#
# Make a MariaDB instance in a low-security Podman container so that something else can use it.
# Note that all parameters are positional and optional. You probably only need to set the
# first one or two in most circumstances.
#
# Usage:
#       makemariadb.sh app-name db-password db-user-name db-host-name db-name db-port db-network-name

appname=${1:-application}
password=${2:-sekrit}
user=${3:-${appname}}
dbhost=${4:-${appname}-mariadb}
dbname=${5:-${appname}}
port=${6:-3306}
netname=${7:-${dbhost}}

echo -e "Application: ${appname}\nDatabase password: ${password}\nDatabase user: ${user}\nDatabase host: ${dbhost}\nDatabase name: ${dbname}\nDatabase port: ${port}\nNetwork name: ${netname}\n--"

echo -n "Create network: "
podman network create "${netname}"
echo -n "Run Mariadb container: "
podman run -d --net "${netname}" --name "${dbhost}" -e "MARIADB_USER=${user}" -e "MARIADB_PASSWORD=${password}" -e "MARIADB_DATABASE=${dbname}" -e "MARIADB_ROOT_PASSWORD=${password}" mariadb:latest --port "${port}"
echo "Busy-waiting for DB to be ready..." # Annoying, but this is apparently the best way to do it
until podman exec -it "${dbhost}" mariadb "-u${user}" "-p${password}" -e "USE ${dbname}"; do
  echo "DB not ready yet, waiting a bit more..."
  sleep 5
done
echo "DB seems to be up and accepting connections"
echo "DB can be accessed inside its container with:"
echo -e "\tpodman exec -it \"${dbhost}\" mariadb \"--user=${user}\" \"--password=${password}\""
echo "...or from a new container with:"
echo -e "\tpodman run --rm -it --net \"${netname}\" mariadb:latest mariadb -h \"${dbhost}\" -u \"${user}\" \"-p${password}\" \"${dbname}\""
echo "To remove the DB, firstly ensure any and all client containers are stopped/removed, then:"
echo -e "* Stop and remove the DB container with:\n\tpodman stop \"${dbhost}\"&&podman rm \"${dbhost}\""
echo -e "* Remove the network with:\n\tpodman network rm \"${netname}\""

Script breakdown:

Initialisation:

    Firstly seven symbolic variables are set from the first seven positional parameters (with sane defaults for all of them):

appname=${1:-application}
password=${2:-sekrit}
user=${3:-${appname}}
dbhost=${4:-${appname}-mariadb}
dbname=${5:-${appname}}
port=${6:-3306}
netname=${7:-${dbhost}}

echo -e "Application: ${appname}\nDatabase password: ${password}\nDatabase user: ${user}\nDatabase host: ${dbhost}\nDatabase name: ${dbname}\nDatabase port: ${port}\nNetwork name: ${netname}\n--"

    So "appname" is the name for the target application (default: "application" and also by default becomes the stem for the names of the container, network, database and user). "password" has a pretty simple/insecure default ("sekrit"). As such, you should probably nominate values for the first one or two parameters in most situations. The rest of the parameters are all derived sensibly from ${appname} or use a standard value (but you're free to override them if you like).

    The initialisation wraps-up by printing-out all parameters so they can be easily copy/pasted to create some other container to access the newly created database.

Create the network:

    The script creates a private Podman network bridge to run the database server on. Any containers that need to talk to the DB should be connected to this with the "--net "${netname}"" parameter later on.

echo -n "Create network: "
podman network create "${netname}"

Run the DB container:

echo -n "Run Mariadb container: "
podman run -d --net "${netname}" --name "${dbhost}" -e "MARIADB_USER=${user}" -e "MARIADB_PASSWORD=${password}" -e "MARIADB_DATABASE=${dbname}" -e "MARIADB_ROOT_PASSWORD=${password}" mariadb:latest --port "${port}"
echo "Busy-waiting for DB to be ready..." # Annoying, but this is apparently the best way to do it
until podman exec -it "${dbhost}" mariadb "-u${user}" "-p${password}" -e "USE ${dbname}"; do
  echo "DB not ready yet, waiting a bit more..."
  sleep 5
done
echo "DB seems to be up and accepting connections"

    The command line to actually start the container is a bit of a mouthful, but makes sense if you parse through the parameters:

• run - Run a new container (possibly downloading the image first)
• -d - Detach from the running container and return to the command line
• --net "${netname}" - Connect the container to the (recently created) private network bridge
• --name "${dbhost}" - Names the new container and implicity becomes the name of the server on the private network bridge
• -e ... - Set a bunch of environment variables for the MariaDB server setup inside the container in order to set the user name, user password, database name and root password (See MariaDB documentation).
• mariadb:latest - Name and version of the container image to download and run. So this is the latest version of the MariaDB image.
• --port "${port}" - Set the TCP port number the MariaDB server should listen on. 3306 is the standard port, which is why it's the default for this script

    Unfortunately once the container is started, it takes quite some time for the MariaDB server to become ready to receive connections. There does not appear to be any simple/obvious way to synchronise on when the DB is ready, so the script "busy-waits" trying to connect to the DB repeatedly in a loop with a 5 second delay until it becomes ready. Note that there's no way for this script to detect a fatal error in the container initialisation, so in that case it will probably wait forever.

Print out a bit of helpful advice:

    Because I expect to run this infrequently with minimal recollection of how to deal with it, I thought I'd print out a simple "here's how to use it and clean it up afterwards" documentation at the end.

echo "DB can be accessed inside its container with:"
echo -e "\tpodman exec -it \"${dbhost}\" mariadb \"--user=${user}\" \"--password=${password}\""
echo "...or from a new container with:"
echo -e "\tpodman run --rm -it --net \"${netname}\" mariadb:latest mariadb -h \"${dbhost}\" -u \"${user}\" \"-p${password}\" \"${dbname}\""
echo "To remove the DB, firstly ensure any and all client containers are stopped/removed, then:"
echo -e "* Stop and remove the DB container with:\n\tpodman stop \"${dbhost}\"&&podman rm \"${dbhost}\""
echo -e "* Remove the network with:\n\tpodman network rm \"${netname}\""

In the simplest case, you can connect to the database by running the client inside the running DB server's container with 'podman exec -it "${dbhost}" mariadb "--user=${user}" "--password=${password}"'. You can also start a new container to talk to the DB through the private network bridge with 'podman run --rm -it --net "${netname}" mariadb:latest mariadb -h "${dbhost}" -u "${user}" "-p${password}" "${dbname}"'.

The final part's about how to clean-up the DB and network by stopping/removing connected containers, then the network bridge.

So how do I actually access this database?

This is hinted-at in the "helpful advice" output. The goal of this script is to set up a DB server that can be used by some other application container. So once the DB is running, you'll need to start your application container and give it the "--net ${netname}" parameter then also tell your application the name of the DB host (The DB container name), the database name, the user name and the user's password. How you tell your application this information is up to your application's developers, so you'll have to work that out for yourself.

How to clean it up afterwards?

Again, this is hinted at in the "helpful advice" output. Essentially all client application containers should be stopped and removed. Next the MariaDB container should be stopped and removed. Finally the private network bridge can also be removed (but note this will normally fail if you haven't removed all containers connected to it first). To summarise:

• Ensure all client containers are stopped and removed
• Stop and remove the DB container
• Remove the network bridge (requires all connected containers to have been successfully removed beforehand)