We drive traffic in Linux. Part two.

Read the first part here .

In this article we will look at:
- Authorization of users from the MySQL database.
- Detailed traffic by directions.

Authorization from MySQL. Freeradius

In the previous article, I did not focus on setting up authorization and left it as it is, i.e. user accounts were stored in a text file. This is convenient if you have 5-10 users and you rarely change passwords, but if there are more users, difficulties arise. It would be much more convenient if pptpd took user accounts from the users table, where there are already user logins / passwords and their ip addresses.
')
PPTPD uses the Radius authorization server to communicate with the database (and indeed, for other authorization methods other than a text file).

Install:

root@srv:~# apt-get install freeradius freeradius-mysql radiusclient1

Let's say PPTPD login through radius, for this we add the line to the / etc / ppp / pptpd-options file:

plugin radius.so

Customize freeradius.

All main configs are stored in the / etc / freeradius directory. In the radiusd.conf config , find the section:

authorize {
#
# The preprocess module takes care of sanitizing some bizarre
# attributes in the request, and turning them into attributes
# which are more standard.
...

and uncomment the parameter

# See "Authorization Queries" in sql.conf
sql
...

Bring the sql.conf config to this view:

sql {
driver = "rlm_sql_mysql"
server = "localhost"

login = "ulog"
password = "1234"
radius_db = "ulogdb"

deletestalesessions = yes

sqltrace = no
sqltracefile = ${logdir}/sqltrace.sql

num_sql_socks = 5
connect_failure_retry_delay = 60

sql_user_name = "%{User-Name}"

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}'"
}

Pay attention to the parameters login , password and radius_db , specify in them the login and password to access our database and the name of the database itself.

PPTPD uses a radiusclient to access radius, since The radius server itself can be a separate machine on the network. For their interaction, you need to specify the “client” of freeradius and the “server” of radiusclient.

Open the clients.conf file and find the section:

client 127.0.0.1

change the value of the secret parameter to any word, it will be a certain “password” for the radiusclient:

secret = habrahabra

Add an entry about our radius server to the / etc / radiusclient / servers file:

localhost habrahabra

As you have already noticed, the second word is “password”, which we specified in clients.conf .

By default, radiusclient does not know how to ms-chap, for this you need an additional dictionary of attributes. We copy:

root@srv:~# cp /usr/share/freeradius/dictionary.microsoft /etc/radiusclient/

Connect it to other dictionaries, add a line to the end of the / etc / radiusclient / dictionary file:

INCLUDE /etc/radiusclient/dictionary.microsoft

Restart pptpd and freeradius:

root@srv:# /etc/init.d/freeradius restart && /etc/init.d/pptpd restart

This is where the pptpd and freeradius configuration is completed, now the authorization will be done from the database. Try to connect to the server, if everything works, go ahead.

Detail by directions

Almost all providers have certain “charging zones”, these are entire subnets or ranges of IP addresses, where the price per MB of traffic differs significantly from the price per MB of external traffic. For example, internal media or gaming servers, Yandex services. And it would be nice to see from which charging zone traffic came. Let's start :)

To do this, we will upgrade our database, add another table:

CREATE TABLE `zones` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(64) NOT NULL,
`firstip` bigint(20) NOT NULL,
`lastip` bigint(20) NOT NULL,
`prio` int(11) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY (`prio`)
) ENGINE=MyISAM DEFAULT CHARSET utf8;

and modify the existing data table:

ALTER TABLE `data` ADD COLUMN `id_zone` int(11) NOT NULL;
ALTER TABLE `data` ADD UNIQUE (`id_user`,`id_zone`,` ts`);

Add 3 charging zones:

#
insert into zones (name,firstip,lastip,prio) values('local',inet_aton('10.1.0.1'),inet_aton('10.1.0.254'),0);
#
insert into zones (name,firstip,lastip,prio) values('habrahabr',inet_aton('62.213.122.2'),inet_aton('62.213.122.2'),1);
#
insert into zones (name,firstip,lastip,prio) values('inet',inet_aton('0.0.0.1'),inet_aton('254.254.254.254'),2);

Pay attention to the value of the prio field; when adding charging zones, you should proceed from the rule: the cheaper the traffic, the higher its priority (0 is the highest).

Parser script:

#!/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";

# 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;
($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+?");

#
#
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;
}
}
}
#
$DBH->disconnect;


That's all for now, in the next part I will describe traffic limiting and speed limiting.

UPD: Oops, ochepyatka crept into the script code, corrected (: