PostgreSQL has native support for using SSL connections to encrypt client/server communications for increased security. See Section 17.8 for details about the server-side SSL functionality.
libpq 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.
To verify the server certificate is trustworthy, place certificates of the certificate authorities (CA) you trust in the file ~/.postgresql/root.crt in the user's home directory. (On Microsoft Windows the file is named %APPDATA%\postgresql\root.crt.) libpq will then verify that the server's certificate is signed by one of the trusted certificate authorities. The SSL connection will fail if the server does not present a trusted certificate. Certificate Revocation List (CRL) entries are also checked if the file ~/.postgresql/root.crl exists (%APPDATA%\postgresql\root.crl on Microsoft Windows).
If the server requests a trusted client certificate, libpq will send the certificate stored in file ~/.postgresql/postgresql.crt in the user's home directory. The certificate must be signed by one of the certificate authorities (CA) trusted by the server. A matching private key file ~/.postgresql/postgresql.key must also be present, unless the secret key for the certificate is stored in a hardware token, as specified by PGSSLKEY. (On Microsoft Windows these files are named %APPDATA%\postgresql\postgresql.crt and %APPDATA%\postgresql\postgresql.key.) The private key file must not be world-readable.
If the environment variable PGSSLKEY is set, its value should consist of a colon-separated engine name and key identifier. In this case, libpq will load the specified engine, i.e. the OpenSSL module which supports special hardware, and reference the key with the specified identifier. Identifiers are engine-specific. Typically, cryptography hardware tokens do not reveal secret keys to the application. Instead, applications delegate all cryptography operations which require the secret key to the hardware token.
If you are using SSL inside your application (in addition
to inside libpq), you can use
PQinitSSL(int) to tell libpq
that the SSL library has already been initialized by your
application.
See http://h71000.www7.hp.com/doc/83final/BA554_90007/ch04.html
for details on the SSL API.
Table 30-1. Libpq/Client SSL File Usage
| File | Contents | Effect |
|---|---|---|
| ~/.postgresql/postgresql.crt | client certificate | requested by server |
| ~/.postgresql/postgresql.key | client private key | proves client certificate sent by owner; does not indicate certificate owner is trustworthy |
| ~/.postgresql/root.crt | trusted certificate authorities | checks server certificate is signed by a trusted certificate authority |
| ~/.postgresql/root.crl | certificates revoked by certificate authorities | server certificate must not be on this list |