Sv: /* Mysql процедуры */

2020-04-21T14:51:57Z

Mysql процедуры

Нова сторінка

<h2>PPPoE сервер на FreeBSD</h2>

Будут задействованы:
* mpd5 в качестве pppoe-сервера
* freeradius2 - в качестве radius-сервера

== Описание принципа работы ==

Mpd позволяет клиентам подключаться к серверу по pppoe или pptp протоколу.

* Mpd принимает запрос на соединение и обращается к radius для проверки соответствия логин/пароль. Radius запускает mysql-процедуру radcheck.
* Если в БД логин существует и соответствует паролю, то клиенту разрешается соединение. Обратите внимание, состояние клиента «заблокирован» или «не заблокирован» не влияет на сам факт соединения. Т.е. легальный абонент создает соединение и будет иметь доступ минимум к своей статистике, даже если доступ в интернет у него запрещен.
* После авторизации, radius передает mdp параметры соединения: ip, маску, а также другие данные, если необходимы (mysql-процедура radreply).
* После авторизации mpd запускает postauth_query: radius запускает mysql-процедуру radupdate, которая запускает процедуру set_auth, которая записывает в таблицу авторизаций auth_now данные текущей авторизации.
* Mpd настраивается таким образом, что с определенным периодом будет посылать radius-у accounting пакеты для каждого соединения. Эти пакеты будут интерпретироваться как keep-alive пакеты, сообщающие NoDeny, что соединение не зависло. Accounting_update_query ссылается на ту же процедуру radupdate, которая опять же вызывает set_auth. При этом если запись в таблице auth_now существует, то поле last будет обновлено текущим временем.
* Ядро NoDeny удаляет записи из таблицы auth_now, у которых поле last давно не обновлялось, т.е. удаляет по таймауту.

Возможна работа как со статическими (когда ip прописан явно в учетке абонента и никогда не меняется), так и с динамическими ip. Во втором случае в админке NoDeny необходимо создать пул ip адресов в количестве раз в 5-10 большим чем предполагаемое количество абонентов. Тип ip - "динамический".

==Установка Radius==

Поскольку понадобится radius с модулем mysql, установим его не из бинарных пакетов (''pkg install freeradius''), а из портов:

<source lang="bash">
cd /usr/ports/net/freeradius2 && make install clean
</source>

'''Обязательно поставим галку напротив опции MYSQL'''

<source lang="bash">
rm /usr/local/etc/raddb/sites-enabled/default
ee /usr/local/etc/raddb/sites-enabled/nodeny
</source>

Вставляем следующий текст:

<pre>
authorize {
sql
}
authenticate {
Auth-Type PAP {
pap
}
}
preacct {
acct_unique
preprocess
}
accounting {
detail
sql
exec
}
session {
radutmp
sql
}
post-auth {
sql
}
</pre>

<source lang="bash">
cp /usr/local/nodeny/etc/raddb/* /usr/local/etc/raddb/
echo radiusd_enable=YES >> /etc/rc.conf
ee /usr/local/etc/raddb/radiusd.conf
</source>

Раскомментируем строку (найти проще всего так: ctrl+y, затем текст "sql.conf", затем enter):

<pre>
$INCLUDE sql.conf
</pre>

===Mysql процедуры===

<pre>
ALTER DATABASE nodeny CHARACTER SET utf8 COLLATE utf8_general_ci;
</pre>

<pre>
DROP PROCEDURE IF EXISTS `radcheck`;
DELIMITER $$
CREATE PROCEDURE `radcheck` (IN login VARCHAR(64))
BEGIN
SELECT id,name,'Password' AS Attribute,AES_DECRYPT(passwd,'hardpass') AS Value,'=='
FROM users WHERE name=login;
END$$
DELIMITER ;
</pre>

<pre>
DROP PROCEDURE IF EXISTS `radreply`;
DELIMITER $$
CREATE PROCEDURE `radreply`(IN login VARCHAR(64))
BEGIN
DECLARE usr_id INT;
DECLARE usr_ip VARCHAR(15) DEFAULT NULL;

SELECT id INTO usr_id FROM users WHERE name=login LIMIT 1;
SELECT get_ip(usr_id) INTO usr_ip;

SELECT NULL,login,'Framed-IP-Address',usr_ip,'=';
SELECT NULL,login,'Framed-IP-Netmask','255.255.255.255','=';
SELECT NULL,login,'Framed-Protocol','PPP','=';
END$$
DELIMITER ;
</pre>

<pre>
DROP PROCEDURE IF EXISTS `radupdate`;
DELIMITER $$
CREATE PROCEDURE `radupdate`(IN login VARCHAR(64), IN ip VARCHAR(16), IN properties VARCHAR(255))
BEGIN
DECLARE usr_id INT;
DECLARE usr_ip VARCHAR(15) DEFAULT NULL;
SELECT id INTO usr_id FROM users WHERE name=login LIMIT 1;
SELECT get_ip(usr_id) INTO usr_ip;
CALL set_auth(usr_ip, CONCAT('mod=pppoe;',REPLACE(properties,':','')));
END$$
DELIMITER ;
</pre>

<pre>
DROP PROCEDURE IF EXISTS `radstop`;
DELIMITER $$
CREATE PROCEDURE `radstop`(IN login VARCHAR(64))
BEGIN
DECLARE usr_id INT;
SELECT id INTO usr_id FROM users WHERE name=login LIMIT 1;
DELETE FROM auth_now WHERE ip = get_ip(usr_id) LIMIT 1;
END$$
DELIMITER ;
</pre>

Проверим:

<pre>
CALL radreply((SELECT name FROM users LIMIT 1));
</pre>

Если получаем ошибку:

* ''ERROR 1267 (HY000): Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '=''' - выполним:
<pre>
ALTER DATABASE nodeny CHARACTER SET utf8 COLLATE utf8_general_ci;
</pre>
и пересоздаем процедуры

===Запускам Radius===

<source lang='bash'>
/usr/local/etc/rc.d/radiusd start
</source>

Проверяем:

<source lang='bash'>
radtest login pass 127.0.0.1 0 hardpass5
</source>

где login и test соответственно логин и пароль реально существующей клиентской записи в NoDeny. Если получаем ошибку ''radclient:: Failed to find IP address for имя_нашего_сервера'' - в /etc/hosts не прописано имя нашего сервера.

Должны получить примерно такой ответ:

<pre>
rad_recv: Access-Accept packet from host 127.0.0.1 port 1812, id=138, length=38
Framed-IP-Address = 10.0.0.10
Framed-IP-Netmask = 255.255.255.255
Framed-Protocol = PPP
</pre>

В ином случае тушим radius и запускаем в режиме детального вывода:

<source lang='bash'>
/usr/local/etc/rc.d/radiusd stop
radiusd -X
</source>

==Настройка mpd==

<source lang="bash">
pkg install mpd5
</source>

Ведение логов

<source lang="bash">
echo '!mpd' >> /etc/syslog.conf
echo '*.* /var/log/mpd.log' >> /etc/syslog.conf
touch /var/log/mpd.log
killall -HUP syslogd
</source>

Ротация логов:

<source lang="bash">
echo '/var/log/mpd.log 600 5 100 * JC' >> /etc/newsyslog.conf
</source>

описание параметров ротации:
* 600 - права на файлы
* 5 - количество файлов в ротации
* 100 - ротация будет произведена при достижении лога 100 кб
* * - ротация по времени отключена
* JC - упаковка файлов утилитой bzip2

Автозапуск:

<source lang="bash">
echo mpd_enable=YES >> /etc/rc.conf
</source>

<source lang="bash">
ee /usr/local/etc/mpd5/mpd.conf
</source>

вставляем текст:

<pre>
startup:
set user admin deletempd
set console self 127.0.0.1 5005
set console open
set web self 0.0.0.0 5006
set web open

default:
load pppoe_server

pppoe_server:

create bundle template B
set ipcp ranges 1.1.1.1/32 127.0.0.2/32
set ipcp dns 1.1.1.1
set ccp yes mppc
set mppc yes e40
set mppc yes e56
set mppc yes e128
set mppc yes stateless
set ecp disable dese-bis dese-old

create link template common pppoe
set link enable multilink
set link action bundle B
set link disable chap pap eap
set link enable pap
load radius
set pppoe service "*"

create link template fxp1 common
set link max-children 1000
set pppoe iface fxp1
set link enable incoming
radius:
set radius server localhost hardpass5 1812 1813
set radius retries 3
set radius timeout 3
set radius me 127.0.0.1
set auth acct-update 45
set auth enable radius-auth
set auth enable radius-acct
set radius enable message-authentic
</pre>

Комментарии к mpd.conf:

* Все строки, кроме комментариев и ссылок (строки которые заканчиваются на двоеточие), должны начинаться с отступа (пробела или табуляции).
* admin и deletempd - логин и пароль для доступа к управлению mpd5 через консоль или web-интерфейс (http://xx.xx.xx.xx:5006/). Не забудьте в фаерволе открыть tcp порт 5006.
* 1.1.1.1 - один из ip вашего сервера. Этот ip будет использован в туннеле: ip сервера <-> клиентский ip. Автор обычно прописывает этот ip на локальной заглушке.
* dns 1.1.1.1 - dns-сервер. Через пробел можно указать несколько.
* fxp1 - Интерфейс, на который будут приниматься pppoe соединения. Обратите внимание: указывается в двух местах конфига.
* acct-update 45 - период посылки accounting пакетов (используем для поддержания авторизаций) 45 секунд.

<source lang="bash">
chmod 600 /usr/local/etc/mpd5/mpd.conf
/usr/local/etc/rc.d/mpd5 forcestart
tail -f /var/log/mpd.log
</source>

PPPoE - Історія редагувань

Sv: /* Mysql процедуры */