TLS, or Transport Layer Security, is the mechanism PostgreSQL deployments rely on to protect client/server traffic carried over TCP. Understanding it requires a short detour into the two families of encryption it combines.
The two encryption models
Symmetric encryption and its limits
Symmetric encryption scrambles data with a key and reverses the operation with the same key. A toy version: shift every letter of I like cheese one position forward in the alphabet to get j mjlf diddtf; the recipient shifts each letter back. Formally, if enc(x, k)=e encrypts x with key k into e, then enc^-1(e, k)=x decrypts it. Real schemes are far more sophisticated and cannot be broken without the key — the World War II Enigma machine is a well-known example, with the more advanced Enigma (IV) still difficult to crack today.
Two practical problems remain. Every connection ideally needs its own key, so that one client cannot read what the server sent to another; and there is no simple way to deliver the key securely to new clients. Neither is a problem for a small set of mutually trusted servers that rarely change, but clients run on hardware and software you do not control, and new ones appear regularly.
Asymmetric encryption
Asymmetric encryption is built on a key pair: a public key that encrypts and a private key that decrypts. Both usually derive from the same random numbers. The public key can be handed out freely, since only the matching private key reverses the operation: enc(x, k_pub)=e and dec(e, k_priv)=x. Again, some schemes are weaker than others, but the point is that encrypted data can be sent without sharing the secret key.
The same mathematics produces signatures. The sender computes a checksum of the data and encrypts it with their private key; the recipient computes the checksum independently and compares it against the signature decrypted with the sender's public key.
Inside the TLS handshake
TLS is a hybrid. Asymmetric cryptography solves key distribution but is slower and costlier to compute, so it is used only during the initial handshake, to agree on a random key that then symmetrically protects the rest of the session.
There are two ways to establish that shared key:
- The client generates the key randomly and encrypts it with the server's public key. The cipher travels to the server encrypted.
- Both parties agree on a random value and derive a shared secret from it, their own private key and the other party's public key — the Diffie-Hellman key exchange. Both sides compute the same result, so the cipher is never transmitted.
The second option requires both parties to hold a key pair, so it is only usable when that condition holds. A browser visiting an HTTPS site typically uses the first approach, since visitors do not carry their own key pairs.
Authentication and certificate chains
Knowing a server's public key lets you verify it: encrypt a random message with that key, send it over, and ask the server to decrypt it. Only a server holding the matching private key can return the original message. This is how the known_hosts file for ssh works.
Web browsing cannot rely on a pre-existing list of public keys per server, and trusting the key a server presents on first contact would leave you open to an attacker diverting your requests to their own machine — you try to reach www.myimaginarydomain.com and land somewhere else entirely.
The fix is for the operator of www.myimaginarydomain.com to use a public key signed by a private key whose public counterpart is already installed on your system. Such keys are called certification authority (CA) certificates. In practice the chain rarely stops at one link: CA certificates usually sign intermediary certificates rather than end-user certificates directly, and the whole chain can be validated. A certificate for www.myimaginarydomain.com might be signed by "Imaginary Corporation CA", itself signed by "Imaginary Global Trust Corporation"; if that last certificate lives in your system's or browser's certificate store, the delivered certificate is trusted. Trust is finally granted only if the address in the certificate matches the address requested — a certificate issued for www.notmyimaginarydomain.com will not establish a connection to www.myimaginarydomain.com.
Terminology
All of the above is public/private key pairs plus metadata and signatures, but TLS has its own vocabulary:
- A public key is generally called a certificate. Server certificates identify a server and let clients send it encrypted data; client certificates do the same in the direction of the client.
- A private key is often simply called a key.
- An entity — company or person — that signs other certificates is a Certificate Authority (CA). Trusting the CA means trusting what it signs.
- The public key belonging to a CA's signing private key is a CA Certificate.
- To get a certificate signed, the user generates a key and a Certificate Signing Request (CSR), which carries the public key and the domain name or IP address the certificate is meant for.
- The server's domain name or IP address sits in the Common Name (CN) field of a certificate. For client certificates, the CN often holds a username.
For hands-on configuration of client/server encryption in PostgreSQL with SSL, see Cybertec's practical material; PostgreSQL Transparent Data Encryption (TDE) is available for encrypting an entire instance.
