Обсуждение: Q: inet operators for IPv4 encapsulated in IPv6

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
IMHOit would be logical if the second statement would also return True.  Is there any option to simply achieve this?  I
usePostgreSQL v. 15 on a Debian Bookworm box. 

Thanks in advance, Albrecht.

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

Am 06.09.23 19:41 schrieb(en) Erik Wienhold:
> The docs don't spell it out, but inet operators and functions expect values of the same inet family.  Comparing IPv4
andIPv6 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."

Ah, I missed that section in the docs.  Thanks a lot for your explanation and the pointers!

> 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)

That's a nice approach, will do that.

Thanks again, Albrecht.