Fail2ban Mysql Database to Manage Persistent Bans

by · Published · Updated

Step 1

Create Database and Tables in MySQL

Add a “source” field for the server name if you want to use a remote database setup (and add it to the UNIQUE KEY).
The UNIQUE KEY prevents duplicates of course. But this means you can refresh the perl script without handling duplicates there which would complicate things considerably.

CREATE TABLE iptable (
id INT(20) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (id),
ip VARCHAR(20) NOT NULL,
octet1 INT(3),
octet2 INT(3),
octet3 INT(3),
octet4 INT(3),
banaction VARCHAR(25),
bantime TIMESTAMP DEFAULT '0000-00-00 00:00:00',
create_date TIMESTAMP DEFAULT NOW(),
UNIQUE KEY (`ip`,`banaction`,`bantime`)
);

Step 2

Create a perl script Fail2ban.pl that will be added to your crontab

use Scalar::MoreUtils qw(empty);
use Log::Handler;
my $log = Log::Handler->new();
$log->add(
    file => {
        filename => "/var/log/f2bscript.log",
        mode     => "append",
        maxlevel => "debug",
        minlevel => "emerg",
    }
);

use DBI;
## local or remote central server
my $iptable   = "iptable";
my $database = "";
my $host     = "use a local or remote central server here";
my $port     = "3306";
my $user     = "";
my $pw       = "";
my $dsn = "dbi:mysql:$database:$host:$port";
my $dbh = DBI->connect( $dsn, $user, $pw ) or die "Connection Error: $DBI::errstr\n";

my $fail2ban = '/etc/fail2ban/persistent.bans';
my $bantime;
my $banaction;
my $OcTeT1;
my $OcTeT2;
my $OcTeT3;
my $OcTeT4;
my @log_lines;
my $badf2bs;
open my $file, '<', $fail2ban;
chomp(@log_lines = <$file>);
close $file;
foreach (@log_lines) {
	## match this >> 2016-11-15 10:42:31 fail2ban-badguy 54.174.81.105
	( $bantime, $banaction, $OcTeT1, $OcTeT2, $OcTeT3, $OcTeT4) = $_ =~ m{^(.*?)\s+fail2ban-(.*?)\s+(\d{1,3})\.(\d{1,3})\.(\d{1,3})\.(\d{1,3})};
	$log->debug("$bantime\t$banaction\t$OcTeT1\t$OcTeT2\t$OcTeT3\t$OcTeT4");
	next if (empty($bantime));
	next if (empty($banaction));
	next if (empty($OcTeT1));
	next if (empty($OcTeT4));
	my $ip = "$OcTeT1\.$OcTeT2\.$OcTeT3\.$OcTeT4";
	$dbh->do( "INSERT IGNORE INTO $iptable (ip,octet1,octet2,octet3,octet4,banaction,bantime) VALUES ('$ip','$OcTeT1','$OcTeT2','$OcTeT3','$OcTeT4','$banaction','$bantime')" );
	$log->info(">>> $ip <<< >>> $banaction <<< ");	
}

__END__

Step 3

Edit vi iptables-allports.conf (make a backup first) update these 2 lines

actionstart = iptables -N fail2ban-<name>
              iptables -A fail2ban-<name> -j RETURN
              iptables -I <chain> -p <protocol> -j fail2ban-<name>
              cat /etc/fail2ban/persistent.bans | awk '/fail2ban-<name>/ {print $4}' \
              | while read IP; do iptables -I fail2ban-<name> 1 -s $IP -j DROP; done
actionban = iptables -I fail2ban-<name> 1 -s <ip> -j DROP
            echo "$( date '+%%Y-%%m-%%d %%T' ) fail2ban-<name> <ip>" >> /etc/fail2ban/persistent.bans

Step 4

Edit vi jail.local (make a backup first) change the ban action to:

banaction = iptables-allports

Step 5

Edit vi /etc/crontab add line for the perl script

08 *    * * *   root    /usr/bin/perl /home/user/Fail2ban.pl

Test to see it its working

sudo nohup perl ~/Fail2ban.pl &
sudo tail -f /var/log/f2bscript.log

Some ways to implement/manage the ips in your MySQL database and iptables

1) output text to /etc/psad/auto_dl and let psad handle them (set to dl 4 or 5)
2) output text to a new(refresh) /etc/fail2ban/persistent.bans and let Fail2ban re-ingest on restart

Tags:

You may also like...