32

Recently my backups have started failing, and I tracked the problem to the file /var/lib/fail2ban/fail2ban.sqlite3. It is over 500mb. I am not sure whether it has been growing over time or if this is a recent development.

How can I get it to a reasonable size and keep it that size? (For the purposes of this let's say under 500mb.)

1
  • 1
    Stop the service; rm -f /var/lib/fail2ban/fail2ban.sqlite3; start it again. Hey presto, your database is now a /lot/ smaller :D (I'm only half joking) Feb 8, 2020 at 22:11

5 Answers 5

36

There is a dbpurgeage parameter in fail2ban.conf, which tells how many days of data to keep in the database. The default is one day (1d), so try do decrease it to a couple of hours:

dbpurgeage = 8h

This setting is coupled with findtime: it makes no sense to have a findtime longer than dbpurgeage.

Edit (2021): The note below was true at the time of writing. However nowadays check out neingeist answer instead: fail2ban 0.11.x which starts being available in Linux distributions (e.g. Debian testing, Ubuntu 20.04 and later, Fedora 33), respects the dbpurgeage setting.

Obsolete note: By looking at my own fail2ban database, the dbpurgeage setting does not seem to be working. Therefore the only solution is to delete the entries manually. For example, in order to delete last year's entries run:

sqlite3 /var/lib/fail2ban/fail2ban.sqlite3 \
  "DELETE FROM bans WHERE DATE(timeofban, 'unixepoch') < '2020-01-01'; VACUUM;"

(the sqlite3 executable is usually in the homonymous package).

There seem to be no way to perform a VACUUM of the database without sqlite performing a copy of the database in the same directory. However you can copy the file to another filesystem before performing the operation and than copy back the smaller database.

12
  • 2
    Above that value in fail2ban.conf it says "Sets age at which bans should be purged from the database". The 500mb can't be a day's worth of bans, can it? (Also findtime appears nowhere in the file.)
    – felwithe
    Feb 9, 2020 at 3:46
  • 500 MiB must be several months of logs. I add the SQL for deleting the old database entries in the answer. Feb 9, 2020 at 17:48
  • 3
    You need to VACUUM afterwards. Feb 9, 2020 at 22:42
  • 4
    The sqlite3 command creates a temporary 'fail2ban.sqlite3-journal' file of equal size. My fail2ban.sqlite3 was 17GB so it was not a viable option, not to mention it takes a long time. So I just did a '/etc/init.d/fail2ban stop' and simply removed fail2ban.sqlite3. A new file was created from scratch when I started fail2ban again.
    – chiappa
    Mar 31, 2020 at 13:16
  • 1
    Thanks for fail2ban's sqlite syntax!! Have a loot at my shell version of this using time lapse instead of fixed date! Jul 13, 2022 at 10:36
21

You can update to 0.11.x (which contains code to do the purge) and then delete the huge database followed by a restart of fail2ban. It will recreate the database. This is the easiest solution with no drawbacks for most people.

While fail2ban 0.11.x actually contains code to purge old entries (the older version did not!), it does no VACUUM. So another option is to wait for fail2ban to purge the old entries (happens every hour) and perform a manual sqlite3 /var/lib/fail2ban/fail2ban.sqlite3 "VACUUM;". Without the VACUUM the database file will stay at its size.

3
  • 2
    I wouldn't recommend deleting the database, since this would mean an amnesty for the most recent attackers. This note aside, since there are deb packages available, I would recommend Debian users to vote this answer from now on. Jan 30, 2021 at 14:25
  • 1
    Running VACUUM on my 235 MB. db reduced it to 118 K. So that seems the best first thing to try.
    – mivk
    Jul 9, 2022 at 9:43
  • I agree with @mivk. If you have fail2ban > 0.11.x, the entries will be purged and you just need to VACUUM.
    – neingeist
    Jul 28, 2022 at 15:41
6
sudo /etc/init.d/fail2ban stop

sudo rm -rf /var/lib/fail2ban

sudo /etc/init.d/fail2ban start

sudo reboot

resolved everything for me. check after reboot

df -h

i had 25gb of sqlite files, memory was 94% of 40gb

2
  • fail2ban-client stop worked for me. Thanks
    – Xel Naga
    Oct 17, 2020 at 12:19
  • If you are on an old version which will not regenerate the fail2ban.sqlite3 file, then you can do sudo apt-get purge fail2ban ; sudo apt-get install fail2ban and then reconfigure fail2ban.
    – cherouvim
    Apr 12, 2022 at 8:34
5

Simple command

In addition to Piotr P. Karwasz's correct answer, I would show my simple syntax, using GNU date (not BSD date):

First show stat of bans table:

sqlite3 fail2ban.sqlite3 'SELECT count(timeofban) FROM bans'
1147784
sqlite3 fail2ban.sqlite3 "SELECT count(timeofban) FROM bans
      WHERE timeofban < `date -d 'now -1 month' +%s`;"
1129083

Doing UNIXEPOCH translation at command line parameter expansion is quicker as sqlite don't have to translate each rows!

Of course, answer should be different on your system!

Then

sqlite3 fail2ban.sqlite3 "DELETE FROM bans WHERE 
        timeofban < $(date -d 'now -1 month' +%s); VACUUM;"

(Don't miss VACUUM;!)

Main advantage of using date command with a time lapse is that this could be placed in a periodically script.

Note: My choice is to run this with now -1 month as time lapse, every nights.

Without GNU date, using sqlite3 syntax:

Browsing sqlite manual, I've finally found a correct syntax:

sqlite3 fail2ban.sqlite3 "DELETE FROM bans
     WHERE timeofban <= STRFTIME('%s', DATE('now', '-1 month'));VACUUM;"

Again: Using this syntax, sqlite3 will make translation on request, not on rows!!

Remark about crontabs

If you plan to write this an a crontab, dont miss to escape all percent signs!!

1 2 * * * root /usr/bin/sqlite3 /var/lib/fail2ban/fail2ban.sqlite3 "delete from bans where timeofban <= strftime('\%s', date('now', '-42 days'));vacuum;"

Will run this every night at 02:01. The command could also be added to a BASH script into /etc/cron.weekly for instance.

0

I ended up on an alternate route when reducing my log.

Because I made own code that summarises the ip adresses using these simple rules.

Given an ip range: If more than 25% of the ips are listed in Fail2ban jails, then I will remove the individual ips and block the entire range instead.

I will then check for larger and larger ip ranges if it is still the case that at least 25% of the ips belongs to a blocked range, in which case the blocked range gets larger.

I used a custom binary search tree for my solution, since it searches through the tree using the ip address to find out if the tree can be reduced.

The math for calcuting the search scope needs a bit of fiddling, but that is basically how I ended up blocking 2 Chinese, 1 Iranian and 1 Russian ISP altogether.

And since there were repeat offenders there is no timer that can take them off the block list.

2
  • Hey, this is good to know; would you like to share your code though? Could possibly become a great improvement in fail2ban itself...
    – ulidtko
    Jan 30 at 20:32
  • 1
    Long time ago I did it, but I basically sorted all the blocked ips numerical and stored them in a search tree, because looking up to check if a new ip that has to be added is already covered by the blocked range. Also when summarizing: Do a ratio calculation between real spammer and "innocent" ips covered by the block every time you expand your blocked range and use this ratio as a criteria whether or not expand block range. And finally: If criteria is 25% of range is spamming, then do not block ranges of 8 ips or smaller. It is not worth the effort. Jan 31 at 21:30

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .