Enabling SSL in Postgres usually requires reading around 3 different blog posts (I’ve probably written a couple, myself). So, to make things easier for myself in the future, here are the very specific instructions on how to do it for the Postgres.app in Mac OS X.

1. Install the Postgres.app (duh) http://postgresapp.com and make sure to follow all the instructions for properly setting up your path.
2. As can be seen from their docs (http://postgresapp.com/documentation#toc_21), the data directory is by default in ~/Library/Application\ Support/Postgres/var. This is a lie. It’s actually Postgres93/var and it could change in the future.  Open a Terminal (I recommend iTerm) and navigate to that directory.

   cd ~/Library/Application\ Support/Postgres93/var

3. Follow the basic instructions on how to set up a self-signed cert. By doing this process in the data directory, the files are already in the right place. The caveat is getting the permissions right so Postgres doesn’t complain. (source: http://www.postgresql.org/docs/9.1/static/ssl-tcp.html)

   openssl req -new -text -out server.req
   openssl rsa -in privkey.pem -out server.key
   rm privkey.pem
   openssl req -x509 -in server.req -text -key server.key -out server.crt
   chmod og-rwx server.key

4. Turn on SSL in the Postgres config by uncommenting the line #ssl = on using your favorite editor

   vim postgresql.conf
   # change this:
   # ssl = on
   # to this:
   ssl = on

5. Restart the Postgres.app. This will restart the Postgresql server automatically
6. Check that the server came up

   ps aux | grep -i postgres
   # this line means the server is up properly. If you don't see it, try a couple more executions of the ps aux | grep command just to make sure
   awesomeuser  85581   0.0  0.0  2617708   2396   ??  S     5:55PM   0:01.75 /Applications/Postgres93.app/Contents/MacOS/bin/postgres -D /Users/awesomeuser/Library/Application Support/Postgres93/var -p5432

   If you still don’t see that line after a couple executions of the ps aux command, then you likely didn’t get the certificate correct. Make sure you followed those instructions exactly.

7. Check connection with SSL

   psql "sslmode=require"

   If a psql console opens, you’re all set.

From your friends at Workherder.com