Table of Contents
- Introduction
- What is IP Address?
- IPv4 Address Representation
- IPv6 Address Representation
- Supernet, Subnet & Sub-subnet
- Host Address
- Private v/s Public IP Address space
- Reserved Addresses
- IP Address Data Types in PostgreSQL
- Difference Between CIDR and INET Data Types
- IP Address Functions in PostgreSQL
Introduction
PostgreSQL, in addition of being a great database management system, has a lot of extended functionality which help developers code less. In this post, I am aiming at providing an overview of the Network address data types and functions bundled with PostgreSQL. Before going into the details of it, we will go through some basics of IP address and related concepts which will make our journey easy.
What is IP Address ?
Every device which participate in a computer network which relays on Internet Protocol(IP) will have an IP address assigned into it. An IP address can either be a 32 bit or 128 bit number known as IPv4 or IPv6 respectively. Although IP address is internally stored in binary format, for readability we use different kinds of notations for IPv4 and IPv6 addresses.
IPv4 Address Representation
IPv4 addresses are commonly represented in dot-decimal notation. This notation is quite popular since it is being used for a long time and is still in use. Dot-decimal representation consists of four decimal numbers, each ranging from 0 to 255, separated by dot(.). We call each decimal number as octet, since the binary representation of each number will contain a group of 8 binary digits.
As an example, consider the following IPv4 address:
172.0.211.255
Each octet can be represented as a group of 8 binary digits, by finding the corresponding binary equivalents:
10101100.00000000.11010011.11111111
As you can see, the binary representation of the 2nd octet 0 contain all zeros while the 4th octet 255 contain all ones. So, in IPv4, each octet ranges from 0 to 255. The total number of bits available is 32.
IPv6 Address Representation
IPv6 address, having a size of 128 bits, is a by product of the “IPv4 address space exhaustion”. Because of it’s large size, we use a different notation for IPv6 instead of the old dot-decimal. An IPv6 address is represented using eight groups of four hexadecimal digits. Each group represents 16 bits.
Below is an example IPv6 address:
2001:0db8:0000:0000:0000:ff00:0042:8329
The corresponding binary format is,
0010000000000001:0000110110111000:0000000000000000:0000000000000000:0000000000000000:1111111100000000:0000000001000010:1000001100101001
The same address can be further trim down by removing all leading zeros in each hexadecimal digit as follows:
2001:db8:0:0:0:ff00:42:8329
Again, the consecutive sections of zeros can be replaced by :: as shown below:
2001:db8::ff00:42:8329
IPv4-mapped IPv6 addresses
This is a special class of transitional IPv6 address in which the first(most significant) 80 bits are zero, the next 16 bits are one, and the remaining 32 bits are IPv4 address.
Example:- ::ffff:128.8.2.126
One may see this representation as the first 96 bits written in standard IPv6 format and the remaining 32 in the IPv4 format.
Supernet, Subnet & Sub-subnet
A supernet is an IP network which is formed of a number of smaller networks called subnets. Supernet will have a CIDR (Classless Inter-Domain Routing) mask which is smaller than it’s constituent subnets. We will discuss about the CIDR mask shortly.
Let’s take an IPv4 Supernet as an example:
182.0.0.0/8
Here 8 represents the CIDR mask. To know what it really mean, let’s first convert this address into binary.
10110110.00000000.00000000.00000000
In this case, the first 8 bits starting from the left is called the prefix or the network portion of the supernet. These 8 bits (or CIDR mask) are be fixed, and all subnets and hosts of the supernet share the same prefix. The remaining bits are called the address portion of the supernet.
For instance, we could say,
10110110.11001000.00000000.00000000 (182.200.0.0/13) is a subnet of 182.0.0.0/8
while,
10110111.11001000.00000000.00000000 (183.200.0.0/13) is not.
In IPv4 the CIDR mask can be any number between 1-32, since the maximum available number of bits in an IPv4 address is 32. IPv6 follows the same convention, but the range of CIDR mask is extended up to 128.
As you can see, there is no distinct difference between a supernet and a subnet netblock address. It is the responsibly of the network administrator to define which is the supernet address and subnet address. The sub-subnet is a further breaking down of a subnet. For example the netblock, 182.200.23.0/24 is a sub-subnet of the subnet 182.200.0.0/13
Host Address
Host address is the actual IP address which we can assign into a device in a network. Host address will have the maximum of the CIDR mask available in the family of that address. That is , an IPv4 host address will always have a CIDR mask of 32 and an IPv6 host – 128.
For example, 120.20.32.56/32 or simply 120.20.32.56 is valid IPv4 host address and,
2001:0db8:0000:0000:0000:ff00:0042:8329/128 or 2001:0db8:0000:0000:0000:ff00:0042:8329 is a valid IPv6 host address.
Given a subnet we could easily calculate the number of host address available in it.
For example, consider the subnet – 120.255.21.0/24
The number of available hosts is 2^(32-24) = 256
The first host address is 120.255.21.0 and the last being 120.255.21.255
The same pattern is applicable to IPv6 netblocks.
Private v/s Public Address space
There are two kinds of IP networks available – private and public. An enterprise or office LAN is an example of private network. The Internet is called public network. The IP address space or range of addresses to be used in a private network is called private address space, while the rest of address is called public address space.
IPv4 Private Address Space
| 10.0.0.0 – 10.255.255.255 |
| 172.16.0.0 – 172.315.255.255 |
| 192.168.0.0 – 192.168.255.25 |
IPv6 Private Address Space
| fc00::/7 |
| fe80::/10 |
Reserved Addresses
There are certain addresses which, generally, should not be used in either public or private networks or is used for some special purpose. Such addresses are known as Reserved addresses. The list of Reserved addresses are shown below.
IPv4 Reserved Address Space
| 0.0.0.0/8 |
| 10.0.0.0/8 |
| 100.64.0.0/10 |
| 127.0.0.0/8 |
| 169.254.0.0/16 |
| 172.16.0.0/12 |
| 192.0.0.0/24 |
| 192.0.2.0/24 |
| 192.88.99.0/24 |
| 192.168.0.0/16 |
| 198.18.0.0/15 |
| 198.51.100.0/24 |
| 203.0.113.0/24 |
| 224.0.0.0/4 |
| 240.0.0.0/4 |
| 255.255.255.255 |
IPv6 Reserved Address Space
| 0000::/8 |
| 0100::/8 |
| 0200::/7 |
| 0400::/6 |
| 0800::/5 |
| 1000::/4 |
| 2000::/3 |
| 4000::/3 |
| 6000::/3 |
| 8000::/3 |
| a000::/3 |
| c000::/3 |
| e000::/4 |
| f000::/5 |
| f800::/6 |
| fe00::/9 |
| fec0::/10 |
| ff00::/8 |
IP Address Data Types in PostgreSQL
PostgreSQL provides specialized datatypes to store and manipulate IPv4 or IPv6 addresses. They are named, inet and cidr. Both of these data types can be used to store IP addresses with all types of representations we discussed so far. These data types are, in all aspects, similar to other data types in PostgreSQL. We can define tables with columns of these types, cast string containing IP address to cidr/inet and so on. Sorting is also possible and while sorting, if the column contains both IPv4 and IPv6 addresses, IPv4 will always be sorted and displayed before IPv6 addresses.
If an address is inserted without specifying a CIDR mask, PostgreSQL will treat it as a host address and will insert the CIDR mask 32 or 128 accordingly.
Difference Between CIDR and INET Data Types
The fundamental difference between these two data types is that inet accepts addresses with non-zero bits to the right of the netmask where cidr doesn’t.
For example, consider the subnet – 172.20.20.0/20
10101100.00010100.00010100.00000000
Here, the 22nd bit is 1. We may say, this subnet is invalid since the netmask is 20, and after the 20th no 1s should appear. But, if you use inet data type it wont validate the netmask against the address, instead it will simply store the address as it is. But if cidr is used, PostgreSQL will throw error:
ERROR: invalid cidr value: “172.20.20.0/20”
SQL state: 22P02
Detail: Value has bits set to right of mask.
Character: 9
cidr is strict about validating the netmask and is very useful if your application need to enforce strict validation against IP addresses to be stored in DB. In other cases if you are not concerned about the correctness of cidr mask, you can choose to use inet.
IP Address Functions in PostgreSQL
PostgreSQL has a set of useful functions available for use with inet and cidr. We will see only the important ones here.
- broadcast(inet/cidr)
Return the broadcast address (last address in the range) of the subnet/supernet. For example, for 172.20.30.0/24 broadcast address is 172.20.30.255 - family(inet/cidr)
Return the family of address . For IPv4, 4 and 6 for IPv6 - masklen(inet/cidr)
Return the length of the netmask. - set_masklen(inet/cidr, int)
To set a different mask length for an address.
So, that is it!
We have covered the basics of IP address and seen how it works in a PostgreSQL database. If you have any questions, feel free to ask here!
