All HowTo's Linux MySQL & MariaDB Redhat, Fedora and CentOS Linux Ubuntu, Mint & Debian Linux

Fail2Ban with MySQL Database for IP Blacklisting

This article demonstrates how to configure Fail2Ban to use a MySQL (or MariaDB, etc) as the storage repository for IP blocking records. This allow multiple Fail2Ban services (running on multiple servers) to report and use a central IP blocking repository.

A little context. In this article, we’re installing everything on a single host with enough information (to you) to allow you to have other hosts utilize this host as a host for the fail2ban IP list.

Side notes: On CentOS/Redhat/Fedora, you’ll need to deal with SELinux issues. You’ll also need to allow port 3306 through the local firewall if other hosts are to utilize the MySQL database on this host.

Create the database and grant permissions:

CREATE DATABASE fail2ban;
GRANT ALL ON fail2ban.* TO fail2ban@"%" IDENTIFIED BY "Kytf54drtyfTRDfgFg";
GRANT ALL ON fail2ban.* TO fail2ban@"localhost" IDENTIFIED BY "Kytf54drtyfTRDfgFg";
flush privileges;

Note: Why add access for both ‘%’ and ‘localhost’? If the fail2ban host is the same host as the MySQL database, then we need to allow from ‘localhost’. If the fail2ban server(s) are on other servers, we need to allow them with the ‘%’ source.

Get the table schema and other dependencies:

mkdir ~/tmp
cd ~/tmp
wget https://github.com/iredmail/iRedMail/raw/1.3/samples/fail2ban/sql/fail2ban.mysql
wget https://github.com/iredmail/iRedMail/raw/1.3/samples/fail2ban/action.d/banned_db.conf
wget https://github.com/iredmail/iRedMail/raw/1.3/samples/fail2ban/bin/fail2ban_banned_db

Import the SQL content:

mysql fail2ban < ~/tmp/fail2ban.mysql

Now we need to modify our Fail2Ban configuration to direct logging to the new database.

Create the database details file:

/root/.my.cnf-fail2ban

Populate the above file with the following. Note the password in this file is the same as used in the MySQL "GRANT" commands above:

[client]
host="127.0.0.1"
port="3306"
user="fail2ban"
password="Kytf54drtyfTRDfgFg"

Note: Change the 'host' address to the server address hosting the MySQL database. This could be on the same host (then use 'localhost' or '127.0.0.1') or it could be remote.

Now we need to move a few files into place. These are those that we downloaded at the start of this walk-through:

mv ~/tmp/banned_db.conf /etc/fail2ban/action.d/
mv ~/tmp/fail2ban_banned_db /usr/local/bin/
chmod 0550 /usr/local/bin/fail2ban_banned_db

Update our Fail2Ban config to use the new 'action' by default. Ie, send the IP blacklist details to the MySQL DB.

Edit the "/etc/fail2ban/jail.local" file and add the following. Or, if you have content in that file already, use the following as a guide:

[DEFAULT]
ignoreip = 127.0.0.1/8 10.0.0.0/8 192.168.0.0/16 172.16.0.0/16
bantime  = 1h
findtime  = 10m
maxretry = 5
action = %(action_mw)s
          banned_db[name=%(__name__)s, port="%(port)s", protocol="%(protocol)s"]
destemail = [email protected]
sender = [email protected]

Restart Fail2Ban to use the new configuration:

systemctl restart fail2ban

Insert a dummy test to verify it's working:

fail2ban-client set sshd banip 192.168.5.3

You can verify the new banned entries in the MySQL database using the following method:

MariaDB [fail2ban]> select * from banned;
+----+-------------+------------+----------+------------------------+-------------------------------------------------+---------------+------+----------+----------+---------------------+--------+
| id | ip          | ports      | protocol | jail                   | hostname                                        | country       | rdns | failures | loglines | timestamp           | remove |
+----+-------------+------------+----------+------------------------+-------------------------------------------------+---------------+------+----------+----------+---------------------+--------+
|  1 | 192.168.5.3 | ssh        | tcp      | sshd                   | ip-172-11.22.33.ap-southeast-2.compute.internal | AU, Australia |      |        0 |          | 2021-05-19 07:14:38 |      0 |
+----+-------------+------------+----------+------------------------+-------------------------------------------------+---------------+------+----------+----------+---------------------+--------+

References:
Some details were used from "https://docs.iredmail.org/fail2ban.sql.html". Thank you.

4 comments

  1. Hi , nice work. Does this read from the same file as well? Like if you remove an IP directly from the MySQL table, does it also get removed from fail2bans local sqlite dB file?

Leave a Reply

Your email address will not be published. Required fields are marked *