0

I was previously using the >> operator for querying a CIDR column and it turns out it fails for some ipv4 values that required =. I want to be certain that I am properly querying this table so that I do not miss any matches on the table. The column contains both IPV4 and IPV6 CIDR ranges.

this is what I was doinng before:

SELECT * FROM public.ip_addresses ips WHERE ips.network >> '2a02:26f7:e484:576c::2'::CIDR

this is what I have graduated to:

SELECT * FROM public.ip_addresses ips WHERE ips.network >>= '108.61.214.189'::CIDR OR ips.network <<= '108.61.214.189'::CIDR

is the second option the all encompassing query option or should I do something else?

Rami
  • 490
  • 7
  • 22

1 Answers1

1

I think the query should do the desired matching. This is based on the following trial

-- Replicate an estimate of table schema
CREATE TABLE public.ip_addresses (
    id SERIAL PRIMARY KEY,
    network CIDR NOT NULL,
    description TEXT
);

-- Insert dummy data for testing
INSERT INTO public.ip_addresses (network, description) VALUES
    ('108.61.214.0/24', 'Should match'),
    ('108.61.214.189/32', 'Should match'),
    ('108.61.215.0/24', 'Vultr'),
    ('108.61.216.0/24', 'Vultr'),
    ('108.61.217.0/24', 'Vultr'),
    ('2001:db8::/32', 'Documentation network'),
    ('2001:db8:85a3::8a2e:370:7334', 'Global Unicast'),
    ('2001:db8::/48', 'Subnet of Global Unicast'),
    ('2001:db8::/64', 'Subnet of Subnet of Global Unicast'),
    ('2001:db8::1/128', 'Loopback address'),
    ('fe80::/10', 'Link-local unicast'),
    ('ff00::/8', 'Multicast'),
    ('::/0', 'Default route'),
    ('::1/128', 'Loopback address'),
    ('2002:c0a8:101::/64', '6to4 relay anycast'),
    ('192.168.0.0/16', 'Local network #1'),
    ('10.0.0.0/8', 'Private network #1'),
    ('172.16.0.0/12', 'Private network #2'),
    ('192.168.1.0/24', 'Local network #2'),
    ('172.31.0.0/16', 'Private network #3'),
    ('10.10.10.0/24', 'Private network #4'),
    ('192.168.2.0/24', 'Local network #3'),
    ('172.17.0.0/16', 'Private network #5'),
    ('10.20.30.0/24', 'Private network #6'),
    ('192.168.3.0/24', 'Local network #4');

PSQL 15 Query Result from psql

To verify the output the following python code was used

import ipaddress
import psycopg2

# Connect to the database
conn = psycopg2.connect(
    host="your-database-ip",
    database="your-database-name",
    port=5432,
    user="your-postgres-user",
    password="your-postgres-user-password"
)

cur = conn.cursor()

# Execute the query to retrieve all rows
cur.execute("SELECT * FROM public.ip_addresses")

# Fetch all the rows
rows = cur.fetchall()

# Execute desired query for sanity checks

cur.execute("SELECT * FROM public.ip_addresses ips WHERE ips.network >>= '108.61.214.189'::CIDR OR ips.network <<= '108.61.214.189'::CIDR")
res = cur.fetchall()

# Close the cursor and connection
cur.close()
conn.close()

# Define the IP address to match
ip = ipaddress.ip_address('108.61.214.189')
ip_network = ipaddress.ip_network(f"{ip}/{ip.max_prefixlen}")

# Loop through the rows and check if the IP address matches
matching_rows = []
for row in rows:
    try:
        if ipaddress.ip_network(row[1]).supernet_of(ip_network):
            matching_rows.append(row)
    except TypeError as te:
            print(f'Error: {te}. Skipping..')


# Print the matching rows
if len(matching_rows) == 0:
    print("No matching rows found.")
else:
    print("Matching rows:")
    for row in matching_rows:
        print(row)

assert len(res) == len(matching_rows)

The python code performs 2 queries, one is for retrieving the result of the query in question and the other retrieves all rows. The supernet_of function of the ipaddress python package is used to achieve an effect equivalent to that of the query. This can be used to test issues with the query in case the number of results from both filtering methods was different

Query Result from Python

PGzlan
  • 81
  • 4