Re: Authentication fails for md5 connections if ~/.postgresql/postgresql.{crt and key} exist - Mailing list pgsql-hackers

From Jelte Fennema
Subject Re: Authentication fails for md5 connections if ~/.postgresql/postgresql.{crt and key} exist
Date
Msg-id CAGECzQSeDdOzBeLk3vteQLf-w4FERO6t8A3SfQeK7zgNowqGGA@mail.gmail.com
Whole thread Raw
In response to Authentication fails for md5 connections if ~/.postgresql/postgresql.{crt and key} exist  (Jim Jones <jim.jones@uni-muenster.de>)
List pgsql-hackers
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.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

pgsql-hackers by date:

Previous
From: Amit Langote
Date:
Subject: Re: ATTACH PARTITION seems to ignore column generation status
Next
From: "Drouvot, Bertrand"
Date:
Subject: Re: Generate pg_stat_get_xact*() functions with Macros