*ฅ^•ﻌ•^ฅ* ✨✨  HWisnu's blog  ✨✨ о ฅ^•ﻌ•^ฅ

Securing your self-hosted Database

Introduction

Hello everyone! Been awhile since my last blogpost (June'25) as I've been busy with office work and also the development process of my stock trading app has gained crazy amount of traction.

I had finished creating a full fledged virtual trade system and next big feature is a Portfolio Watchlist with alert system which will be fully integrated with the backbone features I have built already that none of the other stock trading apps provided.

And no, I don't use AI for any of the investing logic. You'd be crazy if you literally trust AI/LLM to manage your hard earned money. But don't let me stop you, go ahead ALL IN and test that cognitive decline!

AI/LLM can be useful, like generating an image like what I will do in the next section, but for something as critical as finance / investing it's far too careless IMO. But you do you!

Theme of the quarter: Self-host

Yeah I've managed to got off the cloud completely and self-host (almost) everything. The process has been super fun and I'm kinda addicted to this DevOps stuff and as always, it's hard only in the beginning. Once you figured it out you won't even think of using cloud services / serverless.

Self host instead of depending on managed Cloud services / serverless self-host Note the image above was generated using Grok.

This post will focus on one thing I think quite challenging and I need to put into writing so that if I need to replicate the process I can always go back to this post. Actually, this whole bearblog account is akin like a large documentation page for me personally.

Self hosting database

So I got like 3+1 managed databases (1 MySQL and 3 Postgres) from various brands. Why 4 you ask? Coz I like to test and compare different brands / products. One funny fact: even though I got 3 Postgres DB, my primary DB is MySQL. All of my Postgres DBs are backups.

So my choice landed on MariaDB so I can have 100% compatibility (drop-in replacement) to MySQL and that has been my experience so far. I need to point out I dislike using ORM, part of the reason is I jump frequently between tech stacks (Python for finance stuff, JS/TS for visual frontend, C/Zig/Rust for low level) and I don't want to memorize different ORM syntax. This means I write raw SQL query..which enables me to be proglang agnostic.

Main issue: bots probing the DB port.

I always try to implement best practices and be quite strict with security, so although I'm using full-verify SSL mode I'm still bothered by the amount of rabid bots trying to connect.

I suggest the audience to read my post on VPS security best practices as I will be utilizing the same tool: fail2ban.

Check your storage mounts

I'm deploying to my servers via Coolify and Docker (which I have extensively covered in part-1 and part-3.

I'm assuming you have set up your webapps and database, so let's get straight to the point: check your DB's docker process ID using this command: sudo docker ps and find the DB process.

Then use sudo docker inspect <process_ID> | grep "Mounts" --> this should give the the storage mounts, in my case: several certs mounts and a data mount. There's no logs mount which is crucial for fail2ban to successfully ban the rabid bots.

Create directories+files for additional storage mounts

  1. sudo mkdir -p /data/coolify/yourdb/logs/ and sudo chown -R 999:999 /data/coolify/yourdb/logs
  2. sudo mkdir -p /data/coolify/yourdb/configs/

Next go to your fail2ban dir: sudo vim /etc/fail2ban/filter.d/yourdb-auth.conf and insert these:

[Definition]
failregex = \[Warning\] Access denied for user '.*'@'<HOST>'
            \[Warning\] Aborted connection \d+ to db: '.*' user: 'unauthenticated' host: '<HOST>' \(This connection closed normally without authentication\)
ignoreregex =

Next create jail file fir the DB: sudo vim /etc/fail2ban/jail.d/yourdb.local and put this content:

    [mariadb-auth]
    enabled  = true
    port     = 3306
    filter   = mariadb-auth
    logpath  = /data/coolify/yourdb/logs/error.log
    maxretry = 3
    findtime = 3600
    bantime  = 86400

Lastly create a custom configuration file: sudo vim /data/coolify/yourdb/config/custom.cnf with this content:

[mysqld]
log_error = /var/log/mysql/error.log
log_warnings = 2

Create Storage Mounts via Coolify UI

We need to create additional storage mounts, in Coolify UI find your database and look for [Configuration] --> [Persistent Storage]. Press Storages +Add

coolify

A new window will appear with several fields, we only fill the Directory Mount several times:

Input the fields on the bottom window coolify

Result: coolify

Restart the database

Once you've done all the steps above, don't forget to restart the DB.

Restart and check fail2ban status

Input these commands:

Test a failed login

Using your SQL cli (in my use case: mariadb cli) let's make a failed login: mariadb -h <hostname_or_ipAddress> -P <port_number> -u <username> -p <db_name>

Then you'll be prompted to input your password --> make sure to input the WRONG password since we want to trigger a failed login. Next go back to your logs dir: /data/coolify/mariadb/logs/ and look for error.log file --> here you'll see the Access Denied and Aborted Connection logging.

Below is what I got after leaving it overnight: server Note now the bots needed to cycle the ip address more frequently because these ip addresses will get banned by fail2ban after 3 failed attempts.

Conclusion

That's it, you've successfully set up fail2ban to work with your db logging and will start banning IP addresses who tries to connect but got denied repeatedly. Now you can have peace of mind and sleep better at night knowing those rabid bots won't be able to even knock on the door of your database server.

Comments section here