Sunday, July 17, 2011

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:
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.


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:..
server started


  1. Thanks Vibhor its really helpful.

  2. Thank you very much Vibhor. it really helpful forme.