When setting up ssl in Postgres, you can't just enable ssl. You must also install a signed certificate on the server.
The first step is to create a certificate signing request (csr) file that contains the host name of the database server. Once created, there are three ways to sign a csr to create a certificate:
-- Self-signed
-- Signed by a local certificate authority
-- Signed by a public certificate authority
If the certificate is to be self-signed, use the key created by the certificate signing request to create a certificate. If using a local certificate authority, sign the csr file with the local certificate authority's key.
The use of public certificate authorities doesn't make sense for most databases because it allows third parties to create trusted certificates. Their only reasonable use is if you wish to allow public certificate authorities to independently issue certificates that you wish to trust. This is necessary for browsers because they often connect to unaffiliated websites where trust must be established by a third party. (Browsers include a list of public certificate authorities who can issue website certificates it trusts.)
Issuing certificates to clients as well enables additional features:
- Server validation of client's certificates
- Client validation of the server's certificate
- Client authentication using certificates
These items require the server and client certificates be signed by the same certificate authority.
Ssl setup in Postgres can be complicated, but it offers a unique set of security and usability features that are unmatched.
Bruce Momjian is Senior Database Architect at EnterpriseDB.
This post originally appeared on his personal blog.