MIME-Version: 1.0
From: Hao Zhang <kennthhz@gmail.com>
Date: Wed, 17 Jul 2024 21:12:52 -0700
Message-ID: <CAGXpB2mwQqJv0pJL8u1ZduiUERYuWxr8_xdGbhWRAfmYyq8J7g@mail.gmail.com>
Subject: psql sslmode behavior and trace_connection_negotiation in PG17
To: pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000009719d3061d7dcbb9"
Archived-At: <https://www.postgresql.org/message-id/CAGXpB2mwQqJv0pJL8u1ZduiUERYuWxr8_xdGbhWRAfmYyq8J7g%40mail.gmail.com>
Precedence: bulk

--0000000000009719d3061d7dcbb9
Content-Type: text/plain; charset="UTF-8"

Hi

I tried to connect with psql + client sslmode = require + server requiring
ssl with PG17 and trace_connection_negotiation = "on". So "SSLRequest
accepted" is logged twice with two different PID. I believe the PID 15553
is psql and 15554 is the PG backend. How do you explain the two connections
with SSLRequest? From the log, it seems psql made a connection to itself
with SSLRequest and proxied that to Postgres server with a full SSL
negotiation. I never saw a log on 15553's connection being closed when I
closed the psql process. Does this behavior match what was talked about in
the below hacker thread on additional connection?

2024-07-17 03:06:54.492 PDT [15553] LOG:  connection received:
host=127.0.0.1 port=54002
2024-07-17 03:06:54.492 PDT [15553] LOG:  SSLRequest accepted
2024-07-17 03:06:59.982 PDT [15554] LOG:  connection received:
host=127.0.0.1 port=54004
2024-07-17 03:06:59.982 PDT [15554] LOG:  SSLRequest accepted
2024-07-17 03:06:59.994 PDT [15554] LOG:  connection authenticated:
identity="postgres" method=md5 (/usr/local/pgsql/data/pg_hba.conf:18)
2024-07-17 03:06:59.994 PDT [15554] LOG:  connection authorized:
user=postgres database=postgres application_name=psql SSL enabled
(protocol=TLSv1.3, cipher=TLS_AES_256_GCM_SHA384, bits=256)


https://www.postgresql.org/message-id/flat/CAM-w4HOEAzxyY01ZKOj-iq=M4-VDk=vzQgUsuqiTFjFDZaebdg@mail.gmail.com

--0000000000009719d3061d7dcbb9
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">Hi<div><br></div><div>I tried to connect with psql=C2=A0+ =
client sslmode =3D require=C2=A0+ server requiring ssl with PG17 and trace_=
connection_negotiation =3D &quot;on&quot;. So &quot;SSLRequest accepted&quo=
t; is logged twice with two different PID. I believe the PID 15553 is psql =
and 15554 is the PG backend. How do you explain the two connections with SS=
LRequest? From the log, it seems psql made a connection to itself with SSLR=
equest and proxied that to Postgres server with a full SSL negotiation. I n=
ever saw a log on 15553&#39;s connection being closed when I closed the psq=
l process. Does this behavior match what was talked about in the below hack=
er thread on additional connection?=C2=A0<br><div><br></div><div>2024-07-17=
 03:06:54.492 PDT [15553] LOG: =C2=A0connection received: host=3D127.0.0.1 =
port=3D54002<br>2024-07-17 03:06:54.492 PDT [15553] LOG: =C2=A0SSLRequest a=
ccepted<br>2024-07-17 03:06:59.982 PDT [15554] LOG: =C2=A0connection receiv=
ed: host=3D127.0.0.1 port=3D54004<br>2024-07-17 03:06:59.982 PDT [15554] LO=
G: =C2=A0SSLRequest accepted<br>2024-07-17 03:06:59.994 PDT [15554] LOG: =
=C2=A0connection authenticated: identity=3D&quot;postgres&quot; method=3Dmd=
5 (/usr/local/pgsql/data/pg_hba.conf:18)<br>2024-07-17 03:06:59.994 PDT [15=
554] LOG: =C2=A0connection authorized: user=3Dpostgres database=3Dpostgres =
application_name=3Dpsql SSL enabled (protocol=3DTLSv1.3, cipher=3DTLS_AES_2=
56_GCM_SHA384, bits=3D256)<br><br><br><a href=3D"https://www.postgresql.org=
/message-id/flat/CAM-w4HOEAzxyY01ZKOj-iq=3DM4-VDk=3DvzQgUsuqiTFjFDZaebdg@ma=
il.gmail.com">https://www.postgresql.org/message-id/flat/CAM-w4HOEAzxyY01ZK=
Oj-iq=3DM4-VDk=3DvzQgUsuqiTFjFDZaebdg@mail.gmail.com</a><br></div></div></d=
iv>

--0000000000009719d3061d7dcbb9--