Most people are familiar with HTTPS (HTTP over TLS). If you are unfamiliar with how it works, web browsers first establish a TLS connection with a web server. Then they communicate using the HTTP protocol via this encrypted connection. In addition, browsers have reasonably well-defined rules for validating server certificates. However, they vary in how the user interface works when validation fails.
Proxying Postgres
Aembit acts as a multiprotocol proxy. The Aembit Agent/Proxy:
- proxies connections from a client workload to a server workload,
- injects credentials into the protocol,
- upgrades connections from clear-text to encrypted, and so on.
And as we were developing Postgres TLS support for Aembit, we stumbled upon numerous details that made Postgres TLS different from HTTPS. The rest of the article covers some of these gotchas.
Establishing TLS
As I mentioned, we are used to TLS being established first and the application protocol riding on top of it.
That is not how Postgres works. Instead, Postgres establishes the application protocol first (in clear) and negotiates whether or not TLS is needed. If TLS is required, a TLS handshake happens, and the rest of the communication goes over TLS.
I think it was done like this because Postgres didn’t support TLS initially, and it was added in a backward-compatible way to avoid breaking either older clients or older servers. However, that resulted in this not-so-elegant TLS negotiation scheme. I believe multiple other older protocols suffer from this, by the way.
Client TLS Modes
Postgres supports six different modes for how a client decides whether to negotiate TLS.
Frankly, I was speechless when I read this for the first time. I was appalled by “I don’t care about security” or the default: “I don’t care about encryption, but I wish to pay the overhead of encryption if the server supports it.”
By the way, all these “I don’t care” guidelines were taken to heart by the community. An excellent article explains that most Postgres instances on the internet are insecure. This article touches on many of the topics that I will mention below.
The table below is copied directly from the official documentation.
| sslmode | Eavesdropping protection | MITM protection | Statement |
disable |
No | No | I don’t care about security, and I don’t want to pay the overhead of encryption. |
allow |
Maybe | No | I don’t care about security, but I will pay the overhead of encryption if the server insists on it. |
prefer |
Maybe | No | I don’t care about encryption, but I wish to pay the overhead of encryption if the server supports it. |
require |
Yes | No | I want my data to be encrypted, and I accept the overhead. I trust that the network will make sure I always connect to the server I want. |
verify-ca |
Yes | Depends on CA policy | I want my data encrypted, and I accept the overhead. I want to be sure that I connect to a server that I trust. |
verify-full |
Yes | Yes | I want my data encrypted, and I accept the overhead. I want to be sure that I connect to a server I trust, and that it’s the one I specify. |
The difference between verify-ca and verify-full depends on the policy of the root CA. If a public CA is used, verify-ca allows connections to a server that somebody else may have registered with the CA. In this case, verify-full should always be used. If a local CA is used, or even a self-signed certificate, using verify-ca often provides enough protection.
The default value for sslmode is prefer. As is shown in the table above, this makes no sense from a security point of view, and it only promises performance overhead if possible. It is only provided as the default for backward compatibility and is not recommended in secure deployments.
Certificate Verification
The only two modes that could be secure are verify-ca and verify-full. The difference between the two modes is subtle. verify-ca does the certificate and chain of trust validation without checking that the hostname matches. verify-full does both certificate and chain of trust validation, and it verifies the hostname, too – i.e., that the hostname matches what is in the certificate CN or SubjectAlternativeName.
That again gives me pause. It means that verify-ca allows impersonating a Postgres server with ANY certificate/private key issued from the same certificate authority. That is incredibly dangerous if this CA issued more than one certificate.
However, it doesn’t stop there. Various client libraries implement verify-full differently because the Postgres documentation doesn’t specify whether to accept or reject outdated certificate versions, deprecated encryption algorithms, etc. As a result, it’s the Wild West regarding how much protection you will get even by using the strongest mode.
Cloud Providers
We initially implemented strong verify-full (based on RustTLS). However, as we implemented it, we realized that it might not work correctly with Postgres RDBMS hosted by different cloud providers.
So, I investigated what Amazon, Google, and Microsoft did with Postgres. I configured AWS RDS, GCP Cloud SQL, and Azure Database to see what was happening.
And here is a summary of server-side certificates that they use and how easy it is to verify-full with each of them.
| AWS RDS (Postgres) | GCP Cloud SQL (Postgres) | Azure Database for Postgres | |
| Is Root CA publicly trusted? | No | No | Yes – DigiCert Global Root CA |
| Is Root CA shared between different instances? | Yes – Shared between all non-Gov regions. | No – A new self-signed CA is created for each instance. | Yes |
| Server certificate CN / Subject alternative name | FQDN of externally accessible endpoint | <projectname>:<instancename> | <id>.database.azure.com |
| Server certificate Subject alternative name | FQDN of externally accessible endpoint | None | <id>.database.azure.com FQDN |
| Can we use verify-full? | Yes | No | Yes |
| Overall grade |
B AWS did a decent job. However, it would be nice if they used a Root CA which is publicly trusted. Strangely, they didn’t do it, considering they own publicly trusted root CAs and a whole certificate management infrastructure. |
C Google recommends using verify-ca (vs. verify-full). Since their self-signed CA has just one certificate, verity-ca will have a similar scope of threats as verify-full. However, none of the libraries that do only verity-full will work with it because of the certificate CN/Subjective Alternative name. |
A They did the best job to let people secure connections to Postgres efficiently and correctly. |
Summary
First, if you need help with Postgres TLS, understanding it, or making it work, please ping us, and we will be happy to share our knowledge.
Second, we recommend checking that your Postgres RDBMS has TLS enabled and enforced. And your client libraries utilize verify-full rather than extremely weak default or prefer.
Aembit can help you secure Postgres – both authentication and ensuring that you utilize the most robust verification mode with proper server certificate validation.
Aembit is the Identity Platform that lets DevOps and Security manage, enforce, and audit access between federated workloads.
We invite you to try it today!
