Для системного администратора

В этой статье мы рассмотрим:
— Авторизацию пользователей из базы данных MySQL.
— Детализацию трафика по направлениям.

Авторизация из MySQL. FreeRadius

В предыдущей статье я не стал акцентировать внимание на настройке авторизации и оставил как есть, т. е. аккаунты пользователей хранились в текстовом файле. Это удобно, если у Вас 5-10 пользователей и пароли Вы меняете редко, но если пользователей больше, возникают трудности. Было бы куда удобней, если бы pptpd брал аккаунты пользователей из таблицы users, где уже есть логины/пароли пользователей и их айпи-адреса.

PPTPD для связи с базой данных (да и вообще, для других способо авторизации, кроме текстового файла) использует сервер авторизации Radius.

Устанавливаем:

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

Скажем PPTPD авторизоваться через radius, для этого в файл /etc/ppp/pptpd-options добавим строчку:

plugin radius.so

Настраиваем freeradius.

Все основные конфиги хранятся в директории /etc/freeradius. В конфиге radiusd.conf найдите секцию:

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

и раскомменитируйте параметр

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

Приведите конфиг sql.conf к такому виду:

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}’”
}

Обратите внимание на параметры login, password и radius_db, укажите в них логин и пароль для доступа к нашей базе и название самой базы.

PPTPD для обращения к radius использует radiusclient, т. к. сам radius-сервер может быть отдельной машиной в сети, для их взаимодействия нужно указать «клиента» freeradius’у и «сервер» radiusclient’у.

Открываем файл clients.conf и находим секцию:

client 127.0.0.1

измените значение параметра secret на какое-нибудь слово, это будет некий «пароль» для radiusclient’a:

secret = habrahabra

Добавляем запись о нашем radius-сервере в файл /etc/radiusclient/servers:

localhost habrahabra

как Вы уже заметили, второе слово — это «пароль», кторый мы указали в clients.conf.

По-умолчанию, radiusclient не умеет ms-chap, для этого нужен дополнительный словарь атрибутов. Копируем:

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

Подключим его к другим словарям, добавьте в конец файла /etc/radiusclient/dictionary строку:

INCLUDE /etc/radiusclient/dictionary.microsoft

Перезапускаем pptpd и freeradius:

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

На этом настройка pptpd и freeradius закончена, теперь авторизация будет производится из БД. Попробуйте подключиться к серверу, если все работает, идем дальше.

Детализация по направлениям

Практически у всех провайдеров есть определённые «зоны тарификации», это целые подсети или диапазоны айпи-адресов, где цена за Мб трафика существенно отличается от цены за Мб внешнего трафика. Например, внутренние медиа- или игровые сервера, сервисы Yandex’a. И было бы неплохо видеть с какой зоны тарификации пришел трафик. Приступим :)

Для этого проведем модернизацию нашей базы, добавим ещё одну таблицу:

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;

и модифицируем существующую таблицу data:

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

Добавим 3 зоны тарификации:

# локальный трафик
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);

Обратите внимание на значение поля prio, при добавлении зон тарификации вы должны исходить из правила чем дешевле трафик, тем выше его приоритет (0 — наивысший).

Скрипт парсера:

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

На этом пока все, в следующей части опишу лимитирование трафика и ограничение скорости.

Постовой

Блог начинающего фотографа. Учимся фотографии вместе.

Охладитель пара