[PATCH] Automatic client certificate selection support for libpq v1 - Mailing list pgsql-hackers

I had a situation where I needed to connect to multiple postgresql
servers in a variety of programs written in a variety of languages,
including some which connected to multiple servers at the same time.
As some of you might know, you cannot usefully put multiple
certificates or keys in the postgresql.crt/.key files.

I was pleased to see that 8.4 had sslcert/sslkey support and if it was
in 8.3, I *might* have done the painful work to update all of the
programs to use host-conditional environmental variables.  However,
since I had to modify my 8.3 postgresql anyway, I decided to go with
an automatic file-selection approach.  Essentially, before trying the
default postgresql.crt (and thus only if the sslcert option is not set
in 8.4), it appends the conn->pgname to the filename and checks to see
if that file exists.  It uses that host-specific file if it does,
otherwise it continues to use the previous default.  As such, it is a
low-impact change.

One possible problem is that as written it does not handle aliases
cleanly.  If the host-specific certificate is named
"postgresql.crt.db.example.com" you will be able to connect if you use
`psql -h db.example.com`.  However, if you use `psql -h db` you will
not match the file on disk (and thus fall back to postgresql.crt which
presumably will not contain the correct certificate).  This can be
manually ``solved'' by creating links or copies of the host specific
file to each needed alias.  If there is demand, the complexity of the
patch could be increased by using DNS to try and discover a canonical
name for the host.  Using the IP address is another option, but is
probably not preferred since it reduces flexibility.

I can provide an 8.3 patch if anyone desires.
                -Seth Robertson                 in-pgsql-hackers@baka.org

diff --git a/src/interfaces/libpq/fe-secure.c b/src/interfaces/libpq/fe-secure.c
index ee0a91e..9a16996 100644
--- a/src/interfaces/libpq/fe-secure.c
+++ b/src/interfaces/libpq/fe-secure.c
@@ -599,7 +599,21 @@ client_cert_cb(SSL *ssl, X509 **x509, EVP_PKEY **pkey)    if (conn->sslcert)        strncpy(fnbuf,
conn->sslcert,sizeof(fnbuf));    else
 
-        snprintf(fnbuf, sizeof(fnbuf), "%s/%s", homedir, USER_CERT_FILE);
+    {
+        fnbuf[0] = 0;
+
+        /* Check to see if there is a destination specific client certificate */
+        if (conn->pghost)
+        {
+            snprintf(fnbuf, sizeof(fnbuf), "%s/%s.%s", homedir, USER_CERT_FILE, conn->pghost);
+            if (access(fnbuf, R_OK) < 0)
+                fnbuf[0] = 0;    /* Cannot find one, try for default certificate */
+        }
+
+        /* Use default certificate file name if there was no hostname present, or host specific file did not exist */
+        if (!fnbuf[0])
+            snprintf(fnbuf, sizeof(fnbuf), "%s/%s", homedir, USER_CERT_FILE);
+    }    /*     * OpenSSL <= 0.9.8 lacks error stack handling, which means it's likely to
@@ -713,8 +727,20 @@ client_cert_cb(SSL *ssl, X509 **x509, EVP_PKEY **pkey)    }    else    {
-        /* No PGSSLKEY specified, load default file */
-        snprintf(fnbuf, sizeof(fnbuf), "%s/%s", homedir, USER_KEY_FILE);
+        /* No PGSSLKEY specified, load default or host specific default file */
+        fnbuf[0] = 0;
+
+        /* Check to see if there is a destination specific client key */
+        if (conn->pghost)
+        {
+            snprintf(fnbuf, sizeof(fnbuf), "%s/%s.%s", homedir, USER_KEY_FILE, conn->pghost);
+            if (access(fnbuf, R_OK) < 0)
+                fnbuf[0] = 0;    /* Cannot find one, try for default key */
+        }
+
+        /* Use default key file name if there was no hostname present, or host specific file did not exist */
+        if (!fnbuf[0])
+            snprintf(fnbuf, sizeof(fnbuf), "%s/%s", homedir, USER_KEY_FILE);    }    if (fnbuf[0] != '\0')