Details
-
Type:
Task
-
Status: Closed
-
Priority:
Minor
-
Resolution: Duplicate
-
Fix Version/s: None
-
Component/s: None
-
Labels:
Description
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
Gliffy Diagrams
Attachments
Issue Links
- is duplicated by
-
-
- Open
-
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
yes fail2ban is a nice tool, sshguard too, but could we do a simple solution? have any plugin that could do this? the problem of fail2ban is windows port i don´t know if it run on windows server and i never tested too
Hi Roberto,
Are you willing either to come up with a complete patch that could be merged into MariaDB, or sponsor implementation of the feature?
well i must develop one, but no idea of how much time i will spent, and when it will be done, but i can try
could i do it with audit plugins? any example?
maybe just to log it to syslog auth.log file could solve *nix problems, but windows version will not have this option (maybe bia windows log functions)
talking with sergei, i will rewrite this MDEV
now a better MDEV description
please close this one, MDEV-4644 is a better explained issue
You can retreive these infos from the general_log if enabled but its definitely less than optimal.
Logging only authentification (having the possibility to only log failures would be even better) either to a file or to syslog would be quite handy.
It would allow more granularity than the global "max_connect_errors" setting (that cover other cases than just an authentication error and as far as i know doesnt allow to retreive informations about these failures) and it could be sent directly to a parsing script or run a cron job such as fail2ban that would check the log for authentication errors and take measures such as blocking according to rules (might block at firewall level and/or at MariaDB level by running specific queries that would block the host from connecting) then unblock automatically after a certain amount of time (and would also permit to log/email these blockings) if required.
That could also be a good solution to block hosts on a cluster of servers or at the router level.