DHCP MYSQL: различия между версиями

Материал из noname.com.ua
Перейти к навигацииПерейти к поиску
Строка 326: Строка 326:
 
</PRE>
  
</PRE>
   
  +
=== Конфиг SUBNET ===
 
<PRE>
  
<PRE>
 
DROP FUNCTION GetAllPoolsForNoPayNet;
  
DROP FUNCTION GetAllPoolsForNoPayNet;

Версия 22:48, 26 марта 2010

Конфиг для DHCP средсвами MYSQL

Есть желание уйти от всяких скриптов (php, bash, perl) для получения конфига из MySQL для dhcpd а так же с помошью представлений отказаться от привязки к конкретным таблицам. Заодно проверить какое из решений будет работать быстрее.
Это только черновик пока и я еще не использую это решение в продакшене
TODO

  • Проверить что бы использовалась только таблица mtraf_status

Создание представдения

Для того что бы не быть привязаным к структуре базы (которую программист биллинга вообще-то может менять по своему усмотрению) все данные для dhcp буду получать из view. Стараюсь хранить данные в виде их "физических сущностей" - а именно IP - как INT UNSIGNED 

DROP view  mtraf_status;
CREATE VIEW
    mtraf_status
AS
    SELECT
        domain AS domain,
        IF( 0n_a<0 OR ( 0n_a=0 AND debt=0 )  ,0,1) AS status,
        ip AS ip,
        mac AS mac,
        IF( 0n_a<0 OR ( 0n_a=0 AND debt=0), inet_ntoa(inet_aton(ip)+6553600),ip) AS ip_dhcp,
        lower(substr(replace(concat(':', mac), ':0', ':') from 2)) AS  mac_dhcp ,
        INET_NTOA(network_mask.network) AS network
FROM
    mtraf,
    network_mask
WHERE
    INET_ATON(mtraf.ip)&network_mask.mask=network_mask.network;

Условие в IF - условие когда абонент "должен" - ему нужно дать другой IP (у меня вместо, например, 10,0,136,10 он получит 10,100,136,10) и соответвенно, пускать только на биллинг (если короко, то файрволл не пускает абонентов никуда кроме отдельного ДНСа (или отдельного view) который на все запросы отвечает адресом странички "дай денег, дорогой абонент" и собственно биллинга) Всем кто будет напоминать про dhcp snooping, opt 82 и прочее - это все делается для остатоков неуправляемых сегментов.

Дополнительные данные о сети

В биллинге (в моем случае) нет никакой информации о топологии сети). Нужно добавить данные о сетях, релеях, гейтвеях. Отмечу, что в сети есть как минимум 2 релея (т.к. сети 10.0... и 10.100... - алиасы ) и запрос прийти может через любой из них. В общем случае релеев может быть сколько угодно - по числу алиасов на интерфейсе. 

DROP TABLE network_relay;
CREATE TABLE network_relay
(
    network int(64) UNSIGNED,
    relay int(64) UNSIGNED
);

INSERT into network_relay values(INET_ATON('10.0.135.0'), INET_ATON('10.0.135.1'));
INSERT into network_relay values(INET_ATON('10.0.135.0'), INET_ATON('10.0.135.254'));
INSERT into network_relay values(INET_ATON('10.0.135.0'), INET_ATON('10.100.135.1'));
INSERT into network_relay values(INET_ATON('10.0.135.0'), INET_ATON('10.100.135.254'));

INSERT into network_relay values(INET_ATON('10.0.136.0'), INET_ATON('10.0.136.1'));
INSERT into network_relay values(INET_ATON('10.0.136.0'), INET_ATON('10.0.136.254'));
INSERT into network_relay values(INET_ATON('10.0.136.0'), INET_ATON('10.100.136.1'));
INSERT into network_relay values(INET_ATON('10.0.136.0'), INET_ATON('10.100.136.254'));

INSERT into network_relay values(INET_ATON('10.0.137.0'), INET_ATON('10.0.137.1'));
INSERT into network_relay values(INET_ATON('10.0.137.0'), INET_ATON('10.0.137.254'));
INSERT into network_relay values(INET_ATON('10.0.137.0'), INET_ATON('10.100.137.1'));
INSERT into network_relay values(INET_ATON('10.0.137.0'), INET_ATON('10.100.137.254'));



DROP TABLE network_mask;
CREATE TABLE network_mask
(
    network int  UNSIGNED  NOT NULL UNIQUE,
    mask int  UNSIGNED,
    is_dhcp int DEFAULT 1
);

INSERT into network_mask(network,mask) values(INET_ATON('10.0.135.0'), INET_ATON('255.255.255.0'));
INSERT into network_mask(network,mask) values(INET_ATON('10.0.136.0'), INET_ATON('255.255.255.0'));
INSERT into network_mask(network,mask) values(INET_ATON('10.0.137.0'), INET_ATON('255.255.255.0'));



DROP TABLE network_gateway;
CREATE TABLE network_gateway
(
    network int UNSIGNED,
    gateway int  UNSIGNED
);

INSERT into network_gateway values(INET_ATON('10.0.135.0'), INET_ATON('10.0.135.254'));
INSERT into network_gateway values(INET_ATON('10.0.136.0'), INET_ATON('10.0.136.254'));
INSERT into network_gateway values(INET_ATON('10.0.137.0'), INET_ATON('10.0.137.254'));

INSERT into network_gateway values(INET_ATON('10.100.135.0'), INET_ATON('10.100.135.254'));
INSERT into network_gateway values(INET_ATON('10.100.136.0'), INET_ATON('10.100.136.254'));
INSERT into network_gateway values(INET_ATON('10.100.137.0'), INET_ATON('10.100.137.254'));


Процедуры и функции

Решил максимально разбить задачу на отдельные атомарные функции, бляго, MySQL к этому распологает.

Получение маски для IP

DROP FUNCTION GetMaskForIP;
delimiter //
CREATE FUNCTION GetMaskForIP(ip1 int(64) UNSIGNED)  RETURNS INT UNSIGNED
BEGIN
    DECLARE mask1 INT UNSIGNED;
    SELECT mask  from network_mask WHERE ip1&mask=network limit 1 INTO mask1;
    RETURN mask1;
END
//
delimiter ;

Пример: 

mysql> SELECT INET_NTOA(GetMaskForIP(INET_ATON('10.0.136.12')));
+---------------------------------------------------+
| INET_NTOA(GetMaskForIP(INET_ATON('10.0.136.12'))) |
+---------------------------------------------------+
| 255.255.255.0                                     |
+---------------------------------------------------+
1 row in set (0.01 sec)

Получение списка relay для сети

Курсоры - довольно удобно. 

DROP FUNCTION GetRelayForNet;
delimiter //
CREATE FUNCTION GetRelayForNet(n1 int UNSIGNED) RETURNS char(255)
BEGIN
-- done is "end of cycle" variable
    DECLARE done INT DEFAULT 0;
    DECLARE current_relay VARCHAR(255);
    DECLARE dhcp_relay VARCHAR(255);
    DECLARE dhcp_relay_res VARCHAR(255) default "";

-- cursor for relay
    DECLARE cursor_relay CURSOR FOR  SELECT  relay from network_relay  WHERE network=n1 LIMIT 1,18446744073709551615;

-- set done=1 when cusror ends
-- эта конструкция применяется для завершения цикла
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
    OPEN cursor_relay;

-- Так как надо собрать часть конфига, то первый элемент немного отличается (не имеет прификса "or .."
    SELECT INET_NTOA(relay) INTO dhcp_relay FROM network_relay WHERE network=n1 LIMIT 1;
    SELECT CONCAT("(  ( binary-to-ascii(10, 8, \".\", packet(24, 4))=\"", dhcp_relay, "\" ) ") INTO dhcp_relay_res;
    FETCH cursor_relay INTO current_relay;

    WHILE done = 0 DO
        SELECT INET_NTOA(relay) INTO dhcp_relay FROM network_relay WHERE relay = current_relay;
        SELECT CONCAT(dhcp_relay_res, " or (binary-to-ascii(10, 8, \".\", packet(24, 4))=\"", dhcp_relay,"\")") INTO dhcp_relay_res;
        FETCH  cursor_relay INTO current_relay;
    END WHILE;
    CLOSE cursor_relay;
    RETURN CONCAT(dhcp_relay_res, ")");
END
//
delimiter ;

SELECT  GetRelayForNet(INET_ATON('10.0.136.0'));
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GetRelayForNet(INET_ATON('10.0.136.0'))                                                                                                                                                                                                                         |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| (  ( binary-to-ascii(10, 8, ".", packet(24, 4))="10.0.136.1" )  or (binary-to-ascii(10, 8, ".", packet(24, 4))="10.0.136.254") or (binary-to-ascii(10, 8, ".", packet(24, 4))="10.100.136.1") or (binary-to-ascii(10, 8, ".", packet(24, 4))="10.100.136.254")) |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

class для IP

Для простоты разделелил генерацию класса на несколько частей 

DROP FUNCTION GetClassForIP;
delimiter //
CREATE FUNCTION GetClassForIP(ip1 int UNSIGNED) RETURNS char(255)
BEGIN
    DECLARE mac_dhcp VARCHAR(255);
    SELECT  lower(substr(replace(concat(':', mac), ':0', ':') from 2)) INTO mac_dhcp FROM mtraf_status WHERE INET_ATON(mtraf_status.ip)=ip1 LIMIT 1;
    RETURN   CONCAT("class \"",INET_NTOA(ip1),"__",mac_dhcp,"\" {  match if(  ( binary-to-ascii(16,  8, \":\", substring(hardware,1, 6)) = \"",mac_dhcp," and");
END
//
delimiter ;

Пример 

mysql> SELECT GetClassForIP(INET_ATON('10.0.136.11'));
+------------------------------------------------------------------------------------------------------------------------------------+
| GetClassForIP(INET_ATON('10.0.136.11'))                                                                                            |
+------------------------------------------------------------------------------------------------------------------------------------+
| class "10.0.136.11__0:13:d4:5:d9:f4" {  match if(  ( binary-to-ascii(16,  8, ":", substring(hardware,1, 6)) = "0:13:d4:5:d9:f4 and |
+------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.10 sec)

Генерация полноценного класса. 

DROP FUNCTION GetFullClassForIP;
delimiter //
CREATE FUNCTION GetFullClassForIP(ip1 int UNSIGNED) RETURNS TEXT
BEGIN
    RETURN  CONCAT( GetClassForIP(ip1), " " ,  GetRelayForNet(GetNetForIP(ip1)) ,"}\n" ) ;
END
//
delimiter ;

Пример (читать так: Классу соответвует запрос с маком $mac прошедьший через $relay[1] или $relay[2] или ... или relay[N] ). В такой конфигурации мак должен быть уникален в пределах сегмента а не в пределах всей сети: 

mysql> SELECT GetFullClassForIP(INET_ATON('10.0.136.11'));
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GetFullClassForIP(INET_ATON('10.0.136.11'))                                                                                                                                                                                                                                                                                                                                                          |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| class "10.0.136.11__0:13:d4:5:d9:f4" {  match if(  ( binary-to-ascii(16,  8, ":", substring(hardware,1, 6)) = "0:13:d4:5:d9:f4 and (  ( binary-to-ascii(10, 8, ".", packet(24, 4))="10.0.136.1" )  or (binary-to-ascii(10, 8, ".", packet(24, 4))="10.0.136.254") or (binary-to-ascii(10, 8, ".", packet(24, 4))="10.100.136.1") or (binary-to-ascii(10, 8, ".", packet(24, 4))="10.100.136.254"))}
 |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.08 sec)


Определить какой сети принадлежит IP

DROP FUNCTION GetNetForIP;
delimiter //
CREATE FUNCTION GetNetForIP(ip1 int UNSIGNED) RETURNS int UNSIGNED
BEGIN
    DECLARE net1 INT  UNSIGNED;
    SELECT network INTO net1 FROM network_mask  WHERE ip1&mask=network;
    RETURN net1;

END
//
delimiter ;

Пример: 

mysql> SELECT INET_NTOA(GetNetForIP(INET_ATON('10.0.136.11')));
+--------------------------------------------------+
| INET_NTOA(GetNetForIP(INET_ATON('10.0.136.11'))) |
+--------------------------------------------------+
| 10.0.136.0                                       |
+--------------------------------------------------+
1 row in set (0.00 sec)

создание пула для IP

DROP FUNCTION GetPoolForIP;
delimiter //
CREATE FUNCTION GetPoolForIP(ip1 int UNSIGNED) RETURNS TEXT
BEGIN
    DECLARE mac_dhcp VARCHAR(255);
    SELECT  lower(substr(replace(concat(':', mac), ':0', ':') from 2)) INTO mac_dhcp FROM mtraf WHERE INET_ATON(mtraf.ip)=ip1 LIMIT 1;
    RETURN   CONCAT( "pool { range ",INET_NTOA(ip1),"; allow members of ",INET_NTOA(ip1), "__", mac_dhcp, " default-lease-time 36000; max-lease-time 72000; } ");
END
//
delimiter ;

Пример: 

mysql> SELECT GetPoolForIP(INET_ATON('10.0.136.11'));
+----------------------------------------------------------------------------------------------------------------------------+
| GetPoolForIP(INET_ATON('10.0.136.11'))                                                                                     |
+----------------------------------------------------------------------------------------------------------------------------+
| pool { range 10.0.136.11; allow members of 10.0.136.11__0:13:d4:5:d9:f4 default-lease-time 36000; max-lease-time 72000; }  |
+----------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.10 sec)

создание пула для ip неплатильщика

Возможно тут надо переписать соеденив 2 ф-и в одну (уточняю) 

DROP FUNCTION GetPoolForNoPayIP;
delimiter //
CREATE FUNCTION GetPoolForNoPayIP(ip1 int UNSIGNED) RETURNS TEXT
BEGIN
    DECLARE mac_dhcp VARCHAR(255);
    SELECT  lower(substr(replace(concat(':', mac), ':0', ':') from 2)) INTO mac_dhcp FROM mtraf WHERE INET_ATON(mtraf.ip)=ip1-6553600 LIMIT 1;
    RETURN   CONCAT( "pool { range ",INET_NTOA(ip1),"; allow members of ",INET_NTOA(ip1), "__", mac_dhcp, " default-lease-time 36000; max-lease-time 72000; } ");
END
//
delimiter ;

Пример: 

mysql> SELECT GetPoolForNoPayIP(INET_ATON('10.100.136.84'));
+---------------------------------------------------------------------------------------------------------------------------------+
| GetPoolForNoPayIP(INET_ATON('10.100.136.84'))                                                                                   |
+---------------------------------------------------------------------------------------------------------------------------------+
| pool { range 10.100.136.84; allow members of 10.100.136.84__0:1d:92:58:27:cf default-lease-time 36000; max-lease-time 72000; }  |
+---------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.04 sec)


Полные пулы для тех кто платит и тех кто нет (из одного сегмента)

DROP FUNCTION GetAllPoolsForNet;
delimiter //
CREATE FUNCTION GetAllPoolsForNet(n1 int UNSIGNED) RETURNS TEXT
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE mask1 int UNSIGNED;
    DECLARE current_ip int UNSIGNED DEFAULT 0;
    DECLARE pools TEXT DEFAULT "";
    DECLARE cursor_ip CURSOR FOR  SELECT INET_ATOn(ip_dhcp) from mtraf_status  WHERE INET_ATON(mtraf_status.ip_dhcp)&mask1=n1;
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
    SELECT mask from network_mask WHERE network=n1 INTO mask1;

    OPEN cursor_ip;
    FETCH cursor_ip INTO current_ip;
    WHILE done = 0 DO
        SELECT  CONCAT(GetPoolForIP(current_ip),"  \n",pools) INTO pools;
        FETCH  cursor_ip INTO current_ip;
    END WHILE;
    CLOSE cursor_ip;
    RETURN pools;
END
//
delimiter ;

Конфиг SUBNET

DROP FUNCTION GetAllPoolsForNoPayNet;
delimiter //
CREATE FUNCTION GetAllPoolsForNoPayNet(n1 int UNSIGNED) RETURNS TEXT
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE mask1 int UNSIGNED;
    DECLARE current_ip int UNSIGNED DEFAULT 0;
    DECLARE pools TEXT DEFAULT "";

    DECLARE cursor_ip CURSOR FOR  SELECT INET_ATON(ip_dhcp) from mtraf_status  WHERE (INET_ATON(mtraf_status.ip_dhcp))&mask1=(n1+6553600);

    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;

    SELECT mask from network_mask WHERE network=n1 INTO mask1;

    OPEN cursor_ip;
    FETCH cursor_ip INTO current_ip;
    WHILE done = 0 DO
        SELECT  CONCAT(GetPoolForNoPayIP(current_ip),"  \n",pools) INTO pools;
        FETCH  cursor_ip INTO current_ip;
    END WHILE;
    CLOSE cursor_ip;
    RETURN pools;
END
//
delimiter ;

Пример (поскипано): 

mysql> SELECT GetAllPoolsForNet(INET_ATON('10.0.136.0'));
...
pool { range 10.0.136.154; allow members of 10.0.136.154__0:e:2e:98:32:c default-lease-time 36000; max-lease-time 72000; }                                                                                                                 
pool { range 10.0.136.153; allow members of 10.0.136.153__0:15:f2:5:44:29 default-lease-time 36000; max-lease-time 72000; }                                                                                                                  
pool { range 10.0.136.152; allow members of 10.0.136.152__0:e0:1c:3c:33:cd default-lease-time 36000; max-lease-time 72000; }                                                                                                                 
pool { range 10.0.136.151; allow members of 10.0.136.151__0:e0:1c:3b:d6:39 default-lease-time 36000; max-lease-time 72000; }                                                                                                                 
pool { range 10.0.136.150; allow members of 10.0.136.150__0:19:d1:e6:1e:2c default-lease-time 36000; max-lease-time 72000; }                                                                                                                 
pool { range 10.0.136.149; allow members of 10.0.136.149__0:18:f3:fb:fe:b6 default-lease-time 36000; max-lease-time 72000; }                                                                                                                 
pool { range 10.0.136.174; allow members of 10.0.136.174__0:26:2d:5f:97:c1 default-lease-time 36000; max-lease-time 72000; }                                                                                                                 
pool { range 10.0.136.148; allow members of 10.0.136.148__0:26:9e:75:50:d2 default-lease-time 36000; max-lease-time 72000; }                                                                                                                 
pool { range 10.0.136.147; allow members of 10.0.136.147__0:a0:d1:ca:47:b default-lease-time 36000; max-lease-time 72000; }                                                                                                                  
pool { range 10.0.136.145; allow members of 10.0.136.145__0:e0:4c:3:d7:55 default-lease-time 36000; max-lease-time 72000; }                                                                                                                  
pool { range 10.0.136.146; allow members of 10.0.136.146__0:1f:c6:4f:ed:7a default-lease-time 36000; max-lease-time 72000; }           
...
1 row in set (13.79 sec)
Источник — https://noname.com.ua/mediawiki/index.php?title=DHCP_MYSQL&oldid=1185