Sv: /* Sql конфиг */

2023-05-30T21:18:55Z

Sql конфиг

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

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

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

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

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

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

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

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

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

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

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

Автозапуск:

<source lang="bash">
sysrc radiusd_enable="yes"
</source>

===Конфигурирование===

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

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

<pre>
server default {
listen {
type = auth
ipaddr = *
port = 1812
}
listen {
type = acct
ipaddr = *
port = 0
}
authorize {
sql
pap
chap
mschap
}
authenticate {
Auth-Type PAP {
pap
}
Auth-Type CHAP {
chap
}
Auth-Type MSCHAP {
mschap
}
}
preacct {
acct_unique
preprocess
}
accounting {
sql
exec
if("%{sql:SELECT IF((SELECT 1 FROM v_ips i JOIN users u ON i.uid=u.id WHERE u.name='%{User-Name}' AND i.ip='%{Framed-IP-Address}'),1,0)}" < 1){
update reply {
mpd-drop-user = 1
}
}
}
session {
radutmp
sql
}
post-auth {
sql
}
Post-Auth-Type REJECT {
sql
}
}
</pre>

===Sql конфиг===
В консоли:

<source lang="bash">
ee /usr/local/etc/raddb/mods-enabled/sql
</source>

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

<pre>
sql {
driver = "rlm_sql_mysql"
mysql {
warnings = auto
}
server = "localhost"
port = 3306
login = "nodeny"
password = "hardpass"
radius_db = "nodeny"

read_groups = no
authorize_check_query = "call radcheck('%{User-Name}', 0)"
authorize_reply_query = "call radreply('%{User-Name}', '%{User-Password}')"
accounting {
reference = "%{tolower:type.%{Acct-Status-Type}.query}"
type {
start {
query = "call radupdate('%{User-Name}','%{Framed-IP-Address}',\
'user=%{Calling-Station-Id};nas=%{NAS-IP-Address};ses=%{Acct-Session-Id}')"
}
interim-update {
query = "${..start.query}"
}
stop {
query = "call radstop('%{User-Name}', '%{Framed-IP-Address}')"
}
}
}
post-auth {
query = "call radupdate('%{User-Name}','%{reply:Framed-IP-Address}',\
'user=%{Calling-Station-Id};nas=%{NAS-IP-Address};ses=%{Acct-Session-Id}')"
}
}
</pre>

===В словарь атрибутов добавим специфичные для mpd5===

Благодаря этим атрибутам, мы сможем заставить mpd5 переконнектить юзера, если изменился его ip. В консоли:

<source lang="bash">
ee /usr/local/share/freeradius/dictionary
</source>

Добавляем в конце файла:

<pre>
VENDOR mpd 12341

BEGIN-VENDOR mpd

ATTRIBUTE mpd-rule 1 string
ATTRIBUTE mpd-pipe 2 string
ATTRIBUTE mpd-queue 3 string
ATTRIBUTE mpd-table 4 string
ATTRIBUTE mpd-table-static 5 string
ATTRIBUTE mpd-filter 6 string
ATTRIBUTE mpd-limit 7 string
ATTRIBUTE mpd-input-octets 8 string
ATTRIBUTE mpd-input-packets 9 string
ATTRIBUTE mpd-output-octets 10 string
ATTRIBUTE mpd-output-packets 11 string
ATTRIBUTE mpd-link 12 string
ATTRIBUTE mpd-bundle 13 string
ATTRIBUTE mpd-iface 14 string
ATTRIBUTE mpd-iface-index 15 integer
ATTRIBUTE mpd-input-acct 16 string
ATTRIBUTE mpd-output-acct 17 string
ATTRIBUTE mpd-action 18 string
ATTRIBUTE mpd-peer-ident 19 string
ATTRIBUTE mpd-iface-name 20 string
ATTRIBUTE mpd-iface-descr 21 string
ATTRIBUTE mpd-iface-group 22 string
ATTRIBUTE mpd-drop-user 154 integer

END-VENDOR mpd
</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), IN only_one_session INT)
BEGIN
SELECT id, name, 'Cleartext-Password' AS Attribute, AES_DECRYPT(passwd, 'hardpass') AS Value, ':='
FROM users u WHERE name=login COLLATE utf8_bin AND
IF(only_one_session < 1, true,
NOT EXISTS (SELECT 1 FROM v_ips WHERE uid=u.id AND auth=1 AND last>(UNIX_TIMESTAMP() - 300) LIMIT 1)
);
END$$
DELIMITER ;
</pre>

<pre>
DROP PROCEDURE IF EXISTS `radreply`;
DELIMITER $$
CREATE PROCEDURE `radreply`(IN login VARCHAR(64), IN password VARCHAR(64))
BEGIN
DECLARE usr_id INT;
DECLARE usr_ip VARCHAR(15) DEFAULT NULL;
SELECT id INTO usr_id FROM users WHERE name=login AND password=AES_DECRYPT(passwd, 'hardpass') LIMIT 1;
IF( usr_id IS NOT NULL ) THEN
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 IF;
END$$
DELIMITER ;
</pre>

<pre>
DROP PROCEDURE IF EXISTS `radupdate`;
DELIMITER $$
CREATE PROCEDURE `radupdate`(IN login VARCHAR(64), IN usr_ip VARCHAR(16), IN properties VARCHAR(255))
BEGIN
IF( SELECT 1 FROM v_ips i JOIN users u ON i.uid=u.id WHERE u.name=login AND i.ip=usr_ip ) THEN
CALL set_auth(usr_ip, CONCAT('mod=pppoe;',REPLACE(properties,':','')));
END IF;
UPDATE users SET name=login WHERE name=login LIMIT 1;
END$$
DELIMITER ;
</pre>

<pre>
DROP PROCEDURE IF EXISTS `radstop`;
DELIMITER $$
CREATE PROCEDURE `radstop`(IN login VARCHAR(64), IN usr_ip VARCHAR(16))
BEGIN
DECLARE usr_id INT;
DECLARE auth_prop VARCHAR(255);
DECLARE start_auth INT UNSIGNED;
SELECT u.id, i.properties, i.start INTO usr_id, auth_prop, start_auth
FROM v_ips i JOIN users u ON i.uid=u.id WHERE u.name=login AND i.ip=usr_ip AND i.auth=1;
IF( usr_id IS NOT NULL ) THEN
INSERT INTO auth_log (uid, ip, start, end, properties) VALUES
(usr_id, INET_ATON(usr_ip), start_auth, UNIX_TIMESTAMP(), auth_prop);
DELETE FROM auth_now WHERE ip=usr_ip LIMIT 1;
END IF;
END$$
DELIMITER ;
</pre>

Проверяем:

<pre>
CALL radreply((SELECT name FROM users ORDER BY id LIMIT 1), (SELECT AES_DECRYPT(passwd, 'hardpass') FROM users ORDER BY id LIMIT 1));
</pre>

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

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

В случае успеха, мы должны получить параметры соединения первого в списке абонентов NoDeny. Ему должен быть выдан статический ip либо же должен быть свободным какой-либо динамический ip в пуле. Примерно это будет выглядеть так:
<pre>
+------+-------+-------------------+----------+---+
| NULL | login | Framed-IP-Address | usr_ip | = |
+------+-------+-------------------+----------+---+
| NULL | iva | Framed-IP-Address | 10.0.0.10| = |
+------+-------+-------------------+----------+---+
</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. Если получаем ошибку
<pre>radclient:: Failed to find IP address for имя_нашего_сервера</pre>
Тогда в /etc/hosts в конце строки 127.0.0.1 допишим имя нашего сервера.

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

<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">
sysrc mpd_enable="yes"
</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>

===Блокирование одновременного подключения нескольких абонентов с одним логином===

Принцип основан на том, что mysql процедура radcheck не будет возвращать пароль если в таблице авторизаций для заданного логина уже существует запись.

Вам необходимо в /usr/local/etc/raddb/mods-enabled/sql в конце строки authorize_check_query заменить ноль на единицу.

Pppoe (mpd5 + freeradius 3) - Історія редагувань

Sv: /* Sql конфиг */