在PostgreSQL上使用ip获取子网

Question

我有一个PostgreSQL表的列(类型为Text)"ip_port"。该列的值如下:

167.123.24.25:59874

这是IP:Port

我正试图编写一个查询，以列出来自这些IP的所有子网和每个子网中的IP计数。在Postgres中是否有一种优雅的方式来做到这一点?

提前感谢!

Answer 1

在PostgreSQL或任何其他工具中，如果您拥有的唯一信息是IP地址，则无法做到这一点。你还必须知道:

• 网络的广播地址;
• 网络子网掩码或掩码前缀长度;或
• 正在使用基于预cidr类的地址分配

考虑一个给定的IP实际上是多个网络的成员，并且网络的规模越来越大。为了本地基于以太网的IP网络，有一个由子网掩码定义的广播域，但也有一组嵌套的越来越广泛的路由聚合域用于该地址，其中一些在公共互联网上可见，其中一些是内部网络私有的。"公共"one_answers"私人"之间的界限可能很模糊;例如，在我的ISP的网络中，他们的大网络块被分割成较小的块，这些块从外部世界不可见，但对ISP的客户可见。

例如，根据sipcalc工具的一些快速计算，167.123.24.25的非详尽网络列表可能是:

• 167.123.24.25/32(主机自身)
• 167.123.24.25/28:(公共ISP分配):可用167.123.24.17 - 167.123.24.30，网掩码255.255.255.240
• 167.123.24.25/24:(一个普通局域网子网大小):可用167.123.24.1 - 167.123.24.254, netmask 255.255.255.0
• 167.123.24.25/20:(一个中级路由聚合):可用167.123.16.1 - 167.123.31.254, netmask 255.255.240.0
• 167.123.24.25/16:此地址块的顶级APNIC分配(根据whois):可用167.123.0.1 - 167.123.255.254, netmask 255.255.0.0

如果您traceroute该地址，考虑到这些路由器中的任何一个可能(但也可能不是)是地址成为更窄，更具体的子网的一部分的点。参见Wikipedia上的无类域间路由。

所有这些都是在你甚至考虑NAT(漂白)和内部WAN路由之前。167.123.24.25可能是一个由nat主机组成的整个隐藏网络的路由器。这些主机从外部对你是不可见的，没有极其复杂的被动映射，只有在它们的路由路径上接近它们的人才能做到。

要了解更多关于地址网络的信息，你必须做一些认真的探测，找出哪些地址可能被视为广播地址。当然，大多数路由器会过滤来自本地路由域以外的广播地址的数据包，所以你可能无法区分"主机不存在"、"是一个广播地址"one_answers"主机存在，但过滤了我用于探测的数据包"。这种探测很慢，而且还会让网络所有者对你大发雷霆。由于这个网络是由美国公共工程部所有的，我建议不要让他们脾气暴躁。