Securing PostgreSQL client with SSL/TLS on RHEL7

Securing postgresql (postgresql-9.2.23-3.el7_4) that uses openssl

This article is part of the Securing Applications Collection

There are a number of ways to specify the requirement for a secured connection at the client end.

The first two involve providing a connection string in one of the recognised formats using connection parameters.

    psql "host=rhel7-64.example.com dbname=postgres user=postgres sslmode=verify-full sslrootcert=$HOME/rhel7-64.example.com.ca.pem"

or

    psql "postgresql://postgres@rhel7-64.example.com/postgres?sslmode=verify-full&sslrootcert=$HOME/rhel7-64.example.com.ca.pem"

The alternate method is to utilise the service name mechanism by placing details in the service file.

    $HOME/.pg_service.conf

in .INI format with a logical name for the service

[mypg7]
host=rhel7-64.example.com
dbname=postgres
user=postgres
sslmode=verify-full
sslrootcert=/home/myhome/rhel7-64.example.com.ca.pem

and then connection with the service name parameter

    # psql "service=mypg7"
    Password:
    psql (9.2.10)
    SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
    Type "help" for help.

    postgres=#

Authority Certificate File

The authority file is specified directly via the sslrootcert parameter, or placed at in

    $HOME/.postgresql/root.crt

Should contain the root certificate that signed the server's certificate.