SSL setup in PostgreSQL

PostgreSQL has native support for using SSL connections to encrypt client/server communications for increased security. This requires that OpenSSL is installed on both client and server systems and that support in PostgreSQL is enabled at build time.

With SSL support compiled in, the PostgreSQL server can be started with SSL enabled by setting the parameter ssl to on in postgresql.conf. The server will listen for both normal and SSLconnections on the same TCP port, and will negotiate with any connecting client on whether to use SSL. By default, this is at the client's option;

PostgreSQL reads the system-wide OpenSSL configuration file. By default, this file is named openssl.cnf and is located in the directory reported by openssl version -d. This default can be overridden by setting environment variable OPENSSL_CONF to the name of the desired configuration file.

OpenSSL supports a wide range of ciphers and authentication algorithms, of varying strength. While a list of ciphers can be specified in the OpenSSL configuration file, you can specify ciphers specifically for use by the database server by modifying ssl_ciphers in postgresql.conf.

Server 1

Step 1

[root@localhost Desktop]# systemctl stop firewalld.service
[root@localhost Desktop]# su - postgres
-bash-4.2$ mkdir -p 9.6/data
-bash-4.2$ cd ..
-bash-4.2$ pwd
/var/lib/pgsql
-bash-4.2$ initdb -D 9.6/data/

Step 2

-bash-4.2$ logout
[root@localhost Desktop]# mkdir /var/lib/CA
[root@localhost Desktop]# cd /var/lib/CA/
[root@localhost CA]# openssl genrsa -out rootCA.key 2048

[root@localhost CA]# openssl req -x509 -new -key rootCA.key -days 1000 -out rootCA.crt
[root@localhost CA]# mkdir server
[root@localhost CA]# cd server/
 
[root@localhost server]# openssl genrsa -out server.key 2048
[root@localhost server]# openssl req -new -key server.key -out server.csr

Note: Common Name (eg, your name or your server's hostname) []:localhost.lo

[root@localhost server]# openssl x509 -req -in server.csr -CA ../rootCA.crt -CAkey ../rootCA.key -CAcreateserial -out server.crt -days 5000[root@localhost server]# cd ..
[root@localhost CA]# mkdir client
[root@localhost CA]# cd client/
[root@localhost client]# openssl genrsa -out client.key 2048
[root@localhost client]# openssl req -new -key client.key -out client.csr

Note :Common Name (eg, your name or your server's hostname) []:edb_username for another server

[root@localhost client]# openssl x509 -req -in client.csr -CA ../rootCA.crt -CAkey ../rootCA.key -CAcreateserial -out client.crt -days 5000
Signature ok

[root@localhost client]# su - postgres 
Last login: Mon May  7 21:18:04 IST 2018 on pts/0
-bash-4.2$ cd 9.6/data/
-bash-4.2$ cp /var/lib/CA/rootCA.crt .
-bash-4.2$ cp /var/lib/CA/server/server.crt .
-bash-4.2$ cp /var/lib/CA/server/server.key .
-bash-4.2$ chmod 600 server.key 

-bash-4.2$ vi postgresql.conf 
 
ssl = on                                # (change requires restart)
ssl_ciphers = 'HIGH:MEDIUM:+3DES:!aNULL' # allowed SSL ciphers
                                        # (change requires restart)
ssl_prefer_server_ciphers = on          # (change requires restart)
ssl_ecdh_curve = 'prime256v1'           # (change requires restart)
ssl_cert_file = 'server.crt'            # (change requires restart)
ssl_key_file = 'server.key'             # (change requires restart)
ssl_ca_file = 'rootCA.crt'

-bash-4.2$ vi pg_hba.conf 
#secure client
host     testdb          anil            192.168.184.170/32      trust 

-bash-4.2$ pg_ctl -D ./ start
-bash-4.2$ 

-bash-4.2$ psql -U postgres
psql.bin (9.6.4)
Type "help" for help.
postgres=# 
postgres=# create role edb with login;
CREATE ROLE
postgres=# create database anildb with owner edb;
CREATE DATABASE
postgres=# \q 

Server 2 :

[root@localhost Desktop]# systemctl stop firewalld.service
[root@localhost Desktop]# su - edb
[edb@localhost Desktop]$ mkdir ~/.postgres
[edb@localhost Desktop]$ scp root@192.168.184.159:/var/lib/CA/rootCA.crt ~/.postgres
[edb@localhost Desktop]$ scp root@192.168.184.159:/var/lib/CA/rootCA.crt ~/.postgres/root.crt
[edb@localhost Desktop]$ scp root@192.168.184.159:/var/lib/CA/client/client.crt ~/.postgres/postgresql.crt
[edb@localhost Desktop]$ scp root@192.168.184.159:/var/lib/CA/client/client.key ~/.postgres/postgresql.key
[edb@localhost Desktop]$ chmod 600 ~/.postgres/postgresql.key
[edb@localhost Desktop]$ psql -h 192.168.184.159 -U edb anildb
psql (9.2.7, server 9.6.4)
WARNING: psql version 9.2, server version 9.6.
         Some psql features might not work.
SSL connection (cipher: ECDHE-RSA-AES256-SHA, bits: 256)
Type "help" for help.
anildb=>

Written BY :- Raghavan Rao