limit traffic on clamav-mirror 1

I´m running a mirror for clamav. Since some clients always download the main.cvd instead of diffs and that results in a traffic up to 150 MB per day for each client. So i searched for a solution, to reduce the current monthly traffic of ~2TB.

Fortunately apache sends already the logs to syslog. I have adjusted syslog so that now two parts of the apache access-log are sent to a mysql-databse – the client-ip and the downloaded bytes.

A simple bash-script grabs all ip from the database with a traffic > 50MB*day of week (each client can download 50 MB / day before the ip is blocked) and add the ip to the firewall. The allowed traffic can can easily be changed.

1. iptables
2. mysql
3. apache
4. syslog
5. shell-script
6. cron
7. statistics
8. files

All files in this post can download at the end.

1. iptables
Add this to your firewall to use a recent-jail and replace $MIRROR_IP
iptables -N DenyAccessClamAV

iptables -A DenyAccessClamAV -p tcp -d $MIRROR_IP --dport 80 -i eth0 -m state --state NEW -m recent --update --hitcount 2 -j DROP

There is no need to adjust the settings for recent as we use a shell-script to add and remove ip inside the recent-jail. I just added ‘update’ and the timelimit in the case of some problems running the script.

2. Setting up the sql-related stuff
login into mysql as root and run:

CREATE DATABASE systemlog;
CREATE USER 'systemlog'@'localhost' IDENTIFIED BY 'MYPASSWORD';
GRANT ALL ON systemlog.* TO 'systemlog'@'localhost' IDENTIFIED BY 'MYPASSWORD';
FLUSH PRIVILEGES;
QUIT;

Import the table-structur:
mysql systemlog < clamavtraffic.sql

3. setup apache
set the logformat to:
LogFormat "%v %h %l %u %t \"%r\" %>s %B \"%{Referer}i\" \"%{User-Agent}i\"" clamav

Make sure, that the vhost for mirror uses the logformat and use logger to send logs to syslog:
CustomLog "| /bin/logger -t apache2" clamav

You can use this logformat generally, so all apache-access-logs are send through syslog. The syslog-handling is explained below.

restart apache

4. configure syslog
Send ip and traffic to mysql – set user, password, and database to your own settings.

destination db_traffic {
program("/usr/bin/mysql -usystemlog -pMYPASSWORD systemlog"
template("INSERT INTO clamavtraffic VALUES (inet_aton('${.apache.client_ip}') ,${.apache.content_length},CURRENT_TIMESTAMP,1 ) ON duplicate KEY UPDATE bytes=bytes+${.apache.content_length}, hits=hits+1;\n")
template-escape(yes));
};


We only want traffic for database.clamav.net add to the database and only log successfull connections (i.e. skip 403):

filter f_mirror-traffic {
match ("200" value(".apache.request_status"));
};

Only parse messages comming from apache.

filter f_apache2 {
program('apache2'); # apache use /bin/logger -t apache2
};


Parse apache-log-messages and split it into different parts.

parser p_apache-access {
csv-parser(columns(
".apache.domain",
".apache.client_ip",
".apache.ident_name",
".apache.user_name",
".apache.timestamp",
".apache.timestamp2",
".apache.request_url",
".apache.request_status",
".apache.content_length",
".apache.referer",
".apache.user_agent")
flags(escape-double-char,strip-whitespace)
delimiters(" ")
quote-pairs('""\[\]') );
};


And finally the log-statement:

log {
source(src);
filter(f_apache2);
parser(p_apache-access);
filter(f_mirror-traffic);
destination (db_traffic);
};


If you would write a normal access-log too, just add a second destination, and change the log-statement:

destination d_apache-logs {
file("/var/log/httpd/${.apache.domain}/${YEAR}${MONTH}${DAY}-access.log"
template("${.apache.client_ip} ${.apache.ident_name} ${.apache.user_name} [${.apache.timestamp}] \"${.apache.request_url}\" ${.apache.request_status} ${.apache.content_length} \"${.apache.referer}\" \"${.apache.user_agent}\"\n")
template_escape(yes)
perm(0644));
};
log {
source(src);
filter(f_apache2);
parser(p_apache-access);
destination (d_apache-logs);
filter(f_mirror-traffic);
destination (db_traffic);
};


restart syslog

5. shell-script
Finally we create a simple shell-script to get the traffic from the database and add ip with too much trafficq to the firewall:

#!/bin/bash
# Program to handle traffic on a clamav-mirror
#
# Created: 12/27/2012
# Version: 1.0
# Author: Florian Schaal (info@schaal-24.de)

# Copyright (c) 2012 Florian Schaal (info@schaal-24.de.) All rights reserved.
#
# This plugin is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License.
# See http://www.fsf.org/licensing/licenses/gpl.html
#
# This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty
#of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
#
#
MAX_MB_DAY=50 # max. daily traffic per ip
DB=systemlog # sql-database
WHITELIST=”78.46.84.243 78.46.84.244 176.9.89.165 176.9.33.188″ # never block whitelisted IP
HTACCESS=/srv/www/clamav.net/httpdocs/.htaccess # add “Deny from” from .htaccess direct to the firewall

# binaries
IPTABLESBIN=/usr/local/sbin/iptables
SQLBIN=/usr/bin/mysql

# calculate allowed traffic
DOM=`date +%e|cut -d” ” -f2`
let MAX_TRAFFIC=$DOM*$MAX_MB_DAY # set allowed_traffic to day of month * MAX_MB_DAY

# get ip from database with traffic >= MAX_TRAFFIC
$SQLBIN $DB -e “select INET_NTOA(source) as source from clamavtraffic where bytes >= $MAX_TRAFFIC*1048576 order by bytes;”|grep -v source|while read ip; do
# add ip to xt_recent
echo +$ip > /proc/net/xt_recent/DenyAccessClamAV
done

# block clients from .htaccess
grep “Deny from” $HTACCESS | grep -v “env=”| cut -d” ” -f3|while read ip; do
echo +$ip > /proc/net/xt_recent/DenyAccessClamAV
done

# remove whitelisted ip from firewall (checking during ban will increase the runtime, so just remove them)
for ip in $(echo $WHITELIST); do
echo -$ip > /proc/net/xt_recent/DenyAccessClamAV
done

6. cron
To add ip with too much traffic, set up a cron-job like
*/1 * * * * /root/scripts/raffic-clamav.sh &> /dev/null

We allow each client a DAILY traffic-limit, so the ip must be removed each day. i use cron for this job:
00 0 * * * echo / > /proc/net/xt_recent/DenyAccessClamAV

As the shell-script calculates the daily traffic based on day-of-month, make sure to flush the database on the first of the month by adding something like this to your crontab:
00 0 1 * * /usr/bin/mysql systemlog -e "TRUNCATE TABLE clamavtraffic;"

You should run cron as root. If you run cron as non-root and/or have no ~/.my.cnf create ~/.my.cnf or use
mysql -u user -ppassword systemlog -e "TRUNCATE TABLE clamavtraffic;"

7. statistics
You can use mysql to show all ip with a daily traffic > 50 MB:

use systemlog;
select INET_NTOA(source) as source,last_hit,bytes/1048576 as traffic_mb from clamavtraffic where bytes >= 50*1048576 order by bytes desc;
+----------------+---------------------+------------+
| source | last_hit | traffic_mb |
+----------------+---------------------+------------+
| 208.125.63.xxx | 2013-01-01 11:14:58 | 117.3044 |
| 66.42.247.xxx | 2013-01-01 11:13:51 | 87.9783 |
| 200.13.242.xxx | 2013-01-01 11:55:03 | 67.5427 |
| 98.90.105.xxx | 2013-01-01 11:58:36 | 58.6522 |
| 69.174.87.xxx | 2013-01-01 11:56:35 | 58.6522 |
| 216.70.185.xxx | 2013-01-01 11:30:32 | 50.9471 |
| 113.97.35.xxx | 2013-01-01 11:41:58 | 50.9471 |
| 50.82.231.xxx | 2013-01-01 11:44:08 | 50.9471 |
+----------------+---------------------+------------+
8 rows in set (0.06 sec)

8. files
clamavtraffic.sql
syslog-ng.conf
traffic-clamav.sh