How to enable SSL in PostgreSQL/PPAS

This has been asked many times, so I thought to write steps for enabling ssl:

Following are steps, which can be use to enable ssl in postgreSQL:

1. Generate a passphrase protected certificate using following command:

openssl req -new -text -out cert.req

Snapshot is given below:

Generating a 1024 bit RSA private key
....................++++++
...................................................++++++
writing new private key to 'privkey.pem'
Enter PEM pass phrase:
Verifying - Enter PEM pass phrase:
-----
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]:
State or Province Name (full name) [Some-State]:
Locality Name (eg, city) []:
Organization Name (eg, company) [Internet Widgits Pty Ltd]:
Organizational Unit Name (eg, section) []:
Common Name (eg, YOUR name) []:
Email Address []:
Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:Singh
An optional company name []:

Above command will create following two files.

ls -ltr 
-rw-r--r--   1 vibhor  staff     963 Jul 16 04:12 privkey.pem
-rw-r--r--   1 vibhor  staff    2096 Jul 16 04:12 cert.req

2. Now, Remove the passphrase, which is necessary to start the postmaster automatically using following command:

openssl rsa -in privkey.pem -out cert.pem

Snapshot is given below:

Enter pass phrase for privkey.pem:
writing RSA key

Above command will create cert.pem file

3. Convert the certificate into a self-signed certificate, using following command:

openssl req -x509 -in cert.req -text -key cert.pem -out cert.cert 

4. Now, copy the files in data directory of postgreSQL:

cp cert.pem $PGDATA/server.key
cp cert.cert $PGDATA/server.crt

5. Change the permission as given below:

chmod 600 $PGDATA/server.key
chmod 600 $PGDATA/server.crt

6. Change the following parameter in $PGDATA/postgresql.conf file:

ssl=on

7. Now start the server.

After starting the PG instance, you can verify through postgreSQL logfile or connecting to database using psql as given below

edbs-MacBook-Pro:pg_log postgres$ psql -h localhost
psql (9.0.3)
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
Type "help" for help.

postgres=# 

Above steps are for password free self-signed certificate. However, sometimes people also ask how to use passphare certificate. Following are steps to use passphase certificate.

1. Create passphrase protected key as given below:

openssl rsa -des3 -out cert2.pem -in privkey.pem 
Enter pass phrase for privkey.pem:
writing RSA key
Enter PEM pass phrase:
Verifying - Enter PEM pass phrase:

2. Now, create passphrase protected certificate as given below:

openssl req -x509 -in cert.req -text -key cert2.pem -out cert.cert
Enter pass phrase for cert2.pem:

3. Now, copy the certificates in PostgreSQL/PostgresPlus Data Directory

cp cert.cert $PGDATA/server.crt
cp cert2.pem $PGDATA/data/server.key

4. Change the permission using following command:

chmod 600 $PGDATA/server.key
chmod 600 $PGDATA/server.crt

5. Now, start the PG Instance using following:

pg_ctl -D $PGDATA start -w

Please note: If user forget to use -w option then user will get following error message:

server starting
edbs-MacBook-Pro:data postgres$ Enter PEM pass phrase:
2011-07-18 00:46:07 IST FATAL:  could not load private key file "server.key": problems getting password

which shows, pg_ctl doesn’t wait for user to enter the pass phrase.

Therefore -w would require to make pg_ctl command wait for passphrase:
Following is a snapshot:

pg_ctl start -w
waiting for server to start....Enter PEM pass phrase:..
 done
server started

Author: Database Technologies

Director, Solutions Architecture @ EnterpriseDB, Bachelor's Degree in Computer Science. Master's Degree in Computer Application Oracle Certified Professional, PostgreSQL Certified Professional, DB2 Certified, ITIL V3 Certified, MongoDB Certified DBA, MongoDB Certified DBA, PostgreSQL Certified Trainer, PostgreSQL Certified Trainer.

4 thoughts on “How to enable SSL in PostgreSQL/PPAS”

  1. Thanks a lot this really help me to solve an issue related to ssl connect that fail due to parameter ssh = off instead ssh=on.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s