PostgreSQL расширяет cidr на отдельные адреса

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

CREATE TEMP TABLE tmp_networks (
    network cidr PRIMARY KEY
);

Предположим, что он заполнен этими значениями для демонстрации:

  • 10.0.0.0/8
  • 10.0.1.0/24
  • 192.168.0.0/24

Когда я запускаю скрипт, Python-скрипт выполнит следующий запрос, чтобы удалить любые перекрытия:

SELECT network
    FROM tmp_networks
    WHERE NOT EXISTS (
        SELECT network
        FROM tmp_networks n
        WHERE n.network >> tmp_networks.network
);

Это прекрасно работает, за исключением одной крошечной проблемы; У меня также есть список отдельных адресов, которые следует исключить из работы. Это также таблица в базе данных:

CREATE TEMP TABLE tmp_except (
    address inet PRIMARY KEY
);

Предположим, что он содержит следующие адреса:

  • 10.0.0.100
  • 192.168.0.10

Теперь мне не удалось найти хороший способ удалить эти конкретные адреса из вывода базы данных. На мой взгляд, решение будет примерно таким:

  • выбрать все подсети
  • если в подсети обнаружен какой-либо адрес исключения, разделите подсеть на более мелкие части, пока единственный адрес исключения не будет удален, а все остальные адреса останутся

Я пытался выяснить, можно ли сделать что-то подобное в чистом PostgreSQL, но не нашел способа решить эту проблему. Любые указатели на то, как это должно быть решено?


postgresql cidr
person agnsaft    schedule 16.05.2013    source источник
comment
Какой результат вы ожидаете от своего запроса? Это всегда адреса (/32) или это сетевые адреса, если возможно, и только адреса, если есть исключение?   -  person Beryllium    schedule 30.05.2013
comment
Привет, я исключаю сетевые адреса, если это возможно, ИЛИ /32, если не существует более крупных блоков.   -  person agnsaft    schedule 06.06.2013

Ответы (1)


arrow_upward
5
arrow_downward

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

CREATE OR REPLACE FUNCTION split_cidr(net cidr, exc inet) returns setof cidr language plpgsql AS $$
DECLARE
  r cidr;
  lower cidr;
  upper cidr;
BEGIN
  IF masklen(net) >= 32 THEN RETURN; END IF;
  lower = set_masklen(net, masklen(net)+1);
  upper = set_masklen( (lower | ~ netmask(lower)) + 1, masklen(lower));
  IF exc << upper THEN
    RETURN NEXT lower;
    FOR r IN SELECT * from split_cidr(upper, exc)
    LOOP RETURN NEXT r;
    END LOOP;
  ELSE
    FOR r IN SELECT * from split_cidr(lower, exc)
    LOOP RETURN NEXT r;
    END LOOP;
    RETURN NEXT upper;
  END IF;
  RETURN;
END $$;

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

CREATE OR REPLACE FUNCTION DOIT() RETURNS Setof cidr  language plpgsql AS $$
DECLARE
 r cidr;
 x cidr;
 z inet;
BEGIN
 -- these are the rows where the network has no exceptions
 FOR r in SELECT network FROM tmp_networks n WHERE NOT EXISTS (
   SELECT address FROM tmp_except WHERE address << n.network )
 LOOP RETURN NEXT r;
 END LOOP;

 -- these are the rows where the network has an exception
 FOR r,z in SELECT network, address from tmp_networks full join tmp_except on true where address << network
 LOOP
   FOR x IN SELECT * FROM split_cidr(r, z)
   LOOP RETURN NEXT x;
   END LOOP;
 END LOOP;
END $$;

Я бы подошел к случаю нескольких адресов исключений в сети, изменив split_cidr так, чтобы он принимал массив адресов исключений, а не один адрес исключения, а затем объединил исключения для каждой сети в массив и вызвал split_cidr_array для сети и ее массива исключений. .

person Robert M. Lefkowitz    schedule 30.05.2013