Store IP addresses in PostgreSQL with IP4R

Introduction

IP4R is a PostgreSQL extension which supports 6 data types allowing you to store IPv4 & IPv6 addresses in a PostgreSQL table. As IP4R is not yet an official PostgreSQL extension, you will have to install it first. I will provide you below with a simple step by step tutorial on how to use IP4R.

Install IP4R on your server

You can install the IP4R package on Debian / Ubuntu with the following command:

apt-get install postgresql-9.X-ip4r

(replace "X" with the version of your PostgreSQL server)

Once the package is installed, you can install the extension on your server with the following command:

postgres@test > CREATE EXTENSION ip4r;
CREATE EXTENSION

IP4R data types

IP4R supports six distinct data types:

• ip4: a single IPv4 address
• ip4r: an arbitrary range of IPv4 addresses
• ip6: a single IPv6 address
• ip6r: an arbitrary range of IPv6 addresses
• ipaddress: a single IPv4 or IPv6 address
• iprange: an arbitrary range of IPv4 or IPv6 addresses

IP4R operators

IP4R supports several operators, here are the most useful ones:

  Operator        | Description
------------------|--------------------------------------------------------
  a = b           | exact equality
  a <> b          | exact inequality
  a >>= b         | a contains b or is equal to b
  a >> b          | a strictly contains b
  a <<= b         | a is contained in b or is equal to b
  a << b          | a is strictly contained in b
  a && b          | a and b overlap

A complete list of the operators is available in the official documentation.

Creating a table with IP4R

To test IP4R, we will create 2 tables with some sample content:

-- Creating a table with single IPv4 addresses
CREATE TEMP TABLE test_ip (
    ip ip4,
    some_text text
);

-- Sample data
INSERT INTO test_ip (ip, some_text) VALUES ('173.201.95.24', 'IP from the USA'), ('188.165.244.5', 'IP from France'),
                                           ('218.108.170.165', 'IP from China'), ('219.93.183.93', 'IP from Malaysia'),
                                           ('190.36.144.155', 'IP from Venezuela');

-- Creating a table with ranges of IPv4 addresses
CREATE TEMP TABLE test_ip_range (
    ip_range ip4r,
    some_text text
);

-- Sample data
INSERT INTO test_ip_range (ip_range, some_text) VALUES ('148.20.45.222', 'Single IP address'), 
                                                       ('148.20.46.0/28', 'block of 16 IP addresses'),
                                                       ('148.20.47.0/27', 'block of 32 IP addresses'), 
                                                       ('148.20.48.0/26', 'block of 64 IP addresses'), 
                                                       ('148.20.49.0/25', 'block of 128 IP addresses'), 
                                                       ('148.20.50.0/24', 'block of 256 IP addresses');

As you can see above, we created 2 tables: "test_ip" which will contain single IPv4 addresses and "test_ip_range" which will contain ranges of IPv4 addresses (but it may also contain single IP addresses).

Searching a single IP address

With the table "test_ip" above you can search single IP addresses in your table:

-- Search an IP
gab@test > SELECT * FROM test_ip WHERE ip = '188.165.244.5';
      ip       |   some_text    
---------------+----------------
 188.165.244.5 | IP from France
(1 row)

-- Search an IP with no result
gab@test > SELECT * FROM test_ip WHERE ip = '85.67.222.44';
 ip | some_text 
----+-----------
(0 rows)

-- Search any IP but 188.165.244.5
gab@test > SELECT * FROM test_ip WHERE ip <> '188.165.244.5';
       ip        |     some_text     
-----------------+-------------------
 173.201.95.24   | IP from the USA
 218.108.170.165 | IP from China
 219.93.183.93   | IP from Malaysia
 190.36.144.155  | IP from Venezuela
(4 rows)

Searching within a range of IP addresses

You can also search easily within a range of IP addresses, for example:

-- Searching a single address within a range
gab@test > SELECT * FROM test_ip_range WHERE ip_range >>= '148.20.50.44';
    ip_range    |         some_text         
----------------+---------------------------
 148.20.50.0/24 | block of 256 IP addresses
(1 row)

-- Searching a full range
gab@test > SELECT * FROM test_ip_range WHERE ip_range >>= '148.20.50.0/24';
    ip_range    |         some_text         
----------------+---------------------------
 148.20.50.0/24 | block of 256 IP addresses
(1 row)

Indexes

You can take advantage of PostgreSQL indexes with IP4R. IP4R supports conventional btree indexes and they "will work for the purposes of unique/primary key constraints, ordering, and equality lookups". You can create an index with the following command:

gab@test > CREATE INDEX ON test_ip (ip);
CREATE INDEX

gab@test > \d test_ip
  Table "pg_temp_12.test_ip"
  Column   | Type | Modifiers 
-----------+------+-----------
 ip        | ip4  | 
 some_text | text | 
Indexes:
    "test_ip_ip_idx" btree (ip)

Official documentation

Feel free to find more information of IP4R official documentation: https://github.com/petere/ip4r-cvs/blob/master/README.ip4r