mysqld-auth.conf not compatible with mysql 5.7.11 #1332

GitzJoey · 2016-02-24T15:44:13Z

Environment:

Fedora 23 Server Edition, Fail2Ban installed via OS

The issue:

it seems that mysqld-auth conf regex is not compatible with the mysql 5.7.11 error log

log sample

2016-02-24T15:26:18.237955Z 6 [Note] Access denied for user 'root'@'localhost' (using password: YES)
as noticed:

separator between date time using "T"

there's leading numeric after time

using [Note] instead of [Warning]

Steps to reproduce

enabled the mysqld-auth filter
emulate fail login in mysql
fail2ban-client status mysqld-auth not adding any ip address

Expected behavior

fail2ban should add new ip address

…ression now); closes fail2ban#1332

sebres · 2016-02-24T16:26:04Z

@GitzJoey fixed in #1333: testing is welcome

GitzJoey · 2016-02-24T16:53:20Z

tested and seems ok now, cant wait to ban those nasty ips

sebres · 2016-02-24T17:13:27Z

BTW: if you not really need to access mysql from outside, the more secure way would be to forbid it completely (via firewall) or configure mysql to listen locally only (like bind-address = 127.0.0.1)
Or if you know all the connector-hosts, another way would be to allow connecting to the mysql-port for this dedicated IP-addresses only (as well via firewall or mysql-privileges).

GitzJoey · 2016-02-25T16:20:02Z

hi,
its seem still failing for this particular ip

2016-02-25T12:27:48.767392Z` 10 [Warning] IP address '61.147.82.8' could not be resolved: Name or service not known
2016-02-25T12:27:49.020704Z 10 [Note] Access denied for user 'root'@'61.147.82.8' (using password: YES)

fyi, this ip hitting my server 12 times while my maxretry is 4 but no ban issued. I need to open the mysqld port since i working remotely with dynamic ip
thanks

GitzJoey · 2016-02-25T16:21:54Z

here's the fail2ban-regex

[root@fedora gitzjoey]# fail2ban-regex /var/log/mysqld.log /etc/fail2ban/filter.d/mysqld-auth.conf

Running tests
=============

Use   failregex filter file : mysqld-auth, basedir: /etc/fail2ban
Use         log file : /var/log/mysqld.log
Use         encoding : UTF-8


Results
=======

Failregex: 13 total
|-  #) [# of hits] regular expression
|   1) [13] ^\s*(<[^.]+\.[^.]+>)?\s*(?:\S+ )?(?:kernel: \[ *\d+\.\d+\] )?(?:@vserver_\S+ )?(?:(?:\[\d+\])?:\s+[\[\(]?mysqld(?:\(\S+\))?[\]\)]?:?|[\[\(]?mysqld(?:\(\S+\))?[\]\)]?:?(?:\[\d+\])?:?)?\s(?:\[ID \d+ \S+\])?\s*(?:\d+ |\d{6} \s?\d{1,2}:\d{2}:\d{2} )?\[\w+\] Access denied for user '[^']+'@'<HOST>'
`-

Ignoreregex: 0 total

Date template hits:
|- [# of hits] date format
|  [14] Year-Month-Day[T ]24hour:Minute:Second(?:\.Microseconds)?(?:Zone offset)?
`-

Lines: 14 lines, 0 ignored, 13 matched, 1 missed [processed in 0.00 sec]
|- Missed line(s):
|  2016-02-25T12:27:48.767392Z 10 [Warning] IP address '61.147.82.8' could not be resolved: Name or service not known
`-
[root@fedora gitzjoey]#

[root@fedora fail2ban]# fail2ban-client status mysqld-auth
Status for the jail: mysqld-auth
|- Filter
|  |- Currently failed: 0
|  |- Total failed:     0
|  `- Journal matches:
`- Actions
   |- Currently banned: 0
   |- Total banned:     0
   `- Banned IP list:

sebres · 2016-02-25T18:39:25Z

Has you reloaded or restarted fail2ban after changing of the filter? for example ?sudo? fail2ban-client reload.
Or is this time 2016-02-25T12:27:49 (or at least 4 log entries you mean with the last time of it) was later as restart time - findtime, that was specified for your jail? Because the fail2ban-regex in the opposite to fail2ban-server does not taken into account the time of failure.

Normally all new failures (that will be found with fail2ban-regex) will be counted and banned after reaching of maxretry within time specified by findtime.

GitzJoey · 2016-02-26T00:31:47Z

yes i'm sure i've restarted
the maxretry is 5 and findtime = 600
here's the full log

2016-02-25T12:27:48.767392Z 10 [Warning] IP address '61.147.82.8' could not be resolved: Name or service not known
2016-02-25T12:27:49.020704Z 10 [Note] Access denied for user 'root'@'61.147.82.8' (using password: YES)
2016-02-25T12:27:49.878115Z 11 [Note] Access denied for user 'root'@'61.147.82.8' (using password: YES)
2016-02-25T12:27:50.764446Z 12 [Note] Access denied for user 'root'@'61.147.82.8' (using password: YES)
2016-02-25T12:27:51.798530Z 13 [Note] Access denied for user 'root'@'61.147.82.8' (using password: YES)
2016-02-25T12:27:52.636730Z 14 [Note] Access denied for user 'root'@'61.147.82.8' (using password: YES)
2016-02-25T12:27:53.528699Z 15 [Note] Access denied for user 'root'@'61.147.82.8' (using password: YES)
2016-02-25T12:27:55.150192Z 16 [Note] Access denied for user 'root'@'61.147.82.8' (using password: YES)
2016-02-25T12:27:58.057936Z 17 [Note] Access denied for user 'root'@'61.147.82.8' (using password: YES)
2016-02-25T12:27:59.014065Z 18 [Note] Access denied for user 'root'@'61.147.82.8' (using password: YES)
2016-02-25T12:27:59.781714Z 19 [Note] Access denied for user 'root'@'61.147.82.8' (using password: YES)
2016-02-25T12:28:00.645264Z 20 [Note] Access denied for user 'root'@'61.147.82.8' (using password: YES)
2016-02-25T12:28:01.581767Z 21 [Note] Access denied for user 'root'@'61.147.82.8' (using password: YES)
2016-02-25T12:28:03.418483Z 22 [Note] Access denied for user 'root'@'61.147.82.8' (using password: YES)

sebres · 2016-02-26T07:55:38Z

Hmm, I don't know - it is as I wrote, so should be banned if you have restarted before 12:17.
BTW you should see in the fail2ban.log the restart resp. reload of jail.

GitzJoey · 2016-02-28T17:43:55Z

fail2ban-client reload mysqld-auth result
i dont know does "backend" setting affecting the jail?

2016-02-29 00:28:38,702 fail2ban.jail           [1006]: INFO    Jail 'mysqld-auth' stopped
2016-02-29 00:28:38,707 fail2ban.server         [1006]: INFO    Changed logging target to /var/log/fail2ban.log for Fail2ban v0.9.3
2016-02-29 00:28:38,708 fail2ban.jail           [1006]: INFO    Creating new jail 'mysqld-auth'
2016-02-29 00:28:38,708 fail2ban.jail           [1006]: INFO    Jail 'mysqld-auth' uses systemd
2016-02-29 00:28:38,710 fail2ban.jail           [1006]: INFO    Initiated 'systemd' backend
2016-02-29 00:28:38,713 fail2ban.filter         [1006]: INFO    Set maxRetry = 5
2016-02-29 00:28:38,714 fail2ban.actions        [1006]: INFO    Set banTime = 86400
2016-02-29 00:28:38,714 fail2ban.filter         [1006]: INFO    Set findtime = 600
2016-02-29 00:28:38,728 fail2ban.filtersystemd  [1006]: NOTICE  Jail started without 'journalmatch' set. Jail regexs will be checked against all journal entries, which is not advised for performance reasons.
2016-02-29 00:28:38,729 fail2ban.jail           [1006]: INFO    Jail 'mysqld-auth' started
2016-02-29 00:40:04,618 fail2ban.jail           [1006]: INFO    Jail 'mysqld-auth' stopped
2016-02-29 00:40:04,626 fail2ban.server         [1006]: INFO    Changed logging target to /var/log/fail2ban.log for Fail2ban v0.9.3
2016-02-29 00:40:04,627 fail2ban.jail           [1006]: INFO    Creating new jail 'mysqld-auth'
2016-02-29 00:40:04,628 fail2ban.jail           [1006]: INFO    Jail 'mysqld-auth' uses systemd
2016-02-29 00:40:04,629 fail2ban.jail           [1006]: INFO    Initiated 'systemd' backend
2016-02-29 00:40:04,635 fail2ban.filter         [1006]: INFO    Set maxRetry = 5
2016-02-29 00:40:04,636 fail2ban.actions        [1006]: INFO    Set banTime = 86400
2016-02-29 00:40:04,637 fail2ban.filter         [1006]: INFO    Set findtime = 600
2016-02-29 00:40:04,650 fail2ban.filtersystemd  [1006]: NOTICE  Jail started without 'journalmatch' set. Jail regexs will be checked against all journal entries, which is not advised for performance reasons.
2016-02-29 00:40:04,651 fail2ban.jail           [1006]: INFO    Jail 'mysqld-auth' started

sebres · 2016-02-29T10:37:53Z

And?
Restart occurred "29 Feb", failures in "25 Feb"...
What do you want to say?

GitzJoey · 2016-02-29T11:52:19Z

sorry maybe i post a bad example
what i'm trying to say is, even thou the fail2ban already restarted with the new regex for mysqld-auth
it still not banning the offender. this is my tries today (i'm using mysql workbench 6.3 to test the connection by creating new connection using bad password)

sebres · 2016-02-29T12:35:14Z

Magic happens here :)

Well, I think your jail does not get any failure of mysqld at all. May've many reasons:

whatsoever still the old regexp expression will be used, (check via fail2ban-client -d | grep "mysqld-auth.*regex", it uses really new expression);
mysqld does not provide logging via systemd (rsyslog?, etc.);
or systemd backend does not get the log entries of mysqld (try polling or pyinotify and specify correct logpath)

GitzJoey · 2016-03-02T02:17:59Z

hi sebres,
just to inform you that its working now after changing backend to auto
BIG THANKS for your help and support.

thanks

@kshetragia

ver. 0.9.4 (2016/03/08) - for-you-ladies ----------- - Fixes: * roundcube-auth jail typo for logpath * Fix dnsToIp resolver for fqdn with large list of IPs (gh-1164) * filter.d/apache-badbots.conf - Updated useragent string regex adding escape for `+` * filter.d/mysqld-auth.conf - Updated "Access denied ..." regex for MySQL 5.6 and later (gh-1211, gh-1332) * filter.d/sshd.conf - Updated "Auth fail" regex for OpenSSH 5.9 and later * Treat failed and killed execution of commands identically (only different log messages), which addresses different behavior on different exit codes of dash and bash (gh-1155) * Fix jail.conf.5 man's section (gh-1226) * Fixed default banaction for allports jails like pam-generic, recidive, etc with new default variable `banaction_allports` (gh-1216) * Fixed `fail2ban-regex` stops working on invalid (wrong encoded) character for python version < 3.x (gh-1248) * Use postfix_log logpath for postfix-rbl jail * filters.d/postfix.conf - add 'Sender address rejected: Domain not found' failregex * use `fail2ban_agent` as user-agent in actions badips, blocklist_de, etc (gh-1271) * Fix ignoring the sender option by action_mw, action_mwl and action_c_mwl * Changed filter.d/asterisk regex for "Call from ..." (few vulnerable now) * Removed compression and rotation count from logrotate (inherit them from the global logrotate config) - New Features: * New interpolation feature for definition config readers - `<known/parameter>` (means last known init definition of filters or actions with name `parameter`). This interpolation makes possible to extend a parameters of stock filter or action directly in jail inside jail.local file, without creating a separately filter.d/*.local file. As extension to interpolation `%(known/parameter)s`, that does not works for filter and action init parameters * New actions: - nftables-multiport and nftables-allports - filtering using nftables framework. Note: it requires a pre-existing chain for the filtering rule. * New filters: - openhab - domotic software authentication failure with the rest api and web interface (gh-1223) - nginx-limit-req - ban hosts, that were failed through nginx by limit request processing rate (ngx_http_limit_req_module) - murmur - ban hosts that repeatedly attempt to connect to murmur/mumble-server with an invalid server password or certificate. - haproxy-http-auth - filter to match failed HTTP Authentications against a HAProxy server * New jails: - murmur - bans TCP and UDP from the bad host on the default murmur port. * sshd filter got new failregex to match "maximum authentication attempts exceeded" (introduced in openssh 6.8) * Added filter for Mac OS screen sharing (VNC) daemon - Enhancements: * Do not rotate empty log files * Added new date pattern with year after day (e.g. Sun Jan 23 2005 21:59:59) http://bugs.debian.org/798923 * Added openSUSE path configuration (Thanks Johannes Weberhofer) * Allow to split ignoreip entries by ',' as well as by ' ' (gh-1197) * Added a timeout (3 sec) to urlopen within badips.py action (Thanks M. Maraun) * Added check against atacker's Googlebot PTR fake records (Thanks Pablo Rodriguez Fernandez) * Enhance filter against atacker's Googlebot PTR fake records (gh-1226) * Nginx log paths extended (prefixed with "*" wildcard) (gh-1237) * Added filter for openhab domotic software authentication failure with the rest api and web interface (gh-1223) * Add *_backend options for services to allow distros to set the default backend per service, set default to systemd for Fedora as appropriate * Performance improvements while monitoring large number of files (gh-1265). Use associative array (dict) for monitored log files to speed up lookup operations. Thanks @kshetragia * Specified that fail2ban is PartOf iptables.service firewalld.service in .service file -- would reload fail2ban if those services are restarted * Provides new default `fail2ban_version` and interpolation variable `fail2ban_agent` in jail.conf * Enhance filter 'postfix' to ban incoming SMTP client with no fqdn hostname, and to support multiple instances of postfix having varying suffix (gh-1331) (Thanks Tom Hendrikx) * files/gentoo-initd to use start-stop-daemon to robustify restarting the service * tag '0.9.4': (138 commits) MANIFEST RELEASE and man pages updates Changes for the 0.9.4 release datedetector: epoch time expression fix (now 10-11 chars, only whole number - anchored ^...\b or by special case within [], audit()) + test cases extended (positive/negative) changelog about gentoo initd added wp-admin ENH(TST): a hypothetical example to show/test needing trailing anchoring ENH: revert back to having detailed suffix anchored at the end for mysqld-auto.conf Changelog for the recent PR and added Tom to THANKS mysqld: failregex fixed (accepts different log level, more secure expression now); closes #1332 Add support for matching postfix multi-instance daemon names by default DOC: removed Nick from listed as FreeBSD maintainer DOC: adjusted ISSUE_TEMPLATE.md picking on @sebres's version ENH: github templates for issues and PRs ENH: add codecov support to travis.yml and bandge to README.md gentoo-initd: Use start-stop-daemon in order to handle crashes better regexp rewritten (few vulnerable as previous) + test case added Update asterisk filter: changed regex for "Call from ...". Sometimes extension can have a plus symbol (+) because they can be phone number. Closes #1309 Add new regex into postfix filter. The new regexp is able to detect bad formatted SMTP EHLO command Remove compression and count from logrotate gentoo-initd: do not hide useful output ...

sebres self-assigned this Feb 24, 2016

sebres added a commit to sebres/fail2ban that referenced this issue Feb 24, 2016

mysqld: failregex fixed (accepts different log level, more secure exp…

667785b

…ression now); closes fail2ban#1332

sebres mentioned this issue Feb 24, 2016

mysqld: failregex fixed (accepts different log level, more secure expression now) #1333

Merged

sebres removed their assignment Feb 24, 2016

sebres closed this as completed in #1333 Feb 25, 2016

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

mysqld-auth.conf not compatible with mysql 5.7.11 #1332

mysqld-auth.conf not compatible with mysql 5.7.11 #1332

GitzJoey commented Feb 24, 2016

sebres commented Feb 24, 2016

GitzJoey commented Feb 24, 2016

sebres commented Feb 24, 2016

GitzJoey commented Feb 25, 2016

GitzJoey commented Feb 25, 2016

sebres commented Feb 25, 2016

GitzJoey commented Feb 26, 2016

sebres commented Feb 26, 2016

GitzJoey commented Feb 28, 2016

sebres commented Feb 29, 2016

GitzJoey commented Feb 29, 2016

sebres commented Feb 29, 2016

GitzJoey commented Mar 2, 2016

mysqld-auth.conf not compatible with mysql 5.7.11 #1332

mysqld-auth.conf not compatible with mysql 5.7.11 #1332

Comments

GitzJoey commented Feb 24, 2016

Environment:

The issue:

Steps to reproduce

Expected behavior

sebres commented Feb 24, 2016

GitzJoey commented Feb 24, 2016

sebres commented Feb 24, 2016

GitzJoey commented Feb 25, 2016

GitzJoey commented Feb 25, 2016

sebres commented Feb 25, 2016

GitzJoey commented Feb 26, 2016

sebres commented Feb 26, 2016

GitzJoey commented Feb 28, 2016

sebres commented Feb 29, 2016

GitzJoey commented Feb 29, 2016

sebres commented Feb 29, 2016

GitzJoey commented Mar 2, 2016