Re: How to enable SSL on client - Mailing list pgsql-admin

From Harshad Adalkonda
Subject Re: How to enable SSL on client
Date
Msg-id CANnQH_inB5jaj7gOOtVaLeQ4pCuG-JVpE3VLA=BPK3g2CuNn=Q@mail.gmail.com
Whole thread Raw
In response to How to enable SSL on client  ("Rajagopalan, Jayashree" <Jayashree.Rajagopalan@emc.com>)
List pgsql-admin
On Fri, Dec 5, 2014 at 3:07 PM, Rajagopalan, Jayashree <Jayashree.Rajagopalan@emc.com> 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@shreeyansh.com

www.shreeyansh.com


pgsql-admin by date:

Previous
From: Matthieu Lejeune
Date:
Subject: Re: Problem pg_upgradecluster from 9.1 to 9.3
Next
From: Harshad Adalkonda
Date:
Subject: Re: How to enable SSL on client