Re: How to enable SSL on client

[Harshad Adalkonda <adalkondaharshad@xxxxxxxxx>]

On Fri, Dec 5, 2014 at 3:07 PM, Rajagopalan, Jayashree <Jayashree.Rajagopalan@xxxxxxx> wrote:

Hi all:

 

I’m on postgres 9.0.18. Want to enable SSL for the DB connections.

The binaries are build including the openssl.

 

I modified the postgresql.conf – for ssl=on.

Copied the server.crt in the  data folder.

Modified pg_hba.conf  - to add:

“hostssl  all         all        0.0.0.0/0       md5”

 

Getting the following errors in the logs when I start DB:

FATAL:  no pg_hba.conf entry for host "127.0.0.1", user "myuser", database "mydb", SSL off

 

Can someone share the right procedure to enable SSL on client?

I’ve created ~/.posgresql directory and added the client.crt and server.key.

 

Regards

Jayashree

Hi Jayashree,

If you have created root, server, client certificate then you need to copy following files in your data directory

rootCA.crt, server.crt, server.key

chmod 600 server.key

Your pg_hba.conf entry should be as following eg. 

hostssl  myuser        mydb          192.168.0.112/0        cert clientcert=1

In your postgresql.conf

ssl=on

ssl_cert_file = 'server.crt'

ssl_key_file = 'server.key'

ssl_ca_file = 'rootCA.crt'

Now create directory in users home directory.

Suppose you are using postgres from myuser in your linux then execute the following commands.

su - myuser

mkdir ~/.postgresql

copy rootCA.crt, postgresql.crt, postgresql.key in the .postgresql directory.

chmod 600 postgresql.key

now try to connect as following eg.

psql -h 192.168.0.112 -U myuser -d mydb -p 5432

The above solution will solve your problem.

Thanks & Regards,
Harshad Adalkonda
Database Administrator
harshad.adalkonda@xxxxxxxxxxxxxx

www.shreeyansh.com