Subject: Re: Delays between "connection received" and "connection authenticated" because of localhost entries in hba

From: Adrian Klaver <[email-masked]>
Date: Tue, 29 Oct 2024 09:38:05 -0700

On 10/29/24 09:30, Daniel Westermann (DWE) wrote:
> Delays between "connection received" and "connection authenticated" because of localhost entries in hba
> 
> Hi,
> 
> we're facing a strange issue with delays between "connection received" and "connection authenticated".
> 
> # select version();
>                                                                version
> -----------------------------------------------------------------------------------------------------------------------------------
>   PostgreSQL 15.6 (Ubuntu 15.6-1.pgdg22.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04) 11.4.0, 64-bit
> (1 ligne)
> 
> I know, this is not the latest minor version.
> 
> # \! cat /etc/os-release | head -1
> PRETTY_NAME="Ubuntu 22.04.4 LTS"
> 
> What we see in the log is this (around 4 seconds delay):
> 
> 2024-05-07 15:29:50.244 CEST [369909] LOG:  connection received: host=xx.xx.xx.100 port=48434
> 2024-05-07 15:29:54.518 CEST [369909] LOG:  connection authenticated: identity="xxxxxx" method=md5 (/etc/postgresql/15/main/pg_hba.conf:121)

1) host=xx.xx.xx.100 = localhost?

2) In postgresql.conf what is listen_addresses set to?

3) What are the settings in /etc/hosts?

> 
> The matching line is this ( I know md5 ):
> host    all             xxxxx     xx.xx.xx.0/24            md5
> 
> What we've found out so far is, that this only happens if we have a localhost(or any other hostname) line before the line matching our connection, something like this:
> host    replication     xxxxx     localhost                md5
> host    all             xxxxx     xx.xx.xx.0/24            md5
> 
> We don't see it if we do it like this:
> host    all             xxxxx     xx.xx.xx.0/24            md5
> host    replication     xxxxx     localhost                md5
> 
> Has anyone experienced such a behavior? It seems clear that this is somehow related to name resolution but we couldn't reproduce something like this on the OS using dig (in a loop several hundred of times). It is also only happening from time to time, and not constantly.
> 
> What we're basically looking for is a way to prove the assumption without involving PostgreSQL at all, if that does make sense?
> 
> Many thanks in advance
> Daniel
> 
> 

-- 
Adrian Klaver
[email-masked]

---

From: "Daniel Westermann (DWE)" <[email-masked]>
Date: Tue, 29 Oct 2024 17:42:45 +0000

> 1) host=xx.xx.xx.100 = localhost?

No, it is not

> 2) In postgresql.conf what is listen_addresses set to?

* 

> 3) What are the settings in /etc/hosts?

Standard Ubuntu settings:

127.0.0.1       localhost
127.0.1.1       abc      cde

# The following lines are desirable for IPv6 capable hosts
::1     localhost ip6-localhost ip6-loopback
ff02::1 ip6-allnodes
ff02::2 ip6-allrouters

Regards
Daniel

---

From: Adrian Klaver <[email-masked]>
Date: Tue, 29 Oct 2024 11:03:30 -0700

>> 1) host=xx.xx.xx.100 = localhost?

Is the localhost machine on the same network?

Is the client connection string using IPV4 address or host name?

> ::1     localhost ip6-localhost ip6-loopback

Contents of resolv.conf?

Would it be possible to crank the logging detail up?