Question Fail2ban for MySQL

[CoyoteKG]

Hi,

I can't find here on forum similar question.

I need to have open port 3306 for all IPs.
Is it possible somehow to protect mysql authentification with Fail2Ban?

I found that enabling general log can slow server, but also I saw that is possible to enable only
"log_warnings = 2",

Is someone protected MySQL logging with fail2ban?

Version of MySQL is
$ mysql -V
mysql Ver 14.14 Distrib 5.6.36, for Linux (x86_64) using EditLine wrapper

 

[Bitpalast]

This describes what you are seeking and provides a proper solution:
Fail2Ban monitoring MySQL | Demon Penguin's Brain

 

[CoyoteKG]

Thank you, I will try that

 

[CoyoteKG]

I followed instruction from that link, and tried to add filter and jail with plesk.

Filter is like this


But when I try to add Jail, I got this error

Error: f2bmng failed: ERROR NOK: ("No 'host' group in 'Access denied for user .*@''.*$'",)
ERROR:f2bmng:Command '['/usr/bin/fail2ban-client', 'reload', 'mysql']' returned non-zero exit status 255
ERROR:f2bmng:Failed to reload following jails due to errors in configuration: mysql

In mysqld.log I already got a huge ammount of Access denied logs, and there are two different kind of those.

2017-06-16T10:51:20.141402Z 77735 [Note] Access denied for user 'db'@'177.73.50.50' (using password: YES)
2017-06-16T12:48:00.383079Z 42 [Note] Access denied for user 'root'@'cable-88-99-29-229.static.sbb.rs' (using password: YES)

Probably I need to adjust regex somehow, but not have experience with this, so if someone already did this, please share . I would be grateful

 

[trialotto]

@CoyoteKG

The failregex should preferably be of a form like

^.*\[Note\] Access denied for user.*<HOST>.*\(using password: YES\)$

and note that

- ^ is the start of the string, whereas $ indicates the end of the string
- the string should be as explicit as possible (saves time parsing regexps)
- the <HOST> variable is a default Fail2Ban variable indicating the IP address in question

Moreover, note that you have to adjust your log output format, in order to prevent logging of log lines containing something like 'root'@'cable-88-99-29-229.static.sbb.rs'.

The log output format should be of such kind that normal IPv4 addresses will be logged.

In general, you should be aware that Fail2Ban will not provide any security: if prevents failed malicious attempts to retry, that is all!

It is highly recommended that you put a firewall or proxy in front of the MySql server AND, more important, redesign the infrastructure, since it is not recommended at all to open the MySql server for traffic originating from "all IPs": there never is the need for this, unless your "design infrastructure" is bad.

For instance, simply using Nginx as a proxy, communicating with a MySql server will allow you to

- close traffic from all IPs, except for the traffic that originates from the server on which the Nginx proxy is present,
- use Nginx caching to speed up (common) MySql queries
- use Fail2Ban and Nginx to automate the permanent blocking of bad IPs (as opposed to temporary banning of IPs, which is the essence of Fail2Ban)

and so on.

In short, (first) create and activate a Fail2Ban filter and/or action (to secure access to the MySql server a bit) and (second) rethink your "design infrastructure"!

Hope the above helps......

Regards....

 

[CoyoteKG]

@trialotto, thank you very much for this detailed post.
You are right and now I'm thinking about installation new server with openvpn. and after that I will allow connection to mysql only from that server.

 

[OverWolf]

@CoyoteKG



Moreover, note that you have to adjust your log output format, in order to prevent logging of log lines containing something like 'root'@'cable-88-99-29-229.static.sbb.rs'.

The log output format should be of such kind that normal IPv4 addresses will be logged.

Hi Trialotto,

how can I parse the log output format ? I cannot find datedetector in /usr/share/fail2ban/server, so what can I do ?

Thank you

P.S.: I'm on CentoOS 7.3 with plesk 17.5.3

 

[mr-wolf]

If you are not from a big country you may consider opening up the port 3306 for your own country only.

With ipset one can create sets of subnets.
These can be referenced in iptables.

If there's interest I may find some time to give examples.

 

[OverWolf]

Hi mr-wolf,
thank you for your support, but I have detected several brute force attacks from my country, so I don't think that this solution is for me.
So, I'm tring to find datedetector.py so I can configure it and then I can see if someone it's tring to access to 3306.

 

[OverWolf]

Hi,
is there a reason because bind-address in my.cnf is empty ?

 

[mr-wolf]

Hi mr-wolf,
thank you for your support, but I have detected several brute force attacks from my country, so I don't think that this solution is for me.
So, I'm tring to find datedetector.py so I can configure it and then I can see if someone it's tring to access to 3306.

Hi OverWolf,

These are of course not mutually exclusive.
You can have a bruteforce protection together with a countryblock or country accept.

Because I don't open port 3306 at all for the outside world I don't know for sure if this bruteforce (working for SSH) is just as effective for MySQL.
The only thing the daemon needs to do is break the connection after it gets a bad password.
SSH does this after 3 tries.

:bruteprotect - [0:0]
# Prevent bruteforce attacks
-A bruteprotect -m recent --set    --rsource --name BRUTEFORCE
-A bruteprotect -m recent ! --update --rsource --seconds 60 --name BRUTEFORCE --hitcount 4 -j RETURN
-A bruteprotect -j LOG --log-prefix "[DROP BRUTEFORCE] : " --log-tcp-options --log-ip-options
-A bruteprotect -j DROP

-A INPUT -p tcp -m tcp --dport 22 -j bruteprotect
-A INPUT -p tcp -m tcp --dport 22 -j ACCEPT

I have SSH listening on port 22 for many, many years and it's getting monitored as well.
I can tell you that they quickly give up hammering.
Personally I think this is more elegant than fail2ban which is dependent of your logfiles.

If you want to combine the script I wrote here: Input - Enhance firewall to block services for certain countries
with this bruteforce for MySQL

You should create this (Replace 11.22.11.22 with your WAN-IP not the one from the server, but the one you use to connect to it)

-A INPUT -p tcp -m tcp --dport 3306 -s 11.22.11.22 -j ACCEPT
-A INPUT -p tcp -m tcp --dport 3306 -j bruteprotect
-A INPUT -p tcp -m tcp --dport 3306 -j DROP

If you would run that script properly configured, it will become (in memory)

-A INPUT -p tcp -m tcp --dport 3306 -s 11.22.11.22 -j ACCEPT
-A INPUT -p tcp -m tcp --dport 3306 -j bruteprotect
-A INPUT -p tcp -m tcp --dport 3306 -m set --match-set ipset-country-nl src -j ACCEPT
-A INPUT -p tcp -m tcp --dport 3306 -j DROP

The effect would be that your own IP has always access
IP's from your country will have access as long as they don't hammer (which is not necessary if they keep the connection open)
The rest of the world will be blocked.

As I said earlier. The bruteforce for SSH is thoroughly tested over many many years.
I don't know really if a typical MySQL connection keeps the connection open. It can't open more than 3 connections in 1 minute or it will be blocked.

If the bruteforce is not appropriate for MySQL you can still use fail2ban.
Doing this in combination with my country-accept makes it even more powerful.

 

[mr-wolf]

@OverWolf

If you like this, you may also like my 2-strikes out system I wrote as an addition to WordFence...
Input - Use Wordfence to fill the firewall

 

[OverWolf]

Thank you mr-wolf,
but I'm on virtual machine, so I cannot use ipsec and so your script (wordfence and enhance fw).
I'll try to search for some other mod. Anyway thank you

 

[mr-wolf]

Thank you mr-wolf,
but I'm on virtual machine, so I cannot use ipsec and so your script (wordfence and enhance fw).
I'll try to search for some other mod. Anyway thank you

Have you tried it?
I just noticed that 1 server of mine, which is the only virtual server has problems with ipset....
Maybe it's only the implementation of the virtualisation that I am running (OpenVZ)??

I used to write scripts that created many iptables-lines.
Thanks to ipset this is not necessary anymore.

It's a pity it doesn't work on all machines, but I'm not getting back to writing these scripts as fallback... it's just too ugly.
I believe fail2ban is using ipset as well, but may fall back to a more traditional approach.
I wouldn't know as I would write such myself.

The nice thing about ipset is that it can easily handle a lot of data without becoming ugly in its implementation. On top of that it works much more efficient.

You can do this easy preliminary test.
If you also get this error then the script will not work.

sudo yum install ipset
sudo apt-get install ipset
ipset create test hash:net
ipset v6.29: Kernel error received: Operation not permitted

 

[OverWolf]

Hi mr-wolf,
I'll test asap. Now I'm configuring other services and I cannot test it for a while.

 

[OverWolf]

Hi mr-wolf,
this morning I've make a backup of my server and while I've testing some services, I try to install ipset. Yes, it's work. I'm on VmWare and I found an article that on OpenVz there is a bug with ipset. So, now I'm try to set some of your scripts, because there are too much traffic on ssh and postfix on my server. I hope you can help me a little ;-)

 

[mr-wolf]

I hope you can help me a little ;-)

Of course I will....

 

[OverWolf]

Hi mr-wolf,
I have had a bruteforce attack (Question - Brute force attack has put down dovecot service) and I've found some scripts for f2b for nginx (www), but nothing for pop3,pop3s. Do you think that I can protect my dovecot service with bruteprotect from your ssh script ?

 

[mr-wolf]

Hi mr-wolf,
I have had a bruteforce attack (Question - Brute force attack has put down dovecot service) and I've found some scripts for f2b for nginx (www), but nothing for pop3,pop3s. Do you think that I can protect my dovecot service with bruteprotect from your ssh script ?

No, as it is quite normal to have multiple connections within a small time frame for the same IP for these services.
Have you examined where they come from?
Probably from one of the several countries that have not much better to do than trying to hack into your mailserver.

The ipset-country script would block those quite easily....

 

[OverWolf]

Hi mr-wolf,
it came from br. I'll try ipset-country script, but first I'm tring to configure f2b to use ipset (and not iptables). Then I'll configure your script to work with f2b.

Thank you for your support.