This is part of an occasional series of blogs about PostgreSQL commitfest patches. Look at the series intro which also lists some ground rules and expectations for what to expect in my thoughts below. 🐘
| Patch title and thread: | Use the system CA pool for certificate verification |
| Commitfest at time of review: | 2023-03 |
| Status at time of review: | Ready for Committer |
Setting up SSL mode in PostgreSQL (and really any hosted service) is an essential, table stakes DBA task. Just as important is understanding how the sslmode parameters of a PostgreSQL connection really work and what users can expect to happen every time they connect to the cluster.
This patch caught my attention because I’ve struggled over the years to get various hosted services to work (and sometimes various applications) because the SSL certificates weren’t in the right spot locally. Before getting to the details of the patch and why it was submitted, let’s briefly talk about SSL connections with Postgres
SSL Client Communication with PostgreSQL
The way that PostgreSQL connection encryption works is through the sslmode parameter of a connection string. The client communication library, libpq, will use this setting to determine what kind of SSL connection it will request of the PostgreSQL cluster.
With many hosted services and tutorials, this connection string setting is often not discussed and taken for granted. Unfortunately, the default setting for this parameter is sslmode=prefer, which doesn’t guarantee a secure connection. Referring to the table in libpq-ssl documentation page, we can see that prefer simply means that the client would like to use SSL if it’s available, but no error will be thrown if the server doesn’t support it. 😱
Ouch!
To make matters worse, libpq will only successfully verify the certificate authority and server name if the root CA is in a very specific location on the client; ~/.postgresql/root.crt. That is, even if the server certificate is signed by a common root CA already located in the system-wide CA pool (like Let’s Encrypt), the libpq client will not search for and use it when either sslmode=verify-ca or sslmode=verify-full are specified. The CA certificate must be named root.crt and located in the hidden directory noted above.
This translates into a lot of frustration for end-users and a fair amount of setup/maintenance of user accounts to make sure the root certificates are in the right place. It’s also challenging to have multiple root certificates if you work between multiple organizations and their certificates are signed by different authorities. The only workaround it so use specify the root certificate location in the connection string using `sslrootcert` and then the full path to the certificate.
In short, the big usability issue here is that most of us have the widely supported root CA certificates on our computers already. PostgreSQL is just expecting them to be in a specific location when we want to verify the server authenticity. In fact, this response in the thread from Jelte Fennema (Microsoft Postgres developer) really brings home the issue.
Huge +1 from me. On Azure we're already using public CAs to sign
certificates for our managed postgres offerings[1][2]. Right now, our
customers have to go to the hassle of downloading a specific root cert or
finding their OS default location. Neither of these allow us to give users
a simple copy-pastable connection string that uses secure settings. This
would change this and make it much easier for our customers to use secure
connections to their database.Improving the End-user SSL Experience in PostgreSQL
To be honest, the patch caught my attention because I’ve struggled with SSL setup in PostgreSQL sometimes over the years. The conversation around the topic, patch approach, and even some surprise in how things are working from long-time PostgreSQL contributors is refreshing.
What I didn’t expect was such a good example of how circuitous the lifecycle of some patches are. Over the last two years, the journey of this patch looked like this.
- Thomas Habets submitted the original patch in September 2021 (first email in the patch thread)
- discussion on the patch eventually ended on October 4, 2021 because there wasn’t full agreement on the best path forward
- a year later another developer, Jacob Champion, implemented a different approach and resubmitted the patch
- conversation ensues, with multiple developers discussing the new approach and moving it a step closer to inclusion
I love how much this illustrates the power of the PostgreSQL developer community. Ideas are shared and collaboration happens. Sometimes this isn’t the right time or approach for the proposed patch. But, it triggers the right conversation and eventually someone else picks up where the first contributor left off.
Currently Proposed Changes
This patch is proposing a pretty small change to the connection string handling in libpq. In Jacob’s proposed patch, he’s adding the ability to use a new keyword with the sslrootcert parameter. If a connection string has sslrootcert=system, then libpq will only look for the root CA in the system CA pool.
Earlier in the thread, there was some discussion over possibly using some fallback logic so that if there was no root.crt file in the correct location, then using sslmode=verify-full would fall back and look in the CA pool.
Instead, Jacob argued that not using a fallback would be more clear and precise, actually leading to easier debugging if someone asked for help with a connection. In the end, everyone seemed to agree.
Current Status
As of the February 15, 2023 (the publish date of this article), this patch is in a “Ready for Committer” state. This means that multiple people have reviewed the patch, applied it locally and tested it. It is now ready for an official committer to prepare the patch and move it forward with a pull request.
Because it changes how libpq works I assume it wouldn’t be included as part of a minor release, which means it’s likely to get included with PostgreSQL 16 as long as a committer has the time to shepherd it forward one last step.