Postgres|检查inet字段上的约束以限制ip网络掩码的范围

Question

问题是关于inet上的检查约束。

我有下表：

-- auto-generated definition
create table administration_ipblacklist
(
ip          inet                     not null
constraint administration_ipblacklist_pkey
primary key,
record_time timestamp with time zone not null,
stretch     interval                 not null
constraint stretch_positive_check
check (stretch > '00:00:00'::interval)
);

其中ip列可以容纳ip4和ip6网络，例如：

# ip4 network
59.9.52.0/24
#ip6 network
2001:db8::1000/122

我想对ip4网络的网络掩码从24到32比特进行检查约束，对ip6网络的网络屏蔽从120到128进行检查约束。

示例：

# ip4 network
59.9.52.0/24 -allowed
59.9.52.0/29 -allowed
59.9.52.0/21 -not allowed, < 24

#ip6 network
2001:db8::1000/122 -allowed
2001:db8::1000/127 -allowed
2001:db8::1000/100 -not allowed, < 120

这样做有没有什么简单而不做作的方法？

谢谢。。。

Answer 1

使用family()和masklen()函数来实现这一点：

create table ipcheck (ip inet not null);
CREATE TABLE
alter table ipcheck add constraint netmask_bits_check check 
(   (family(ip) = 4 and masklen(ip) between 24 and 32) 
or (family(ip) = 6 and masklen(ip) between 120 and 128)); 
ALTER TABLE
insert into ipcheck values ('59.9.52.0/24');
INSERT 0 1
insert into ipcheck values ('59.9.52.0/29');
INSERT 0 1
insert into ipcheck values ('59.9.52.0/21');
ERROR:  new row for relation "ipcheck" violates check constraint "netmask_bits_check"
DETAIL:  Failing row contains (59.9.52.0/21).
insert into ipcheck values ('2001:db8::1000/122');
INSERT 0 1
insert into ipcheck values ('2001:db8::1000/127');
INSERT 0 1
insert into ipcheck values ('2001:db8::1000/100');
ERROR:  new row for relation "ipcheck" violates check constraint "netmask_bits_check"
DETAIL:  Failing row contains (2001:db8::1000/100).