Биллинг на FreeBSD: пишем сами, используя PHP, trafd, MySQL

Если пользователи жалуются, что Internet "тормозит", а начальство ругается, что счета от провайдера слишком большие, и требует указать растратчиков трафика, покупка готового биллинга - не единственный выход.

ЧТО ТАКОЕ ADC_BILLING ИЛИ О ЧЁМ ЭТА СТАТЬЯ?

Назначение

система биллинга IP-трафика

Возможности

Недостатки

Актуальность проблемы

В настоящее время большинство юридических лиц - пользователей сети Internet подключены к ней по тарифам с оплатой исходя из количества принятого (и/или переданного) трафика. Причём три года тому назад, когда было начато написание системы, безлимитных тарифных планов практически не было, а лимитные были более дорогими.

Для сравнительно небольших организаций плата за трафик не является такой уж незначительной, и остро встаёт вопрос выявления количества трафика, израсходованного конкретным пользователем, и целей, на которые этот трафик был потрачен. Проще говоря, руководству организации нужна информация о посещённых сайтах в случае использования протокола http, скаченных файлах в случае использования протокола ftp и т. д. Причём некоторых людей, особенно - далёких от компьютера, бывает сложно убедить, что такой большой трафик есть продукт работы их компьютеров - читай, результат их собственных действий, если не предоставить подробного списка ресурсов с данными о трафике с каждого/на каждый из них.

По тем или иным причинам (забота организации об информационной безопасности, дефицит реальных IP-адресов у провайдера) подключение внутренней ЛВС (локальной вычислительной сети) организации к сети Internet обычно происходит через роутер, установленный на границе между внутренней ЛВС организации и сетью Internet.

В качестве роутера может использоваться ПК с 2-мя сетевыми картами и специальным образом настроенной (включён сервис NAT - Network Address Translation) сетевой ОС (FreeBSD, Linux, Windows, Solaris и т.д.). Или же это может быть специально разработанное устройство (нередко также использующее одну из перечисленных выше ОС, например FreeBSD или Linux). Возможны также другие варианты, но упоминать о них не представляется возможным в силу ряда причин, начиная с их меньшей распространённости и кончая выходом за рамки темы и ограниченностью размеров данной статьи.

В своё время перед автором встала именно такая задача - учёт и ограничение трафика. В качестве роутера использовался ПК с 2-мя сетевыми картами и установленной ОС FreeBSD. Несмотря на кажущуюся распространённость - почти банальность - задачи, полностью устраивающее готовое решение найти не удалось. (Оговорюсь, что рассматривались только бесплатные системы.) В результате, было принято решение писать собственную биллинговую систему.

Альтернативные варианты биллинга использования Internet

Используемое ПО

СИСТЕМА УЧЁТА IP-ТРАФИКА

Функционально в системе биллинга можно условно выделить три модуля: учёт трафика, вывод информации об учтённом трафике и модуль разрешения/запрещения пользования сетью Internet. Поскольку данные в таблицы пишет первый из перечисленных выше модулей, раздел с описанием структуры таблиц логично отнести именно в данный раздел.

Структура таблиц

Создадим базу данных с именем 'traffic'. В базе создадим две таблицы: 'Ip' - данные о IP-адресе и 'Log' - данные о трафике; назначение полей этих таблиц понятно из их названий.

Также создадим двух пользователей, имеющих право подсоединяться к базе данных только с локального компьютера: 'insert_user' - пользователь, который имеет право только добавлять данные в базу и 'view_user' - пользователь, который имеет право только читать данные из базы. Пароли, конечно же, только для статьи - при инсталляции системы рекомендуется сгенерировать более стойкие к взлому путём перебора.

Листинг. Файл create_db_&_users_&_tables.sql

CREATE DATABASE traffic
    DEFAULT CHARACTER SET koi8r
    DEFAULT COLLATE koi8r_general_ci;

GRANT INSERT ON traffic.* TO 'insert_user'@'localhost' IDENTIFIED BY 'pass';
GRANT SELECT ON traffic.* TO 'view_user'  @'localhost' IDENTIFIED BY 'pass';

USE traffic;

CREATE TABLE Ip (
    IP             VARCHAR(15) NOT NULL PRIMARY KEY,
    QUOTA          INTEGER,
    PCNAME         VARCHAR(100),
    FNAME          VARCHAR(100),
    MNAME          VARCHAR(100),
    LNAME          VARCHAR(100),
    EMAIL          VARCHAR(200),
    INDEX(IP)
) ENGINE = MyISAM;

CREATE TABLE Log (
    NN          INTEGER UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
    IP_FROM     INTEGER UNSIGNED NOT NULL,
    IP_TO       INTEGER UNSIGNED NOT NULL,
    SRC_PORT    VARCHAR(5) NOT NULL,
    DEST_PORT   VARCHAR(5) NOT NULL,
    PROTO       VARCHAR(4) NOT NULL,
    DATA_BYTES  INTEGER UNSIGNED NOT NULL,
    ALL_BYTES   INTEGER UNSIGNED NOT NULL,
    FIRST_TIME  INTEGER NOT NULL,
    LAST_TIME   INTEGER NOT NULL,
    INDEX(IP_FROM),
    INDEX(IP_TO),
    INDEX(SRC_PORT),
    INDEX(DEST_PORT),
    INDEX(PROTO),
    INDEX(ALL_BYTES),
    INDEX(FIRST_TIME),
    INDEX(IP_FROM,FIRST_TIME),
    INDEX(IP_TO,FIRST_TIME)
) ENGINE     = MyISAM
  ROW_FORMAT = fixed;

Как видно из скрипта create_db_&_users_&_tables.sql, в качестве формата базы был выбран - MyISAM, который обеспечивает большую скорость работы за счёт отсутствия транзакций.

Параметр "ROW_FORMAT" определяет, каким образом должны храниться строки в файле базе данных. Задавая этому параметру значения "fixed", мы предписываем MySQL под каждую переменную типа VARCHAR выделять не реально занимаемое её значением место, а максимально возможное. Например, если мы при создании базы определили поле "IP_TO" как VARCHAR(15), то, независимо от длины помещённых в него данных, оно всегда будет занимать 15 байт. Хотя из-за этого файл базы данных будет занимать больше места, нам в этой ситуации важнее, что операции с базой будут проходить несколько быстрее.

Настройка ОС FreeBSD

Ядро системы FreeBSD должно включать поддержку pseudo-device BPF, для чего в файле конфигурации должна присутствовать строка

Листинг. Файл adc-kernel

# The `bpf' device enables the Berkeley Packet Filter.
device		bpf			#Berkeley packet filter

Установка программ

Автор рекомендовал бы устанавливать trafd, MySQL, PHP и прочее программное обеспечение из портов (как и любой софт под FreeBSD, кроме cvsup).

Сноска после слова cvsup: Порт net/cvsup зависит от системы Modula-3, которой потребуется существенный объем времени и пространства на диске для загрузки и установки, поэтому обычно автор устанавливает cvsup из пакетов.

Установка trafd

Демон trafd, собранный из порта net-mgmt/bpft настроен на немного другие пути, чем все наши скрипты. Поэтому перед сборкой необходимо заменить в дистрибутиве файл pathnames.h на прилагающийся в архиве и отредактировать Makefile, задав значение переменной PATH_TOSAVE равным /var/trafd/ (PATH_TOSAVE=/var/trafd/).

Порт net-mgmt/bpft содержит много дополнительных файлов, никак не применяемых в нашей задаче. Поэтому автор не устанавливает его нормальным образом (make install), а просто копирует два нужных файла - trafd и traflog в /usr/local/sbin/.

Настройка trafd

Настройка основных параметров trafd осуществляется в файле rc.conf, как и должно быть у всякого порядочного демона для системы FreeBSD.

Листинг. Файл rc.conf

trafd_enable="YES"
trafd_ifaces="xl0 rl0"
trafd_flags="-p -r"
trafd_pid="/var/run/trafd"
trafd_db_server="192.168.0.8"
trafd_db_user="insert_user"
trafd_db_password="pass"

Крайне полезна возможность пакета программ trafd, позволяющая существенно сэкономить усилия при написании биллинга, который в качестве анализатора трафика использует trafd - не надо писать свой парсер логов. В состав пакета программ trafd входит утилита traflog, которая может экспортировать данные по трафику в любой текстовой файл в соответствии с форматом, заданном в её конфигурационном файле - traflog.format.

В traflog.format был прописан формат sql-команды insert для нашей базы.

Листинг. Файл traflog.format

mysql {
from:"insert into Log (ip_from,ip_to,src_port,dest_port,proto,data_bytes,all_bytes,first_time,last_time) values(INET_ATON('%s'),"
to:"INET_ATON('%s'),"
sport:"'%s',"
dport:"'%s',"
proto:"'%s',"
bytes:"%ld, "
psize:"%ld, "
ftime:"%s,"
ltime:"%s);\n"
};

Командные файлы

Были написаны два командных файла для связки trafd и MySQL: trafd_dump и trafd_to_mysql.

Краткое описание выполняемых действий: trafd_dump даёт команду trafd скинуть данные по трафику на всех прослушиваемых им интерфейсах во временные файлы. Информация об успехе или неуспехе подачи команды сохраняется в syslogd.

Листинг. Файл trafd_dump

#!/bin/sh

. /etc/rc.conf

for iface in ${trafd_ifaces}; do
    if [ -f ${trafd_pid}.${iface} ]; then
      kill -INT `cat ${trafd_pid}.${iface}`
      logger -t trafd_dump "signaling trafd on ${iface} dump to file"
    else
      logger -t trafd_dump "trafd on ${iface}: file ${trafd_pid}.${iface} not found (trafd don't listen on ${iface}?)"
    fi
done

Краткое описание выполняемых действий: trafd_to_mysql импортирует данные по трафику на всех прослушиваемых trafd интерфейсах из временных файлов, созданных trafd_dump, в базу MySQL. Информация об успехе или неуспехе подачи команды сохраняется в syslogd.

Листинг. Файл trafd_to_mysql

#!/bin/sh

. /etc/rc.conf

for iface in ${trafd_ifaces}; do
    if [ -f /var/trafd/trafd.${iface} ]; then
      logger -t trafd_to_mysql "inserting traffic data for ${iface} into mysql"
      /usr/local/sbin/traflog -a -o mysql -n -i /var/trafd/trafd.$iface | /usr/local/bin/mysql --host=${trafd_db_server} --user=${trafd_db_user} --password=${trafd_db_password} traffic
      rm /var/trafd/trafd.$iface
    else
      logger -t trafd_to_mysql "failed to insert traffic data for ${iface} into mysql: file /var/trafd/trafd.${iface} not found"
    fi
done

Эти два файла должны регулярно запускаться. Насколько регулярно - зависит от интенсивности трафика на роутере. Для наших условий нормально делать это каждые пять минут. Прописываем в /etc/crontab

Листинг. Файл crontab

*/5	*	*	*	*	root	/usr/local/sbin/trafd_dump && sleep 30 && /usr/local/sbin/trafd_to_mysql

WEB-ИНТЕРФЕЙС СИСТЕМЫ УЧЁТА ТРАФИКА

Просмотр статистики - как пользователем, так и администратором - производится через web-интерфейс (выполнен в виде HTML страниц с кодом на языке PHP внутри) любым браузером.

При открытии в браузере ресурса, который размещён по назначенному администратором для страницы статистики адресу, пользователь сразу видит имеющуюся в базе данных информацию об IP-адресе, с которого он зашел на страницу статистики (рис. 1.).

Рис. 1.
Рис. 1. Интерфейс биллинговой системы, каким его видит пользователь

Интерфейс администратора предлагается расположить в другом виртуальном домене или, по крайней мере, в другой директории. Его защита от несанкционированного доступа может быть реализована различными способами. Например, если в качестве Web-сервера используется Apache, можно использовать такую схему

Листинг. Файл httpd-vhosts.conf

<VirtualHost *:80>
    ServerName   admin.aokp.ru
    DocumentRoot "/usr/local/www/admin.aokp.ru/htdocs"
    ScriptAlias  "/cgi-bin/" "/usr/local/www/admin.aokp.ru/cgi-bin/"
    ErrorLog  /usr/local/www/admin.aokp.ru/log/httpd-error.log
    CustomLog /usr/local/www/admin.aokp.ru/log/httpd-access.log common
    CustomLog /usr/local/www/admin.aokp.ru/log/httpd-referer.log referer
    CustomLog /usr/local/www/admin.aokp.ru/log/httpd-agent.log agent
    CustomLog /usr/local/www/admin.aokp.ru/log/httpd-combined.log combined
    <Location />
        AuthType Digest
        AuthName "Billing System"
        AuthDigestDomain / http://admin.aokp.ru/
        AuthDigestProvider file
        AuthUserFile /usr/local/www/admin.aokp.ru/htdocs/.htdigest
        Require valid-user
    </Location>
</VirtualHost>

Файл с именами пользователей и паролями создаётся командой:

# htdigest /usr/local/www/admin.aokp.ru/htdocs/.htdigest "Billing System" admin

Экран администратора разделён на две части: в верхней - меню, в нижней - таблица с результатами запроса. Сразу при открытии показывается таблица трафика за сегодняшний день и за текущий месяц (рис. 2.).

Рис. 2.
Рис. 2. Интерфейс администратора сразу после загрузки страницы

В выпадающих списках "Период" задаётся период, по которому мы хотим получить информацию.

Кнопка "Список IP-адресов сети + входящий и исходящий трафик" - выводит полную информацию обо всех имеющихся в базе данных IP-адресах внутренней сети, а также количество входящего и исходящего трафика для каждого из них за указанный период (рис. 3.).

Рис. 3.
Рис. 3. Список, выдаваемый в результате нажатия на кнопку "Список IP-адресов сети + входящий и исходящий трафик"

Кнопка "Суммарная - по хостам - для IP-адреса" - выводит список всех входящих и исходящих соединений для IP-адреса, заданного в поле ввода с таким же названием. При этом для каждого IP-адреса из списка считается суммарный трафик, и таблица сортируется по убыванию. Желтым цветом обозначен входящий трафик, зелёным - исходящий (рис. 4.).

Рис. 4.
Рис. 4. Статистика для IP-адреса с группировкой по хостам, с которыми были соединения

Кнопка "Подробная для IP-адреса" - выводит список всех входящих и исходящих соединений для IP-адреса, заданного в поле ввода с таким же названием. По сути, это просто дамп базы данных с отбором по заданному IP-адресу. Желтым цветом обозначен входящий трафик, зелёным - исходящий (рис. 5.).

Рис. 5.
Рис. 5. Подробная статистика для IP-адреса

БИЛЛИНГ

Итак, мы установили и запустили в эксплуатацию два модуля системы биллинга из трех. Но учитывать и выводить информацию об использованном трафике недостаточно: полноценная система биллинга должна также содержать в себе лимиты входящего и исходящего трафика, задаваемые для каждого пользователя, и обеспечивать отключение соответствующего пользователя при превышении им допустимых лимитов.

Ниже рассматривается вопрос написания и установки основного модуля системы биллинга, который служит этим целям.

Настройка ОС FreeBSD

По результатам работы биллинга какие-то IP-адреса должны получать доступ в Internet, а какие-то - нет. В данной системе это осуществляется путём добавления IP-адресов, которым разрешен доступ в Internet, в таблицу № 0 (table 0) встроенного firewall'а FreeBSD ipfw. Файл инициализации правил firewall'а ipfw может содержать среди прочих такие строки:

Листинг. Файл ipfw.rules

#  Переменные ${iif} и ${iip} заданы в файле rc.conf
#  ${iif} - имя интерфейса (сетевой карты) внутренней сети роутера
#  ${iip} - внутренний IP-адрес роутера
#
    # Disallow users access to our proxy
${fwcmd} add deny log ip from not table\(0\) to ${iip} 3128

    # Disallow users to have Internet
${fwcmd} add deny log ip from not table\(0\) to any in via ${iif}

Чтобы биллинг запускался при старте системы, во-первых, нужно прописать в /etc/rc.conf:

Листинг. Файл rc.conf

adc_billing_enable="YES"

Во-вторых, нужно создать в /usr/local/etc/rc.d/ файл с приведённым ниже содержанием.

Листинг. Файл adc_billing

1 : #!/bin/sh

2 : # PROVIDE: adc_billing
3 : # REQUIRE: mysql
4 : # KEYWORD: shutdown

5 : # Add the following lines to /etc/rc.conf to enable adc_billing:
6 : #adc_billing_enable="YES"

7 : . /etc/rc.subr

8 : name="adc_billing"
9 : rcvar=`set_rcvar`
10:command="/usr/home/root/sbin/adc_billing"
11: stop_cmd="adc_billing_stop"

12: adc_billing_enable=${adc_billing_enable:-"NO"}

13: adc_billing_stop()
14: {
15:         echo 'Stopping '${name}'.'
16: }

17: load_rc_config $name
18: run_rc_command "$1"

Давайте разберём построчно, что делается в скрипте adc_billing. Номера строк приведены для удобства рассказа о его содержимом, в реальном командном файле они отсутствуют.

1 : "Магическая последовательность" символов "#!", сообщающая текущему командному интерпретатору, что за ней следуют путь и имя того командного интерпретатора, в котором данный сценарий должен выполняться. Лучше всего стартовые скрипты (в каталог /usr/local/etc/rc.d) писать на языке sh - в противном случае теряется, во-первых, очень удобная возможность задания очередности пуска скрипта (см. пояснение к строкам 3 и 4, а также rcorder(8)), во-вторых, приходится заново писать весь код, уже существующий в rc.subr и, в-третьих, в силу во-первых и во-вторых, это противоречит идеологии системы.

2 : Имя сервиса, с которым работает данный скрипт.

3 : Имя одного или нескольких сервисов, которые должны быть запущены до данного скрипта. Регистр имеет значение, поэтому имя сервиса должно быть указано точно так, как оно написано в соответствующем скрипте в # PROVIDE

4 : Ключевое слово. В нашем скрипте оно одно - shutdown. Благодаря его присутствию, наш скрипт будет вызываться скриптом /etc/rc.shutdown при выключении и перезагрузке системы.

5, 6: Напоминание об имени и возможных значениях переменной, которая, будучи добавлена в rc.conf, управляет работой данного скрипта.

7 : В файле /etc/rc.subr содержатся функции для выполнения типичных операций. Ими рекомендуется пользоваться всем rc.d скриптам. Естественно, для того, чтобы ими пользоваться, нужно включить их в текущий скрипт, что и делается в этой строке.

8 : Задание обязательной для rc.subr переменной "name" значения - имени скрипта. Должна быть установлена до вызова любой из функций, содержащихся в rc.subr.

9 : Получение имени переменной, изменением значения которой разрешается или запрещается запуск нашего сервиса. (Причиной существования функции "set_rcvar" являются различия в соглашении об именовании таких переменных во FreeBSD и NetBSD. Во FreeBSD в rc.conf пишут service_enable="YES", в то время как в NetBSD - service="YES".)

10: Переменная, значение которой - командная строка для запуска нашего сервиса. Если она задана, rc.subr будет действовать по сценарию обслуживания стандартного демона. В частности, предоставляются методы по умолчанию для следующих аргументов скрипта: start, stop, restart, status, poll, rcvar.

11: Определение имени функции, которая будет вызвана при запуске нашего скрипта с аргументом "stop". Как видно из строк 13-16, в ней ничего не делается, кроме вывода сообщения. В принципе, это не является строго необходимым, но автору представляется, что так правильнее и красивее ;-), к тому же, это задел на будущее - если биллинг так разовьётся, что нужно будет делать какие-либо действия при его выключении.

12: Если переменная, определяющая запуск нашего скрипта, в rc.conf не декларирована, то она объявляется и ей присваивается значение "NO". Тем самым мы избегаем запуска скрипта.

17: Загрузить переменные из rc.conf

18: Вызов основной функции из rc.subr(8) для выполнения действия, ради которого скрипт был запущен. При этом она (функция) будет использовать переменные и другие функции, определённые в скрипте. Обычно это последняя строка скрипта.

ВРЕЗКА: Из истории развития стартовых скриптов BSD-систем

Когда-то в BSD-системах был один стартовый скрипт - /etc/rc. Процесс init(8) вызывал его при запуске ОС для выполнения всех действий, которые надо сделать в однопользовательском окружении (проверка дисков, монтирование разделов, установка параметров сети, запуск различных демонов и т. д.). Конечно же, список этих действий не одинаков для различных систем, и администраторам приходилось подгонять его под свои нужды путём редактирования файла /etc/rc. Найти нужные строки в длинном файле было большой проблемой. Другим недостатком такой системы была невозможность перезапуска какого-либо одиночного сервиса.

В связи с этим сообществом разработчиков NetSBD была создана система rc.d, которая позднее была импортирована во FreeBSD. Вот идеи, положенные в её основу.

Более подробную информацию (на английском языке) по rc.d скриптам можно найти в руководстве по FreeBSD - темы rc(8), rc.subr(8), rcorder(8), rc.conf(5), а также в статье Yar Tikhiy "A practical guide to BSD rc.d scripting" по адресу http://people.freebsd.org/~yar/rcng/article.html.

КОНЕЦ ВРЕЗКИ

Необходимо, чтобы основная программа биллинга запускалась каждые 10 минут и обновляла правила firewall'а в соответствии с информацией из базы данных. Для этого в файл /etc/crontab добавляется следующая строка:

Листинг. Файл crontab

*/10	*	*	*	*	root	/usr/local/sbin/adc_billing

Основная программа биллинга

Краткое описание выполняемых действий: adc_billing запрашивает у MySQL сумму трафика за текущий месяц (из таблицы 'Log') по каждому IP-адресу (из перечисленных в таблице 'Ip'), у которого поле 'Ip.quota' не равно NULL, и затем, если число вычисленного трафика меньше значения 'Ip.quota', добавляет этот IP-адрес в таблицу № 0 (table 0) firewall'а ipfw.

Листинг. Файл adc_billing

#!/usr/local/bin/php
<?php
require ("/usr/local/etc/adc_billing.ini");

$link = mysql_connect($dbServer, $dbUser, $dbPassword) or die("Could not connect: " . mysql_error());
        mysql_select_db("traffic")                     or die("Could not select database");

$query = "
SELECT Ip.ip,
       Ip.quota,
       Incoming.bytes_in
FROM
    (
     SELECT
           Log.ip_to AS ip_to,
           SUM(Log.all_bytes) AS bytes_in
     FROM
         Log
     WHERE
          Log.ip_to BETWEEN INET_ATON('192.168.0.1') AND INET_ATON('192.168.0.255')
      AND Log.first_time > UNIX_TIMESTAMP('" . date("Y-m") . "-01 00:00:00')
     GROUP BY
             Log.ip_to
    ) Incoming
RIGHT JOIN
          Ip ON Ip.ip = INET_NTOA(Incoming.ip_to)
WHERE
     Ip.quota IS NOT NULL
 AND (Incoming.bytes_in < Ip.quota OR Incoming.bytes_in IS NULL);
";

$result = mysql_query($query) or die("Query N1 failed: " . mysql_error());

exec("/sbin/ipfw table 0 flush");

while ($row = mysql_fetch_object($result))
     {
      exec("/sbin/ipfw table 0 add " . $row->ip);
     }
?>

Заключение

Надеюсь, что описанные в статье методика и набор программ, доведённые в процессе уже достаточно длительной эксплуатации до того уровня, на котором они существуют сегодня, помогут коллегам решать аналогичные исходные проблемы.

Любые отклики на статью и усовершенствования представленного пакета программ автор с удовольствием и благодарностью примет в форуме или по электронной почте.

P.S. Статья была опубликована в журнале "Системный администратор" за август 2006 года.

P.P.S. Архив со всеми скриптами.