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

From: Jim Jones <jim(dot)jones(at)uni-muenster(dot)de>
To: Jelte Fennema <postgres(at)jeltef(dot)nl>
Cc: PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: Re: Authentication fails for md5 connections if ~/.postgresql/postgresql.{crt and key} exist
Date: 2023-01-06 19:04:10
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

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!


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
> <>  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

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Peter Geoghegan 2023-01-06 19:16:00 Re: pgsql: Delay commit status checks until freezing executes.
Previous Message Pavel Stehule 2023-01-06 19:02:41 Re: Schema variables - new implementation for Postgres 15 (typo)