DHCP MYSQL

Конфиг для 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)