Обсуждение: BUG #17919: "client hello" message / SNI / Openshift Routes

The following bug has been logged on the website:

Bug reference:      17919
Logged by:          Ronald van de Kuil
Email address:      ronald.van.de.kuil@nl.ibm.com
PostgreSQL version: 15.2
Operating system:   windows server 2019
Description:

I have deployed postgresql in Openshift with a certificate that matches its
openshift route name. 

Then it should be possible to connect to the database instance via targeting
the route in psql. The way that works, is that the openshift router looks at
the SNI, and then it will be able to route it into the Pod that has the
certificate with the same CN or SAN.

I have wiresharked the connection, and noticed that psql does not send a
client hello message.

I would make a guess that this is related to the version of libpq, based on
something which has been seen before on another project that is using
postgresql in combination with terraform, see:
https://github.com/cyrilgdn/terraform-provider-postgresql/pull/295 

When I take a look at the latest source code then I believe that provision
have been made for setting up SNI connections:

https://github.com/postgres/postgres/blob/master/doc/src/sgml/libpq.sgml#L1946

Is this a bug?

On Wed, May 3, 2023 at 5:57 PM PG Bug reporting form
<noreply@postgresql.org> wrote:
>
> The following bug has been logged on the website:
>
> Bug reference:      17919
> Logged by:          Ronald van de Kuil
> Email address:      ronald.van.de.kuil@nl.ibm.com
> PostgreSQL version: 15.2
> Operating system:   windows server 2019
> Description:
>
> I have deployed postgresql in Openshift with a certificate that matches its
> openshift route name.
>
> Then it should be possible to connect to the database instance via targeting
> the route in psql. The way that works, is that the openshift router looks at
> the SNI, and then it will be able to route it into the Pod that has the
> certificate with the same CN or SAN.
>
> I have wiresharked the connection, and noticed that psql does not send a
> client hello message.
>
> I would make a guess that this is related to the version of libpq, based on
> something which has been seen before on another project that is using
> postgresql in combination with terraform, see:
> https://github.com/cyrilgdn/terraform-provider-postgresql/pull/295
>
> When I take a look at the latest source code then I believe that provision
> have been made for setting up SNI connections:
>
> https://github.com/postgres/postgres/blob/master/doc/src/sgml/libpq.sgml#L1946
>
> Is this a bug?

What proxy do you use in openshift, and is it PostgreSQL aware?

PostgreSQL will send the client hello message *after* it has
negotiated with the server that SSL should be used. So to use SNI to
route things, you need a proxy that's aware of the PostgreSQL
protocol, performs the SSL negotiation and *then* looks at the SNI
packages. (In the documentation source link you sent, that is
explained in line 1957-1959).

--
 Magnus Hagander
 Me: https://www.hagander.net/
 Work: https://www.redpill-linpro.com/

On Mon, May 15, 2023 at 12:47 PM Ronald Van de Kuil
<ronald.van.de.kuil@nl.ibm.com> wrote:
>
> Openshift uses haproxy. And I have configured a passthrough route for the postgresql service.
>
> In addition, I have managed to make a tcpdump of connecting to the Postgres instance via oc-port-forward, a CLI
utilitywhich is not production grade. However, it gives me a chance to understand the postgresql handshake. There I see
aClient Hello, then a Client Hello with a change of Cypher Spec, and then the Server hallo. 
>
> On this connection that was established, I see the absence of an "Extension: server_name". I see that in connections
thatare established to the console of Openshift. I would therefore like to believe that some work needs to be done on
thePostgreSQL client to send the SNI. 

What version of the client are you using? I would expect the host name
extension to be set in clienthello by default - but it does require
the client libpq to be at least version 14. Maybe you have more than
one version of libpq around, and it's picking up the wrong one? (Note
that it is perfectly possible to have different versions of psql and
libpq, and you need to verify the libpq version specifically).

//Magnus