PostgreSQL SSL code updates

I am currently working on several updates to the POstgreSQL SSL code, to make it more secure and add some functionality. I'd be interested to hear from people who are either using this today, or are interested in using the new functionality - there is still room to make further adjustments to the code before the release.

Certificate validation in libpq

This patch was applied today. The idea is to be able to control how the certificate validation is done in libpq. Previously, libpq would verify the server certificate if a root certificate file was found, and otherwise never do it. This made the system very fragile. And it would never attempt to verify that the certificate actually matched the server.

With this patch, there is now a new connection parameter sslverify, that controls this behavior. It's all controlled by this parameter, and never by just checking the existance of a file. It can have the following values:
cn : Default. Verify that the certificate chains to a trusted root, and that the server name matches.
cert : Verify that the certificate chains to a trusted root, but ignore the name.
none : Disable certificate verification completely.

The version that is committed does not support subject alternate names or wildcard certificates. It's something I am hoping to have the time to add before the release. Feel free to send me a patch ;-)

Requiring a client certificate

This patch is currently pending review in this commitfest. The idea here is to move from having the requesting of client certificate to be controlled by if the root certificate file exists or not, to it being an explicit configuration variable. This makes it much more secure against "admin mistakes" - explicit configuration is always better when it comes to security.

This patch builds on the changes to the pg_hba.conf file, and therefor just adds a connection option to the hostssl rows (obviously you can only require client certificates on SSL connections). Set it to 1 to require client certificates. Of course, it also needs the root certificate file to be present.

Having this in pg_hba.conf also makes it possible to configure this value differently depending on which addresses your client are connecting from, if required.

Client certificate authentication

This patch is pending some final cleanups before I post it. The idea here is, obviously, to be able to use your SSL client certificate to perform the actual authentication, thus doing away with the need to have a password as well. Given that our client certificate code already supports for example smartcards (through OpenSSL), this can be a high security option for remote logins. I'm sure there are other usecases as well - it's a feature that have been asked for more than once.

I plan to make this code just use the cn attribute of the certificate to authenticate. This can then be passed through a pg_ident.conf map to map to "real" username, in case the syntax is not identical. In a lot of cases it can probably be very useful to combine this with regexp entries in the ident maps which is another patch that's in the queue for this commitfest.

One thing I'm unsure about here is - will it be enough to be able to use the cn attribute for authentication, or will it be required to use other attributes as well? How do the enterprise PKI solutions that you'd use this together with work?


Magnus, Since you are working on the ssl, how about a libpq.dll for windows with minimal dependnecies? i.e. just openssl.

For many many client applications all the other dependencies are not needed and it's a pain to deploy all of them. It would be nice to have a libpq.dll that did not require the MS C runtime libraries as well.

Posted on Nov 17, 2008 at 17:19 by Tony Caduto.

It would make sense to have that, but that's really completely unrelated to the SSL work I'm doing. That's more a packaging issue. If you're up for getting a patch into the main build system for that, that'd be great :)

Posted on Nov 18, 2008 at 14:18 by Magnus.

I have an enterprise Apache/Mod_perl/PostgreSQL application in development for aircraft dispatch. The connection from the browser to mod_perl is 100%25 encripted throughout the application. Each authorized user is issued a unique credential so I can use the CN to authenticate to the mod_perl routines. Apache also gives me access to the credential itself so it would be ideal to pass that through to the database for authentication as well. In this way, there would be a seamless one password solution for the entire application, dealing with encription and authentication at once for the web server and the database.

So I hope this is going well, and should the need be, I'd throw some code at it. Contact welcome.


Posted on Jun 22, 2009 at 02:50 by Phillip Rulon.


I speak at and organize conferences around Open Source in general and PostgreSQL in particular.


Mar 2-5, 2017
Pasadena, California, USA
Open Source Infrastructure @ SCALE
Mar 2, 2017
Pasadena, California, USA
Confoo Montreal 2017
Mar 8-10, 2017
Montreal, Canada
Nordic PGDay 2017
Mar 21, 2017
Stockholm, Sweden 2017
Mar 23, 2017
Paris, France
PGCon 2017
May 23-26, 2017
Ottawa, Canada


FOSDEM + PGDay 2017
Feb 2-4, 2017
Brussels, Belgium
PGConf.Asia 2016
Dec 2-3, 2016
Tokyo, Japan
Berlin PUG
Nov 17, 2016
Berlin, Germany
PGConf.EU 2016
Nov 1-4, 2016
Tallinn, Estonia
Stockholm PUG 2016/5
Oct 25, 2016
Stockholm, Sweden
More past conferences