Рулим трафиком в Linux. Часть вторая.

    Первую часть читайте здесь.

    В этой статье мы рассмотрим:
    — Авторизацию пользователей из базы данных 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;

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

    UPD: Упс, в код скрипта вкралась очепятка, поправил(:
    AdBlock похитил этот баннер, но баннеры не зубы — отрастут

    Подробнее
    Реклама

    Комментарии 17

      +1
      Спасибо большое! Коллекционирую ваши статьи про трафик. У меня сейчас связка squid+htb.init, ищу свежее решение, буду ждать продолжения.
        +2
        Спасибо! Как раз ночью начал переходить с win 2003 + traffic inspector на ubuntu, первая статья этой ночью пригодилась :)
          +1
          Рад, что кому-то действительно пригодилось)
          –6
          Как говорятся: «Аффтар, пиши истчё»!!!
            +3
            в следующих статьях хотелось бы получить решения для следующих вариантов:
            1) учет трафика пользователей, залогиненных на одной и той же машине. (--owner-id)
            2) запрет пользования интернетом при превышении лимита, либо снижение скорости.
            3) задание приоритета по скорости.
            4) учет служебного трафика (например --owner-id root)
            5) цена трафика по времени.
              0
              Маленькое уточнение: dictionary.microsoft лежит в /usr/share/freeradius/dictionaries (дистрозависимо, конечно). В старых версиях в словаре нет описания полей для MS-CHAP-V2, вот их надо дописывать ручками.
                0
                А я не мог вспомнить, откуда же его брал раньше) Спасибо, сейчас поправлю.
                0
                спасибо, очень полезная статья.
                пожалуйста, в следующем топике напишите про настройку биллинга без vpn.
                  0
                  Спасибо
                    +1
                    я бы посоветовал обратить внимание на netams
                      +1
                      очень актуально, пишите, пожалуйста, продолжение…
                        +1
                        В тему, интересно будет посмотреть на продолжение.
                          0
                          эээ, прошу прощения но не заметил куда сам скрипт то писать? :)
                            0
                            куда угодно(: можете добавить в кронтаб
                          0
                          великолепно! спасибо =)
                            +1
                            Пишите продолжение!!! И вообще статей с подобной тематикой. Очень интересно и актуально.
                              0
                              ALTER TABLE `data` ADD UNIQUE (`id_user`,`id_zone`,` ts`);
                              По-моему, перед ts лишний пробел. Начал сейчас по вашей инструкции делать, вот тут ошибку выдает.

                              Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.

                              Самое читаемое