From: Erik Wienhold <ewie@ewie.name>
To: Albrecht Dreß <albrecht.dress@posteo.de>
To: pgsql-general@lists.postgresql.org
Subject: Re: Q: inet operators for IPv4 encapsulated in IPv6
Date: Wed, 6 Sep 2023 19:41:28 +0200 (CEST)
Message-ID: <704799538.14934.1694022088295@office.mailbox.org> (raw)
In-Reply-To: <SXMXUDTK.3SCF2S3E.4WL2Z437@M4LKTL7F.IEVOZHH2.TS4HJ37P>
References: <SXMXUDTK.3SCF2S3E.4WL2Z437@M4LKTL7F.IEVOZHH2.TS4HJ37P>
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=ae11d...
[2] https://www.postgresql.org/docs/current/datatype-net-types.html
--
Erik
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-general@postgresql.org
Cc: ewie@ewie.name, albrecht.dress@posteo.de
Subject: Re: Q: inet operators for IPv4 encapsulated in IPv6
In-Reply-To: <704799538.14934.1694022088295@office.mailbox.org>
References: <SXMXUDTK.3SCF2S3E.4WL2Z437@M4LKTL7F.IEVOZHH2.TS4HJ37P> <704799538.14934.1694022088295@office.mailbox.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox