The PostgreSQL 9.0 Reference Manual - Volume 3 - Server Administration Guide by The PostgreSQL Global Development Group Paperback (6"x9"), 274 pages ISBN 9781906966072 RRP £9.95 ($14.95) Sales of this book support the PostgreSQL project! Get a printed copy>>>

PostgreSQL 9.0 Reference Manual Volume 3 Buy the book here! >>> support free documentation

3.8 Secure TCP/IP Connections with SSL

PostgreSQL has native support for using SSL connections to encrypt client/server communications for increased security. This requires that OpenSSL is installed on both client and server systems and that support in PostgreSQL is enabled at build time (see section 1 Installation from Source Code).

With SSL support compiled in, the PostgreSQL server can be started with SSL enabled by setting the parameter ssl to on in ‘postgresql.conf’. The server will listen for both normal and SSL connections on the same TCP port, and will negotiate with any connecting client on whether to use SSL. By default, this is at the client's option; see section 5.1 The pg_hba.conf file about how to set up the server to require use of SSL for some or all connections.

PostgreSQL reads the system-wide OpenSSL configuration file. By default, this file is named ‘openssl.cnf’ and is located in the directory reported by openssl version -d. This default can be overridden by setting environment variable OPENSSL_CONF to the name of the desired configuration file.

OpenSSL supports a wide range of ciphers and authentication algorithms, of varying strength. While a list of ciphers can be specified in the OpenSSL configuration file, you can specify ciphers specifically for use by the database server by modifying ssl_ciphers in ‘postgresql.conf’.

Note: It is possible to have authentication without encryption overhead by using NULL-SHA or NULL-MD5 ciphers. However, a man-in-the-middle could read and pass communications between client and server. Also, encryption overhead is minimal compared to the overhead of authentication. For these reasons NULL ciphers are not recommended.

To start in SSL mode, the files ‘server.crt’ and ‘server.key’ must exist in the server's data directory. These files should contain the server certificate and private key, respectively. On Unix systems, the permissions on ‘server.key’ must disallow any access to world or group; achieve this by the command chmod 0600 server.key. If the private key is protected with a passphrase, the server will prompt for the passphrase and will not start until it has been entered.

In some cases, the server certificate might be signed by an “intermediate” certificate authority, rather than one that is directly trusted by clients. To use such a certificate, append the certificate of the signing authority to the ‘server.crt’ file, then its parent authority's certificate, and so on up to a “root” authority that is trusted by the clients. The root certificate should be included in every case where ‘server.crt’ contains more than one certificate.