We drive traffic in Linux. Part Three

Part 1 , Part 2

Add traffic limiting and bandwidth limiting.

We limit traffic

We are modernizing our base:

ALTER TABLE `users` ADD COLUMN `status` char(1) NOT NULL DEFAULT '1';
ALTER TABLE `users` ADD COLUMN `speed` int(11) NOT NULL DEFAULT '0';
ALTER TABLE `users` ADD COLUMN `traf_limit` bigint(20) NOT NULL DEFAULT '0';
ALTER TABLE `users` ADD COLUMN `traf_remain` bigint(20) NOT NULL DEFAULT '0';


The status field determines the current status of the account (1 - enabled, 0 - blocked), speed - speed limit in Kbit'ah (0 - no limit). The traf_limit and traf_remain fields have a traffic limit in bytes and the remaining amount, respectively, if traf_limit is 0, then we assume that there is no limit.
')
Since now when connecting, we need to sift out users whose limit has expired or their accounts in the status “blocked”, we will correct the configuration of freeradius. In the /etc/freeradius/sql.conf file , replace the lines

authorize_check_query = "SELECT id, login, 'User-Password' AS \"Attribute\", `password` AS \"Value\", '==' AS \"op\" FROM users WHERE login = '%{SQL-User-Name}'"
authorize_reply_query = "SELECT id, login, 'Framed-IP-Address' as \"Attribute\", ip as \"Value\", ':=' as \"op\" FROM users WHERE login = '%{SQL-User-Name}'"
authorize_group_check_query = "SELECT '1' as \"id\",'default' AS \"GroupName\", 'Auth-Type' as \"Attribute\", CASE WHEN status='1' THEN 'MS-CHAP' ELSE 'REJECT' END as \"Value\", ':=' as \"op\" FROM users WHERE login='%{SQL-User-Name}'"

on such:

authorize_check_query = "SELECT id, login, 'User-Password' AS \"Attribute\", `password` AS \"Value\", '==' AS \"op\" FROM users WHERE login = '%{SQL-User-Name}' and status='1' and (traf_remain>0 or traf_limit=0)"
authorize_reply_query = "SELECT id, login, 'Framed-IP-Address' as \"Attribute\", ip as \"Value\", ':=' as \"op\" FROM users WHERE login = '%{SQL-User-Name}' and status='1' and (traf_remain>0 or traf_limit=0)"
authorize_group_check_query = "SELECT '1' as \"id\",'default' AS \"GroupName\", 'Auth-Type' as \"Attribute\", CASE WHEN status='1' THEN 'MS-CHAP' ELSE 'REJECT' END as \"Value\", ':=' as \"op\" FROM users WHERE login='%{SQL-User-Name}' and status='1' and (traf_remain>0 or traf_limit=0)"

and restart freeradius.

Now we update the parser script, code:

#!/usr/bin/perl

use DBI;

# ...
sub inet_aton {
my @addr = split(/\./,$_[0]);
my $dec = 0;
for($n = 3; $n >= 0; $n--) {
$dec += ($addr[-$n-1] << 8 * $n);
}
return $dec;
}

# ,
my $db_name = "ulogdb";
my $db_user = "ulog";
my $db_pass = "1234";

# -
$account_log = "/var/log/ulog-acctd/account.log";

#
my $DBH = DBI->connect("DBI:mysql:$db_name:localhost",$db_user,$db_pass) or die "Error connecting to database";

# --set-limits,
if ($ARGV[0] eq "--set-limits") {
print "$ARGV[0]\n";
# 1,
my $move_unused = 1;
if ($move_unused) {
$STH = $DBH->prepare("update users set traf_remain=traf_remain+traf_limit where traf_limit");
} else {
$STH = $DBH->prepare("update users set traf_remain=traf_limit where traf_limit");
}
$STH->execute; $STH->finish;
exit;
}

# ip+id_user
my $STH = $DBH->prepare("select ip,id from users");
$STH->execute;
while (@tmp = $STH->fetchrow_array()) {
$users{$tmp[0]} = $tmp[1];
}
$STH->finish;

#
my $STH = $DBH->prepare("select prio,firstip,lastip,id from zones order by prio");
$STH->execute;
while (@tmp = $STH->fetchrow_array()) {
$zones[$tmp[0]] = [$tmp[1], $tmp[2], $tmp[3]];
}
$STH->finish;

#
system "cp $account_log /tmp/ulog-parser.tmp && cat /dev/null > $account_log";
open LOGFILE,"< /tmp/ulog-parser.tmp";
while (<LOGFILE>) {
chomp;

# $saddr ,
#

($ts,$saddr,$daddr,$bytes) = split /\t/;

# ,
#
# . 1

$ts = $ts - $ts % 60;

#
# -
# :
# -> id ->

if (exists($users{$daddr})) {
#
$zone_id = 0;
for($i=0;$i>=$zones;$i++) {
$nip = inet_aton($saddr);
if ($zones[$i][0] <= $nip and $zones[$i][1] >= $nip) {
$zone_id = $zones[$i][2];
last;
}
}
$data{$ts}{$users{$daddr}}{$zone_id} += $bytes;
}
}
close LOGFILE;
unlink("/tmp/ulog-parser.tmp");

# , mgyk :)
my $STH = $DBH->prepare("insert into data (id_user,id_zone,ts,bytes) values(?,?,?,?) on duplicate key update bytes=bytes+?");
my $STH_LIMIT = $DBH->prepare("update users set traf_remain=traf_remain-? where id=? and traf_limit");
#
#
for $ts (keys %data) {
for $id_user (keys %{$data{$ts}}) {
for $id_zone(keys %{$data{$ts}{$id_user}}) {
$STH->execute($id_user,$id_zone,$ts,$data{$ts}{$id_user}{$id_zone},$data{$ts}{$id_user}{$id_zone});
$STH->finish;
# -
$STH_LIMIT->execute($data{$ts}{$id_user}{$id_zone},$id_user);
$STH_LIMIT->finish;
}
}
}

# ,
$STH = $DBH->prepare("select ip from users where traf_limit>0 and traf_remain<=0");
$STH->execute;
while (($ip) = $STH->fetchrow_array) {
my $lnk = `/sbin/ip addr show|/bin/grep $ip`;
$lnk =~ m/^.+(ppp[0-9]+)$/;
#
system("/bin/kill `cat /var/run/$1.pid`");
}
#
$DBH->disconnect;

If you have users with limited traffic, add a parser script to the crontab with the --set-limits parameter on the first day of the month, thus users will be charged traffic every month. In order to timely disable those whose limit has already expired, set the interval for starting the parser once every 1-2 minutes. For example:

* * * * * root /usr/bin/ulog-parser.pl
1 0 1 * * root /usr/bin/ulog-parser.pl --set-limits


every minute the log is parsed and at 00:01 on the 1st of every month we update the traffic limits. There is simply no place (:

We cut speed

Add a rule to iptales:

iptables -t mangle -A FORWARD -d 10.1.0.0/24 -j MARK --set-mark 0x1

We mark all the packages going to the addresses of our users.

When initializing any ppp session, the scripts in /etc/ppp/ip-up.d are run. We have this as it should be to the article. Create a script in this directory that, when a user connects, will limit the bandwidth of his interface, I called it set_speed :

#!/usr/bin/perl

use DBI;

my $db_name = "ulogdb";
my $db_user = "ulog";
my $db_pass = "1234";

my ($ip,$iface) = @ARGV[4,0];

my $DBH = DBI->connect("DBI:mysql:$db_name:localhost",$db_user,$db_pass) or die "Error connecting to database";

my $STH = $DBH->prepare("select speed from users where ip='$ip'");
$STH->execute;
my ($speed) = $STH->fetchrow_array;
$STH->finish;

if ($speed) {
system("/sbin/tc qdisc add dev $iface root handle 1: htb");
system("/sbin/tc class add dev $iface classid 1:1 htb rate ${speed}kbit");
system("/sbin/tc filter add dev $iface protocol ip handle 1 fw classid 1:1");
}
$DBH->disconnect;

It also needs to specify the username of the database and password. In fact, it would be better to store them in a separate config, but I did not bother with the implementation, you can do it yourself (:

Remember to add the + x attribute to the scripts.

Ready scripts, configs and database schema here .