html

Solving fail2ban's Oversized SQLite Database Issue

Many sysadmins encounter unexpected disk usage from fail2ban's SQLite database at /var/lib/fail2ban/fail2ban.sqlite3. The database typically grows from:

Excessive banned IP records
Long default ban durations
Lack of automatic purging
Verbose logging configurations

First, let's shrink the existing database. Connect to the SQLite database and clean old records:

sqlite3 /var/lib/fail2ban/fail2ban.sqlite3
-- Check current size
PRAGMA page_count * PRAGMA page_size;

-- Remove banned IPs older than 30 days
DELETE FROM bans WHERE timeofban < strftime('%s', 'now', '-30 days');

-- Compact the database
VACUUM;

-- Verify new size
PRAGMA page_count * PRAGMA page_size;
.exit

Add these settings to /etc/fail2ban/fail2ban.local:

[Definition]
# Auto-purge bans after 7 days
dbpurgeage = 604800

# Reduce log verbosity
loglevel = INFO

# Limit max bans retained
maxretry = 3

Create a cron job at /etc/cron.weekly/fail2ban-maintenance:

#!/bin/bash
# Compact fail2ban database weekly
sqlite3 /var/lib/fail2ban/fail2ban.sqlite3 "VACUUM;"
# Restart service to apply changes
systemctl restart fail2ban

For high-traffic servers, consider moving to MySQL:

[DEFAULT]
# In /etc/fail2ban/fail2ban.local
dbengine = mysql
dbhost = localhost
dbuser = fail2ban
dbpassword = your_secure_password
dbname = fail2ban

Add this to your monitoring system (e.g., Nagios or Zabbix):

#!/bin/bash
DBSIZE=$(du -b /var/lib/fail2ban/fail2ban.sqlite3 | awk '{print $1}')
WARN=300000000 # 300MB
CRIT=500000000 # 500MB

if [ "$DBSIZE" -gt "$CRIT" ]; then
  echo "CRITICAL: fail2ban db $DBSIZE bytes"
  exit 2
elif [ "$DBSIZE" -gt "$WARN" ]; then
  echo "WARNING: fail2ban db $DBSIZE bytes"
  exit 1
else
  echo "OK: fail2ban db $DBSIZE bytes"
  exit 0
fi

When managing server security with fail2ban, many administrators encounter an unexpectedly large SQLite database file (/var/lib/fail2ban/fail2ban.sqlite3). The database can grow beyond 500MB, causing issues with:

Backup failures due to size limitations
Increased disk I/O operations
Potential performance degradation

fail2ban's SQLite database primarily stores:

1. Ban records (including IPs, timestamps, and jails)
2. Persistent bans (if configured)
3. Long-term statistical data

To quickly reduce the database size, you have several options:

Option 1: Purge Old Records

# First, create a backup
sudo cp /var/lib/fail2ban/fail2ban.sqlite3 /var/lib/fail2ban/fail2ban.sqlite3.bak

# Then connect to the SQLite database
sudo sqlite3 /var/lib/fail2ban/fail2ban.sqlite3

-- Delete records older than 30 days
DELETE FROM bans WHERE timeofban < strftime('%s', 'now', '-30 days');

-- Vacuum the database to reclaim space
VACUUM;

-- Exit SQLite
.quit

Option 2: Reset the Database Completely

sudo systemctl stop fail2ban
sudo rm /var/lib/fail2ban/fail2ban.sqlite3
sudo systemctl start fail2ban

Configuration Adjustment

Add these settings to your /etc/fail2ban/jail.local:

[DEFAULT]
# Automatically purge bans after 7 days
bantime = 7d

# Enable database purging
dbpurgeage = 7d

Regular Maintenance Script

Create a cron job to regularly clean the database:

#!/bin/bash
# /usr/local/bin/fail2ban_db_clean.sh

DB_PATH="/var/lib/fail2ban/fail2ban.sqlite3"
KEEP_DAYS=14

sqlite3 "$DB_PATH" "DELETE FROM bans WHERE timeofban < strftime('%s', 'now', '-$KEEP_DAYS days');"
sqlite3 "$DB_PATH" "VACUUM;"

# Add to cron (runs weekly)
# 0 3 * * 0 /usr/local/bin/fail2ban_db_clean.sh

Using fail2ban-client

For newer versions of fail2ban, you can use built-in commands:

# List all banned IPs
sudo fail2ban-client status sshd

# Unban specific IPs
sudo fail2ban-client set sshd unbanip 192.168.1.100

Database Optimization

Consider these SQLite performance settings if you need to keep historical data:

PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
PRAGMA cache_size = -2000;  # 2MB cache

Add this to your monitoring system to alert when the database grows too large:

#!/bin/bash
# /usr/local/bin/check_fail2ban_db.sh

MAX_SIZE_MB=500
DB_SIZE=$(du -m /var/lib/fail2ban/fail2ban.sqlite3 | awk '{print $1}')

if [ "$DB_SIZE" -gt "$MAX_SIZE_MB" ]; then
    echo "CRITICAL: fail2ban database size is ${DB_SIZE}MB"
    exit 2
else
    echo "OK: fail2ban database size is ${DB_SIZE}MB"
    exit 0
fi

ServerDevWorker