The PostgreSQL 9.0 Reference Manual - Volume 2 - Programming Guide by The PostgreSQL Global Development Group Paperback (6"x9"), 478 pages ISBN 9781906966065 RRP £14.95 ($19.95) Sales of this book support the PostgreSQL project! Get a printed copy>>>

PostgreSQL 9.0 Reference Manual Volume 2 Buy the book here! >>> learn more

1.17.1 Certificate verification

By default, PostgreSQL will not perform any verification of the server certificate. This means that it is possible to spoof the server identity (for example by modifying a DNS record or by taking over the server IP address) without the client knowing. In order to prevent spoofing, SSL certificate verification must be used.

If the parameter sslmode is set to verify-ca, libpq will verify that the server is trustworthy by checking the certificate chain up to a trusted certificate authority (CA). If sslmode is set to verify-full, libpq will also verify that the server host name matches its certificate. The SSL connection will fail if the server certificate cannot be verified. verify-full is recommended in most security-sensitive environments.

In verify-full mode, the cn (Common Name) attribute of the certificate is matched against the host name. If the cn attribute starts with an asterisk (*), it will be treated as a wildcard, and will match all characters except a dot (.). This means the certificate will not match subdomains. If the connection is made using an IP address instead of a host name, the IP address will be matched (without doing any DNS lookups).

To allow server certificate verification, the certificate(s) of one or more trusted CAs must be placed in the file ‘~/.postgresql/root.crt’ in the user's home directory. (On Microsoft Windows the file is named ‘%APPDATA%\postgresql\root.crt’.)

Certificate Revocation List (CRL) entries are also checked if the file ‘~/.postgresql/root.crl’ exists (‘%APPDATA%\postgresql\root.crl’ on Microsoft Windows).

The location of the root certificate file and the CRL can be changed by setting the connection parameters sslrootcert and sslcrl or the environment variables PGSSLROOTCERT and PGSSLCRL.