Choosing the Right Level of PostgreSQL SSL Protection
Before setting up SSL authentication for PostgreSQL, it’s important to define your actual security goal. The configuration differs drastically depending on whether you just want encrypted traffic, want clients to verify the server’s identity, or also want the server to verify each client.
Option 1: TLS-Only Encryption
If the only requirement is that traffic between clients and the server is encrypted, you only need to supply the server with a certificate and private key. Clients then connect using sslmode=require. This prevents eavesdropping but does nothing to authenticate the server to the clients.
Option 2: Server Authentication
To have clients verify that they are actually talking to your server, you must distribute the server’s certificate to the clients. If you’re using a certificate issued by a third-party CA such as LetsEncrypt, ensure clients can validate the entire chain from LetsEncrypt’s root certificate down to your server’s certificate. Without the full chain, verification will fail.
Option 3: Mutual Authentication (Server + Client)
For the highest level of security, both sides authenticate. This requires distinct certificates and keys for the server and for each client. Ideally, every client has its own certificate so that no client can impersonate another client, and no certificate holder can act as the server. If anyone other than your server gets the server’s private key, the authenticity guarantee for the server is void.
Understanding Certificate Roles
Certificates are hierarchical. The “root” certificate (and its key) is used either directly or to sign “child” certificates. When a root signs child certificates, it is referred to as a CA (certificate authority). To verify any child certificate, clients must also have access to the CA certificate.
A common example of a root certificate used directly is in SSH key authentication. For web servers and typical database clients, it is unusual to use the root certificate itself. Instead, you generate a child certificate and key to hand out to the server or each client, while keeping the root key secret. This design is practical: you can rotate a compromised server or client certificate by issuing a new child certificate, without redistributing a new trust anchor to the other side. The other party simply continues to verify against the original CA certificate.
