[#MDEV-575] CLOSE - TCP SECURITY - BAD Login / IP blocker - brute force security

Implement a 'fail2ban' log, and a internal table, TCP connection blocker

bad login = wrong user or wrong password, that don't give access to server and disconect
GMT = 0:00 time zone (no timezone or unixtime)

Create a internal table (in memory or disk?) with:
HOST (ipv6 or ipv4?)
BAD_LOGIN_TIME (utime)
BAD_LOGIN_UNLOCK_TIME (utime)

implementation:
1) global server vars:
VARIABLES

bad_login_time = 999999 in seconds  (setting to 0 = turn off bad login)

bad_login_tries = 999999 in seconds

bad_login_log_file = xxxxxxxxx (for fail2ban implementation, if = OFF or EMPTY, turn off log)

bad_login_log_size = 9999999 (after this size create a new file, if =0 set to 1MB)

bad_login_log_rotate = 99999 (number of files, if=0 set to 4 files)

bad_login_garbage_collector_disconnects = 9999 (number of disconnects to flush bad_login internall table, if =0, set to 1000)

bad_login_good_list = ip ; ip ; ip ; ip

bad_login_bad_list = ip ; ip ; ip ; ip

STATUS

bad_login_garbage_collector_counter = 0 (set to 0 at server startup)

bad_login_garbage_collector_max_unlock_time = 0 (set to 0 at server startup)

2) BAD_LOGIN table:
CREATE TABLE BAD_LOGIN (
HOST VARCHAR(32) NOT NULL DEFAULT '' COMMENT 'SHOULD STORE IPv4 AND IPv6',
BAD_LOGIN_TIME BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '(unixtimestamp in seconds - GMT)',
BAD_LOGIN_UNLOCK_TIME BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '(unixtimestamp in seconds - GMT)',
KEY HOST (HOST,BAD_LOGIN_UNLOCK_TIME)
);

3) BAD_LOGIN log file (CSV?):
HOST, LOGIN TIME (GMT), UNLOCK TIME (GMT), HOST LOCKS, HOST LOCKED NOW (Y/N), HOST MAX UNLOCK TIME (GMT)

4) BAD_LOGIN table flush
something like:

STATUS bad_login_garbage_collector_counter=@@bad_login_garbage_collector_disconnects

IF bad_login_garbage_collector_max_unlock_time < NOW() THEN

     TRUNCATE INFORMATION_SCHEMA.BAD_LOGIN;

     STATUS bad_login_garbage_collector_max_unlock_time = 0

ELSE

    DELETE FROM INFORMATION_SCHEMA.BAD_LOGIN WHERE BAD_LOGIN_UNLOCK_TIME<NOW();

    IF rows deleted > 0 THEN

         OPTIMIZE BAD_LOGIN;

         STATUS bad_login_garbage_collector_max_unlock_time = SELECT MAX( BAD_LOGIN_UNLOCK_TIME ) FROM INFORMATION_SCHEMA.BAD_LOGIN

5) ADD HOST TO LOG
check if log is ON
check if should rotate the log (file size and log files)

6) ADD HOST TO INTERNAL TABLE
add host to internal table:
INSERT INTO INFORMATION_SCHEMA.BAD_LOGIN (HOST,BAD_LOGIN_TIME,BAD_LOGIN_UNLOCK_TIME) VALUES
(HOST, now(), now() + @@bad_login_time )

IF now() + @@bad_login_time > STATUS bad_login_garbage_collector_max_unlock_time THEN
STATUS bad_login_garbage_collector_max_unlock_time = now() + @@bad_login_time

maybe a optimization like:
IF "bad logins for this user" > bad_login_tries THEN
ADD host
REMOVE the oldest bad login, to make internal table small

7) At new IPv6 or IPv4 connection
Check if the IP is a 'BAD IP':
something like:

IF connection type = TCP THEN

     IF host IN @@bad_login_bad_list THEN

          DISCONNECT

          EXIT

     IF STATUS bad_login_garbage_collector_max_unlock_time > NOW()  AND host NOT IN @@bad_login_good_list  THEN

        SELECT COUNT(*) AS count FROM INFORMATION_SCHEMA.BAD_LOGIN WHERE HOST=current_ip AND BAD_LOGIN_UNLOCK_TIME>=NOW()

        IF 'count' >= @@bad_login_tries THEN

           DISCONNECT

           STATUS bad_login_garbage_collector_counter -= 1

           IF STATUS bad_login_garbage_collector_counter <=0 OR  STATUS bad_login_garbage_collector_max_unlock_time < NOW()  THEN

                   BAD_LOGIN table flush (4)

           EXIT

     ... continue with auth ...

     ... auth get wrong ...

     IF wrong user/password  THEN

           DISCONNECT

           STATUS bad_login_garbage_collector_counter -= 1

           IF STATUS bad_login_garbage_collector_counter <=0 OR  STATUS bad_login_garbage_collector_max_unlock_time < NOW()  THEN

                   BAD_LOGIN table flush (4)

           IF host NOT IN @@bad_login_good_list  THEN

               ADD host / datetime to INFORMATION_SCHEMA.BAD_LOGIN

               ADD host / datetime to LOG

           EXIT

8) add a INFORMATION_SCHEMA.BAD_LOGIN table

9) maybe write a fail2ban parser based in CSV file? just first and second columns will be used

[MDEV-575] CLOSE - TCP SECURITY - BAD Login / IP blocker - brute force security - fail2ban support Created: 2012-10-04 Updated: 2013-06-18 Resolved: 2013-06-18
Status:	Closed
Project:	MariaDB Server
Component/s:	None
Fix Version/s:	None

[MDEV-575] CLOSE - TCP SECURITY - BAD Login / IP blocker - brute force security - fail2ban support Created: 2012-10-04 Updated: 2013-06-18 Resolved: 2013-06-18