Re: How to enable SSL on client

On Fri, Dec 5, 2014 at 3:07 PM, Rajagopalan, Jayashree <
Jayashree(dot)Rajagopalan(at)emc(dot)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(dot)adalkonda(at)shreeyansh(dot)com

www.shreeyansh.com