Postgresql and client side SSL setup for Web Servers

Posted at 8:16:57 PM in SQL (4) | Read count: 4475

I'm using Posgtresql 9.1 on Ubuntu 13.x.x 64-bit. I actually don't remember if I installed OpenSSL on it or not, but those are the packages I used during this install. It is one of the more difficult tasks I've had to complete. A few nice things before I go into the details of installing SSL for the ODBC client on a Server 2003 IIS server for use with my web pages.

I noticed that the Postgresql 9.1 and OpenSSL already enabled SSL by default without authentication. That means that the data being transmitted between client and server was encrypted, but it was not verifying the certificate as being one that the server has approved. i.e. no key or crt was being provided by the client at all. And the client crt was not signed by the servers CA.

By default, there is a crt and key symlinked to the Posgtresql data directory (which on Ubuntu is located in \var\lib\posgtresql\9.1\main). In the data directory they are called server.crt and server.key. There was no root.crt which as I understand it contains the CA (certificate authority). Without root.crt, any client that requests an SSL connection will get one. As soon as you put a root.crt file in there, the requesting connection will be checked against that file, however, only verify-ca and verify-full will fail if it doesn't match.

The most helpful link was this one on The other link I read only explained how to make the server certificates (info on But nicely, also explains how to setup SSL on windows installations of Postgresql. I thought there would be a problem in creating the server certificates with the command line arguments of the and the command line arguments on howtoforage as I did the server ones and the clients ones using the different sites respectively. 

The only problem I ran into while setting up the server with self-signed certificates was the crt and key files already existing in the data directory. These are symlinked and provided by OpenSSL; linked from snakeoil.crt and snakeoil.key in the OpenSSL directory. Copying over the new files would not overwrite the symlinked files. I had to delete the symlinks first. I probably should have unlinked them then copied in the new certs, but I didn't have any problems.

After creating the client crts per the site, I didn't know where to place them on the client server. The default location to look is the <user>\appdata\postgresql directory, but the web user IUSR does not have a profile and there for no appdata directory. This site gave a little more info, but the individual was trying to get the windows cert manager to host the certificates so it gets a little confusing. After seeing the environment keys that needed to be set the documentation on the postgresql site made a little more sense.

Basically, you need to set these environment variables:

PGSSLKEY="C:\path\to\my.key" <-- modified from the web site as capi is not part of OpenSSL.

That's done by right clicking my computer and selecting properties. Then open the advanced tab and at the bottom open the Environmental Variables button and add the variables to the system set in the bottom window. I replaced the capi:My name with postgresql.key in the same directory as the other path info. I was hoping it would take path and the ODBC client would search for they that worked, as this will create problems with connections to different replicated servers. I am guessing that I should be able to use the same keys and certs but add the CA authority to the root.crt file for any other servers that might be handling connections from this client, but I haven't tested that yet.

The last part is to change pg_hba.conf so that it only accepts SSL connections. To do that, edit the pg_hba.conf file which is located in /etc/posgtresql/9.1/main on Ubuntu. Add the line:

type database user address method options <-- don't add this line, for information purposes only

hostssl  all  all  cert clientcert=1

I also specified the addresses these connections can connect from. Technically though, if you are using a certificate and not a password, then it should be safe to expose access to any client that has a certificate.

There is also a suggestion that hostssl be put before host lines, but I'm not sure of the reason for that. I tested this with pgadmin. You can set the certificates for the database connection properties under the SSL tab. As successful connection shows in the properties for the connection SSL encrypted.

Written by Leonard Rogers on Friday, July 26, 2013 | Comments (0)

    Email address is not published
    Remember Me

    CAPTCHA Reload
    Write the characters in the image above