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

From: Jim Jones <jim(dot)jones(at)uni-muenster(dot)de>
To: PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: Authentication fails for md5 connections if ~/.postgresql/postgresql.{crt and key} exist
Date: 2022-12-09 12:55:25
Message-ID: 07eaed48-0da7-bf5b-b299-6f5f49264dae@uni-muenster.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

Attachment Content-Type Size
v1-0001-add-sslmode-no-clientcert.patch text/x-patch 2.9 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Andrew Dunstan 2022-12-09 13:06:58 Re: Error-safe user functions
Previous Message Aleksander Alekseev 2022-12-09 12:49:18 Re: Add 64-bit XIDs into PostgreSQL 15