4

I've started getting errors when getting certificate keys from the postgres website:

wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | apt-key add -
#6 0.310 --2021-10-04 20:56:35--  https://www.postgresql.org/media/keys/ACCC4CF8.asc
#6 0.315 Resolving www.postgresql.org (www.postgresql.org)... 87.238.57.232, 72.32.157.230, 217.196.149.50, ...
#6 0.318 Connecting to www.postgresql.org (www.postgresql.org)|87.238.57.232|:443... connected.
#6 0.902 ERROR: The certificate of 'www.postgresql.org' is not trusted.
#6 0.902 ERROR: The certificate of 'www.postgresql.org' has expired.

Which I tried working around by using --no-check-certificate, but then I get a different issue when installing the postgres libraries:

apt-get update \
    && DEBIAN_FRONTEND=noninteractive apt-get install -y \
    postgresql-client-common postgresql-client-13 \
    && rm -rf /var/lib/apt/lists/*

#9 2.579   404  Not Found [IP: 147.75.85.69 80]
#9 2.606 Fetched 7888 kB in 2s (3486 kB/s)
#9 2.606 Reading package lists...
#9 2.974 W: The repository 'http://apt.postgresql.org/pub/repos/apt -pgdg Release' does not have a Release file.
#9 2.974 E: Failed to fetch http://apt.postgresql.org/pub/repos/apt/dists/-pgdg/main/binary-arm64/Packages  404  Not Found [IP: 147.75.85.69 80]
#9 2.974 E: Some index files failed to download. They have been ignored, or old ones used instead.

Is there another way to deal with this? It suddenly started happening today.

3
  • 1
    What system/distro is this, and are you using standard repos? What version of ca-certificates and of openssl/libssl are current for your system? If this is Debian family does /etc/ssl/certs contain entries for DST_Root_CA_X3 or ISRG_Root_X1 or both? You need to have ISRG present, and if you have OpenSSL below 1.1.0 you must also have DST absent, and the standard package(s) don't do that yet. You can patch this; see serverfault.com/questions/1079199/… . Oct 5, 2021 at 2:31
  • It's a docker image actually, which is based on Debian hub.docker.com/_/ruby Oct 5, 2021 at 16:05
  • I hit the similar issue with openssl 1.1.1c , you may be interested to see this article
    – Ham
    Nov 30, 2021 at 4:55

3 Answers 3

6

PostgreSQL.org uses certificates signed by LetsEncrypt (I've just checked) and validated against a root certificate.

That certificate expired. It has been renewed, but you probably do not have that CA certificate in your system, so your system can't verify the new X1 certificate, and therefore rejects it.

The same happens for the http repositories, which as far as I'm able to see, do automatic HTTPS upgrade. I don't see this upgrade reported by apt, so my guess is that it tries but fails silently, and you get a "No such file" error while the actual error is more "Could not connect to retrieve the file". You can verify by instructing apt to ignore invalid certificates.

But what you need to do is to update your CA certificates. You probably will notice a "ca-certificate" package among the updates. Once that's in place, everything else will start working again.

Force install of ISRG X1 certificate

  • Get the certificate (e.g. curl -k https://letsencrypt.org/certs/isrgrootx1.pem > isrgrootx1.pem)
  • Extract the CRT with openssl crl2pkcs7 -nocrl -certfile isrgrootx1.pem | openssl pkcs7 -print_certs -out isrgrootx1.crt
  • Copy it to /usr/local/share/ca-certificates/
  • Run sudo update-ca-certificates.

If it reports that there is a duplicate certificate, find it in /etc/ssl/certs and check it out. If it has, "subject=C = US, O = Internet Security Research Group, CN = ISRG Root X1", then you already had the ISGR X1 cert installed, so the problem is elsewhere.

2
  • I've tried upgrading ca-certificates but didn't have any luck. Is there another package I also need to upgrade? Thank you very much for the info! Oct 4, 2021 at 22:43
  • @CarlosMartinez I've added a butcherous way of doing, I think, the same thing. Let me know how it goes.
    – LSerni
    Oct 5, 2021 at 11:53
5

Credit to LSerni for pointing me in the right direction. I needed one more step in order to solve the issue in my case: explicitly disabling mozilla/DST_Root_CA_X3.crt (using sed, see below) in /etc/ca-certificates.conf.

Also, an interesting note is that I couldn't reproduce the bug in Docker using debian:stretch, but I can reproduce it using python:3.7.8-stretch.

The commands to reproduce the bug are essentially just these 3:

apt-get update && apt-get install -y lsb-release wget
sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | apt-key add -

Running the commands above will produce the output gpg: no valid OpenPGP data found., which is the underlying issue.

The image ID, for reproducing the bug:

me@home$ docker images
REPOSITORY                        TAG             IMAGE ID       CREATED         SIZE
python                            3.7.8-stretch   b64658cdf594   14 months ago   902MB

Output reproducing the bug:

me@home$ docker run -it python:3.7.8-stretch /bin/bash
root@771da843c08d:/# apt-get update && apt-get install -y lsb-release wget
Ign:1 http://deb.debian.org/debian stretch InRelease
Get:2 http://security.debian.org/debian-security stretch/updates InRelease [53.0 kB]
Get:3 http://deb.debian.org/debian stretch-updates InRelease [93.6 kB]
Get:4 http://deb.debian.org/debian stretch Release [118 kB]           
Get:5 http://deb.debian.org/debian stretch Release.gpg [3177 B]                  
Get:6 http://security.debian.org/debian-security stretch/updates/main amd64 Packages [720 kB]
Get:7 http://deb.debian.org/debian stretch/main amd64 Packages [7080 kB]         
Fetched 8067 kB in 1s (4448 kB/s)                     
Reading package lists... Done
Reading package lists... Done
Building dependency tree       
Reading state information... Done
wget is already the newest version (1.18-5+deb9u3).
Suggested packages:
  lsb
The following NEW packages will be installed:
  distro-info-data lsb-release
0 upgraded, 2 newly installed, 0 to remove and 106 not upgraded.
Need to get 32.9 kB of archives.
After this operation, 78.8 kB of additional disk space will be used.
Get:1 http://deb.debian.org/debian stretch/main amd64 distro-info-data all 0.36 [5810 B]
Get:2 http://deb.debian.org/debian stretch/main amd64 lsb-release all 9.20161125 [27.1 kB]
Fetched 32.9 kB in 0s (274 kB/s)       
debconf: delaying package configuration, since apt-utils is not installed
Selecting previously unselected package distro-info-data.
(Reading database ... 30586 files and directories currently installed.)
Preparing to unpack .../distro-info-data_0.36_all.deb ...
Unpacking distro-info-data (0.36) ...
Selecting previously unselected package lsb-release.
Preparing to unpack .../lsb-release_9.20161125_all.deb ...
Unpacking lsb-release (9.20161125) ...
Setting up distro-info-data (0.36) ...
Setting up lsb-release (9.20161125) ...
root@771da843c08d:/# sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
root@771da843c08d:/# wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | apt-key add -
gpg: no valid OpenPGP data found.

The output (including the solution step) showing the problem resolved:

root@479753dc1044:/# sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
root@479753dc1044:/# # Remove the DST X3 root certificate, per
root@479753dc1044:/# # https://medium.com/geekculture/will-you-be-impacted-by-letsencrypt-dst-root-ca-x3-expiration-d54a018df257
root@479753dc1044:/# sed -i 's/mozilla\/DST_Root_CA_X3.crt/!mozilla\/DST_Root_CA_X3.crt/g' /etc/ca-certificates.conf
root@479753dc1044:/# update-ca-certificates
Updating certificates in /etc/ssl/certs...
0 added, 1 removed; done.
Running hooks in /etc/ca-certificates/update.d...
done.
root@479753dc1044:/# wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | apt-key add -
OK
root@479753dc1044:/# apt-get update
Ign:1 http://deb.debian.org/debian stretch InRelease
Hit:2 http://security.debian.org/debian-security stretch/updates InRelease
Hit:3 http://deb.debian.org/debian stretch-updates InRelease
Hit:4 http://deb.debian.org/debian stretch Release 
Get:6 http://apt.postgresql.org/pub/repos/apt stretch-pgdg InRelease [81.6 kB]
Get:7 http://apt.postgresql.org/pub/repos/apt stretch-pgdg/main amd64 Packages [270 kB]
Fetched 352 kB in 1s (220 kB/s)  
Reading package lists... Done

Once you've applied the fix above, you can continue (for example, installing an old Postgres client).

root@479753dc1044:/# apt-get -y install postgresql-client-12
Reading package lists... Done
Building dependency tree       
Reading state information... Done
The following additional packages will be installed:
  libpq-dev libpq5 pgdg-keyring postgresql-client-common
Suggested packages:
  postgresql-doc-14 postgresql-12 postgresql-doc-12
The following NEW packages will be installed:
  pgdg-keyring postgresql-client-12 postgresql-client-common
The following packages will be upgraded:
  libpq-dev libpq5
2 upgraded, 3 newly installed, 0 to remove and 104 not upgraded.
Need to get 1823 kB of archives.
After this operation, 6032 kB of additional disk space will be used.
Get:1 http://apt.postgresql.org/pub/repos/apt stretch-pgdg/main amd64 libpq-dev amd64 14.0-1.pgdg90+1 [135 kB]
Get:2 http://apt.postgresql.org/pub/repos/apt stretch-pgdg/main amd64 libpq5 amd64 14.0-1.pgdg90+1 [169 kB]
Get:3 http://apt.postgresql.org/pub/repos/apt stretch-pgdg/main amd64 pgdg-keyring all 2018.2 [10.7 kB]
Get:4 http://apt.postgresql.org/pub/repos/apt stretch-pgdg/main amd64 postgresql-client-common all 231.pgdg90+1 [91.3 kB]
Get:5 http://apt.postgresql.org/pub/repos/apt stretch-pgdg/main amd64 postgresql-client-12 amd64 12.8-1.pgdg90+1 [1417 kB]
Fetched 1823 kB in 1s (1422 kB/s)              
debconf: delaying package configuration, since apt-utils is not installed
(Reading database ... 30604 files and directories currently installed.)
Preparing to unpack .../libpq-dev_14.0-1.pgdg90+1_amd64.deb ...
Unpacking libpq-dev (14.0-1.pgdg90+1) over (9.6.17-0+deb9u1) ...
Preparing to unpack .../libpq5_14.0-1.pgdg90+1_amd64.deb ...
Unpacking libpq5:amd64 (14.0-1.pgdg90+1) over (9.6.17-0+deb9u1) ...
Selecting previously unselected package pgdg-keyring.
Preparing to unpack .../pgdg-keyring_2018.2_all.deb ...
Unpacking pgdg-keyring (2018.2) ...
Selecting previously unselected package postgresql-client-common.
Preparing to unpack .../postgresql-client-common_231.pgdg90+1_all.deb ...
Unpacking postgresql-client-common (231.pgdg90+1) ...
Selecting previously unselected package postgresql-client-12.
Preparing to unpack .../postgresql-client-12_12.8-1.pgdg90+1_amd64.deb ...
Unpacking postgresql-client-12 (12.8-1.pgdg90+1) ...
Setting up libpq5:amd64 (14.0-1.pgdg90+1) ...
Processing triggers for libc-bin (2.24-11+deb9u4) ...
Setting up pgdg-keyring (2018.2) ...
Removing apt.postgresql.org key from trusted.gpg: OK
Setting up libpq-dev (14.0-1.pgdg90+1) ...
Setting up postgresql-client-common (231.pgdg90+1) ...
Setting up postgresql-client-12 (12.8-1.pgdg90+1) ...
update-alternatives: using /usr/share/postgresql/12/man/man1/psql.1.gz to provide /usr/share/man/man1/psql.1.gz (psql.1.gz) in auto mode
1

Root cause

As answered by LSerni recently a root certificate expired and caused some shenanigans.

Here is the official answer from Letsencrypt:

The main determining factor for whether a platform can validate Let’s Encrypt certificates is whether that platform trusts ISRG’s “ISRG Root X1” certificate. Prior to September 2021, some platforms could validate our certificates even though they don’t include ISRG Root X1, because they trusted IdenTrust’s “DST Root CA X3” certificate. From October 2021 onwards, only those platforms that trust ISRG Root X1 will validate Let’s Encrypt certificates (with the exception of Android).

More on https://letsencrypt.org/docs/certificate-compatibility/

All debian version >= jessie should include the required certificates to recognize Letsencrypt. (after update)

stretch should now already include the ISRG Root X1 certificate which is listed there.

To update try to use:

apt update
apt install -y libssl1.0.2 ca-certificates

More on this here https://serverfault.com/questions/1079199/client-on-debian-9-erroneously-reports-expired-certificate-for-letsencrypt-issue

Side notes on Postgresql installation

Note that documentation on https://wiki.postgresql.org/wiki/Apt#Quickstart was updated on 20 August 2021‎ stating "apt-key is deprecated".

You probably want to replace:

wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | apt-key add -

By:

curl https://www.postgresql.org/media/keys/ACCC4CF8.asc | gpg --dearmor | sudo tee /etc/apt/trusted.gpg.d/apt.postgresql.org.gpg

Plus, you don't need to install lsb_release on stretch, you can use the /etc/os-release file, example:

OS_CODENAME=$(awk -F= '$1=="VERSION_CODENAME" { print $2 ;}' /etc/os-release)

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .