31

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
  • 856
  • 1
  • 9
  • 16
  • 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) – SmallClanger Feb 08 '20 at 22:11

5 Answers5

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.

Piotr P. Karwasz
  • 5,748
  • 2
  • 11
  • 21
  • 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 09 '20 at 03:46
  • 500 MiB must be several months of logs. I add the `SQL` for deleting the old database entries in the answer. – Piotr P. Karwasz Feb 09 '20 at 17:48
  • So this is interesting. I made a copy of the file, opened it with sqlite, deleted **every line from every table**, and the size of the database is still the same, 519mb. Every single table has 0 rows now (there are 4 as shown with the .tables command: bans, fail2banDb, jails, and logs). Size of the file: still 519mb. (Minus a few bytes, which I assume was the small amount of data I deleted. There were only ~100 lines in `bans`.) – felwithe Feb 09 '20 at 22:39
  • 3
    You need to [VACUUM](https://sqlite.org/lang_vacuum.html) afterwards. – Piotr P. Karwasz Feb 09 '20 at 22:42
  • That worked, thanks. It's now 16kb. I have a hard time understanding where all that data is coming from. There were only 108 lines in `bans`, 8 in `jail`, 8 in `logs`, and 1 in `fail2banDb`. I still have not touched the original database because I'm still not 100% on what's happening and I'm not experienced in sqlite. But somewhere in those 125 lines is 500mb of data. – felwithe Feb 09 '20 at 22:53
  • The column `bans.data` is in JSON format and unlimited in size. It contains all the loglines that caused the ban. – Piotr P. Karwasz Feb 09 '20 at 22:57
  • 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 '20 at 13:16
  • @chiappa - That's savage! The fastest & easiest possible solution also for my 3 GBs of fail2ban.sqlite3 file. – Touhid Jun 19 '20 at 03:48
  • 9GB fail2ban.sqlite3 database here. I only noticed after Fail2ban was updated, attempted to auto-upgrade the sqlite data file, failed, automatically made a copy of it, and then repeated the process a further 10 times. Ouch. My dbpurgeage is set to 1 day, but 9GB must be several months or years worth of data. – MrCarrot Aug 05 '20 at 21:55
  • Why do you think your note is obsolete? I use fail2ban from the current Debian stable repository and had exactly the problem you described (dbpurgeage is 1d, but the DB still contained years worth of entries). Your "obsolete note" helped, thanks! – Heinzi May 02 '21 at 08:45
  • While Debian 10 ships with fail2ban 0.10.x, there are [upstream deb files](https://github.com/fail2ban/fail2ban/releases) of fail2ban 0.11.x, which actually respects `dbpurgeage`. – Piotr P. Karwasz May 02 '21 at 12:27
  • 1
    Thanks for `fail2ban`'s `sqlite` syntax!! Have a loot at my [shell version of this](https://serverfault.com/a/1105465/142978) using time lapse instead of fixed date! – F. Hauri - Give Up GitHub Jul 13 '22 at 10:36
20

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
  • 341
  • 2
  • 5
  • 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](https://github.com/fail2ban/fail2ban/releases), I would recommend Debian users to vote this answer from now on. – Piotr P. Karwasz Jan 30 '21 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 09 '22 at 09: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 '22 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

Andrew Schulman
  • 8,811
  • 21
  • 32
  • 47
crystygye
  • 61
  • 1
  • 1
  • fail2ban-client stop worked for me. Thanks – Xel Naga Oct 17 '20 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 '22 at 08: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.

F. Hauri - Give Up GitHub
  • 1,057
  • 9
  • 14
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
  • 947
  • 10
  • 27