On 06/09/2023 16:51 CEST Albrecht Dreß <albrecht.dress@posteo.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