Securing PostgreSQL client with SSL/TLS on RHEL5

Securing postgresql (postgresql-8.1.23-10.el5_!0) 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 environment variables to specify a secure connection.

    PGSSLMODE=require psql -h rhel5-32.example.com -U postgres postgres

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

    /etc/sysconfig/pgsql/pg_service.conf

in .INI format with a logical name for the service

[mypg5]
host=rhel5-32.example.com
dbname=postgres
user=postgres
sslmode=require

and then connection with the service name parameter

    $ PGSERVICE=mypg5 psql
    Password:
    Welcome to psql 8.1.23, the PostgreSQL interactive terminal.

    Type:  \copyright for distribution terms
           \h for help with SQL commands
           \? for help with psql commands
           \g or terminate with semicolon to execute query
           \q to quit

    SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)

    postgres=#

Authority Certificate File

The authority file is specified in

    $HOME/.postgresql/root.crt

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