SSL configuration in PostgreSQL for PgAdmin

SSL configuration in PostgreSQL for PgAdmin

This document describes how to set up SSL to enable ssl connections from PgAdmin on some client machine to postgresql on a server machine. In my example, I am using Centos 7 for PostgreSql-9.5 server and Windows for PgAdmin.The assumption is that Postgresql (compiled with ssl support) and openssl are already installed on the server (Linux Centos7). PgAdmin is already installed on the client (either Windows or Linux).

On the server, three certificates are required in the data directory.

root.crt (trusted root certificate)
server.crt (server certificate)
server.key (private key) 

On CentOS7 default Data directory is /var/lib/pgsql/9.5/data/:

Now, connect your system with user root.

Step 1

Su –

Go to data directory

[root@localhost ~]# cd /var/lib/pgsql/9.5/data/

Out put

[root@ localhost data]#

Step 2 :

Generate a private key (you must provide a passphrase).

[root@ localhost data]#openssl genrsa -des3 -out server.key 1024
Output of above command is :
Generating RSA private key, 1024 bit long modulus

......................++++++

..........++++++

e is 65537 (0x10001)

Enter pass phrase for server.key: <write any text>     [Here, I wrote prakash]

Verifying - Enter pass phrase for server.key:prakash [Here, I wrote prakash]

[root@ localhost data]#

You can see one file has created in data directory with name server.key

Step 3:

Remove the passphrase.

[root@ localhost data]#openssl rsa -in server.key -out server.key

Output

Enter pass phrase for server.key:prakash                  [Here, You’ll write prakash]

Output

writing RSA key

[root@ localhost data]#

Step 4:

Set appropriate permission and owner on the private key file.

[root@ localhost data]#chmod 400 server.key
[root@ localhost data]#chown postgres.postgres server.key

[root@ localhost data]#

Step 5:

Create the server certificate.
-subj is a shortcut to avoid prompting for the info.
-x509 produces a self signed certificate rather than a certificate request.

[root@ localhost data]#

openssl req -new -key server.key -days 3650 -out server.crt -x509 -subj '/C=CA/ST=India pune/L=Comox/O=The India.ca/CN=192.168.x.x/emailAddress=xyz1@epps.com'

[Hint 192.168.x.x -machine ip address, Change your IP address in place of 192.168.x.x]

Output

[root@ localhost data]#

You can see one file has created in data directory with name server.crt

Now, We have two files server.key and server.crt.

Step 6:

Since you are self-signing, you can use the server certificate as the trusted root certificate.

[root@ localhost data]#cp server.crt root.crt

Step 7:  

You need to edit pg_hba.conf file.  Which is located in Data Directory.

For example:

Comment all lines and Add below lines in pg_hba.conf file

[root@ localhost data]# vi pg_hba.conf

local                             all        all                                     md5

hostssl                         all        postgres   0.0.0.0/0          md5         clientcert=1

Step 8:  

You need to edit postgresql.conf to actually activate ssl:

[root@ localhost data]# vi postgresql.conf

listen_addresses = '*'

ssl = on

ssl_ca_file = 'root.crt' 

[Hint:If we configure hostssl all    postgres    0.0.0.0/0      md5      clientcert=1]

Save and exit

Step 9: 

Postgresql server must be restarted.

[root@ localhost data]# systemctl restart postgresql-9.5.service

And reboot the system

[root@ localhost data]# reboot

Step 10 :

Copy these three files, you have created on the server in /var/lib/pgsql/9.5/data/ directory to the client machine.

Eg. You can copy these file in D:\SSL directory or anywhere you want through Putty or any tools. Like below:

Suppose your pscp.exe is on D:\ Putty directory. Then on Window you open cmd and go to location of Putty.

C:\>cd d:\putty

D:\Putty> pscp.exe root@192.168.x.x:/var/lib/pgsql/9.5/data/server.crt D:\SSL\

                 root@ 192.168.x.x's password:<root password>

D:\Putty> pscp.exe root@192.168.x.x:/var/lib/pgsql/9.5/data/root.crt D:\SSL\

                 root@ 192.168.x.x's password:<root password>

D:\Putty> pscp.exe root@192.168.x.x:/var/lib/pgsql/9.5/data/server.key D:\SSL\

                 root@ 192.168.x.x's password:<root password>

Step 11 : 
Open PgAdmin
Go to File menu Click on Add Server, this displays the New Server Registration dialog box as shown below.

Input As per your configuration like below:

                    

Click on SSL menu and select your files as given below:

       

Now, click on OK.