sslrootcert=system is broken, requires installing ca-certificates package

[jawj]

Since Postgres 16, the sslrootcert=system connection parameter turns on sslmode=verify-full and instructs psql to use the system CA cert store. This is a lot more secure than sslmode=require and works very nicely for people with Postgres providers who use public CAs to secure their databases.

Unfortunately, sslrootcert=system currently doesn't work on the psql binaries shipped in these Docker images:

# psql 'postgresql://user:[email protected]/neondb?sslrootcert=system'
psql: error: connection to server at "ep-broad-dew-xyz.us-east-2.aws.neon.tech" (3.x.x.x), port 5432 failed: SSL error: certificate verify failed

Fortunately, the fix is simple: apt-get update && apt-get install ca-certificates does the trick. It's also simple to test the fix using a free-tier Neon DB.

Would you be able to update all images from 16.x up to come with the ca-certificates package installed, to make this work?

I'm maintaining a list of psql distributions that do and don't work here: https://gist.github.com/jawj/57bc9d1f350ffd5250942cf24957b3a7

[ImreSamu]

Hello, I'm just a power user, and my response is not an official one.

Based on your provided gist:

Docker: Postgres official images
docker run -it --rm --name psql-check -e POSTGRES_PASSWORD=xyz postgres:17.3 bash
...
docker run -it --rm --name psql-check -e POSTGRES_PASSWORD=xyz postgres:16.7 bash

It is important to note that the https://hub.docker.com/_/postgres/ lists the currently supported postgres docker tags.

The tags 17.3 and 16.7 are no longer listed, which means they are not supported and do not receive updates ( including the ca-certificates package ).

Therefore, it is advisable to use the postgres:17 or postgres:17.4 tag for your testing.

I tested this command, and the output was as follows:

$ docker run -it --rm postgres:17 bash -c 'apt update && apt upgrade'
Get:1 http://deb.debian.org/debian bookworm InRelease [151 kB]
Get:2 http://deb.debian.org/debian bookworm-updates InRelease [55.4 kB]                     
Get:3 http://deb.debian.org/debian-security bookworm-security InRelease [48.0 kB]             
Get:4 http://deb.debian.org/debian bookworm/main amd64 Packages [8,792 kB]               
Get:5 http://apt.postgresql.org/pub/repos/apt bookworm-pgdg InRelease [129 kB]
Get:6 http://deb.debian.org/debian bookworm-updates/main amd64 Packages [13.5 kB]
Get:7 http://deb.debian.org/debian-security bookworm-security/main amd64 Packages [246 kB]
Get:8 http://apt.postgresql.org/pub/repos/apt bookworm-pgdg/main amd64 Packages [361 kB]
Fetched 9,796 kB in 2s (4,386 kB/s)                      
Reading package lists... Done
Building dependency tree... Done
Reading state information... Done
All packages are up to date.
Reading package lists... Done
Building dependency tree... Done
Reading state information... Done
Calculating upgrade... Done
0 upgraded, 0 newly installed, 0 to remove and 0 not upgraded.

[jawj]

Thanks @ImreSamu.

The tags 17.3 and 16.7 are no longer listed, which means they are not supported and do not receive updates ( including the ca-certificates package).

I originally tested on those versions, but I've since confirmed that 17.4 has the same problem. I don't doubt that 16.8 does as well.

I tested this command, and the output was as follows

The command you tested was an update followed by an upgrade, which I'm not sure is relevant here.

It is true that the apt-get install ca-certificates command works fine.

The issue is that it should not be necessary: it needs to be possible to use sslrootcert=system without manually installing this extra package (first, for convenience, and second, because most users will not know that it is the lack of this package that makes sslrootcert=system fail).

Therefore the ca-certificates package should be preinstalled in these images.

[tianon]

I would imagine this hasn't come up sooner (in the ~10 years we've been maintaining this image) because it's kind of an odd feature for a database server TBH -- the biggest implication of using the public CA apparatus for your (hopefully internal!) database instance is that your database instance is publicly accessible (or that you've jumped through hoops to get a public CA certificate for your internal server for some hard-to-understand reason), and a publicly accessible database instance is almost always a pretty bad idea. 😬

I don't disagree with your argument of convenience, but if we follow convenience for determining what ends up in the images, they'll be on the order of gigabytes instead of megabytes in size, so we have to be (understandably) a little choosy about where we draw the line, and it's really hard to keep that mostly objective. 😅

Personally, if I see "SSL error: certificate verify failed", my first assumption is that the problem is either the server I'm connecting to (which is pretty easy to verify with other tools) or my local certificates, and so I'd figure out pretty quickly that it's ca-certificates that I need, especially since that's what provides the "system" CA store, and the option I'm providing is explicitly requesting said "system" CA store.

It's not ideal from a user perspective, no, but it's also not the only use case with this image that requires users to put in additional effort to accomplish something that's otherwise pretty uncommon usage, so I'm currently leaning towards not including it.

[jawj]

OK. Thanks for your response here. I'd very much like to persuade you to change your mind. 😄

I would imagine this hasn't come up sooner (in the ~10 years we've been maintaining this image)

Roughly 18 months rather than 10 years is probably the relevant timeframe, since sslrootcert=system was introduced in Postgres 16.

I think this issue is rather chicken-and-egg: sslrootcert=system isn't widely used because it hasn't been widely supported up to now. If it was widely supported, I think it would be more widely used, and then it would provide a real improvement in connection security.

because it's kind of an odd feature for a database server ... a publicly accessible database instance is almost always a pretty bad idea

There are various platforms that provide access to a Postgres database on a public IP. Some of these probably give you direct access to the Postgres server, some of them do transparent proxying via their own software. Whether it's a good or bad idea, it's a fact, and it's good to make it as secure as reasonably possible.

At the moment, these platforms generally provide a connection URL with sslmode=require on the end. This is not a great situation, since sslmode=require instructs psql to accept any self-signed certificate with any server's name on it: in other words, it provides no protection at all from MITM attacks.

Some platforms use a certificate that has the right server name and is signed by a public CA. These platforms could provide connection strings with sslrootcert=system on the end and get decent (https-style) protection from MITM attacks. I think a key reason they don't do that is that it hasn't previously been reliable.

It would be great if this changed! As you'll see from my Gist, on the vanilla Linux distros I've tested, sslrootcert=system works already. On the Mac, Homebrew always worked, Postgres.app and MacPorts have just been fixed, and EnterpriseDB is working on it. On Windows, WSL (based on an underlying Linux distro) works, and again EnterpriseDB is working on it. So we are getting close ...

if we follow convenience for determining what ends up in the images, they'll be on the order of gigabytes instead of megabytes in size, so we have to be (understandably) a little choosy about where we draw the line

OK, so let's not call it convenience, let's call it: shipping a psql binary with all features working! I don't think that's too much to expect from an official Postgres Docker image? 😀

From what I can see, the compressed Docker image is around 150MB, and the compressed certificates are about 150KB (or an addition of about 0.1%). So the added size is both absolutely and relatively tiny.

if I see "SSL error: certificate verify failed", my first assumption is that the problem is either the server I'm connecting to (which is pretty easy to verify with other tools) or my local certificates, and so I'd figure out pretty quickly that it's ca-certificates that I need

If the error message was: "SSL error: client CA root certificates not installed, please install the ca-certificates package", then maybe it would be fair enough not to do anything about it.

But as you point out, from the error we actually get it's not even clear which end of the connection has had a problem.

I have some sysadmin experience and I've previously spent some time trying to get sslrootcert=system working on different psql installations. Even then, with a pretty good guess that missing root certs were going to be the problem, it took me several annoying minutes of Googling to figure out:

• How do I install stuff on this particular Linux distro (is it apt, zypper, etc)?
• What's the name of the thing I need to apt-get here?
• Why doesn't apt-get install ca-certificates work? (It turns out you have to run apt-get update first, but my initial thought was that I had the wrong package name).

I suspect most people would take longer than this, and many would give up well before fixing it.

So ... can I ask you very nicely to reconsider? 🙏

[ImreSamu]

Hi @jawj ,

Unfortunately, sslrootcert=system currently doesn't work on the psql binaries shipped in these Docker images:

Updating outdated CA certificates (e.g., via update-ca-certificates ) is not straightforward in these images.

So I recommend using the CA certificates from the host system.
( On Debian/Ubuntu-based hosts, this approach is quite simple. )

Reproducing the Issue ( ~ SSL error: certificate verify failed )

$ docker run -it --rm postgres:17 bash
root@75d26438a858:/# psql 'postgresql://ImreSamu:[email protected]/tr2?sslrootcert=system'
psql: error: connection to server at "ep-crimson-fog-207560-pooler.eu-central-1.aws.neon.tech" (2a05:d014:aa3:7a20:baca:a828:c69b:8952), port 5432 failed: SSL error: certificate verify failed

Option 1: Mounting the entire /etc/ssl/certs directory ( ubuntu host ) ✅

$ docker run -it --rm -v /etc/ssl/certs:/etc/ssl/certs:ro postgres:17 bash
root@1bf5727115de:/# psql 'postgresql://ImreSamu:[email protected]/tr2?sslrootcert=system'
psql (17.4 (Debian 17.4-1.pgdg120+2), server 15.12)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off, ALPN: postgresql)
Type "help" for help.

tr2=> 

Option 2: Mounting only the ca-certificates.crt file ( ubuntu host ) ✅

$ docker run -it --rm --volume=/etc/ssl/certs/ca-certificates.crt:/etc/ssl/certs/ca-certificates.crt:ro postgres:17 bash
root@c04e6307c179:/# psql 'postgresql://ImreSamu:[email protected]/tr2?sslrootcert=system'
psql (17.4 (Debian 17.4-1.pgdg120+2), server 15.12)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off, ALPN: postgresql)
Type "help" for help.

tr2=> 

References:

• https://stackoverflow.com/questions/26028971/docker-container-ssl-certificates
• https://stackoverflow.com/questions/73620124/update-ca-certificates-command-not-found-despite-ca-certificates-being-installed

If this issue affects many users, it might be worth documenting this volume-based SSL approach as a best practice in the official PostgreSQL Docker documentation:

• https://github.com/docker-library/docs/tree/master/postgres

[tianon]

In most distributions/packaging contexts, this dependency relationship is what's often called "Recommends": https://www.debian.org/doc/debian-policy/ch-relationships.html#binary-dependencies-depends-recommends-suggests-enhances-pre-depends

This declares a strong, but not absolute, dependency.

The Recommends field should list packages that would be found together with this one in all but unusual installations.

To put that another way, the point of Recommends dependencies is that not everyone needs them, but if they're just Depends, then users who don't need them can't opt-out, and that might have unpleasant side effects (like being difficult to update the CA certificates bundle pre-included in the image, for example). Unfortunately, we don't have a clean way to express dependencies like this in the container ecosystem because when you use a container image, you're getting one large bundle with everything, not installing all the bits separately, so we have to be very choosy about what goes into that bundle because users removing things from it afterwards is not really feasible (and updating one is often a whole process, but one that's easier if there's less to review in the update).

As of this writing, there is no direct dependency relation between postgresql-client and ca-certificates in Debian (or Ubuntu) or the packages provided/maintained by upstream (using the not-quite-released-but-likely-this-year Debian Trixie because that's the only place in Debian proper we have PostgreSQL 16+ yet):

$ docker run -it --rm --pull=always debian:trixie
trixie: Pulling from library/debian
692a22a94aa3: Pull complete 
Digest: sha256:2ad7bf4cde8aafcdd2711d03cdd55faaaa0d0f833510ef1c731f885e8ef5b445
Status: Downloaded newer image for debian:trixie
root@eb2a96e0a8e4:/# apt update -qq                      
38 packages can be upgraded. Run 'apt list --upgradable' to see them.
root@eb2a96e0a8e4:/# apt install -y postgresql-client # not using "--no-install-recommends" because that's where a "ca-certificates" dependency would likely be
Installing:                     
  postgresql-client

Installing dependencies:
  krb5-locales        libkrb5support0   libsasl2-modules-db
  libcom-err2         libldap-common    netbase
  libgdbm-compat4t64  libldap2          perl
  libgdbm6t64         libperl5.40       perl-modules-5.40
  libgssapi-krb5-2    libpq5            postgresql-client-17
  libk5crypto3        libreadline8t64   postgresql-client-common
  libkeyutils1        libsasl2-2        readline-common
  libkrb5-3           libsasl2-modules  sensible-utils

Suggested packages:
  gdbm-l10n                          perl-doc
  krb5-doc                           libterm-readline-gnu-perl
  krb5-user                          | libterm-readline-perl-perl
  libsasl2-modules-gssapi-mit        make
  | libsasl2-modules-gssapi-heimdal  libtap-harness-archive-perl
  libsasl2-modules-ldap              postgresql-17
  libsasl2-modules-otp               postgresql-doc-17
  libsasl2-modules-sql               readline-doc

Summary:
  Upgrading: 0, Installing: 25, Removing: 0, Not Upgrading: 38
  Download size: 11.4 MB
  Space needed: 68.1 MB / 663 GB available

Get:1 http://deb.debian.org/debian trixie/main amd64 netbase all 6.4 [12.8 kB]
Get:2 http://deb.debian.org/debian trixie/main amd64 readline-common all 8.2-6 [69.4 kB]
Get:3 http://deb.debian.org/debian trixie/main amd64 sensible-utils all 0.0.24 [24.8 kB]
Get:4 http://deb.debian.org/debian trixie/main amd64 krb5-locales all 1.21.3-5 [101 kB]
Get:5 http://deb.debian.org/debian trixie/main amd64 perl-modules-5.40 all 5.40.1-2 [3017 kB]
Get:6 http://deb.debian.org/debian trixie/main amd64 libgdbm6t64 amd64 1.24-2 [75.2 kB]
Get:7 http://deb.debian.org/debian trixie/main amd64 libgdbm-compat4t64 amd64 1.24-2 [50.3 kB]
Get:8 http://deb.debian.org/debian trixie/main amd64 libperl5.40 amd64 5.40.1-2 [4315 kB]
Get:9 http://deb.debian.org/debian trixie/main amd64 perl amd64 5.40.1-2 [267 kB]
Get:10 http://deb.debian.org/debian trixie/main amd64 libcom-err2 amd64 1.47.2-1 [24.0 kB]
Get:11 http://deb.debian.org/debian trixie/main amd64 libkrb5support0 amd64 1.21.3-5 [33.0 kB]
Get:12 http://deb.debian.org/debian trixie/main amd64 libk5crypto3 amd64 1.21.3-5 [81.5 kB]
Get:13 http://deb.debian.org/debian trixie/main amd64 libkeyutils1 amd64 1.6.3-4 [9092 B]
Get:14 http://deb.debian.org/debian trixie/main amd64 libkrb5-3 amd64 1.21.3-5 [326 kB]
Get:15 http://deb.debian.org/debian trixie/main amd64 libgssapi-krb5-2 amd64 1.21.3-5 [138 kB]
Get:16 http://deb.debian.org/debian trixie/main amd64 libldap-common all 2.6.9+dfsg-2 [34.8 kB]
Get:17 http://deb.debian.org/debian trixie/main amd64 libsasl2-modules-db amd64 2.1.28+dfsg1-9 [19.8 kB]
Get:18 http://deb.debian.org/debian trixie/main amd64 libsasl2-2 amd64 2.1.28+dfsg1-9 [57.5 kB]
Get:19 http://deb.debian.org/debian trixie/main amd64 libldap2 amd64 2.6.9+dfsg-2 [194 kB]
Get:20 http://deb.debian.org/debian trixie/main amd64 libpq5 amd64 17.4-1+b1 [226 kB]
Get:21 http://deb.debian.org/debian trixie/main amd64 libreadline8t64 amd64 8.2-6 [169 kB]
Get:22 http://deb.debian.org/debian trixie/main amd64 libsasl2-modules amd64 2.1.28+dfsg1-9 [66.7 kB]
Get:23 http://deb.debian.org/debian trixie/main amd64 postgresql-client-common all 274 [46.5 kB]
Get:24 http://deb.debian.org/debian trixie/main amd64 postgresql-client-17 amd64 17.4-1+b1 [2038 kB]
Get:25 http://deb.debian.org/debian trixie/main amd64 postgresql-client all 17+274 [13.5 kB]
Fetched 11.4 MB in 2s (5920 kB/s)            

... repetitive installation output removed for brevity ...

Setting up postgresql-client (17+274) ...
Processing triggers for libc-bin (2.40-7) ...
root@eb2a96e0a8e4:/# psql 'postgresql://user:[email protected]/neondb?sslrootcert=system'
psql: error: connection to server at "ep-broad-dew-xyz.us-east-2.aws.neon.tech" (3.143.47.40), port 5432 failed: SSL error: certificate verify failed

Here's the same on a very stock, minimal Ubuntu 24.04, where I get an even worse error message (I guess that's probably something that changed for the better in PostgreSQL 17 as this is installing 16?):

$ docker run -it --rm --pull=always ubuntu:24.04
24.04: Pulling from library/ubuntu
5a7813e071bf: Pull complete 
Digest: sha256:72297848456d5d37d1262630108ab308d3e9ec7ed1c3286a32fe09856619a782
Status: Downloaded newer image for ubuntu:24.04
root@bd0716c57c63:/# apt update -qq
18 packages can be upgraded. Run 'apt list --upgradable' to see them.
root@bd0716c57c63:/# apt install -y postgresql-client
Reading package lists... Done
Building dependency tree... Done
Reading state information... Done
The following additional packages will be installed:
  krb5-locales libgdbm-compat4t64 libgdbm6t64 libgssapi-krb5-2 libk5crypto3
  libkeyutils1 libkrb5-3 libkrb5support0 libldap-common libldap2
  libperl5.38t64 libpq5 libreadline8t64 libsasl2-2 libsasl2-modules
  libsasl2-modules-db netbase perl perl-base perl-modules-5.38
  postgresql-client-16 postgresql-client-common readline-common
Suggested packages:
  gdbm-l10n krb5-doc krb5-user libsasl2-modules-gssapi-mit
  | libsasl2-modules-gssapi-heimdal libsasl2-modules-ldap libsasl2-modules-otp
  libsasl2-modules-sql perl-doc libterm-readline-gnu-perl
  | libterm-readline-perl-perl make libtap-harness-archive-perl postgresql-16
  postgresql-doc-16 readline-doc
The following NEW packages will be installed:
  krb5-locales libgdbm-compat4t64 libgdbm6t64 libgssapi-krb5-2 libk5crypto3
  libkeyutils1 libkrb5-3 libkrb5support0 libldap-common libldap2
  libperl5.38t64 libpq5 libreadline8t64 libsasl2-2 libsasl2-modules
  libsasl2-modules-db netbase perl perl-modules-5.38 postgresql-client
  postgresql-client-16 postgresql-client-common readline-common
The following packages will be upgraded:
  perl-base
1 upgraded, 23 newly installed, 0 to remove and 17 not upgraded.
Need to get 12.8 MB of archives.
After this operation, 59.2 MB of additional disk space will be used.
Get:1 http://archive.ubuntu.com/ubuntu noble-updates/main amd64 perl-base amd64 5.38.2-3.2build2.1 [1823 kB]
Get:2 http://archive.ubuntu.com/ubuntu noble-updates/main amd64 perl-modules-5.38 all 5.38.2-3.2build2.1 [3110 kB]
Get:3 http://archive.ubuntu.com/ubuntu noble/main amd64 libgdbm6t64 amd64 1.23-5.1build1 [34.4 kB]
Get:4 http://archive.ubuntu.com/ubuntu noble/main amd64 libgdbm-compat4t64 amd64 1.23-5.1build1 [6710 B]
Get:5 http://archive.ubuntu.com/ubuntu noble-updates/main amd64 libperl5.38t64 amd64 5.38.2-3.2build2.1 [4867 kB]
Get:6 http://archive.ubuntu.com/ubuntu noble-updates/main amd64 perl amd64 5.38.2-3.2build2.1 [231 kB]
Get:7 http://archive.ubuntu.com/ubuntu noble-updates/main amd64 krb5-locales all 1.20.1-6ubuntu2.5 [14.5 kB]
Get:8 http://archive.ubuntu.com/ubuntu noble-updates/main amd64 libkrb5support0 amd64 1.20.1-6ubuntu2.5 [34.1 kB]
Get:9 http://archive.ubuntu.com/ubuntu noble-updates/main amd64 libk5crypto3 amd64 1.20.1-6ubuntu2.5 [82.0 kB]
Get:10 http://archive.ubuntu.com/ubuntu noble/main amd64 libkeyutils1 amd64 1.6.3-3build1 [9490 B]
Get:11 http://archive.ubuntu.com/ubuntu noble-updates/main amd64 libkrb5-3 amd64 1.20.1-6ubuntu2.5 [347 kB]
Get:12 http://archive.ubuntu.com/ubuntu noble-updates/main amd64 libgssapi-krb5-2 amd64 1.20.1-6ubuntu2.5 [143 kB]
Get:13 http://archive.ubuntu.com/ubuntu noble/main amd64 readline-common all 8.2-4build1 [56.5 kB]
Get:14 http://archive.ubuntu.com/ubuntu noble/main amd64 libreadline8t64 amd64 8.2-4build1 [153 kB]
Get:15 http://archive.ubuntu.com/ubuntu noble/main amd64 netbase all 6.4 [13.1 kB]
Get:16 http://archive.ubuntu.com/ubuntu noble-updates/main amd64 libldap-common all 2.6.7+dfsg-1~exp1ubuntu8.2 [31.7 kB]
Get:17 http://archive.ubuntu.com/ubuntu noble-updates/main amd64 libsasl2-modules-db amd64 2.1.28+dfsg1-5ubuntu3.1 [20.4 kB]
Get:18 http://archive.ubuntu.com/ubuntu noble-updates/main amd64 libsasl2-2 amd64 2.1.28+dfsg1-5ubuntu3.1 [53.2 kB]
Get:19 http://archive.ubuntu.com/ubuntu noble-updates/main amd64 libldap2 amd64 2.6.7+dfsg-1~exp1ubuntu8.2 [196 kB]
Get:20 http://archive.ubuntu.com/ubuntu noble-updates/main amd64 libpq5 amd64 16.8-0ubuntu0.24.04.1 [142 kB]
Get:21 http://archive.ubuntu.com/ubuntu noble-updates/main amd64 libsasl2-modules amd64 2.1.28+dfsg1-5ubuntu3.1 [69.9 kB]
Get:22 http://archive.ubuntu.com/ubuntu noble-updates/main amd64 postgresql-client-common all 257build1.1 [36.4 kB]
Get:23 http://archive.ubuntu.com/ubuntu noble-updates/main amd64 postgresql-client-16 amd64 16.8-0ubuntu0.24.04.1 [1292 kB]
Get:24 http://archive.ubuntu.com/ubuntu noble-updates/main amd64 postgresql-client all 16+257build1.1 [11.6 kB]
Fetched 12.8 MB in 11s (1127 kB/s)                                             
( again, removing repetitive output )
Setting up postgresql-client (16+257build1.1) ...
Processing triggers for libc-bin (2.39-0ubuntu8.3) ...
root@bd0716c57c63:/# psql 'postgresql://user:[email protected]/neondb?sslrootcert=system'
psql: error: connection to server at "ep-broad-dew-xyz.us-east-2.aws.neon.tech" (3.23.186.13), port 5432 failed: SSL error: unregistered scheme

(I verified afterwards that installing ca-certificates in both cases "fixes" it and I get the appropriate ERROR: password authentication failed for user 'user' instead)

In other words, any result where this is working on Debian or Ubuntu is incidental (thanks to ca-certificates already being installed for other reasons / via other means), not intentional as a relationship/result of installing postgresql-client.

I really like the simplicity of mounting the bundle from the host, and documenting that does sound like a good solution here.

[jawj]

In most distributions/packaging contexts, this dependency relationship is what's often called "Recommends"

I agree that Recommends is pretty much spot-on for the nature of the relationship here.

So I asked the Debian Postgres maintainers to add ca-certificates as a Recommends, and they've now done so.

Thus sslrootcert=system should work out-of-the-box on Debian in future, unless you go out of your way to do a --no-install-recommends.

I really like the simplicity of mounting the bundle from the host, and documenting that does sound like a good solution here.

This is an option if you're running Docker on a Linux host, but presumably not much help if you're running Docker from Windows.

I still take the view that including ca-certificates in the Docker image, so that the sslrootcert=system connection option just works, is the right solution.