PostgreSQL - Connect using SSL

Used software: PostgreSQL 9.2.5

1. Install Postgres

Use the --with-openssl option with ./configure while installing postgres

$ CFLAGS="-g -O0" ./configure --enable-debug --enable-cassert --enable-depend --prefix=<install_path> --with-openssl

$ make

$ make install

2. Create a Data Directory

$ cd <postgres_install_path>/bin

$ ./initdb -d <cluster_path>

3. Modify the postgresql.conf file

ssl  = on

4. Create self signed certificate

[Ref: http://www.postgresql.org/docs/current/static/ssl-tcp.html#SSL-CERTIFICATE-CREATION]

$ cd <cluster_path>

$ openssl req -new -text -out server.req

Enter a pass phrase (at least 4 char long).
Fill in other details if required.
Challenge password can be left blank.

$ openssl rsa -in privkey.pem -out server.key

Enter the same pass phrase entered before.

$ openssl req -x509 -in server.req -text -key server.key -out server.crt

$ chmod og-rwx server.key

4. Start postgres

$ cd <postgres_install_path>/bin

$ ./pg_ctl -D <cluster_path> start

5. Connect using ssl

$ ./psql "sslmode=require host=localhost dbname=postgres"

psql (9.2.5)

SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)

Type "help" for help.

postgres=#