The Problem
I used to follow the typical docker-compose solution for hosting databases, spin up a new database instance for each new application being hosted. This works reasonably well for starting out, but I ran into issues with storage and being able to take reliable backups (outside of copying the data folder) which was further exacerbated when I moved my set-up to kubernetes
. I was unable to use NFS shares reliably for DB storage, backups were no longer “simple”. I did run zalando and CrunchyData postgres K8s operators with varying levels of success, but they did not “feel” right. I have recently been reading about CloudNativePG which gets high praise, but I am yet to try it out.
Background
Switching tracks though, after moving from ESXi to Proxmox, I wanted to make use of their low overhead LXC containers and the TurnKey postgres caught my eye. It promised web control panel for managing and administering postgres which came in the form of Webmin for management and Adminer for administration. I set it up and moved to a central database architecture, making good use of LXC snapshots and backups for backing up the database. I am currently running this exact setup without any issues. Webmin lets me quickly create users and assign then to new databases which I can then use with any new services I deploy. Overall a very pleasant experience.
Changing Landscapes
My goals changed when I recently acquired a new QNAP NAS on which I am running TrueNAS Scale. I wanted to be able to provide all storage related services from this NAS, such as prometheus, MinIO, Frigate, and of course postgres. Running a full LXC container did not seem necessary, and I did not use Adminer at all, if I needed to edit a table, I would usually turn to psql or use something like pgAdmin
or Postico
.
TrueNAS Scale has the concept of Apps which allow running docker containers managed through TrueNAS itself. This is a pretty slick setup where TrueNAS manages storage for the containers on any of the datasets available and I wanted to use this for hosting the database as well.
The Solution
Enter PSQLmin : A PostgreSQL container with Webmin embedded into it. The simplest of requirements with the simplest of solutions. Arriving at the final version of this was not straightforward, as Webmin really does not want to work as a configurable add on or an afterthought (Or it could be me and I was not competent enough to understand Webmin works).
Problems with Webmin
Installing webmin is really simple, they provide a curl | sh script to run. So far, so good.
Next, I wanted to disable HTTPS, since I did not want webmin handling it. It is almost always better to let a reverse proxy handle your TLS termination at a sigle point of entry. Immediately I ran into roadblocks. I could not figure out a config that could disable HTTPS. After an afternoon of digging into a webmin container looking for clues, I settled for disabling SSL in miniserv
, the webserver used by Webmin, in a slightly janky way which in turn meant I had to disable cookies which no longer work without HTTPS. It looked like webmin really does not want to be served under HTTP.
Further down the line was authentication. Webmin ships with basic form based user authentication, which was really not necessary nor secure under HTTP. I would much prefer it either have no authentication so I can place it behind an Identity Provider (IdP) using an OAuth2 proxy, or have it support OAuth2 itself. Another half a day in I settled for making it user configurable and exposed via environment variables . This was again done by text wrangling webmin and miniserv config files.
With further tweaks to fix where webmin looks for postgres data, making ports configurable and only exposing webmin’s postgres UI by messing with the theme file configs, I was finally done and let the postgres entrypoint take control. I now have a working webmin instance alongside the postgres container, working perfectly.
Building multi-arch multi-version containers using Github Actions
Coming to the favorite part of setting up any repo (NOT), getting the github actions CI pipeline publishing docker images. Usually it is just building a Dockerfile for a handful of architectures using QEMU and buildx, but now, I needed to build for multiple versions of postgres. After all, if anyone else other than me were to use this, there is no guarantee that they will be running the same version of postgres I am.
As can be seen in the CI definition
, I made use of the matrix strategy to run the pipeline for multiple values of a variable, in this case, the postgres version. Getting the right set of docker tags was a small nightmare, but I ended up settling with a version for this app appended to the postgres version.
Conclusion
Throwing in a quick icon you see below, PSQLmin was complete. A single container that can be used to manage the database running inside the container, being released into the world. If one person, other than me, has a usecase for this, I would consider this project successful.
Feel free to look at the repo adyanth/psqlmin on Github and let me know your thoughts below if this helped you!