Windows SSL for PostgreSQL

I had to do this for work, so I see no reason not to share it with the world at large.
Setting up SSL for PostgreSQL on Windows servers:

In order for this to work, you need the following installed on the machine with PostgreSQL:
1. PostgreSQL 8.3 (it was not tested with earlier versions, and as of today I couldn't get it to work on PostgreSQL 8.4)
2. Cygwin, with the following extra features checked at install time: openssl, Gnu Make (possibly labeled ‘make’ in the Cygwin installer), and a Cygwin text editor. "joe" and "nano" are easy for Unix/Cygwin newbies, though "vim" and "emacs" are available for veterans.


Certificate Creation (on the PostgreSQL server):
(Every command that should be executed at a command prompt or Cygwin prompt will be
written with # in front)
1. Open a Cygwin command prompt (henceforth called ‘Cygwin shell’) and make a directory for creating the certificates, and move in to that directory
#mkdir ssl
#cd ssl
2. Copy the files ‘Makefile’ and ‘openssl.cnf’ from this page: http://sial.org/howto/openssl/ca/
Then edit the openssl.conf with
#nano openssl.cnf
OR
#joe openssl.cnf
OR
whatever other editor you want
NOTE: You can modify the file in any text editor you want PROVIDED it saves the results with Unix line endings ONLY. You can’t use, for example, Notepad or Wordpad because it will insert the “Carriage Return” invisible character at the end of each line, and then many Cygwin tools will crash trying to read the file.
4. In the text editor you launched, scroll down the file to find the section with the heading
“[ root_ca_distinguished_name ]”
And set the values in it to whatever you think is appropriate. You don’t need to modify anything else in the file, though you may wish to change default_bits to 2048. When you’re finished, save the file and exit.
5. In the Cygwin shell in the ssl folder we will now create our public and private certificate. Run:
make init
6. The command above should spit out some quick information about what it’s doing. When that’s finished, our public certificate is in PEM format in ca-cert.pem, and our private key to the certificate will be in the folder named ‘private’ with the name ca-key.pem. We want to verify the output. In the Cygwin shell run
#openssl x509 -text -in ca-cert.pem
The bottom of the output should be a bunch of funky characters, but near the top it should have a section listing all of the data you entered in the [ root_ca_distinguished_name ] section of the openssl.cnf file.
If there is a problem and the wrong data is included, exit Cygwin, open Windows Explorer to the ssl directory you created in step 1, delete the directory, and then start over at Step 1.
7. Now we must transmit the public certificate from PEM to DER format (No, I have no clue what DER means either, but that’s the format that PostgreSQL wants). Run the following command in the Cygwin shell:
#openssl x509 -outform der -in ca-cert.pem -out root.crt
Now there is a file root.crt with the root certificate we created. You can do
#ls root.crt
To verify that the file was created.
8. Open a separate Windows Command Prompt (cmd.exe) and run the command
#ping –a localhost
The result of the ping should include the name of the server you are on. For example, on this workstation the result is oscar.network3.somecompany.com
Note that value.
NOTE: If the server you are using always has the same IP address, you may instead use the IP address. Note, however, that if you change the server IP address in the future without creating a new certificate, connections will stop working!
9. Now we are going to generate our PostgreSQL server SSL certificate. In the Cygwin shell, do:
#openssl req -newkey rsa:2048 -days 1000 -text -out server.req
You will be asked a series of questions about settings. Only three are important. For the keystore PEM pass phrase, use “changeit”. For the Common Name value, enter the host name result you got from the ping –a localhost command above. For the challenge password, just hit Enter to leave it blank.
Note: I’m not certain that the ‘-days 1000’ parameter above is necessary…
10. After the step above, the public and private key are created. However, we do not want PostgreSQL to prompt for the password to them each time it is started. So run the following two commands in the Cygwin shell:
#openssl rsa -in privkey.pem -out server.key
(You will be prompted to enter the “changeit” password.)
#rm privkey.pem
11. Now we want to digitally sign our server certificate using the root certificate we created earlier for 1000 days (or substitute whatever number you want). In Cygwin shell do:
#openssl req -x509 -in server.req -text -days 1000 -key private/ca-key.pem -out server.crt
12. The files are created, now the permissions must be set. To do this easily, we will create a special subdirectory and put the files there, and then change permissions and owners in that directory.
Note: The last line below assumes the PostgreSQL database is run under user account ‘postgres’. If it runs under a different account, substitute the name of that account instead.
#mkdir postgres_certs
#cp root.crt postgres_certs
#cp server.key postgres_certs
#cp server.crt postgres_certs
#cd postgres_certs
#chmod 600 *
#chown postgres *
13. Open Windows Explorer to C:\cygwin\home\(whatever user you are)\ssl\postgres_certs and copy the 3 files there to the PostgreSQL data directory, on my machine it's
F:\Program Files\PostgreSQL\8.3\data .


PostgreSQL configuration
14. In order for all of this to work, you must have done the steps to create a certificate on the server and put the certificate in the proper location.
15. On the server with PostgreSQL, a System environmental variable with the location of the openssl.cnf file must be created. This must not point to the openssl.cnf file used in Certificate Creation. The normal location of the Cygwin openssl.cnf value we want to use is
C:\cygwin\usr\ssl\openssl.cnf, so in the “System” control panel
“Advanced -> Environmental Variables” setting, create a new System Environmental Variable OPENSSL_CONF with the value C:\cygwin\usr\ssl\openssl.cnf
16. In the PostgreSQL configuration file postgresql.conf change the ssl settings to “ssl=on” and “ssl_ciphers= ALL “
17. In the PostgreSQL configuration file pg_hba.conf change all of the entries from “host” to “hostssl”
18. Re-start the PostgreSQL service. If it does not start, check the Windows Event Viewer for errors.
19. Open pgAdmin3 and create an SSL connection to the server. Try to connect, and verify it works.
20. In any other machine configured to connect to the server, in pgAdmin3 create an SSL connection and verify it works.
21. If you try the PostgreSQL command-line tool “psql” for a connection to the database, it should connect automatically and give a message that an SSL connection was established.

NOTE 1: All this gives you SSL with self-signed certificates. To connect with, for example, JDBC and PostgreSQL (which is what we use), you would need to have a fairly recent JDBC driver and make your connection URL;
jdbc:postgresql://hostname:5432/databasename?ssl=true&sslfactory=org.postgresql.ssl.SSLNonValidatingFactory
(If you're writing in XML files, the '&' must be written as '&')

NOTE 2: If you want to use certificates signed by a recognized Certificate Authority, you'll
do steps 9 and 10 above, submit the resulting server.req to a Certificate Authority for them to make the server.crt, and then use your server.key, their server.crt, and their root.crt and continue from step 12. At least, I think that's what you'll do. I haven't done it.