Thread: Psql wants to use IP6 when connecting to self using tcp...

Today I upgraded my Fedora system to Fedora 35 which included an upgrade to
PostgreSQL 13.x.x.
I did an initdb and restored my existing db from a dump.

I modified pg_hba.conf to allow access machines on my local network
host    all             all             192.168.1.0/24          trust
I modified postgresql.conf to listen to all interfaces.

 Localhost works...
[jerry@bigbox ~]$ psql
psql (13.3)
Type "help" for help.

levan=# \q
The below fails... the 'fe80....' is the ip6 address of big box.
[jerry@bigbox ~]$ psql -h bigbox
psql: error: FATAL:  no pg_hba.conf entry for host "fe80::fe3f:dbff:fed1:f62e%enp7s0", user "lxxx", database "lxxx",
SSLoff 

Using the ip4 address of bigbox works
[jerry@bigbox ~]$ psql -h 192.168.1.75
psql (13.3)
Type "help" for help.

lxxx=#

I can connect to/from the other machines in my local network...
Netstat shows that Postgres is listening on a ip6 interface.

What  did I have to do get the 'fatal' above case to work?

Trapped in Steve Jobs Reality Distortion Field

Jerry Levan <jerry.levan@gmail.com> writes:
> The below fails... the 'fe80....' is the ip6 address of big box.
> [jerry@bigbox ~]$ psql -h bigbox
> psql: error: FATAL:  no pg_hba.conf entry for host "fe80::fe3f:dbff:fed1:f62e%enp7s0", user "lxxx", database "lxxx",
SSLoff 

This is mostly a matter of how your local DNS is configured,
not PG per se.  What does "nslookup bigbox" say?

> What  did I have to do get the 'fatal' above case to work?

Adjust your DNS setup to return the IPv4 address first.

Or, perhaps, change PG's listen_addresses so that it's
not listening on an IPv6 address at all.  There's not
going to be much point in that if you don't want
to put IPv6 entries into pg_hba.conf.

            regards, tom lane

On 22.06.2021 02:44, Jerry Levan wrote:
> The below fails... the 'fe80....' is the ip6 address of big box.
> [jerry@bigbox ~]$ psql -h bigbox
> psql: error: FATAL:  no pg_hba.conf entry for host "fe80::fe3f:dbff:fed1:f62e%enp7s0", user "lxxx", database "lxxx",
SSLoff
 
> [...]
> Netstat shows that Postgres is listening on a ip6 interface.

You stated earlier, that you instructed PostgreSQL to listen to all 
interfaces. This includes IPv6. Your configuration misses to configure 
trust for your IPv6 network.

Alternatively you can change your network configuration to run on IPv4 
and not use IPv6. You would remove the IPv6 interface from PostgreSQL 
and change resolver to return IPv4 (A record, not AAAA) for your host.

Stephan

On 2021-06-21 19:44:16 -0500, Jerry Levan wrote:
> The below fails... the 'fe80....' is the ip6 address of big box.
> [jerry@bigbox ~]$ psql -h bigbox
> psql: error: FATAL:  no pg_hba.conf entry for host "fe80::fe3f:dbff:fed1:f62e%enp7s0", user "lxxx", database "lxxx",
SSLoff 

This looks like a configuration error in your DNS. FE80:... is a
link-local address, and you generally don't want them in DNS. Either
remove that completely (as others have already suggested) or replace if
with the global unicast (or at least unique local) IPv6 address (and
then add that to pg_hba.conf).

If the address isn't in DNS, check your /etc/hosts.

        hp

--
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

> On Jun 23, 2021, at 12:51 PM, Peter J. Holzer <hjp-pgsql@hjp.at> wrote:
>
> On 2021-06-21 19:44:16 -0500, Jerry Levan wrote:
>> The below fails... the 'fe80....' is the ip6 address of big box.
>> [jerry@bigbox ~]$ psql -h bigbox
>> psql: error: FATAL:  no pg_hba.conf entry for host "fe80::fe3f:dbff:fed1:f62e%enp7s0", user "lxxx", database "lxxx",
SSLoff 
>
> This looks like a configuration error in your DNS. FE80:... is a
> link-local address, and you generally don't want them in DNS. Either
> remove that completely (as others have already suggested) or replace if
> with the global unicast (or at least unique local) IPv6 address (and
> then add that to pg_hba.conf).
>
> If the address isn't in DNS, check your /etc/hosts.
>

My dns is the Synology DNS that comes with their diskstation.

When I set it up I did NOT include any IP6 info so it is not returning
the IP6 address. The local name of my network is 'skynet'.

bigbox has a freshly installed Fedora 34 system and it runs postgresql 13.3

the following are being run ON bigbox

nslookup bigbox  returns the correct ipv4 address
nslookup bigbox.skynet returns the correct ipv4 address

psql -h bigbox.skynet allows me to connect to the database
psql -h bigbox  fails because the ipv6 address is returned and there is no hba entry..

I have three other machines running a postgresql database and I can use attach to them
using the long and short names with no problem from bigbox.

ie psql -h <foo> and psql -h <foo>.skynet all work.

So the question is: Why does using the short name evidently cause postresql to use the ipv6 address
and using the full name use the ipv4 address?

The problem has a workaround 1) disable the server from listening on ipv6
                             2) or add bigbox and bigbox.skynet to /etc/hosts

let me note that systemd-resolve { bigbox | bigbox.skynet } always returns the ipv4 address...

Jerry LeVan <jerry.levan@gmail.com> writes:
> bigbox has a freshly installed Fedora 34 system and it runs postgresql 13.3

> the following are being run ON bigbox

> nslookup bigbox  returns the correct ipv4 address
> nslookup bigbox.skynet returns the correct ipv4 address

> psql -h bigbox.skynet allows me to connect to the database
> psql -h bigbox  fails because the ipv6 address is returned and there is no hba entry..

It's quite odd that nslookup isn't agreeing with glibc's name
resolution code.  I suspect that the problem lies with systemd, which
has relentlessly continued its campaign to break everything in sight,
and has lately gotten its fingers into name resolution as well:

https://fedoraproject.org/wiki/Changes/systemd-resolved

Perhaps something on that page will help you figure out what's up.
My guess based on what you've said here is that nslookup and glibc
are not using the same DNS search path.  Traditionally that would be
set by a "search" line in /etc/resolv.conf, but ghod knows how it's
determined under systemd-resolved.

            regards, tom lane

So the question is: Why does using the short name evidently cause postresql to use the ipv6 address

and using the full name use the ipv4 address?

On 2021-06-23 20:33:33 -0400, Tom Lane wrote:
> Jerry LeVan <jerry.levan@gmail.com> writes:
> > bigbox has a freshly installed Fedora 34 system and it runs postgresql 13.3
>
> > the following are being run ON bigbox
>
> > nslookup bigbox  returns the correct ipv4 address
> > nslookup bigbox.skynet returns the correct ipv4 address
>
> > psql -h bigbox.skynet allows me to connect to the database
> > psql -h bigbox  fails because the ipv6 address is returned and there is no hba entry..
>
> It's quite odd that nslookup isn't agreeing with glibc's name
> resolution code.

AFAIK nslookup uses only DNS. Glibc uses all methods in nsswitch.conf.
Typically that also includes "files", and at least on Debian/Ubuntu that
has higher priority than "dns", which is why I advised the OP to
check /etc/hosts. As Alan noted, mdns4_minimal is also often there and
might return spurious results.

> I suspect that the problem lies with systemd,

This is the next one, yes. On my Ubuntu laptop, the hosts line reads:

hosts:
    files
    mdns4_minimal [NOTFOUND=return]
    resolve [!UNAVAIL=return]
    dns
    myhostname
    mymachines

(wrapped for readability)

So glibc first looks in /etc/hosts, then does an MDNS (Avahi) lookup,
then asks systemd-resolved, and only then gets around to do a
traditional DNS lookup.

(And systemd-resolved does do some magic for the local hostname, so that
might indeed be the problem)

        hp

--
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

"Peter J. Holzer" <hjp-pgsql@hjp.at> writes:
> On 2021-06-23 20:33:33 -0400, Tom Lane wrote:
>> It's quite odd that nslookup isn't agreeing with glibc's name
>> resolution code.

> AFAIK nslookup uses only DNS. Glibc uses all methods in nsswitch.conf.

Ah, right.  So looking at a Fedora 34 installation, I see in
/etc/nsswitch.conf:

...
hosts:      files myhostname resolve [!UNAVAIL=return] dns
...

/etc/hosts is pretty innocuous:

127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6

On the other hand, "man nss-myhostname" says

       o   The local, configured hostname is resolved to all locally
           configured IP addresses ordered by their scope, or -- if none are
           configured -- the IPv4 address 127.0.0.2 (which is on the local
           loopback) and the IPv6 address ::1 (which is the local host).

so it might be that that's what is causing the weird behavior.
On this machine, with a recent PG build installed but no running
server, I see:

$ psql -h rpi3
psql: error: connection to server at "rpi3" (fe80::ba27:ebff:fe51:3b34), port 5432 failed: Connection refused
        Is the server running on that host and accepting TCP/IP connections?
connection to server at "rpi3" (192.168.1.61), port 5432 failed: Connection refused
        Is the server running on that host and accepting TCP/IP connections?

so indeed *something* is injecting an IPv6 address that I didn't
ask for.  However, I see the same two addresses probed with the
fully-qualified machine name, so it's not quite like Jerry's result.

            regards, tom lane