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.
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.
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 ;-)
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.
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?
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 :)
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.
pjr
New comments can no longer be posted on this entry.
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.