I'm trying to create a database of IP networks. I'd like to have the ability
to enforce network boundaries within the database, instead of doing it
through my application. Or lack thereof. I see that Postgres has built in
data capabilities for both inet and cidr, however, they don't act the way
I'm expecting them to.
If I create this table:
nettest=> create table cidr (net cidr NOT NULL, UNIQUE(net));
NOTICE: CREATE TABLE / UNIQUE will create implicit index 'cidr_net_key' for table 'cidr' CREATE TABLE
which is fairly simple, but just for illustration purposes, I would expect
that the values in the field 'net' would be required to be unique.
However, the field type doesn't appear to honor the bitmask. If I insert
networks of the same length, it works as I would expect it to:
nettest=> insert into cidr values('10.1.0.0/22');
INSERT 58180 1
nettest=> insert into cidr values('10.1.1.0/22');
ERROR: invalid CIDR value '10.1.1.0/22': has bits set to right of mask
nettest=> insert into cidr values('10.1.4.0/22');
INSERT 58181 1
Which, is the way it should be, since a 22 bit netmask allows for 4 (24 bit) networks.
However, when I add a 'larger' netmask that includes those networks, it
allows that value to be entered:
nettest=> insert into cidr values('10.1.0.0/20');
INSERT 58182 1
And, the values are actually all there:
nettest=> select * from cidr;
net
-------------
10.1.0.0/22
10.1.4.0/22
10.1.0.0/20
(3 rows)
While it seems intelligent enough to know that 10.1.1.0/22 would overlap, it
doesn't know that 10.1.0.0/20 would envelop other networks that already exist
in it's dataset. Am I doing something incorrectly here?
While it's possible it's just my viewpoint, it seems that this is kind of weird
behavior. If the datatype has any notion of networks and that data being
'unique' shouldn't it also know about netmasks? I guess it's a different kind
of unique than people are used to seeing. But, you can't have two networks,
one 10.1.0.0/22 and 10.1.0.0/20 on the same wire, or connected to a router, or whatever.
Am I missing something? Doing something incorrectly?
Thanks for any guidance,
Tim