TLS: Demystifying communication encryption in PostgreSQL

In this post, we want to give you a brief introduction to TLS (Transport Layer Security), which is a technology widely used in combination with PostgreSQL to encrypt client / server connections.

What is TLS?

TLS is short for "Transport Layer Security", which is a means of making sure that whatever data you are sending via TCP connections is secured against attackers.

To understand how TLS works, you first need to understand a few things about encryption.

Symmetric Encryption

If you have a text "I like cheese" and replace every character with the next one in the English alphabet (replacing a with b, b with c, ... and z with a), you have basic symmetric encryption. You can transfer the message "j mjlf diddtf" to your friend, and they can go through the message and replace every character with the preceding one (replacing b with a, c with b, ... and a with z), and they'll know your favourite snack.

Mathematically, symmetric encryption can be expressed like this:

if enc(x, k)=e is the function that encrypts x, using key k, into e, then the inverse function enc^-1(e, k)=x is used to decrypt e, using k, back into x.

This is an extremely simplified example, and easy to crack. There are way more sophisticated ways of encrypting data symmetrically, which cannot be cracked unless you know the key. A well-known example of symmetric encryption is the Enigma machine, used during the second World War. While the enigma (III) encryption was crackable for the later part of World War Two, the more advanced enigma (IV) is still difficult to crack, even to this day.

There are two practicability issues with symmetric encryption.

On one hand, you’ll need many different keys (one for each connection, essentially), so client B cannot decrypt whatever the server was sending to client A, for example. On the other hand, there is no simple way of securely communicating the key itself to new clients.

This is not an issue when you're managing two servers that talk to each other, because they both are trusted to a high degree and connections do not change that often. Your clients (over whose hardware and software you have little control) can not be trusted to the same degree, and new ones might be added regularly.

Thus, for the clients to be able to send and receive encrypted messages, you need something different.

Asymmetric Encryption

At the basis of Asymmetric Encryption is the principle of private and public keys, which together are called a key pair. Both are usually derived from the same set of random numbers, and while the public key can be used to encrypt data, only the private key can be used to decrypt it again.

This means that you can hand your public key out to anyone so that they can encrypt what they want to send to you, and only you will be able to decrypt it with the private key.

Mathematically, asymmetric encryptions can be expressed like this:
if enc(x, k_pub)=e is the function that encrypts x, using public key k_pub, into e,
then the function dec(e, k_priv)=x is used to decrypt e, using private key k_priv, back into x.

Just as with symmetric encryption, there are asymmetric encryptions that are less secure than others, but we don't need to concern ourselves with that right now.
What counts is that we have a way of sending encrypted data without having to trust the client with our secret key.

Signature Validation

Another benefit of asymmetric encryption is that it can not only be used for encryption but also to affix a signature to data. The sender can calculate a checksum of the data and encrypt it with their private key to generate a signature. The recipient calculates the checksum on the same data and compares this to the sender's checksum, which was decrypted from the signature using the public key.

How does TLS work?

TLS uses a mixture of asymmetric and symmetric encryption. Remember that symmetric encryption ideally needs different keys for every connection and that the key exchange is difficult to do securely. But at the same time, symmetric encryption is much easier (i.e. faster, cheaper) to compute compared to asymmetric encryption, and still provides similar security.

So asymmetric encryption is only used for the initial handshake, to derive or exchange a random key that will be used for symmetrically encrypting the remainder of the connection.

There are two ways of creating and exchanging the random key:

1. The client generates the key randomly and encrypts it using the public key of the server. In this case, the cipher is sent encrypted to the server.
2. Both parties agree on a random value and derive a shared secret using this random value, their own private key, and the other party’s public key. This calculation can be done on both sides and produces the same cipher, so the cipher itself never needs to be transferred. (This is known as the Diffie-Hellman key exchange)

The second approach is only useful if both parties have a key pair.

When you’re visiting a website from your computer, the first approach is usually used, since you don’t have to have a key pair to visit HTTPS-encrypted websites.

How can asymmetric encryption be used for authentication?

If you already know the public key for a given server, then you can challenge them to test if they indeed have the matching private key. Using the known public key, you can encrypt a random message and send it to the server, asking it to decrypt that for you. If the known public key matches the private key installed in the server, then the decrypted message sent back by the server will also match the random message that you initially encrypted.

This means that you can trust the server to be authentic - otherwise, it should not be in possession of the private key matching the public key that you already know. This is how the known_hosts file for ssh works.

There is, however, another way of authenticating the other party. When you're browsing the web, you do not have a list of known public keys for each server on the internet.
So, upon first connection, the server sends you its public key. But how can you be sure that this key actually belongs to the server that you're expecting? After all, you could be trying to access www.myimaginarydomain.com and an attacker diverts your calls to their own server.

The solution to this problem is quite easy:

the operator of www.myimaginarydomain.com can use a public key that has been signed by a private key, whose public key is already installed on your system. These public keys are called "certification authority" (CA) certificates. Sometimes it is more complicated though, as the CA certificates themselves are almost never used to directly sign "end-user" certificates, but rather used to sign intermediary certificates, and it is possible to validate the whole chain.

For example, the certificate for www.myimaginarydomain.com might be signed by "Imaginary Corporation CA", which might be signed by "Imaginary Global Trust Corporation". If the Certificate of "Imaginary Global Trust Corporation" is part of your systems' or browsers' certificate store, then the certificate delivered by the server www.myimaginarydomain.com will be trusted by your system.

In the end, a connection will only be trusted if the other parties' address matches the address contained in the certificate. If www.myimaginarydomain.com uses a certificate intended for www.notmyimaginarydomain.com, no connection will be established.

Nomenclature in the world of TLS

While it is easy to think of everything that we've just talked about as public and private key pairs - and in fact, it is nothing more than that, except for a little bit of metadata and signatures - there are certain terms that are frequently used in the context of TLS.

• A public key is generally called a "certificate" - there are server certificates, which can be used to identify a server and send encrypted messages to them, and there are client certificates, which can be used to do the same thing, except that everything goes in the direction of the client.
• A private key in the context of TLS is often simply called a "key".
• An entity (a company, or a person) that signs other certificates is called a Certificate Authority (CA); If you trust this CA, you can trust certificates signed by them.
• A public key that belongs to a private key used by a CA to sign other certificates is called a CA Certificate.
• In order to have a certificate signed off by a CA, the user creates a key and a Certificate Signing Request (CSR) - the CSR contains the public key and the domain name or IP address that this certificate should be used for.
• The domain name or IP address of the server that is going to be using the certificate is contained in the Common Name (CN) field of a certificate. For client certificates, the CN often contains a username.

Further reading

If you want to learn more about security, we recommend checking out our practical content about configuring client / server encryption in PostgreSQL using SSL.

Sometimes it is necessary to fully encrypt an entire PostgreSQL instance. PostgreSQL Transparent Data Encryption (TDE) can do exactly that, and we recommend checking it out to make your application’s backend even more secure.

In order to receive regular updates on important changes in PostgreSQL, subscribe to our newsletter, or follow us on Twitter, Facebook, or LinkedIn.