Thread: [PATCH] Automatic client certificate selection support for libpq v1
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')
Seth Robertson <in-pgsql-hackers@baka.org> writes:
> 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.
Hmm, shouldn't we fix *that* rather than inventing a hack like this?
regards, tom lane
On Fri, May 8, 2009 at 12:10 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Seth Robertson <in-pgsql-hackers@baka.org> writes: >> 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. > > Hmm, shouldn't we fix *that* rather than inventing a hack like this? Possibly a la SSH's authorized_keys or known_hosts formats? David
In message <8766.1241799013@sss.pgh.pa.us>, Tom Lane writes: Seth Robertson <in-pgsql-hackers@baka.org> writes: > 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 multipleservers at the same time. > As some of you might know, you cannot usefully put multiple > certificates or keysin the postgresql.crt/.key files. Hmm, shouldn't we fix *that* rather than inventing a hack like this? I certainly agree that it would be ideal. My understanding is that OpenSSL does not really support certificate stores/wallets or other methods of automatically handling multiple certificates (see http://gagravarr.org/writing/openssl-certs/general.shtml and http://www.openssl.org/docs/ssl/SSL_CTX_use_certificate.html, the latter which says: "NOTES The internal certificate store of OpenSSL can hold two private key/certificate pairs at a time: one key/certificate of type RSA and one key/certificate of type DSA.") The http://www.openssl.org/docs/ssl/SSL_CTX_set_client_cert_cb.html document suggests that the callback function (which postgresql already uses) can be extended to search a private certificate store to select and return the proper certificate. However, it is not clear from this manual page how to get access to the information about the requested certificate--I can only presume the information was sent to the client. Following each certificate chain from the client certificates loaded back to see if any match would be pretty painful as well. Basically doing this would probably become a project instead of a 5 minute hack to support 80% of the functionality. I understand the desire to limit the number of hacks in the source code, though. -Seth Robertson in-pgsql-hackers@baka.org
Seth Robertson <in-pgsql-hackers@baka.org> writes: > In message <8766.1241799013@sss.pgh.pa.us>, Tom Lane writes: >> Hmm, shouldn't we fix *that* rather than inventing a hack like this? > Basically doing this would probably become a project instead of a 5 > minute hack to support 80% of the functionality. I understand the > desire to limit the number of hacks in the source code, though. It's certainly possible that what you have done represents the best available engineering tradeoff. But at this point it's too late for 8.4 and so we have quite a bit of time to think about it. I'd like to at least consider alternative solutions before we choose this one. BTW, I was reminded today that Fedora/Red Hat are hoping to standardize all crypto-related functionality in their entire distro on the NSS libraries: http://fedoraproject.org/wiki/FedoraCryptoConsolidation This is a long way from fruition, but at some point we are going to be faced with using a compatibility wrapper that sort of emulates openssl (they are not even pretending it'll be 100% compatible). So I'm feeling a bit leery of wiring in any additional dependence on details of openssl functionality. I hesitate though to suggest that we think about porting ourselves to NSS --- I'm not sure that there would be benefits to us within the context of Postgres alone. Is anyone sufficiently up on the different crypto libraries to comment on that? regards, tom lane
In message <12314.1241809436@sss.pgh.pa.us>, Tom Lane writes:
Seth Robertson <in-pgsql-hackers@baka.org> writes: > In message <8766.1241799013@sss.pgh.pa.us>, Tom Lane writes:
>> Hmm, shouldn't we fix *that* rather than inventing a hack like this? > Basically doing this would probably
becomea project instead of a 5 > minute hack to support 80% of the functionality. I understand the > desire to
limitthe number of hacks in the source code, though. It's certainly possible that what you have done represents
thebest available engineering tradeoff. But at this point it's too late for 8.4 and so we have quite a bit of time
tothink about it. I'd like to at least consider alternative solutions before we choose this one. BTW, I was
remindedtoday that Fedora/Red Hat are hoping to standardize all crypto-related functionality in their entire distro
onthe NSS libraries:
I'm not sure that there would be benefits to us within the context of Postgres alone. Is anyone sufficiently up
onthe different crypto libraries to comment on that?
I am not perfectly up to speed, but switching to NSS would solve this
(automatic client certificate selection) problem in the crypto
library, since NSS supports a client certificate database and
furthermore has a default callback function NSS_GetClientAuthData
which searches the certificate database for a suitable match. It also
supports OCSP (online certificate status protocol) which is an online
certificate revocation check (better than the current TODO item of
"Allow SSL CRL files to be re-read during configuration file reload,
rather than requiring a server restart").
Well, I guess that openssl supports OCSP as well, but the support does
not seem as complete (no AIA support--revocation URL embedded in the
certificate--that I can see).
It is of course possible to support both at the same time (at
compile-time, if nowhere else).
-Seth Robertson in-pgsql-hackers@baka.org
Seth Robertson <in-pgsql-hackers@baka.org> writes:
> In message <12314.1241809436@sss.pgh.pa.us>, Tom Lane writes:
> BTW, I was reminded today that Fedora/Red Hat are hoping to standardize
> all crypto-related functionality in their entire distro on the NSS
> libraries:
> I am not perfectly up to speed, but switching to NSS would solve this
> (automatic client certificate selection) problem in the crypto
> library, since NSS supports a client certificate database and
> furthermore has a default callback function NSS_GetClientAuthData
> which searches the certificate database for a suitable match.
Interesting.
> It also
> supports OCSP (online certificate status protocol) which is an online
> certificate revocation check (better than the current TODO item of
> "Allow SSL CRL files to be re-read during configuration file reload,
> rather than requiring a server restart").
> Well, I guess that openssl supports OCSP as well, but the support does
> not seem as complete (no AIA support--revocation URL embedded in the
> certificate--that I can see).
Well, one of the arguments the Fedora crowd is making for NSS is that
it's more feature-complete than the other crypto libraries, so this
doesn't surprise me much.
> It is of course possible to support both at the same time (at
> compile-time, if nowhere else).
Yes, I suppose we'd not wish to just drop openssl completely.
I wonder how much code duplication would ensue from a compile-time
choice of which library to use ...
regards, tom lane
In message <14727.1241816192@sss.pgh.pa.us>, Tom Lane writes:
> It is of course possible to support both at the same time (at > compile-time, if nowhere else). Yes, I
supposewe'd not wish to just drop openssl completely. I wonder how much code duplication would ensue from a
compile-time choice of which library to use ...
My only datapoint for you is curl, which is an application I happen to
have discovered that can use either NSS and OpenSSL.
Lines Words Chars Filename 2508 7890 74682 ssluse.c 1331 3708 36411 nss.c
I imagine that you would more or less have to provide a different
be-secure.c and fe-secure.c file for the two different
libraries--whether as a separate file or via #ifdefs. It looks like
there is a small amount of common code present (why *is*
pg_block_sigpipe() in that file anyway?)
-Seth Robertson in-pgsql-hackers@baka.org
On Friday 08 May 2009 22:03:56 Tom Lane wrote: > I hesitate though to suggest that we think about porting > ourselves to NSS --- I'm not sure that there would be benefits to us > within the context of Postgres alone. That could be attractive if we ripped out the OpenSSL code at the same time, as the NSS API is purportedly more abstract and presumably would reduce the amount and the complexity of the code.
Peter Eisentraut wrote: > On Friday 08 May 2009 22:03:56 Tom Lane wrote: >> I hesitate though to suggest that we think about porting >> ourselves to NSS --- I'm not sure that there would be benefits to us >> within the context of Postgres alone. > > That could be attractive if we ripped out the OpenSSL code at the same time, > as the NSS API is purportedly more abstract and presumably would reduce the > amount and the complexity of the code. Is NSS available on all the platforms that we are (and that has OpenSSL today)? Another thought: if we were to make ourselves support multiple SSL libraries (that has been suggested before - at that point, people wanted GnuTLS), we could also add support for Windows SChannel, which I'm sure some win32 people would certainly prefer - much easier to do SSL deployments within an existing MS infrastructure... But no, that certainly wouldn't *reduce* the amount of code... //Magnus
Seth Robertson wrote: > In message <14727.1241816192@sss.pgh.pa.us>, Tom Lane writes: > > > It is of course possible to support both at the same time (at > > compile-time, if nowhere else). > > Yes, I suppose we'd not wish to just drop openssl completely. > I wonder how much code duplication would ensue from a compile-time > choice of which library to use ... > > My only datapoint for you is curl, which is an application I happen to > have discovered that can use either NSS and OpenSSL. > > Lines Words Chars Filename > 2508 7890 74682 ssluse.c > 1331 3708 36411 nss.c IIRC, they also support gnutls. So we can probably get hints there about how to get this support if we want to :-) > I imagine that you would more or less have to provide a different > be-secure.c and fe-secure.c file for the two different > libraries--whether as a separate file or via #ifdefs. It looks like > there is a small amount of common code present (why *is* > pg_block_sigpipe() in that file anyway?) Clearly this would be a good time to fix such abstraction errors if we decide to go ahead :-) -- Magnus HaganderSelf: http://www.hagander.net/Work: http://www.redpill-linpro.com/
Magnus Hagander wrote: > Another thought: if we were to make ourselves support multiple SSL > libraries (that has been suggested before - at that point, people wanted > GnuTLS), we could also add support for Windows SChannel, which I'm sure > some win32 people would certainly prefer - much easier to do SSL > deployments within an existing MS infrastructure... If we were to support multiple libraries, would they be selected at run time or compile time? If only compile time, how would it work for the Windows installer with the SChannel thingy --- would they have to distribute two separate packages, for OpenSSL and SChannel? -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
In message <4A07DB89.2080508@hagander.net>, Magnus Hagander writes:
Is NSS available on all the platforms that we are (and that has OpenSSL today)?
NSS stopped publishing their supported platform list for NSS for some
strange reasons (older version have it). But I'd probably assume that
the list includes AIX, Tru64, HPUX, Linux, Windows, Solaris, Mac OSX.
I specifically don't see signs that they attempt to support the *BSD
platforms, but the *BSD people have working ports.
Generally from almost dozens of minutes of googling, I'd have to say
that in terms of portability and declared actual ports:
OpenSSL >> PostgreSQL >> Mozilla NSS >> GnuTLS
GnuTLS doesn't seem to be as mature as either OpenSSL and Mozilla NSS,
at least in my current hot-button issue of client certificate
validation.
Good luck with that Windows SChannel thing...I didn't find any
opensource program which uses it.
-Seth Robertson in-pgsql-hackers@baka.org
In message <20090511144317.GC8689@alvh.no-ip.org>, Alvaro Herrera writes:
Magnus Hagander wrote: > Another thought: if we were to make ourselves support multiple SSL > libraries (that
hasbeen suggested before - at that point, people wanted > GnuTLS), we could also add support for Windows SChannel,
whichI'm sure > some win32 people would certainly prefer - much easier to do SSL > deployments within an existing
MSinfrastructure... If we were to support multiple libraries, would they be selected at run time or compile
time? If only compile time, how would it work for the Windows installer with the SChannel thingy --- would they have
to distribute two separate packages, for OpenSSL and SChannel?
While I have successfully performed runtime conditional dynamic
loading inside programs (each shared library with its own list of
dependent libraries) on one platform with one selected dynamic loading
API, I cannot say I recommend it. This would aid neither portability,
debug-ability, or performance (though compared to the overhead of SSL,
the jump table is kinda irrelevant).
-Seth Robertson in-pgsql-hackers@baka.org
On Monday 11 May 2009 11:02:17 Magnus Hagander wrote: > Another thought: if we were to make ourselves support multiple SSL > libraries (that has been suggested before - at that point, people wanted > GnuTLS), we could also add support for Windows SChannel, which I'm sure > some win32 people would certainly prefer - much easier to do SSL > deployments within an existing MS infrastructure... > > But no, that certainly wouldn't *reduce* the amount of code... We'll call that Plan C: Making PostgreSQL the first piece of software in the world to support four different crypto libraries. ;-)
Peter Eisentraut wrote: > On Monday 11 May 2009 11:02:17 Magnus Hagander wrote: >> Another thought: if we were to make ourselves support multiple SSL >> libraries (that has been suggested before - at that point, people wanted >> GnuTLS), we could also add support for Windows SChannel, which I'm sure >> some win32 people would certainly prefer - much easier to do SSL >> deployments within an existing MS infrastructure... >> >> But no, that certainly wouldn't *reduce* the amount of code... > > We'll call that Plan C: Making PostgreSQL the first piece of software in the > world to support four different crypto libraries. ;-) I could've sworn curl did :-) But it turns out they do SChannel *through* OpenSSL. :-) So we can probably live with that ;) //Magnus
Wow, blast from the past.
First, I no longer have this use case.
Second, the PGSSLCERT, PGSSLKEY, and other relevant environmental
variables should make most clients able to be "fairly easily" switched
from one server to another.
Third, the only real use case where this feature would be critical is
a client which needs to have connections to two different PostgreSQL
servers at the same time. Those applications are likely fairly rare
and doing custom programming to support different filenames would
likely be warranted.
However, I still think that this feature would make it easier for
users often connect to multiple servers from different
administrative/security domains.
Given the lack of "me too" or "+1" posts over the past 16 years, I
suspect there may be features with higher user benefit. I would not
cry if it gets removed.
Thanks,
-Seth Robertson
From: "Robin Haberkorn"
Date: Mon, 31 Mar 2025 13:57:55 +0300
To: "Seth Robertson"
Subject: Re: [PATCH] Automatic client certificate selection support for libpq v1
Hello everybody!
I was investigating the item "Allow automatic selection of SSL client
certificates from a certificate store" from the Todo list [1]. If I
understand Seth Robertson in his initial mail correctly, he wanted libpq to
select client certificates automatically based on the host while storing
several client certificates and keys in single crt and key files.
Client certs are currently loaded with SSL_CTX_use_certificate_chain_file()
in src/interfaces/libpq/fe-secure-openssl.c.
While it is theoretically possible to implement host-specific logic using
SSL_CTX_set_client_cert_cb(), I don't think you could store all
the possible certificates in a single file as you might actually already
need several certificates for a single host in the form of a certificate
chain. As was pointed out in the thread back then, you would have to
implement something like a certificate wallet/store from scratch.
At the most, Seth's initial patch could be improved by looking
up per-host client certificate/key files based on the host-reported
server name (SSL_get_servername()), which should be more reliable when
working with host aliases.
But then on the other hand, there are sslcert/sslkey connection parameters
since 8.4, which Seth was apparently aware of. As far as I understand,
he just wanted this patch for 8.3 as well and he didn't want to update
all of his existing programs. Considering that his initial mail was
written 16 years ago, I don't think this is a valid argument anymore.
It should be possible to adapt programs easily, e.g. by accepting
"postgresql://" URIs instead of domains and manually choosing appropriate
certificate/key filenames.
In my opinion there is little than can and should be done in Postgres
at this point. Or does anybody think, that a server-name-based certificate
file selection feature should still be implemented?
If yes, I would be happy to take care of it.
If not, I would suggest to remove this item from the agenda/wiki.
Best regards,
Robin Haberkorn
[1]: http://archives.postgresql.org/pgsql-hackers/2009-05/msg00406.php
--
Robin Haberkorn
Senior Software Engineer
Tel.: +49 157 85228715
E-Mail: haberkorn@b1-systems.de
B1 Systems GmbH
Osterfeldstraße 7 / 85088 Vohburg / https://www.b1-systems.de
GF: Ralph Dehner / Unternehmenssitz: Vohburg / AG: Ingolstadt, HRB 3537
On Mon, Mar 31, 2025 at 9:01 AM Seth Robertson <in-pgsql-hackers@baka.org> wrote: > Third, the only real use case where this feature would be critical is > a client which needs to have connections to two different PostgreSQL > servers at the same time. Those applications are likely fairly rare > and doing custom programming to support different filenames would > likely be warranted. Can this be handled well enough with a service file? > Given the lack of "me too" or "+1" posts over the past 16 years, I > suspect there may be features with higher user benefit. I would not > cry if it gets removed. Yeah, at least not without a solid use case. (If anyone does feel motivated to pick it up, be aware of the server-side SNI work [1]. It'd be nice if the two halves were complementary -- or at minimum, not clashing with each other.) Thanks! --Jacob [1] https://postgr.es/m/1C81CD0D-407E-44F9-833A-DD0331C202E5%40yesql.se
Yes, at first glance the service file looks like it should work and is
a much more elegant and generic method than my proposed hack. I can't
trivially tell if the ssl configuration aspect of it was available in
8.3/8.4, but that isn't overly relevant since it is certainly
available now.
Thanks!
-Seth Robertson
From: Jacob Champion
Date: Mon, 31 Mar 2025 09:52:49 -0700
To: Seth Robertson,
To: Robin Haberkorn
Subject: Re: [PATCH] Automatic client certificate selection support for libpq v1
On Mon, Mar 31, 2025 at 9:01 AM Seth Robertson
<in-pgsql-hackers@baka.org> wrote:
> Third, the only real use case where this feature would be critical is
> a client which needs to have connections to two different PostgreSQL
> servers at the same time. Those applications are likely fairly rare
> and doing custom programming to support different filenames would
> likely be warranted.
Can this be handled well enough with a service file?
> Given the lack of "me too" or "+1" posts over the past 16 years, I
> suspect there may be features with higher user benefit. I would not
> cry if it gets removed.
Yeah, at least not without a solid use case. (If anyone does feel
motivated to pick it up, be aware of the server-side SNI work [1].
It'd be nice if the two halves were complementary -- or at minimum,
not clashing with each other.)
Thanks!
--Jacob
[1] https://postgr.es/m/1C81CD0D-407E-44F9-833A-DD0331C202E5%40yesql.se