Re: Authentication fails for md5 connections if ~/.postgresql/postgresql.{crt and key} exist

The easiest way to achieve the same (without patching libpq) is by setting
sslcert to something non-existent. While maybe not the most obvious way, I
would consider this the recommended approach.

(sorry for the resend Jim, my original message got blocked to the wider
mailing list)

On Fri, 6 Jan 2023 at 09:15, Jim Jones <jim(dot)jones(at)uni-muenster(dot)de> wrote:

> Dear PostgreSQL Hackers,
>
> Some time ago we faced a small issue in libpq regarding connections
> configured in the pg_hba.conf as type *hostssl* and using *md5* as
> authentication method.
>
> One of our users placed the client certificates in ~/.postgresql/ (
> *postgresql.crt,**postgresql.key*), so that libpq sends them to the
> server without having to manually set *sslcert* and *sslkey* - which is
> quite convenient. However, there are other servers where the same user
> authenticates with password (md5), but libpq still sends the client
> certificates for authentication by default. This causes the authentication
> to fail even before the user has the chance to enter his password, since he
> has no certificate registered in the server.
>
> To make it clearer:
>
> Although the connection is configured as ...
>
>
> *host all dummyuser 192.168.178.42/32 <http://192.168.178.42/32> md5 *
>
> ... and the client uses the following connection string ...
>
> *psql "host=myserver dbname=db user=**dummyuser" *
>
> ... the server tries to authenticate the user using the client
> certificates in *~/.postgresql/* and, as expected, the authentication
> fails:
>
> *psql: error: connection to server at "myserver" (xx.xx.xx.xx), port 5432
> failed: SSL error: tlsv1 alert unknown ca*
>
> Server log:
>
>
> *2022-12-09 10:50:59.376 UTC [13896] LOG: could not accept SSL
> connection: certificate verify failed *
>
> Am I missing something?
>
> Obviously it would suffice to just remove or rename *~/.postgresql/*
> *postgresql.{crt,key}*, but the user needs them to authenticate in other
> servers. So we came up with the workaround to create a new sslmode
> (no-clientcert) to make libpq explicitly ignore the client certificates, so
> that we can avoid ssl authentication errors. These small changes can be
> seen in the patch file attached.
>
> *psql "host=myserver dbname=db user=**dummyuser sslrootcert=server.crt
> sslmode=no-clientcert"*
>
> Any better ideas to make libpq ignore *~/.postgresql/*
> *postgresql.{crt,key}*? Preferably without having to change the source
> code :) Thanks in advance!
>
> Best,
>
> Jim
>
>