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
From 8013619a1ee2187fce289054a846e17a5414801d Mon Sep 17 00:00:00 2001
From: Jim Jones <jim.jo...@uni-muenster.de>
Date: Mon, 21 Nov 2022 15:05:33 +0100
Subject: [PATCH v1] add sslmode no-clientcert
---
doc/src/sgml/libpq.sgml | 11 +++++++++
src/interfaces/libpq/fe-connect.c | 1 +
src/interfaces/libpq/fe-secure-openssl.c | 29 +++++++++++++++++++++++-
3 files changed, 40 insertions(+), 1 deletion(-)
diff --git a/doc/src/sgml/libpq.sgml b/doc/src/sgml/libpq.sgml
index f9558dec3b..aa14253f7e 100644
--- a/doc/src/sgml/libpq.sgml
+++ b/doc/src/sgml/libpq.sgml
@@ -8638,6 +8638,17 @@ ldap://ldap.acme.com/cn=dbserver,cn=hosts?pgconnectinfo?base?(objectclass=*)
</entry>
</row>
+ <row>
+ <entry><literal>no-clientcert</literal></entry>
+ <entry>Yes</entry>
+ <entry>Depends on CA policy</entry>
+ <entry>I want my data encrypted, and I accept the overhead. I want to be
+ sure that I connect to a server that I trust, but I do not wish to
+ authenticate the client using ssl certificates.
+ </entry>
+ </row>
+
+
<row>
<entry><literal>verify-ca</literal></entry>
<entry>Yes</entry>
diff --git a/src/interfaces/libpq/fe-connect.c b/src/interfaces/libpq/fe-connect.c
index f88d672c6c..08c7f21ec1 100644
--- a/src/interfaces/libpq/fe-connect.c
+++ b/src/interfaces/libpq/fe-connect.c
@@ -1268,6 +1268,7 @@ connectOptions2(PGconn *conn)
&& strcmp(conn->sslmode, "allow") != 0
&& strcmp(conn->sslmode, "prefer") != 0
&& strcmp(conn->sslmode, "require") != 0
+ && strcmp(conn->sslmode, "no-clientcert") != 0
&& strcmp(conn->sslmode, "verify-ca") != 0
&& strcmp(conn->sslmode, "verify-full") != 0)
{
diff --git a/src/interfaces/libpq/fe-secure-openssl.c b/src/interfaces/libpq/fe-secure-openssl.c
index bad85359b6..c092020c5e 100644
--- a/src/interfaces/libpq/fe-secure-openssl.c
+++ b/src/interfaces/libpq/fe-secure-openssl.c
@@ -1098,7 +1098,34 @@ initialize_SSL(PGconn *conn)
if (conn->sslcert && strlen(conn->sslcert) > 0)
strlcpy(fnbuf, conn->sslcert, sizeof(fnbuf));
else if (have_homedir)
- snprintf(fnbuf, sizeof(fnbuf), "%s/%s", homedir, USER_CERT_FILE);
+ {
+
+ /* sslmode no-clientcert */
+ if (conn->sslmode[0] == 'n')
+ {
+
+ /*
+ * The option "no-clientcert" ignores the client certificate in case they are
+ * stored in ~/.postgresql/postgresql.crt and ~/.postgresql/postgresql.key,
+ * and therefore automatically sent to the server. This is useful for
+ * pg_hba.conf entries of type "hostssl" without "cert" as authentication
+ * method - e.g. using "md5" or "scram-sha-256" - as they will fail if
+ * the client certificate is sent to the server in the background and it
+ * does not exist in the server's 'ssl_ca_file'.
+ */
+
+ fnbuf[0] = '\0';
+
+ }
+ else
+ {
+
+ snprintf(fnbuf, sizeof(fnbuf), "%s/%s", homedir, USER_CERT_FILE);
+
+ }
+
+
+ }
else
fnbuf[0] = '\0';
--
2.25.1
