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.
- Install the Postgres.app (duh) http://postgresapp.com and make sure to follow all the instructions for properly setting up your path.
- 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
- 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
- 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
- Restart the Postgres.app. This will restart the Postgresql server automatically
- 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.
- Check connection with SSL
psql "sslmode=require"
If a psql console opens, you’re all set.
From your friends at Workherder.com