From: "Daniel Westermann (DWE)" <daniel.westermann@dbi-services.com>
To: "pgsql-general@lists.postgresql.org" <pgsql-general@lists.postgresql.org>
Subject: Delays between "connection received" and "connection authenticated"
 because of localhost entries in hba
Thread-Topic: Delays between "connection received" and "connection
 authenticated" because of localhost entries in hba
Thread-Index: AQHbKh+aHf1bKlFziUSkLwj47Vg/rQ==
Date: Tue, 29 Oct 2024 16:30:01 +0000
Message-ID:
 <GV0P278MB0419EB5885DF99C5354804A9D24B2@GV0P278MB0419.CHEP278.PROD.OUTLOOK.COM>
Accept-Language: en-US
Content-Language: en-US
msip_labels:
authentication-results: dkim=none (message not signed)
 header.d=none;dmarc=none action=none header.from=dbi-services.com;
x-ms-publictraffictype: Email
x-ms-traffictypediagnostic: GV0P278MB0419:EE_|ZR1P278MB1101:EE_
x-ms-office365-filtering-correlation-id: 136883b3-ce3a-4497-8f08-08dcf836effc
x-ms-exchange-senderadcheck: 1
x-ms-exchange-antispam-relay: 0
x-microsoft-antispam: BCL:0;ARA:13230040|1800799024|366016|376014|38070700018;
x-microsoft-antispam-message-info:
 =?iso-8859-1?Q?7xv3O8XS7auHoE2lD/whwMkyVmF5eG4LG9cJaK3U4sQvyEzMDdNbtKXfkq?=
 =?iso-8859-1?Q?FOhatbsxz9d55L6Xxahy5ZhhnXm4LEqI8ucvWJyLQvKUZ3OeiI0xyZPtTv?=
 =?iso-8859-1?Q?mJiP6K8QBDgr+n0nwealAuw4XmXmi2u1bD+vVVYFZFz3M+/Zp5EE19dHJG?=
 =?iso-8859-1?Q?MmxXxblsO5JIUm3nNNHu9PydEoClR7G/5w+eXX/l243x/gj8oH847astcL?=
 =?iso-8859-1?Q?bgP9CIpH9Sp49yEzEVk+0kAd2WtboQvvcGn2rhe9198za/v5AcWz/i+ou/?=
 =?iso-8859-1?Q?ZSM1gwIohRTw7iG5sL+q1eu+rr4ERPdHPQhdWwwlSUjFJrbXWtjZ2VCdVG?=
 =?iso-8859-1?Q?1zQTOqdUOiaBXk5gUSX8b6lqpyacQle+Z3CDNwH4zX/z0BxANYkB9tB1RR?=
 =?iso-8859-1?Q?vZQ3/9clknkKIPlmdZrfKc4m1U9Tq1V7ibXQQoAYeEdh4n4w46SeMoEqTM?=
 =?iso-8859-1?Q?wz+8wVcXZDLlKrTwTZ1DTdTQmkBqHM+ac6UTo5LgW22NSWYwN1kjoPsEIo?=
 =?iso-8859-1?Q?NqR6dEyDJppvLWyZTFas67PX2cJ62JVcT4qsfNJV4ngRivN+1UkZe4Kle9?=
 =?iso-8859-1?Q?sYsdQrGYT69FeXmxs0bwQ2LuoS1pg05DPfEvg8K4FmsHgbSMtf85Wr74s6?=
 =?iso-8859-1?Q?g40IIUxU6rzn5bi9qvh7eAAZVwPCIi+2pRTRyN8h3AV0CAxWXeh98l79D5?=
 =?iso-8859-1?Q?HYfkZvCOYNxWQi7eYq5j1GyZxMm9d3imtH5Nv3WPl8a2n7bfnIqaDQt0Df?=
 =?iso-8859-1?Q?TvEr5Qg/unksfkDfS0V7+mb3l0n1dEhK7uAqFjqeVM1BQaT55sc6gU1tgR?=
 =?iso-8859-1?Q?eKQ8BvowHUfMuTDkfJOziB42djz/88QvAK4QU+FLUzmJpQq0ECHf5oCFlG?=
 =?iso-8859-1?Q?uS89YylMgeBo/BcprrPuTfIkPDXBm6zosO+plbHdAfB8iDTWW9BHlHJ+Or?=
 =?iso-8859-1?Q?V5aa7F2TQV2721Wq1ksOl0g9gWKzZGmUsm3eKyllxSVdjEIhK0FkmJZ3mk?=
 =?iso-8859-1?Q?xd0ewzGu4PGzrZ1BTXCVyxxnpFbGgCV/S6/UevksQEdDmg30zvCqvS6fvw?=
 =?iso-8859-1?Q?DY4F7LCFZtnsVmfT6wttyHoovqS09otMX96SJmvwSlIY491Ptv+o8Ud4Pu?=
 =?iso-8859-1?Q?w3/+3Doel4l2LxBo5ALRWml4sissCcO+mIfgatSByD5nMjR3qMYhULvLei?=
 =?iso-8859-1?Q?C+r9f6Ej5Nu5RlxuL0IetEWYZcIpo/HF/vj1qceWwr4tbGzFHpGzBKObgV?=
 =?iso-8859-1?Q?LQmsOJZNzYUKIgQ1WAX0jL16zhwSY1catqUg8s92X9Gz7w1gp0GwdtRVZ5?=
 =?iso-8859-1?Q?mx8auzMWCJlJxo1YcPWKJYs5Agg7DgGbPMc2Yevvxu6OxweqxU1eZ2bgXz?=
 =?iso-8859-1?Q?gtEpxpY/OhMpiKrneNLhlJt3fO0t3FuZjgCvS4QrbFOdJAYKVUFQU=3D?=
x-forefront-antispam-report:
 CIP:255.255.255.255;CTRY:;LANG:en;SCL:1;SRV:;IPV:NLI;SFV:NSPM;H:GV0P278MB0419.CHEP278.PROD.OUTLOOK.COM;PTR:;CAT:NONE;SFS:(13230040)(1800799024)(366016)(376014)(38070700018);DIR:OUT;SFP:1102;
x-ms-exchange-antispam-messagedata-chunkcount: 1
x-ms-exchange-antispam-messagedata-0:
 =?iso-8859-1?Q?IPDWGtyPtlXxjJcCIKFzYQ27Nlc/A+PuqNhTASKdVVZTXKzEKjxy8JVG87?=
 =?iso-8859-1?Q?G9bgGjjxAqdN1M6nvge76Oh5r8eYSZHOYc8yVJx2TnejdYU8RLGmFup5/I?=
 =?iso-8859-1?Q?OoNXEmGWnltBdIFteSu6HiA4N2r/bIHnnhVSgu8mq4DMuttVbs6e57TfAk?=
 =?iso-8859-1?Q?VM3GvDEFWcwlpjpmzzmHBDbOPmQN80q2LCiLwiD8cIUzRc9UiR5nDdOCkK?=
 =?iso-8859-1?Q?f52gywQKwai/JsrwGbAcTGRQYBFNuo1ptoNqfuV6H5itmAKUh6fi42UT59?=
 =?iso-8859-1?Q?rxtNLGWuBJ2a3PxAf9k4iMkE+AL8XqXqGG6z7zHpTAVCoAl5Po+f+ZihLg?=
 =?iso-8859-1?Q?VsIESVemmblHZ2ZfGjGHD9KAfSfVQEWcui2zGU37Z2Kw1Yw/5V7iYMmkcY?=
 =?iso-8859-1?Q?ulT+CnqgRtq1Khj3phwfvpfaaJM2V0NFe/UnErztabW5gK3E9EOqveHrOQ?=
 =?iso-8859-1?Q?j7uyChHRvcgx2fqAuYJ7YggPwheHMP6OiL2cIN1t+PF4HV8F3iKvk8G4MF?=
 =?iso-8859-1?Q?uHCHy/5hODeErYVIiNVk0bcjfFFWsrbzlwiZqaqQDP65MLySvygTw+XC/L?=
 =?iso-8859-1?Q?OB9tg+sfUSQbbSjNE38A2rLgUKx8ZBN3zUd9/efCTX56Ur+3r53nA/CWev?=
 =?iso-8859-1?Q?JIdKgmOhEbvb5zQTSZqElPN/uPqOlWlkh0HAXsGpHOPjsWvk6y1BoTn8GE?=
 =?iso-8859-1?Q?XvSmSOxe3szRtF1rsVyKf6ZEP7xWCrEmVmgbOcyFBP9mUV4RxFvw/gMycZ?=
 =?iso-8859-1?Q?JCpl+vOm2R0NG9fWZDwMn1qHmhQBEKtjkZEoH6V8RC0+xTBPGCQfuRWy0W?=
 =?iso-8859-1?Q?dx8QToZS+BuG3twAH/AROFGFkqFSOVkXehUxi2ER1lcvHmUA5EmqzXAfZ4?=
 =?iso-8859-1?Q?Mwipk1SWJFjQSjbG8PzOkRuz9jy7EgoXvOJl1hOJ1nZy6yMm9t6ZdI2drW?=
 =?iso-8859-1?Q?zkSJQpFzRQp0VVon/tvosGiY8GQoOvwn7MQ1YhmzW4U+zCWKzmWaXy5fHu?=
 =?iso-8859-1?Q?OwzGctuyTiwkpuFi7FXUtw0BSlNUf4XmDqIb0/zlW5r5Osu18ifyM+rHKe?=
 =?iso-8859-1?Q?N47DfliQydHsF/VvA72qtMX7U+ZQjwqNJAFSsL+s/Zc+Ip5yrusFCHoM7N?=
 =?iso-8859-1?Q?rmO4z1X0e1uFWzGmSm92Z94f8jhSd1rb3j+CScKvdmOUgbBRL3+xA11Uhl?=
 =?iso-8859-1?Q?nsMtOVBAxiSnuPU2pDjtNgRH7EzxSo+xrjnog1d+yK8Ytuvnob+GT86/El?=
 =?iso-8859-1?Q?q8Gpv6h7cAJJ2JCmAsva6/iGn7Au7ydVyDz8DI52j19t7pTNaslYy+pqPF?=
 =?iso-8859-1?Q?LCnfI50UXH8cjiFpUHO1qDzcXZmhXWYwQ0iDRedas3HvmuM5MkLNCLiT7w?=
 =?iso-8859-1?Q?Y52TLkhbjxp6Bd+Ii51icGBRJW8tswpCprfV43movewHh8PCgOG8JujQ/b?=
 =?iso-8859-1?Q?dN9pyDhEGuccHEhTCjaubu4LjL4TGykRci0xR6jzUqZJInJkEeYNA9dWNC?=
 =?iso-8859-1?Q?oJZD6WfKa5m1XqB6ZnFAjR2gtdcG625aRR6XmWpbm0aEnWK2SjZvKguxR/?=
 =?iso-8859-1?Q?4TfzIiJ/GMRDG3ZNow3SDqnEuwMBLAMDrQMh+STO/T5Fx7ydCskZMiiTE2?=
 =?iso-8859-1?Q?nqtooA9rix1f3rS7JwA5c9MY6F/g9p07xkiwj3Cak6Nab/XfQNfr7wjw?=
 =?iso-8859-1?Q?=3D=3D?=
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
MIME-Version: 1.0
X-OriginatorOrg: dbi-services.com
X-MS-Exchange-CrossTenant-AuthAs: Internal
X-MS-Exchange-CrossTenant-AuthSource: GV0P278MB0419.CHEP278.PROD.OUTLOOK.COM
X-MS-Exchange-CrossTenant-Network-Message-Id: 136883b3-ce3a-4497-8f08-08dcf836effc
X-MS-Exchange-CrossTenant-originalarrivaltime: 29 Oct 2024 16:30:01.7875
 (UTC)
X-MS-Exchange-CrossTenant-fromentityheader: Hosted
X-MS-Exchange-CrossTenant-id: b9c70613-b1ea-47b7-9fdd-e6f3a67ce75c
X-MS-Exchange-CrossTenant-mailboxtype: HOSTED
X-MS-Exchange-CrossTenant-userprincipalname: v476PynyHTTHbR1DtGm2xZ6br6LQEKeJHjw/FSjcCD2uF2TTglywvdTxKgd5iACh1bNiBvaYoxLbqJEo3+jH73hvmXFzjuaAxS5zuwDp1T2ZX5C+7x50+qHPLq3ROmz5
X-MS-Exchange-Transport-CrossTenantHeadersStamped: ZR1P278MB1101
List-Id: <pgsql-general.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-general@lists.postgresql.org>
List-Owner: <mailto:pgsql-general-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-general>
Archived-At: <https://www.postgresql.org/message-id/GV0P278MB0419EB5885DF99C5354804A9D24B2%40GV0P278MB0419.CHEP278.PROD.OUTLOOK.COM>
Precedence: bulk

Delays between "connection received" and "connection authenticated" because=
 of localhost entries in hba=0A=
=0A=
Hi,=0A=
=0A=
we're facing a strange issue with delays between "connection received" and =
"connection authenticated".=0A=
=0A=
# select version();=0A=
                                                              version=0A=
---------------------------------------------------------------------------=
--------------------------------------------------------=0A=
 PostgreSQL 15.6 (Ubuntu 15.6-1.pgdg22.04+1) on x86_64-pc-linux-gnu, compil=
ed by gcc (Ubuntu 11.4.0-1ubuntu1~22.04) 11.4.0, 64-bit=0A=
(1 ligne)=0A=
=0A=
I know, this is not the latest minor version.=0A=
=0A=
# \! cat /etc/os-release | head -1=0A=
PRETTY_NAME=3D"Ubuntu 22.04.4 LTS"=0A=
=0A=
What we see in the log is this (around 4 seconds delay):=0A=
=0A=
2024-05-07 15:29:50.244 CEST [369909] LOG:  connection received: host=3Dxx.=
xx.xx.100 port=3D48434=0A=
2024-05-07 15:29:54.518 CEST [369909] LOG:  connection authenticated: ident=
ity=3D"xxxxxx" method=3Dmd5 (/etc/postgresql/15/main/pg_hba.conf:121)=0A=
=0A=
The matching line is this ( I know md5 ):=0A=
host    all             xxxxx     xx.xx.xx.0/24            md5=0A=
=0A=
What we've found out so far is, that this only happens if we have a localho=
st(or any other hostname) line before the line matching our connection, som=
ething like this:=0A=
host    replication     xxxxx     localhost                md5=0A=
host    all             xxxxx     xx.xx.xx.0/24            md5=0A=
=0A=
We don't see it if we do it like this:=0A=
host    all             xxxxx     xx.xx.xx.0/24            md5=0A=
host    replication     xxxxx     localhost                md5=0A=
=0A=
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 o=
n the OS using dig (in a loop several hundred of times). It is also only ha=
ppening from time to time, and not constantly.=0A=
=0A=
What we're basically looking for is a way to prove the assumption without i=
nvolving PostgreSQL at all, if that does make sense?=0A=
=0A=
Many thanks in advance=0A=
Daniel=0A=