Question fail2ban log bans to mysql

[Bjorn]

Hi,

I want to add a custom action to all jails, this action calls a script that writes the ban info to mysql.
When i run my script directly from the console, it works fine. But it does not get called with the actionban...

First some info:
Plesk 17.5.3 CentOS 7
Fail2Ban v0.9.6
MariaDB 5.5.52

This is what i have done so far:

1) created the script in /usr/local/bin/fail2ban-mysql.sh
2) created a fail2ban-mysql action in /etc/fail2ban/action.d/fail2ban-mysql.conf
3) added (inside plesk) the new action to the plesk-wordpress jail for testing.

Files

Fail2ban-mysql.sh

#!/usr/bin/bash

#Script to run automated sql queries

#Declaring mysql DB connection

MASTER_DB_USER='fail2ban_user'
MASTER_DB_PASSWD='********'
MASTER_DB_PORT=3306
MASTER_DB_HOST='localhost'
MASTER_DB_NAME='fail2ban_db00'

#Check if all arguments are passed
if [ $# -eq 5 ]
then

#Prepare sql query
Q_HOSTNAME=$1
Q_NAME=$2
Q_PROTOCOL=$3
Q_PORT=$4
Q_IP=$5

SQL_Query="INSERT INTO bans set hostname='$Q_HOSTNAME', name='$Q_NAME', protocol='$Q_PROTOCOL', port='$Q_PORT', ip='$Q_IP', created=NOW()"

#mysql command to connect to database

mysql -u$MASTER_DB_USER -p$MASTER_DB_PASSWD -P$MASTER_DB_PORT -h$MASTER_DB_HOST -D$MASTER_DB_NAME <<EOF
$SQL_Query
EOF
echo "Success"

else
echo "Missing required arguments"

fi​

-----------------------------

fail2ban-mysql.conf

[INCLUDES]

before = iptables-common.conf

[Definition]

# custom actionban
_mysqlscript = /usr/local/bin/fail2ban-mysql.sh

actionban = %(_mysqlscript)s qdsrv1 test_jail ssh 22 123.123.123.132

[Init]​

---------------------------

Testing:

- I saw some script 'if' errors in the fail2ban log ( /var/log/fail2ban.log ) after restart, so i know my banaction is called.
- After fixing the errors, the fail2ban log stays clear of errors, but nothing is added to mysql.
- output of 'fail2ban-client get plesk-wordpress action fail2ban-mysql actionban' =
/usr/local/bin/fail2ban-mysql.sh qdsrv1 test_jail ssh 22 123.123.123.132

It seems all F2B configuration settings are correct, does anyone know why my script doens't work?

Thanks

Best regards,
Bjorn

 

[Dieter]

Hi Bjorn,

You could define actions that insert directly into you mysql database like:

actionban = printf %%b "insert into table('ban', '<ip>', <failures>, '<name>');" | mysql -u user, --password=password database

Have also à look to following web page:
http://www.seleads.com/fail2ban-mysql-database-manage-persistent-bans/

Here is an other example with "actionban"

actionban = /home/all/scripts/fail2ban-sms.sh ban <ip>
#You need actionban to do something every time a ban triggers.

source : Extend Fail2ban to send text messages every time a user/bot gets banned


Best Regards, Dieter

 

[Bjorn]

Hi Dieter,

Thanks for your response.

1) when i try to INSERT directly with the actionban, i'm getting mysql defaults errors, setting --no-default did not resolve the problem.

2) The seleads link is interesting, write bans to a log and then use a cron for processing to mysql, i do not prefer this method, but i'm going to try it as a fallback.

3) Using the actionban to call a script is what i want, i did see that website (toon.io) already.

When i restart the plesk-wordpress jail, I see 'permission denied' errors in the fail2ban.log. So fail2ban cannot execute the script, even when i chmod to 777.
user:group of script file is root:root. I checked /etc/passwd and i can't find a fail2ban user...

How do i let fail2ban run my script?

Best regards,
Bjorn

 

[Bjorn]

Hmmm, when i try to use the actionban to echo to a log file, I also get permission denied errors...

Tried:
actionban = echo "test" >> /scripts/test.log

Result: Permission denied

Even when i open the dir and log 777...?

I suspect this problem is also preventing the script to run..

Any thoughts?

Regards,
Bjorn

 

[Linulex]

why not run a cronjob script that every x minutes parses the fail2ban log and adds the bans to a mysql database?

in fact: why not enter every line to mysql, not only the bans. You can then filter and create statistics on every ip address ever seen by fail2ban.
If you have moe then 1 server, you could enter all the fail2ban logs to a central database and add permanent firewall rules for ip adrresses that have been seen (banned) on more then half of the servers.... or whatever you can think of to do with it.

just my 2 cents

regards
Jan

 

[Bjorn]

Hi Jan,

Thank you for your 2 cents
I already created a script called by cron that reads the fail2ban log and updates the banned IPs to sql.

But it feels dirty and really not the way to go. Setting the actionban to call a script with ban/unban actions is what i want. But i always get a permission denied response, even when i try:

actionban = echo "test" >> /usr/local/bin/test.log

I know i have to execute the script in the correct F2B path, and '/usr/local/bin' is one of those paths...

So the question is:

What to do if you get 'permission denied' when calling a script from the actionban?

BTW, i tried setting the script to chmod 777 and user:group is root:root.

Regards,
Bjorn

 

[Linulex]

Why should that be dirty, after all, fail2ban itself is nothing more then a fancy log parser when you think about it.

If you are using selinux then perhaps thats the reason.

regards
Jan

 

[Bjorn]

Because i connected my support site to the DB so customers can check if an IP is banned for themselfs. The cron needs to run every 10min, read the complete log, with many entries i dont need (log rotation etc).

actionban is simple and effective, only run script when needed.

 

[Linulex]

actionban is simple

is it? why this posting then? (joking)

i would think that reading the log with a cron is faster and takes less resources then actionban.
After all, you never know how many times the actionban script has to run in any given period of time.

Have you looked at this?

http://fail2sql.sourceforge.net/

It works on our testserver with these few changes:

readme says
5. Update Geo IP Database (./fail2ban -u)
must be
5. Update Geo IP Database (./fail2sql -u)

I had to change the server from 127.0.0.1 to localhost.

change in the database:
port from int(11) to varchar(150)

change in fail2sql script
$port = $_SERVER["argv"][3];
if (!preg_match('/^\d{1,5}$/', $port)) {
$port = getservbyname($_SERVER["argv"][3], $protocol);
}

to

$port = $_SERVER["argv"][3];
//if (!preg_match('/^\d{1,5}$/', $port)) {
// $port = getservbyname($_SERVER["argv"][3], $protocol);
//}

result:

7 recidive tcp ssh 131.255.4.44 1 -58.3817 -34.6033 AR , Argentina
2 recidive tcp 22 131.255.4.44 3 -58.3817 -34.6033 AR , Argentina
3 recidive tcp 22 152.249.249.216 3 -46.3333 -23.8 BR Rio Grande Da Serra, Brazil
4 recidive tcp 22 163.23.97.196 3 121 23.5 TW , Taiwan
5 recidive tcp 22 190.90.8.228 3 -74.0758 4.5981 CO , Colombia
6 recidive tcp 22 80.82.77.203 3 55.6667 -4.5833 SC , Seychelles
8 plesk-postfix tcp smtp,smtps,submission 91.200.12.180 1 30.5233 50.45 UA , Ukraine
9 recidive tcp ssh 152.249.249.216 1 -46.3333 -23.8 BR Rio Grande Da Serra, Brazil
10 recidive tcp ssh 163.23.97.196 1 121 23.5 TW , Taiwan
11 recidive tcp ssh 190.90.8.228 1 -74.0758 4.5981 CO , Colombia
12 recidive tcp ssh 80.82.77.203 1 55.6667 -4.5833 SC , Seychelles

regards
Jan

 

[Bjorn]

Yeah I also used fail2sql, i even contacted the author for help, he doesn't support fail2sql anymore but tried to help me.

Like you, i modified fail2sql to read the fail2ban.log, it still works great with all the geo data.
You can see the map with our banned IPs here:
SERVER STATUS – Quintin Design | The Sequel Support (site is work in progress)

Still hoping someone has a clear answer for the actionban method ^^

Regards,
Bjorn

 

[Linulex]

Then why not simply replace the fail2sql script with your own script. This works, so this is clearly how to do it.

regards
Jan

 

[Bjorn]

huh? i modified the fail2sql script to read the fall2ban.log. This script get's called by a cron-job.

Not the same as actionban method ^^

Regards,
Bjorn

 

[Linulex]

i meant:

install the fail2sql script like in the readme file, with actionban and all, because this use of actionban works. i tested that (see above) and i understand you already have that working

Then change the fail2sql script in that working installation.

I see 2 possible ways to change it (i am sure there are more)
1) remove the current function, change the script so it doesn't look up ip adresses first to keep a counter, but just add bans at the bottom of the table.

2) add a second table to the database and add some code that adds the bans to that second table. that creates a record of what ip address was banned when.
Then you have both:
- a record of what ip was banned when
- a record of what ip was banned how many times (the original fail2sql function)

the extra table could be

DROP TABLE IF EXISTS `fail2banbans`;
CREATE TABLE `fail2banbans` (
`banid` int NOT NULL AUTO_INCREMENT,
`name` text NOT NULL,
`protocol` varchar(4) NOT NULL,
`port` varchar(150) NOT NULL,
`ip` varchar(20) NOT NULL,
`time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`banid`)
) ENGINE=MyISAM;

and the extra code is only 1 query (i think, this is from the top of my head, its not installed anymore on the testserver)

$query = "INSERT INTO `fail2banbans` values ('', '".$name."', '".$protocol."', '".$port."', '".$ip."', '')";

come to think of it: adding a timestamp to the original wouldn't be a bad idea also.


regards
Jan

 

[Bjorn]

Hi Jan,

Maybe i'm misunderstanding things now , but calling a script ( F2SQL & custom script ) from the actionban has never worked for me.
Like i said before, even when i try to echo a simple 'test' to a log file, I get Permission denied.

Then i checked which paths F2B uses ( usr/local/bin | usr/local/sbin | etc. ), i tried them all, and every time i get the Permission denied error.

So my question is:

What to do if you get 'permission denied' when calling a script from the actionban?

BTW, really appreciate that you're trying to help me!

Regards,
Bjorn

 

[Linulex]

Like you, i modified fail2sql to read the fail2ban.log, it still works great with all the geo data.
You can see the map with our banned IPs here:
SERVER STATUS – Quintin Design | The Sequel Support (site is work in progress)

I was under the impression you had fail2sql working and fail2sql is called via actionban, so you know a way to call actionban that works.
fail2sql works different from the method you try to use, so if your doesn't work (a script called before iptables-common.conf) and fail2sql does work (adding a line to iptables.conf).

Then what would i do? I would stop try to re-invent the wheel with my way of doing things and go with the fail2ql method.

regards
Jan

 

[OverWolf]

Hi,
I've implemented fail2sql with success (thanks to this thread), but I've found that in my DB are logged only ssh port. That's strange because I've some ftp nad postfix in my fail2ban log but no one are write in the db. Why ?

 

[ekoari]

Your comment is awaiting moderation.

please help
I’ve configured jail.local on [ssh] and I’ve got the ip blocked and the logs in /var/log/auth.log have been read, but the fail2sql database is still empty. .
what is less than my configuration?
thank you

 

[Bjorn]

Your comment is awaiting moderation.

please help
I’ve configured jail.local on [ssh] and I’ve got the ip blocked and the logs in /var/log/auth.log have been read, but the fail2sql database is still empty. .
what is less than my configuration?
thank you

When you install fail2sql, it has to read the fail2ban.log
/var/log/fail2ban.log

Please note, I haven't completely finished this, i noticed my cron isn't working anymore.
Maybe fail2sql no longer works with the current Plesk/fail2ban version, i'm not sure.

I'm hoping to pick this up in the next weeks, if i find something i'll report it here.

Regards,
Bjorn

 

[ekoari]

thank you. . hope there is another way to fetch the log fail2ban and thrust into the mysql database..

 

[ekoari]

the fail2ban version now does not support fail2sql, which version supports fail2sql. . ? thank you