Fail2Ban and Cacti - read MySQL table

fail2ban_cacti_deprecated.md

Fail2Ban and Cacti - read MySQL/MariaDB table

Please Note: This is an old way of working, for a newer please refer to https://gist.github.com/GAS85/4fd4715eb7c2c9f585dc0f6309940562

I took it from here, but becasue of newer MySQL it needs to be updated a bit.

Prerequsits

• Ubuntu 16.04
• MySQL 5.7
• cacti, fail2ban and e.g. iptables are installed

Intro

Cacti logs failed login attempts to a MySQL table, which Fail2ban can't read. By using MySQL's CSV storage engine we can write login attempts to an additional table, which on-disk format is ASCII or UTF-8, and thus readable by Fail2ban. Note: This includes at least a trigger and and an optional scheduled event inside the database. I consider this "dirty", especially within a replication scenario.

Setup

Connect to your Cacti database, e.g.:

$ mysql cacti

Create the new table:

mysql> CREATE TABLE fail2ban (
    -> `time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
    -> ip varchar(40) NOT NULL DEFAULT '',
    -> username varchar(50) NOT NULL DEFAULT '')
    -> ENGINE=CSV;

Create the trigger. For this, we have to alter the input delimiter of the MySQL client temporarily:

mysql> delimiter |
mysql> CREATE DEFINER=cacti@localhost TRIGGER failed_logins
    -> AFTER INSERT ON user_log FOR EACH ROW
    -> BEGIN
    ->   IF NOT NEW.result = 1
    ->     THEN INSERT INTO fail2ban SET time=NEW.time, ip=NEW.ip, username=NEW.username; 
    ->   END IF;
    -> END; |

Optionally, start the event scheduler and create an event which deletes any old records from that table:

mysql> SET GLOBAL event_scheduler = 1 |
mysql> CREATE DEFINER=cacti@localhost EVENT delete_old_logs
    -> ON SCHEDULE EVERY 1 hour COMMENT 'wipe old failed login attempts'
    -> DO DELETE FROM fail2ban WHERE time <= subtime(now(), '01:00:00') |
mysql> quit|

Do not forget to set enable (event scheduler)[https://dev.mysql.com/doc/refman/5.7/en/events-configuration.html] in your MySQL server:

SET GLOBAL event_scheduler = ON;
SET @@global.event_scheduler = ON;
SET GLOBAL event_scheduler = 1;
SET @@global.event_scheduler = 1;

As the trigger writes only failed login attempts (result=1) to the new table, we can get away with a fairly simple Fail2ban filter:

[Definition]
failregex = ","<HOST>","
ignoreregex =

Save it to a file called filter.d/cacti.local within your Fail2ban configuration directory, e.g. /etc/fail2ban, or /usr/local/etc/fail2ban on FreeBSD. Finally, extend your jail.local, e.g.:

[cacti-ipfw]
enabled = yes
filter = cacti
action = iptables-multiport
         sendmail-whois-lines[name=cacti, logpath=/data/mysql/cacti/fail2ban.CSV]
logpath = /var/lib/mysql/cacti/fail2ban.CSV

Either restart Fail2ban, or activate the new jail:

# fail2ban-client add cacti-ipfw
# fail2ban-client start cacti-ipfw

Enjoy