36

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.)

felwithe
  • 936

5 Answers5

38

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 in available in Linux distributions (e.g. Debian 11 and later, Ubuntu 20.04 and later and later, Fedora 33 and later), 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.

Piotr P. Karwasz
  • 6,316
23

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.

neingeist
  • 395
7

1. Using GNU date, 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!

  • Compare:

    time sqlite3 fail2ban.sqlite3 "SELECT count(timeofban) FROM bans
   WHERE DATE(timeofban, 'unixepoch') < '$(date -d 'now -1 month' +'%F %T')';"

    94074
real    0m0.427s
user    0m0.363s
sys     0m0.060s

    With:

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

    93709

    real    0m0.169s
user    0m0.121s
sys     0m0.045s

    Where using DATE() conversion for each row make this operation near 3 time slower!! (Result differ because DATE() is day rounded, while GNU date return EPOCH in seconds.)

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.

2. 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.

F. Hauri - Give Up GitHub
  • 1,089
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

Andrew Schulman
  • 9,142
crystygye
  • 69
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.

Lasse Michael Mølgaard
  • 1,293