From: | Erik Wienhold <ewie(at)ewie(dot)name> |
---|---|
To: | Albrecht Dreß <albrecht(dot)dress(at)posteo(dot)de>, pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: Q: inet operators for IPv4 encapsulated in IPv6 |
Date: | 2023-09-06 17:41:28 |
Message-ID: | 704799538.14934.1694022088295@office.mailbox.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 06/09/2023 16:51 CEST Albrecht Dreß <albrecht(dot)dress(at)posteo(dot)de> wrote:
> I have a question regarding the use of inet operators in environments with
> mixed IPv4 and IPv6 notations.
>
> Consider the example from sect. 9.12. “Network Address Functions and Operators”
> in the docs which returns properly
>
> <snip>
> postgres=# select '192.168.1.5'::inet << '192.168.1/24'::inet;
> ?column?
> ----------
> t
> (1 row)
> </snip>
>
> However, for an IPv6 encapsulated IPv4 address, the result is
>
> <snip>
> postgres=# select '::ffff:192.168.1.5'::inet << '192.168.1/24'::inet;
> ?column?
> ----------
> f
> (1 row)
> </snip>
>
> Although the representation is different, in reality '192.168.1.5' and
> '::ffff:192.168.1.5' designate the same node, so IMHO it would be logical if
> the second statement would also return True. Is there any option to simply
> achieve this? I use PostgreSQL v. 15 on a Debian Bookworm box.
The docs don't spell it out, but inet operators and functions expect values of
the same inet family. Comparing IPv4 and IPv6 always returns false, e.g. [1].
The only hint in the docs that may imply this is [2]:
"When sorting inet or cidr data types, IPv4 addresses will always sort
before IPv6 addresses, including IPv4 addresses encapsulated or mapped
to IPv6 addresses, such as ::10.2.3.4 or ::ffff:10.4.3.2."
You can write your own function that converts IPv4-mapped IPv6 addresses to
IPv4 and then do the comparison:
postgres=# SELECT ltrim(host('::ffff:192.168.1.5'::inet & '::255.255.255.255'::inet), ':')::inet;
ltrim
-------------
192.168.1.5
(1 row)
[1] https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/utils/adt/network.c;h=ae11de0ba5007e445017b91acfeff3adc2e3d6af#l923
[2] https://www.postgresql.org/docs/current/datatype-net-types.html
--
Erik
From | Date | Subject | |
---|---|---|---|
Next Message | Albrecht Dreß | 2023-09-06 18:32:16 | Re: Q: inet operators for IPv4 encapsulated in IPv6 |
Previous Message | pgdba pgdba | 2023-09-06 17:27:28 | Ynt: Pgbackrest Restore Error - Segmentation fault (core dumped) |