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

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

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s