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

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.jones@uni-muenster.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  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