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

Hi Jelte, thanks for the message. You're right, an invalid cert path
does solve the issue - I even use it for tests. Although it solves the
authentication issue it still looks in my eyes like a non intuitive
workaround/hack. Perhaps a new sslmode isn't the right place for this
"feature"? Thanks again for the suggestion!

Jim

On 06.01.23 09:32, Jelte Fennema wrote:
> 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.
>
> 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
>