Setting PostgreSQL to SSL Enabled

Follow the steps below to set your PostgreSQL database to SSL enabled.

At the very begining, we have to determine the operating system that we are using, I don't know how to do in Linux or Unix because the OS I'm using is Windows.


1. Download Win32 OpenSSL program from Shining Light Productions

2. Install the exe file by clicking next, next ... next, done ( very basic way to install a program)

3. Open command prompt (cmd.exe) and go to your Win32 OpenSSL installed folder (in default, C:\OpenSSL\bin)

4. type "openssl req -new -text -out server.req" and press enter

Loading 'screen' into random state - done
Generating a 1024 bit RSA private key
.................................................................................++++++.............................++++++
writing new private key to 'privkey.pem'
Enter PEM pass phrase: "ENTER PASSWORD" (example, "changeit")
Verifying - Enter PEM pass phrase:"REENTER PASSWORD" (example, "changeit")
-----You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [AU]: Just Press Enter (leave it empty)
State or Province Name (full name) [Some-State]:(leave it empty)
Locality Name (eg, city) []:(leave it empty)
Organization Name (eg, company) [Internet Widgits Pty Ltd]:(leave it empty)
Organizational Unit Name (eg, section) []:(leave it empty)
Common Name (eg, YOUR name) []: your host name (lanf-blog.blogspot.com)
Email Address []:Just Press Enter (leave it empty)

Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:(leave it empty)
An optional company name []:(leave it empty)

5. now that if you go back to openSSL instaled folder c:\openSSL\bin\ you will see "privkey.pem" and "server.req"

6. go back to command prompt and enter "openssl rsa -in privkey.pem -out server.key"
Enter pass phrase for privkey.pem: enter the password you entered from previous step (example, "changeit")
writing RSA key

7. now that you'll see "server.key" in your bin folder

8. Delete privkey.pem. (We delete this is because it's not needed anymore, if we leave it there, we then have to type our password everytime we restart our computer <-- not tested, I just deleted) 9. go back to command prompt and enter "openssl req -x509 -in server.req -text -key server.key -out server.crt" to create server.crt certificate

10. copy server.crt and server.key to your PostgreSQL data folder (int default, "C:\Program Files\PostgreSQL\8.2\data"

11. at the same folder mentioned from previous step, search for "#ssl" and change "#ssl = off" to "ssl = on" (delete "#" and change "off" to "on")

12. stop and start PostgreSQL server

13. done


to test if it's properly modified, we then open pgAdmin III (exists when installed PostgreSQL server in windows environment) and check the database property "Encryption" to see if the value is "SSL encrypted".



Some references
Mandarin SSL setting tutorial
Win32 OpenSSL Official Site
The PostgreSQL JDBC Interface reference
Other SSl setting tutorial (not used but kind of provides other details)