Securing PostgreSQL client with SSL/TLS on RHEL6

Securing postgresql (postgresql-8.4.20-8.el6_9) 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 involves providing a connection string using connection parameters.

    psql "host=rhel6-64.example.com dbname=postgres user=postgres sslmode=verify-full sslrootcert=$HOME/rhel6-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

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

and then connection with the service name parameter

    $ psql "service=mypg6"
    Password:
    psql (8.4.20)
    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.