PostgreSQL SSL configuration

PREREQUEST :
============
1. OpenSSL should be install on both client and server systems
2. Confirm whether openssl is installed or not .

nijam@2ndquadrant.in:~ # rpm -qa|grep -i openssl
openssl-1.0.2j-60.52.1.x86_64
libopenssl1_0_0-1.0.2j-60.52.1.x86_64
python-pyOpenSSL-16.0.0-4.17.1.noarch

3. Checking openSSL version  :

nijam@2ndquadrant.in:~ # openssl version
OpenSSL 1.0.2j-fips  26 Sep 2016

4. We can print the OpenSSL directory with version -d option like below.

nijam@2ndquadrant.in:~ # openssl version -d
OPENSSLDIR: "/etc/ssl"

nijam@2ndquadrant.in:~ # openssl version -a
OpenSSL 1.0.2j-fips  26 Sep 2016
built on: reproducible build, date unspecified
platform:
options:  bn(64,64) rc4(16x,int) des(idx,cisc,16,int) blowfish(idx)
compiler: cc -I. -I.. -I../include  -g
OPENSSLDIR: "/etc/ssl"

5. Collecting the postgres Server details:

nijam@2ndquadrant.in:/home/nijam> ps -ef|grep -i postgres|grep -v -edle
nijam   2693  2641  0 21:12 pts/1    00:00:00 grep --color=auto -i postgres
nijam  19150     1  0 Jun11 ?        00:10:54 /ins_path/10.5/bin/postgres -D /data/nijam
nijam  19151 19150  0 Jun11 ?        00:00:00 postgres: logger process
nijam  19153 19150  0 Jun11 ?        00:00:31 postgres: checkpointer process
nijam  19154 19150  0 Jun11 ?        00:07:15 postgres: writer process
nijam  19155 19150  0 Jun11 ?        00:03:48 postgres: wal writer process
nijam  19156 19150  0 Jun11 ?        00:01:19 postgres: autovacuum launcher process
nijam  19157 19150  0 Jun11 ?        00:00:04 postgres: archiver process   last was 000000010000000100000027
nijam  19158 19150  0 Jun11 ?        00:03:45 postgres: stats collector process
nijam  19159 19150  0 Jun11 ?        00:00:01 postgres: bgworker: logical replication launcher

CONFIGURATION :
===============
This describes how to set up ssl certificates to enable encrypted connections from PgAdmin on some client machine to postgresql on a server machine. The assumption is that postgresql (compiled with ssl support) and openssl are already installed and functional on the server (Linux). PgAdmin is already installed on the client (either Windows or Linux).

On the server, three certificates are required in the data directory. CentOS default is /var/lib/pgsql/data/: 

    1.root.crt (trusted root certificate) 
    2.server.crt (server certificate) 
    3.server.key (private key)

connect as root user and go to postgres data directory path then generate SSL certificate form there only .

Step 1.
=====

$ sudo su -
# cd /data/nijam

Step 2. Generate a private key (you must provide a passphrase):
=====

2ndquadrant.in:/data/nijam # openssl genrsa -des3 -passout pass:gsahdg -out server.key 1024
Generating RSA private key, 1024 bit long modulus
........++++++
...++++++
e is 65537 (0x10001)

Step 3. Remove the passphrase.
=======

2ndquadrant.in:/data/nijam # openssl rsa -in server.key -out server.key
Enter pass phrase for server.key:
writing RSA key

Note : Give below inputs if prompt 
gsahdg   (it is like password you can give whatever you want).

Step 4. Set appropriate permission and owner on the private key file.
=====

# chmod 400 server.key
# chown nijam.nijam server.key

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.

2ndquadrant.in:/data/nijam # openssl req  -nodes -new -x509 -days 3650  -keyout server.key -out server.crt
Generating a 2048 bit RSA private key
.....................................................+++
.......................................+++
writing new private key to 'server.key'
-----
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [AU]:
State or Province Name (full name) [Some-State]:
Locality Name (eg, city) []:
Organization Name (eg, company) [Internet Widgits Pty Ltd]:
Organizational Unit Name (eg, section) []:
Common Name (e.g. server FQDN or YOUR name) []:
Email Address []:

Step 6. Since we are self-signing, we use the server certificate as the trusted root certificate. 
=====
2ndquadrant.in:/data/nijam # cp server.crt root.crt

You'll need to edit pg_hba.conf. For example:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust
#hostssl    all             all            0.0.0.0/0             trust  clientcert=1
# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
# host    all             all             127.0.0.1/32            pam
# IPv6 local connections:
# host    all             all             ::1/128                 pam
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            pam
host    replication     all             ::1/128                 pam
hostssl         DB      DB_writer 53.126.142.211/32        trust clientcert=1
hostssl         DB      DB_reader 53.126.142.222/32      trust clientcert=1

======================================(OR)===============================================
if you don't want to use client certificate means you can ignore clientcert=1 , so connection will not authenticate by using client certifcate , its network encryption-decryption only. below is the sample configuration setup.

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust

# "local" is for Unix domain socket connections only
 local   all             all                                     trust
# IPv4 local connections:
# host    all             all             127.0.0.1/32            pam
# IPv6 local connections:
# host    all             all             ::1/128                 pam
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            pam
host    replication     all             ::1/128                 pam
hostssl         DB      DB_writer 53.126.142.211/32        password
hostssl         DB      DB_reader 53.126.142.222/32        password

Step 7. You need to edit postgresql.conf to actually activate ssl. 
====
ssl = on
#ssl_cert_file = 'server.crt'
#ssl_key_file = 'server.key'
ssl_ca_file = 'root.crt'

Step 8. Postgresql server must be restarted. 
=====

nijam@2ndquadrant.in:/data/nijam> pg_ctl restart $DATA
waiting for server to shut down.... done
server stopped
waiting for server to start....2019-07-03 21:45:14.785 CST [5594] LOG:  listening on IPv4 address "0.0.0.0", port 25084
2019-07-03 21:45:14.785 CST [5594] LOG:  listening on IPv6 address "::", port 25084
2019-07-03 21:45:14.792 CST [5594] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.25084"
2019-07-03 21:45:14.804 CST [5594] LOG:  listening on Unix socket "/tmp/.s.PGSQL.25084"
2019-07-03 21:45:14.815 CST [5594] LOG:  redirecting log output to logging collector process
2019-07-03 21:45:14.815 CST [5594] HINT:  Future log output will appear in directory "log".
 done
server started

POSTREQUEST SERVER SIDE :
==========================

Step 1 . Checking postgres SSL .
=====

nijam@2ndquadrant.in:/data/nijam> psql -h localhost
psql (10.5)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

nijam=#

Step 2 . Verifying certificate.
=====

nijam@2ndquadrant.in:/data/nijam>  openssl verify -CAfile root.crt server.crt
server.crt: OK

nijam=# show ssl_cert_file;
 ssl_cert_file
---------------
 server.crt
(1 row)

nijam=# show ssl_key_file;
 ssl_key_file
--------------
 server.key
(1 row)

nijam=# show ssl_ca_file;
 ssl_ca_file
-------------
 root.crt
(1 row)

nijam=# show ssl_crl_file;
 ssl_crl_file
--------------

(1 row)

Step 3. Checking SSL mode with Require .
=====

nijam@2ndquadrant.in:/data/nijam> psql "postgresql://localhost/?sslmode=require"
psql (10.5)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

nijam=#

CLIENT SIDE CONFIGURATION :
==========================

If the server fails to (re)start, look in the postgresql startup log, /var/lib/pgsql/pgstartup.log default for CentOS, for the reason.

On the client, we need three files. For Windows, these files must be in %appdata%\postgresql\ directory. 
You need to create the directory on the client machine if linux ( mkdir ~/.postgresql )

• root.crt (trusted root certificate) 
• postgresql.crt (client certificate) 
• postgresql.key (private key)

Step 1.
====
Generate the the needed files on the server machine, and then copy them to the client. We'll generate the needed files in the /tmp/directory.
First create the private key postgresql.key for the client machine, and remove the passphrase.

2ndquadrant.in:~ # openssl genrsa -des3 -passout pass:gsahdg -out /tmp/postgresql.key 1024
Generating RSA private key, 1024 bit long modulus
.++++++
...++++++
e is 65537 (0x10001)

2ndquadrant.in:~ # openssl rsa -in /tmp/postgresql.key -passout pass:gsahdg -out /tmp/postgresql.key
Enter pass phrase for /tmp/postgresql.key:
writing RSA key

Note : Give gsahdg while prompting 

Step 2.
=====
Then create the certificate postgresql.crt. It must be signed by our trusted root (which is using the private key file on the server machine). Also, the certificate common name (CN) must be set to the database user name we'll connect as.

2ndquadrant.in:~ # openssl req -new -key /tmp/postgresql.key -out /tmp/postgresql.csr
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [AU]:
State or Province Name (full name) [Some-State]:
Locality Name (eg, city) []:
Organization Name (eg, company) [Internet Widgits Pty Ltd]:
Organizational Unit Name (eg, section) []:
Common Name (e.g. server FQDN or YOUR name) []:
Email Address []:

Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:

Step 3. Extending SSL validation expiry.
=====

2ndquadrant.in:~ # openssl x509 -days 3650 -req -in /tmp/postgresql.csr -CA root.crt -CAkey server.key -out /tmp/postgresql.crt -CAcreateserial
Signature ok

subject=/C=AU/ST=Some-State/O=Internet Widgits Pty Ltd
Error opening CA Certificate root.crt
139936769549968:error:02001002:system library:fopen:No such file or directory:bss_file.c:407:fopen('root.crt','re')
139936769549968:error:20074002:BIO routines:FILE_CTRL:system lib:bss_file.c:409:
unable to load certificate

Reason:
Reroot.crt file path is missing

Solution :

2ndquadrant.in:~ # cd /data/nijam/
2ndquadrant.in:/data/nijam # openssl x509 -days 3650 -req -in /tmp/postgresql.csr -CA root.crt -CAkey server.key -out /tmp/postgresql.crt -CAcreateserial
Signature ok
subject=/C=AU/ST=Some-State/O=Internet Widgits Pty Ltd
Getting CA Private Key

Copy the three files we created from the server /tmp/ directory to the client machine. 
Copy the trusted root certificate root.crt from the server machine to the client machine (for Windows pgadmin %appdata%\postgresql\ or for Linux pgadmin ~/.postgresql/). Change the file permission of postgresql.key to restrict access to just you (probably not needed on Windows as the restricted access is already inherited). Remove the files from the server /tmp/ directory.
(After copying the three files from the server (/tmp/{postgresql.key,postgresql.crt,root.crt}) to the client machine (into directory ~/.postgresql/), you'll need to set the permission of the key to not world readable: chmod 0400 ~/.postgresql/postgresql.key
On windows, permissions in the are handled automatically for you.)

========================================END===========================================

Basic Issues while configuring SSL certificate :

ISSUES-1 :

$ psql "postgresql://localhost/?sslmode=verify-ca"
psql: root certificate file "/home/nijam/.postgresql/root.crt" does not exist
Either provide the file or change sslmode to disable server certificate verification.

Solution :

$ cp /data/nijam/root.crt /home/nijam/.postgresql/
nijam@2ndquadrant.in:/home/nijam> mkdir /home/nijam/.postgresql/

$ psql "postgresql://localhost/?sslmode=verify-ca"
psql (10.5)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

ISSUES-2 :

In client side should be change permissions as

chmod 0600 .postgresql/postgresql.key

from client 53.126.142.211 :
password_encryption = md5 
psql -h 2ndquadrant.in -p 5432 -U DB_writer -d DB

check localdomain account.

For server side :

chmod 400 server.crt
chown nijam.nijam server.crt
chmod 400 root.crt
chown nijam.nijam root.crt

Some basic notes of SSL :
https://discuss.tutorialdba.com/1158/enable-openssl-postgressql-server