PostgreSQL将cidr扩展为单个地址

Question

我在一个网络中有一个大的子网列表，存储在以下布局中。它被用作存储资产的主表，这些资产将被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中做这样的事情，但没有找到任何方法来解决这个问题。关于如何解决这个问题，有什么建议吗?

Answer 1

我将用两个函数来处理这个问题。第一个函数接受一个cidr和一个异常地址，并返回一组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。