CODEWITHSUNDEEP
postgresqlnetwork-programming
Sergio Ayestarán
Sergio Ayestarán

Reputation: 5920

Query to get subnets using IPs on PostgreSQL

I have a PostgreSQL table with a column (of type Text) 'ip_port'. This column has values like:

167.123.24.25:59874

This is IP:Port

And I'm trying to write a query to list all the subnets from those IP and a count of IPs in each subnet. Is there an elegant way of doing this in Postgres?

Thanks in advance!

Upvotes: 3

Views: 1521

Answers (1)

Craig Ringer
Craig Ringer

Reputation: 325061

You cannot do this - in PostgreSQL or any other tool - if the only information you have is the IP address. You must also know:

  • The broadcast address for the network;
  • The network subnet mask or mask prefix length; or
  • that pre-CIDR class-based address allocation is being used

Consider that a given IP is actually a member of multiple networks of increasing size. For the purpose of local area Ethernet-based IP networking there's a broadcast domain defined by the subnet mask, but there are also a nested set of increasingly broad routing aggregation domains for that address, some of which are visible on the public Internet and some of which are private to an internal network. The line between "public" and "private" can be blurry; for example, within my ISP's network their large network blocks are split into smaller chunks that are not visible from the outside world but are visible to the ISP's customers.

For example, a non-exhaustive list of networks for 167.123.24.25 might be, according to some quick calculations with the sipcalc tool:

  • 167.123.24.25/32 (the host its self)
  • 167.123.24.25/28: (a common ISP allocation): usable 167.123.24.17 - 167.123.24.30, netmask 255.255.255.240
  • 167.123.24.25/24: (a common LAN subnet size): usable 167.123.24.1 - 167.123.24.254, netmask 255.255.255.0
  • 167.123.24.25/20: (a midlevel routing aggregation): usable 167.123.16.1 - 167.123.31.254, netmask 255.255.240.0
  • 167.123.24.25/16: The top-level APNIC allocation of this address block according to whois): usable 167.123.0.1 - 167.123.255.254, netmask 255.255.0.0

If you traceroute that address, consider that any of those routers might (but also might not) be the point at which the address becomes part of a narrower, more specific subnet. See Classless inter-domain routing on Wikipedia.

All that is before you even consider NAT (blech) and internal WAN routing. 167.123.24.25 might be a router for a whole hidden network of NATed hosts. These hosts are invisible to you from the outside without extremely sophisticated passive mapping that only someone close to them along their routing path can do.

To learn more about the address's network you have to do some serious probing to find out what addresses might be treated as broadcast addresses. Of course, most routers will filter packets destined for broadcast addresses from outside the local routing domain, so you might not be able to tell the difference between "host doesn't exist", "is a broadcast address" and "host exists but filters the packet I'm using for probing". Such probing is slow, and it'll also tend to make the network owners grumpy at you. Since this network is owned by the US Department of Public Works, I'd recommend not making them grumpy.

Upvotes: 1

Related Questions