File: README.ip4r

package info (click to toggle)
ip4r 1.04-1
  • links: PTS
  • area: main
  • in suites: squeeze
  • size: 128 kB
  • ctags: 235
  • sloc: ansic: 1,420; makefile: 51
file content (346 lines) | stat: -rw-r--r-- 13,186 bytes parent folder | download
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346

IP4R  - IPv4 and IPv4 range index type for PostgreSQL
=====================================================

RATIONALE
=========

While PostgreSQL already has builtin types 'inet' and 'cidr', the
authors of this module found that they had a number of requirements
that were not addressed by the builtin type.

Firstly and most importantly, the builtin types have no support for
index lookups of the form (column >>= parameter), i.e. where you have
a table of IP address ranges and wish to find which ones include a
given IP address.  This requires an rtree or gist index to do
efficiently, and also requires a way to represent IP address ranges
that do not fall precisely on CIDR boundaries.

Secondly, the builtin inet/cidr are somewhat overloaded with
semantics, with inet combining two distinct concepts (a netblock, and
a specific IP within that netblock). Furthermore, they are variable
length types (to support ipv6) with non-trivial overheads, and the
authors (whose applications mainly deal in large volumes of single
IPv4 addresses) wanted a more lightweight representation.

IP4R therefore supports two distinct data types (so far):

  ip4   - a single IPv4 address
  ip4r  - an arbitrary range of IPv4 addresses

Simple usage examples:

CREATE TABLE ipranges (range ip4r primary key, description text not null);
CREATE INDEX ipranges_range_idx ON ipranges USING gist (range);
INSERT INTO ipranges VALUES ('10.0.0.0/8','rfc1918 block 1');
INSERT INTO ipranges VALUES ('172.16.0.0/12','rfc1918 block 2');
INSERT INTO ipranges VALUES ('192.168.0.0/16','rfc1918 block 3');
INSERT INTO ipranges VALUES ('0.0.0.0/1','classical class A space');
INSERT INTO ipranges VALUES ('10.0.1.10-10.0.1.20','my internal network');
INSERT INTO ipranges VALUES ('127.0.0.1','localhost');

CREATE TABLE access_log (id serial primary key, ip ip4 not null);
CREATE INDEX access_log_ip_idx ON access_log (ip);
INSERT INTO access_log(ip) VALUES ('10.0.1.15');
INSERT INTO access_log(ip) VALUES ('24.1.2.3');
INSERT INTO access_log(ip) VALUES ('192.168.10.20');
INSERT INTO access_log(ip) VALUES ('127.0.0.1');

-- find all accesses from 10.0.0.0/8
SELECT * FROM access_log WHERE ip BETWEEN '10.0.0.0' AND '10.255.255.255';

-- find all applicable descriptions for all entry in the access log
-- returns multiple rows for each entry if there are overlapping ranges
SELECT id,ip,range,description FROM access_log, ipranges WHERE ip <<= range;

-- find only the most specific description for all IPs in the access log
SELECT DISTINCT ON (ip) ip,range,description
  FROM access_log, ipranges
 WHERE ip <<= range
 ORDER BY ip,ip4r_size(range);


INSTALLATION
============

In order to use the types in a given database, the ip4r.sql script must be
run in that database, while connected as a database superuser.  For example:

psql -U postgres -f /usr/share/postgresql/8.4/contrib/ip4r.sql mydatabase

This installs the type and its support functions in the catalog of the
specified database. If you install it in template1, then newly-created
databases will have the type pre-installed.

The type, functions, etc. are by default installed in the "public"
schema.  This can be changed by editing the ip4r.sql file.


USAGE
=====

Type "ip4"
----------

ip4 accepts input in the form 'nnn.nnn.nnn.nnn' in decimal base only
(no hex, octal, etc.).  An ip4 value is a single IP address, and is
stored as a 32-bit unsigned integer.

ip4 supports the following type conversions:

  Source type   | Dest type |  Form
----------------|-----------|----------------------------------------------
  ip4           |  text     |  text(ip4)  or  ip4::text  (explicit)
  text          |  ip4      |  ip4(text)  or  text::ip4  (explicit)
  ip4           |  cidr     |  cidr(ip4)  or  ip4::cidr  (assignment)
  inet          |  ip4      |  ip4(inet)  or  inet::ip4  (assignment)
  ip4           |  bigint   |  to_bigint(ip4)  or  ip4::bigint  (explicit)
  bigint        |  ip4      |  ip4(bigint)     or  bigint::ip4  (explicit)
  ip4           |  float8   |  to_double(ip4)  or  ip4::float8  (explicit)
  float8        |  ip4      |  ip4(float8)     or  float8::ip4  (explicit)
  ip4           |  ip4r     |  ip4r(ip4)  or  ip4::ip4r  (implicit)

The conversions from bigint and float8 accept values which are exact
integers in the range 0 .. 2^32-1, which are converted to IPs in the
range 0.0.0.0 - 255.255.255.255 in the obvious way. This is useful for
conversions from applications which store IPs in numeric form, as is
often done for performance in certain other databases.

The conversion to cidr always results in a /32. The conversion from
inet ignores any prefix length and just takes the specific IP address.

An ip4 value implicitly converts to an ip4r range containing only the
single IP address.

ip4 supports the following operators with the conventional meanings:
=, <>, <, >, <=, >=, and supports ORDER BY and btree indexes in the
obvious fashion. However, the planner does not understand how to
transform a query of the form

  WHERE ip4column <<= value

into a btree range scan (it does this transformation for the builtin
inet type using a function which is not extensible by plugins). As a
workaround, use the following form instead:

  WHERE ip4column BETWEEN lower(value) AND upper(value)

which will use a btree range scan.

ip4 supports the following additional operators and functions:

 ip4_netmask(integer) returns ip4
 | returns an ip4 value that represents a netmask for a prefix length

 ip4_net_lower(ip4, integer) returns ip4
 | returns the lowest address in the cidr block of the specified prefix
 | length, containing the specified IP
 | equivalent to: network(set_masklen(cidr(ip4),integer))

 ip4_net_upper(ip4, integer) returns ip4
 | returns the highest address in the cidr block of the specified prefix
 | length, containing the specified IP
 | equivalent to: broadcast(set_masklen(cidr(ip4),integer))

  Operator        | Description
------------------|--------------------------------------------------------
 ip4 + integer    | add the given integer to the IP 
 ip4 - integer    | subtract the given integer from the IP 
 ip4 + bigint     | add the given integer to the IP 
 ip4 - bigint     | subtract the given integer from the IP 
 ip4 - ip4        | (returns bigint) difference between two IPs
 ip4 & ip4        | bitwise-AND the two values
 ip4 | ip4        | bitwise-OR the two values
 ip4 # ip4        | bitwise-XOR the two values
 ~ ip4            | bitwise-NOT the value

Arithmetic on ip4 values does not wrap below 0.0.0.0 or above
255.255.255.255 - attempting to go beyond these limits raises an
error.

More complex arithmetic on IP addresses can be performed by converting
the IPs to bigint first; the above are only intended to cover the
common cases without requiring casts.


Type "ip4r"
-----------

An ip4r value denotes a single range of one or more IP addresses,
for example '192.0.2.100-192.0.2.200'. Arbitrary ranges are allowed,
though input can also be in the form of CIDR netblocks, e.g.
'192.0.2.0/24' is equivalent to '192.0.2.0-192.0.2.255'. A single
value such as '192.0.2.25' represents a range containing only that
value.

Values are displayed in CIDR form if they represent a CIDR range,
otherwise in range form.

Currently, abbreviated CIDR forms are not accepted at all, i.e. all
four octets must be supplied.

An ip4r value can be constructed from two IPs explicitly using the
function ip4r(ip4,ip4). The ends of the range can be specified in
either order.

An ip4r value can be constructed from an IP and a prefix length
using the function ip4r_net_prefix(ip4,integer), or from an IP
and a netmask using the function ip4r_net_mask(ip4,ip4). In the
latter case, non-contiguous netmasks will be rejected.

ip4r supports the following type conversions:

  Source type   | Dest type |  Form
----------------|-----------|----------------------------------------------
  ip4           |  ip4r     |  ip4r(ip4)  or  ip4::ip4r  (implicit)
  ip4r          |  text     |  text(ip4r) or  ip4r::text (explicit)
  text          |  ip4r     |  ip4r(text) or  text::ip4r (explicit)
  ip4r          |  cidr     |  cidr(ip4r) or  ip4r::cidr (explicit)
  cidr          |  ip4r     |  ip4r(cidr) or  cidr::ip4r (assignment)

The conversion cidr(ip4r) returns NULL if the ip4r value does not
represent a valid CIDR range.

ip4r supports the following functions:

  is_cidr(ip4r) returns boolean
  |  returns TRUE if the ip4r value is a valid CIDR range

  lower(ip4r) returns ip4
  |  returns the lower end of the ip4r range, as an ip4 value

  upper(ip4r) returns ip4
  |  returns the upper end of the ip4r range, as an ip4 value

  ip4r_size(ip4r) returns double precision
  |  returns the number of IP addresses in the range

An ip4r value can also be subscripted with [0] and [1] with the
same effect as lower() and upper(), though this isn't encouraged
(it won't be supported for future ip6 range types).

ip4r supports the following operators:

  Operator        | Description
------------------|--------------------------------------------------------
  a = b           | exact equality
  a <> b          | exact inequality
  a < b           | note [1]
  a <= b          | note [1]
  a > b           | note [1]
  a >= b          | note [1]
  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 <<< b         | strictly less than, note [2]
  a &<< b         | note [2]
  a >>> b         | strictly greater than, note [2]
  a &>> b         | note [2]

For historical reasons @ is equivalent to >>=, and ~ is equivalent
to <<=, but use of these is not recommended in new code.

[1]: the operators <, <=, >, >= implement an ordering for the purposes of
btree indexes, DISTINCT and ORDER BY; the ordering is not necessarily
useful for applications. The ordering used is a lexicographic ordering
of (lower,upper).

[2]: the operators <<<, &<<, >>> and &>> exist to satisfy the requirements
for an rtree index. (a <<< b) if all IPs contained in a are strictly less
than all IPs contained in b, and similarly for (a >>> b). The semantics of
&<< and &>>, unfortunately, are different between different releases of
PostgreSQL due to changes in the rtree infrastructure, and so their use in
applications is not recommended.

For testing whether an ip4r range contains a specified single ip, use the
>>= operator, i.e.  ip4r >>= ip4.  The implicit conversion from ip4 to ip4r
handles this case.


ip4r Indexes
------------

ip4r values can be indexed in several ways.

A conventional btree index on ip4r values will work for the purposes of
unique/primary key constraints, ordering, and equality lookups (i.e.
WHERE column = value). Btree indexes are created in the usual way and
are the default index type.

However, ip4r's utility comes from its ability to use gist (or rtree)
indexes to support the following lookup types:

  WHERE column >>= value      (or >>)
  WHERE column <<= value      (or <<)
  WHERE column && value

These lookups require a GiST index (or rtree, though with PostgreSQL
8.1 or later, rtree should be considered deprecated). This can be
created as follows:

CREATE INDEX indexname ON tablename USING gist (column);

It is also possible to create a functional ip4r index over a column of
'cidr' type as follows:

CREATE INDEX indexname ON tablename USING gist (ip4r(cidrcolumn));

This can then be used for queries of the form:

  WHERE ip4r(cidrcolumn) >>= value    (or >>, <<=, && etc)

One advantage of this method is that the ip4r type can be dropped and
recreated without losing data. This is useful for accelerating queries
on an existing table designed without ip4r in mind.

Another idiom sometimes seen for representation of ranges of IP
addresses is for applications to create two integer columns, and do
range queries of the form:

  WHERE value BETWEEN column1 and column2

This is an attempt to get some use out of a btree index, but it performs
poorly in most cases. This can also be converted to use a functional ip4r
index as follows:

CREATE INDEX indexname ON tablename
   USING gist (ip4r(column1::ip4,column2::ip4));

and then doing queries of the form:

  WHERE ip4r(column1::ip4,column2::ip4) >>= value

A common requirement is to get the longest-prefix (most specific)
match to an IP address from a table of ranges or CIDR prefixes.
This can usually be best achieved using ORDER BY ip4r_size(column),
for example:

SELECT * FROM tablename
 WHERE column >>= value
 ORDER BY ip4r_size(column)
 LIMIT 1

When looking up multiple IPs, one can do queries of the following
form:

SELECT DISTINCT ON (ips.ip) ips.ip, ranges.range
  FROM ips, ranges
 WHERE ranges.range >>= ips.ip
 ORDER BY ips.ip, ip4r_size(ranges.range)



AUTHORS
=======

this code by andrew@supernews.net Oct 2004 - Dec 2005
derived from 'ipr' by Steve Atkins <steve@blighty.com> August 2003
derived from the 'seg' type distributed with PostgreSQL.

Distributed under the same terms as PostgreSQL itself.

Currently maintained at:
  http://pgfoundry.org/projects/ip4r/