Skip to content

PostgreSQL SSL Configuration

Configuration file

The following parameters need to be set in your postgresql.conf file:

ssl = on
ssl_ciphers = 'TLSv1.2:!aNULL'      # allowed SSL ciphers  # (change requires restart)
ssl_cert_file = '/etc/dbod/certificates/hostcert.pem'  # (change requires restart)
ssl_key_file = '/etc/dbod/certificates/hostkey_pg.pem' # (change requires restart)
ssl_ca_file = '/etc/dbod/certificates/ca.pem'          # (change requires restart)

And you'll need to add an entry per account on the pg_hba.conf file specifying the connection as ssl (with the hostssl keyword):

# TYPE  DATABASE        USER            ADDRESS                 METHOD
hostssl all             user_ssl                pcixx.dyndns.cern.ch         md5
hostssl all             user_cert               pcixx.dyndns.cern.ch         cert

This file modifications doesn't require a restart, you can reload the configuration file from the web interface.

Using CERT as authentication method

When using cert as authentication method the server will check only if the user is providing a user certificate valid by the server CA. Adding the clientcert=1 modifier to the authentication method will require also verification of the user certificate identity (which is probably the desired behaviour).

When this is done, by default the certificate Common Name (CN) is taken as the database user attempting to establish the connection. If the certificate is valid and the CN matches the database account login will be allowed.

In order to allow the CN to be different from the database user name an user mapping will need to bet setup in the pg_ident.conf file. Several usernames (certificate subjects) can be mapped to the same PG username:

# MAPNAME       SYSTEM-USERNAME         PG-USERNAME
mymap   cern-username-1                user_cert
mymap   cern-username-2                user_cert

The resulting entry in the pg_hba.conf file will look like:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
hostssl test_cert       user_cert       pcxx.dyndns.cern.ch     cert clientcert=1 map=mymap

Note

At the moment of writing this documentation is not possible to upload pg_ident.conf files through the DBOD interface, so please let us know if you want to use this kind of authentication in order to set up any user mapping for you.

SSL configuration on libraries/clients

All TLS/SSL related parameters (sslmode, sslcert, sslkey,...) can be set as part of the connection string. If you encounter any problems doing this please check the documentation for your library of choice.

For libraries or clients based on libpq, it is possible to set the connection parameters as environment variables:

pcitdb46:dbod $ psql -h dbod-pgtest.cern.ch -p 6601 test_ssl user_ssl
Password for user user_ssl:
psql (9.6rc1, server 9.4.5)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

test_ssl=>

pcitdb46:dbod $ export |grep SSL
PGSSLCERT=/etc/dbod/icoteril-cert.pem
PGSSLKEY=/etc/dbod/icoteril-key.pem
PGSSLMODE=require

pcitdb46:dbod $ psql -h dbod-pgtest -p 6601 test_cert user_cert
psql (9.6rc1, server 9.4.5)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

test_cert=>

You can use this query: select name, setting from pg_settings where name like '%ssl%'; to get the values of SSL related PostgreSQL variables:

    postgres> select name, setting from pg_settings where name like '%ssl%';
    +---------------------------+---------------------------------------+
    | name                      | setting                               |
    |---------------------------+---------------------------------------|
    | ssl                       | on                                    |
    | ssl_ca_file               | /etc/dbod/certificates/ca.pem         |
    | ssl_cert_file             | /etc/dbod/certificates/hostcert.pem   |
    | ssl_ciphers               | TLSv1.2:!aNULL                        |
    | ssl_crl_file              |                                       |
    | ssl_ecdh_curve            | prime256v1                            |
    | ssl_key_file              | /etc/dbod/certificates/hostkey_pg.pem |
    | ssl_prefer_server_ciphers | on                                    |
    | ssl_renegotiation_limit   | 0                                     |
    +---------------------------+---------------------------------------+
    SELECT 9
    Time: 0.004s