the log line TO JAIL in /etc/mysql/error.log
2022-06-23T16:19:10.452205Z 233 [Note] [MY-010926] [Server] Access denied for user 'webadmin'@'93.223.131.127' (using password: YES)
the regex
(?:(?:\d{6}|\d{4}-\d{2}-\d{2})[ T]\s?\d{1,2}:\d{2}:\d{2}).?(?:\d+[A-Z]) ?(?:\d+ ) ?\[\w+\] (?:\[[^\]]+\] )*Access denied for user '[^']+'@'0.0.0.0' (to database '[^']*'|\(using password: (YES|NO)\))*\s*$
adjusted in www.regex101.com and confirmed working.
match output
0-132 2022-06-23T16:19:10.452205Z 233 [Note] [MY-010926] [Server] Access denied for user 'webadmin'@'93.22...
111-132 (using password: YES)
128-131 YES
tried in filter.d/mysqld-auth.conf
#before = common.conf
failregex = ^%(?:(?:\d{6}|\d{4}-\d{2}-\d{2})[ T]\s?\d{1,2}:\d{2}:\d{2}).?(?:\d+[A-Z]) ?(?:\d+ ) ?\[\w+\] (?:\[[^\]]+\] )*Access denied for user '[^'...etc...
testing with fail2ban-regex gives me
Running tests
=============
Use failregex filter file : mysqld-auth, basedir: /etc/fail2ban
Traceback (most recent call last):
File "/usr/local/bin/fail2ban-regex", line 4, in <module>
__import__('pkg_resources').run_script('fail2ban==0.9.4', 'fail2ban-regex')
File "/usr/lib/python3/dist-packages/pkg_resources/__init__.py", line 667, in run_script
self.require(requires)[0].run_script(script_name, ns)
File "/usr/lib/python3/dist-packages/pkg_resources/__init__.py", line 1463, in run_script
exec(code, namespace, namespace)
File "/usr/local/lib/python3.8/dist-packages/fail2ban-0.9.4-py3.8.egg/EGG-INFO/scripts/fail2ban-regex", line 34, in <module>
exec_command_line()
File "/usr/local/lib/python3.8/dist-packages/fail2ban-0.9.4-py3.8.egg/fail2ban/client/fail2banregex.py", line 596, in exec_command_line
if not fail2banRegex.start(opts, args):
File "/usr/local/lib/python3.8/dist-packages/fail2ban-0.9.4-py3.8.egg/fail2ban/client/fail2banregex.py", line 496, in start
if not self.readRegex(cmd_regex, 'fail'):
File "/usr/local/lib/python3.8/dist-packages/fail2ban-0.9.4-py3.8.egg/fail2ban/client/fail2banregex.py", line 288, in readRegex
reader.getOptions(None)
File "/usr/local/lib/python3.8/dist-packages/fail2ban-0.9.4-py3.8.egg/fail2ban/client/configreader.py", line 283, in getOptions
self._opts = ConfigReader.getOptions(
File "/usr/local/lib/python3.8/dist-packages/fail2ban-0.9.4-py3.8.egg/fail2ban/client/configreader.py", line 137, in getOptions
return self._cfg.getOptions(*args, **kwargs)
File "/usr/local/lib/python3.8/dist-packages/fail2ban-0.9.4-py3.8.egg/fail2ban/client/configreader.py", line 220, in getOptions
v = self.get(sec, option[1])
File "/usr/lib/python3.8/configparser.py", line 799, in get
return self._interpolation.before_get(self, section, option, value,
File "/usr/lib/python3.8/configparser.py", line 395, in before_get
self._interpolate_some(parser, option, L, value, section, defaults, 1)
File "/usr/local/lib/python3.8/dist-packages/fail2ban-0.9.4-py3.8.egg/fail2ban/client/configparserinc.py", line 58, in _interpolate_some
return super(BasicInterpolationWithName, self)._interpolate_some(
File "/usr/lib/python3.8/configparser.py", line 427, in _interpolate_some
raise InterpolationSyntaxError(option, section,
configparser.InterpolationSyntaxError: bad interpolation variable reference "%(?:(?:\\d{6}|\\d{4}-\\d{2}-\\d{2})[ T]\\s?\\d{1,2}:\\d{2}:\\d{2}).?(?:\\d+[A-Z]) ?(?:\\d+ ) ?\\[\\w+\\] (?:\\[[^\\]]+\\] )*Access denied for user '[^']+'@'<HOST>' (to database '[^']*'|\\(using password: (YES|NO)\\))*\\s*$"
tried also in /filter/mysqld-auth.conf
before = common.conf
failregex = ^%(__prefix_line)s(?:etc...
leading to Running tests =============
Use failregex filter file : mysqld-auth, basedir: /etc/fail2ban
Use log file : /var/log/mysql/sample.log
Use encoding : UTF-8
Results
=======
Failregex: 0 total
|- #) [# of hits] regular expression
| 1) [0] ^<lt_<logtype>/__prefix_line>(?:(?:\d{6}|\d{4}-\d{2}-\d{2})[ T]\s?\d{1,2}:\d{2}:\d{2}).?(?:\d+[A-Z]) ?(?:\d+ ) ?\[\w+\] (?:\[[^\]]+\] )*Access denied for user '[^']+'@'<HOST>' (to database '[^']*'|\(using password: (YES|NO)\))*\s*$
`-
Ignoreregex: 0 total
Date template hits:
|- [# of hits] date format
| [1] Year-Month-Day[T ]24hour:Minute:Second(?:\.Microseconds)?(?:Zone offset)?
| [0] (?:DAY )?MON Day 24hour:Minute:Second(?:\.Microseconds)?(?: Year)?
| [0] (?:DAY )?MON Day Year 24hour:Minute:Second(?:\.Microseconds)?
| [0] Year(?P<_sep>[-/.])Month(?P=_sep)Day 24hour:Minute:Second(?:,Microseconds)?
| [0] Day(?P<_sep>[-/])Month(?P=_sep)(?:Year|Year2) 24hour:Minute:Second
| [0] Day(?P<_sep>[-/])MON(?P=_sep)Year[ :]?24hour:Minute:Second(?:\.Microseconds)?(?: Zone offset)?
| [0] Month/Day/Year:24hour:Minute:Second
| [0] Month-Day-Year 24hour:Minute:Second\.Microseconds
| [0] TAI64N
| [0] Epoch
| [0] ^24hour:Minute:Second
| [0] ^<Month/Day/Year2@24hour:Minute:Second>
| [0] ^Year2MonthDay ?24hour:Minute:Second
| [0] MON Day, Year 12hour:Minute:Second AMPM
| [0] ^MON-Day-Year2 24hour:Minute:Second
`-
Lines: 1 lines, 0 ignored, 0 matched, 1 missed
[processed in 0.00 sec]
what is the right way of writing this filter with fail2ban? Is #before important? how does ^%(__prefix_line)s impact? Are my tries hoax and lead python to except or is my python not properly installed?
Using Ubuntu 20.04 THANKS!!!
There's a number of problems with your attempt:
Your first regex begins with %( string interpolation notation but it doesn't use any variable and it doesn't end like it should, so you get Python error bad interpolation variable reference. Python's correct string interpolation operator is %(...)s.
You need to replace the 0.0.0.0 part with either <ADDR> or <HOST> tag (first one matches IP addresses, latter one IP + host names). As it is now, it can only match 0.0.0.0 (and it won't even work with fail2ban which will throw you ERROR: No failure-id group).
Remove the part that tries to match the date pattern from the failregex. Fail2ban first cuts out the date pattern from each input log line and then it applies the failregex.
So, this will match what you want:
^\s*(?:\d+ ) ?\[\w+\] (?:\[[^\]]+\] )*Access denied for user '[^']+'@'<HOST>' (to database '[^']*'|\(using password: (YES|NO)\))*\s*$
%(__prefix_line)s is used to match common line prefixes and you can see its regex in /etc/fail2ban/filter.d/common.conf. It's optional, in your specific use case you can omit this. Otherwise you could use:
^%(__prefix_line)s(?:\d+ ) ?\[\w+\] (?:\[[^\]]+\] )*Access denied for user '[^']+'@'<HOST>' (to database '[^']*'|\(using password: (YES|NO)\))*\s*$