IP4R

CREATE TABLE ip_access_rules (
    tenant_id uuid NOT NULL,
    rule_id integer NOT NULL,
    network iprange,
    description text,
    is_allowed boolean,
    PRIMARY KEY(tenant_id, rule_id)
);

-- Insert sample IPv4 rules
INSERT INTO ip_access_rules (tenant_id, rule_id, network, description, is_allowed) VALUES
    ('d1c06023-3421-4fbb-9dd1-c96e42d2fd02', 1, '10.0.0.0/8', 'Internal network', true),
    ('d1c06023-3421-4fbb-9dd1-c96e42d2fd02', 2, '192.168.1.0/24', 'Office network', true),
    ('d1c06023-3421-4fbb-9dd1-c96e42d2fd02', 3, '203.0.113.0/24', 'Blocked range', false);

-- Insert IPv6 rules
INSERT INTO ip_access_rules (tenant_id, rule_id, network, description, is_allowed) VALUES
    ('d1c06023-3421-4fbb-9dd1-c96e42d2fd02', 5, '2001:db8::/32', 'IPv6 documentation range', true),
    ('d1c06023-3421-4fbb-9dd1-c96e42d2fd02', 6, '2001:db8:1::/48', 'IPv6 subnet', false);

-- Check if an IP is in a range
SELECT network, description
FROM ip_access_rules
WHERE network >> '10.0.1.5'::ip4;

-- Find overlapping networks
SELECT a.network, a.description, b.network, b.description
FROM ip_access_rules a
JOIN ip_access_rules b ON a.network && b.network
WHERE a.rule_id < b.rule_id;

-- Get the containing network
SELECT network, description
FROM ip_access_rules
WHERE network >>= '192.168.1.0/24'::ip4r;

-- Check if an IP address is allowed (direct query)
SELECT EXISTS (
    SELECT 1
    FROM ip_access_rules
    WHERE network >> '10.0.1.5'::ip4
    AND is_allowed = true
);

-- Get all matching rules for an IP address
SELECT network, description, is_allowed
FROM ip_access_rules
WHERE network >> '10.0.1.5'::ip4
ORDER BY masklen(network) DESC  -- Most specific match first
LIMIT 1;

-- Check multiple IPs at once
SELECT
    client_ip,
    EXISTS (
        SELECT 1
        FROM ip_access_rules
        WHERE network >> client_ip::ipaddress
        AND is_allowed = true
    ) as is_allowed
FROM (
    VALUES
        ('10.0.1.5'),
        ('192.168.1.100'),
        ('203.0.113.1')
) as client_ips(client_ip);

-- Find all subnets within a larger network
SELECT network, description
FROM ip_access_rules
WHERE network <<= '10.0.0.0/8'::ip4r;

-- Calculate number of addresses in each range
SELECT
    network,
    description,
    CASE
        WHEN family(network) = 4 THEN masklen(network::ip4r)
        ELSE masklen(network::ip6r)
    END as prefix_length
FROM ip_access_rules;

-- Create GiST index
CREATE INDEX idx_ip_ranges ON ip_access_rules USING gist (network);