Difficulty with postgresql verifying CA

Help
1

I'm not trying to get too much into the weeds on this question and if it's inappropriate for this forum please let me know.

My domain is: gohilton.com

I've recently setup a pair of postgresql databases in a primary/secondary configuration with replication using LE certs.

LE certs were obtained using acme.sh which I've used on many different occasions.

As part of the replication process whereby WAL files are replicated from the primary to the secondary, the secondary can communicate with the primary over TLS and there are options of "verify-CA" or "verify-full" for the secondrary to verify the SSL certs presented by the primary server. This setup is not unique and is very common for a bunch of different programs.

So my issue is with verification. Let's encrypt presents me with 4 certificates -- key.pem, cert.pem, fullchain.pem and ca.pem. For the primary node, I'm using the key.pem and fullchain.pem as the SSL certificates (similar to how you would with nginx).

I can manually verify the certs on the CLI using the following commands.
If I visually want to see the text representation of the fullchain.pem file I use:

openssl storeutl -noout -text -certs fullchain.pem

And if I want to verify the fullchain.pem against the ca.pem file I use the following:

 openssl verify -CAfile ca.pem fullchain.pem
fullchain.pem: OK

The problem arises with postgres replication. For the sslrootcert I'm using the ca.pem file given by let'sencrypt. The specific command I'm using is the following:

primary_conninfo = 'user=replication password=''***'' host=archbw-postgres.gohilton.com hostaddr=10.0.1.81 port=5432 sslmode=verify-ca sslrootcert=/var/lib/postgres/data/ca.pem sslsni=1 ssl_min_protocol_version=TLSv1.3 channel_binding=prefer sslnegotiation=postgres load_balance_hosts=disable'

When I run this command, I get an error saying: certificate validation failed.

However if I choose a different sslrootcertificate -- specifically /etc/ssl/certs/ca-certificates.crt (which is pretty much the default in most linux distro's I've worked with) -- the certificate validation succeeds.

Why is the general ca-certificates.crt proven valid as a rootCA in this case where as the ca.pem given by let's encrypt not?

Of interests, the text representation of the ca.pem file given is the following:

Certificate:
    Data:
        Version: 3 (0x2)
        Serial Number:
            83:8f:6c:63:ce:b1:39:8c:62:06:62:83:15:c9:fd:de
        Signature Algorithm: sha256WithRSAEncryption
        Issuer: C=US, O=Internet Security Research Group, CN=ISRG Root X1
        Validity
            Not Before: Mar 13 00:00:00 2024 GMT
            Not After : Mar 12 23:59:59 2027 GMT
        Subject: C=US, O=Let's Encrypt, CN=E5
        Subject Public Key Info:
            Public Key Algorithm: id-ecPublicKey
                Public-Key: (384 bit)
                pub:
                    04:0d:0b:3a:8a:6b:61:8e:b6:ef:dc:5f:58:e7:c6:
                    42:45:54:ab:63:f6:66:61:48:0a:2e:59:75:b4:81:
                    02:37:50:b7:3f:16:79:dc:98:ec:a1:28:97:72:20:
                    1c:2c:cf:d5:7c:52:20:4e:54:78:5b:84:14:6b:c0:
                    90:ae:85:ec:c0:51:41:3c:5a:87:7f:06:4d:d4:fe:
                    60:d1:fa:6c:2d:e1:7d:95:10:88:a2:08:54:0f:99:
                    1a:4c:e6:ea:0a:ac:d8
                ASN1 OID: secp384r1
                NIST CURVE: P-384
        X509v3 extensions:
            X509v3 Key Usage: critical
                Digital Signature, Certificate Sign, CRL Sign
            X509v3 Extended Key Usage:
                TLS Web Client Authentication, TLS Web Server Authentication
            X509v3 Basic Constraints: critical
                CA:TRUE, pathlen:0
            X509v3 Subject Key Identifier:
                9F:2B:5F:CF:3C:21:4F:9D:04:B7:ED:2B:2C:C4:C6:70:8B:D2:D7:0D
            X509v3 Authority Key Identifier:
                79:B4:59:E6:7B:B6:E5:E4:01:73:80:08:88:C8:1A:58:F6:E9:9B:6E
            Authority Information Access:
                CA Issuers - URI:http://x1.i.lencr.org/
            X509v3 Certificate Policies:
                Policy: 2.23.140.1.2.1
            X509v3 CRL Distribution Points:
                Full Name:
                  URI:http://x1.c.lencr.org/

    Signature Algorithm: sha256WithRSAEncryption
    Signature Value:
        1f:72:9d:34:45:42:41:da:a4:d0:b2:b2:b8:d2:26:4c:a7:51:
        25:8d:42:da:ec:36:48:96:a3:ba:1a:a4:c8:63:d8:f0:2f:b3:
        ce:cb:9f:67:e9:a0:9e:19:ea:d4:0d:8a:55:03:92:ca:43:84:
        9d:46:f1:d5:cc:ba:df:ba:c1:02:28:71:f7:ba:fe:6d:cc:1b:
        64:ce:ac:4c:32:1a:12:b8:91:fc:f2:e4:e8:b2:ac:f4:17:b4:
        ba:85:71:80:e2:83:72:91:bd:b2:f0:f7:dc:9f:86:f4:b7:1f:
        bf:52:bd:96:e0:e6:49:38:06:e9:73:45:20:de:6f:7c:8e:60:
        b3:f9:4c:3f:2a:23:10:c7:48:cc:af:5b:95:c9:76:ff:5b:ca:
        c4:ef:16:18:27:23:be:c4:35:9c:9f:cf:c2:df:0b:41:90:5f:
        38:5c:95:5c:ff:2e:6c:0a:7f:6a:ed:dd:73:81:0a:58:6f:4c:
        3b:9c:dc:c7:5a:93:f7:e3:57:44:67:55:5b:11:af:98:11:51:
        01:a8:dc:88:c7:d7:30:4d:59:b8:69:a4:df:f1:8e:92:80:0c:
        ed:99:23:66:69:5e:ca:89:0f:d4:b1:b3:99:f2:5c:51:df:6c:
        ed:e7:ae:d7:ff:7f:7a:0e:57:95:77:7f:e7:91:ad:62:30:0c:
        f8:2e:03:1b:98:bb:79:a3:6a:72:6d:85:fb:2c:58:20:fb:7a:
        71:b6:ed:61:53:49:08:67:c7:5a:a1:c4:43:81:58:4a:d5:32:
        16:7b:fc:b2:3c:aa:53:cc:a9:81:96:8d:27:d6:95:71:64:88:
        08:b3:88:13:5f:d0:bf:fe:e8:2a:c9:d9:09:62:7d:db:ac:14:
        e9:1a:86:d4:e6:0f:18:e8:b5:ce:e0:01:84:bc:3a:d5:cb:8f:
        54:34:f6:f2:74:12:fd:ee:b3:f7:97:09:5e:ad:1e:2b:50:5c:
        68:9e:9f:25:9b:26:6e:34:60:0f:9a:77:9a:f1:1f:e6:f7:50:
        33:b3:02:12:f5:34:b4:76:ec:c7:62:39:98:71:c9:a0:00:47:
        6f:c2:95:06:05:a9:fe:57:17:19:68:96:69:e3:b2:07:b4:4f:
        f8:e7:c3:b6:f8:b6:3a:c6:a9:c5:78:95:ee:f3:55:b3:b7:cc:
        96:b4:63:63:58:e8:29:aa:a6:9b:27:27:06:f0:2a:d7:80:04:
        6e:dc:8b:b1:57:ce:4b:ae:81:f1:aa:64:78:55:f6:35:8e:17:
        3c:46:15:e1:94:82:7b:c5:47:3e:b7:6b:11:19:36:c0:82:c6:
        dd:3f:c4:1a:64:88:90:26:15:50:c4:a7:8e:62:5d:55:00:fd:
        17:a3:5a:ff:ec:e6:5c:27

Wheres my ca-certificates.crt file contains the following ISRG ca certificates:

$ openssl storeutl -noout -text -certs ca-certificates.crt | grep -A4 -B4 ISRG
        Version: 3 (0x2)
        Serial Number:
            82:10:cf:b0:d2:40:e3:59:44:63:e0:bb:63:82:8b:00
        Signature Algorithm: sha256WithRSAEncryption
        Issuer: C=US, O=Internet Security Research Group, CN=ISRG Root X1
        Validity
            Not Before: Jun  4 11:04:38 2015 GMT
            Not After : Jun  4 11:04:38 2035 GMT
        Subject: C=US, O=Internet Security Research Group, CN=ISRG Root X1
        Subject Public Key Info:
            Public Key Algorithm: rsaEncryption
                Public-Key: (4096 bit)
                Modulus:
--
        Version: 3 (0x2)
        Serial Number:
            41:d2:9d:d1:72:ea:ee:a7:80:c1:2c:6c:e9:2f:87:52
        Signature Algorithm: ecdsa-with-SHA384
        Issuer: C=US, O=Internet Security Research Group, CN=ISRG Root X2
        Validity
            Not Before: Sep  4 00:00:00 2020 GMT
            Not After : Sep 17 16:00:00 2040 GMT
        Subject: C=US, O=Internet Security Research Group, CN=ISRG Root X2
        Subject Public Key Info:
            Public Key Algorithm: id-ecPublicKey
                Public-Key: (384 bit)
                pub:
2

LetsEncrypt does not transmit any root certificates in the ACME protocol; distributing the root is optional under the RFC (see below) and I don't know of any public CAs that do this.

The ca.pem file that you are referring to is not a Trusted Root Certificate; instead it is a CA Intermediate certificate. In this particular case, it is an E5 certificate -- see Chains of Trust - Let's Encrypt -- which was signed by the private key of the self-signed X2 root (which is in your trust store). (There is also a version of the X2 root that uses the same pkey, but is cross-signed by X1 for wider compatibility.)

Your cert.pem is: the End Entity/Leaf Certificate
Your ca.pem is: Intermediate Certificate(s) that signed your cert; most ACME clients call this chain.pem
Your fullchain.pem is just: cat cert.pem ca.pem

With Public CAs - and most ACME clients - it is expected that trusted roots are independently transmitted. See RFC 8555 - Automatic Certificate Management Environment (ACME) ::

In order to provide easy interoperation with TLS, the first
certificate MUST be an end-entity certificate.  Each following
certificate SHOULD directly certify the one preceding it.  Because
certificate validation requires that trust anchors be distributed
independently, a certificate that represents a trust anchor MAY be
omitted from the chain, provided that supported peers are known to
possess any omitted certificates.

AFAIK, no public CA's include their root in the chain; it's expected to be in the system trust store AND clients are not expected to have sufficient permissions to manipulate the trust store.

TLDR; ca.pem is the CA's Intermediate certificate chain bridging trust between the cert.pem and the Trusted Root on your system (ca-certificates.crt); it is not a CA root. You likely got confused by the unfortunate naming convention that acme-sh uses.

As an aside, IMHO with Postgres replication or access, self-signed or private CA certs are much easier.

6 Likes
4

So -- wow did I learn something today. I'm aware that fullchain was concatenation of chain and cert, however I had no idea the chain file ONLY contained the intermediate certificate. I believe part of this confusion honestly was in part due to the acme.sh client that has an option where you install the cert and has the Le_RealCACertPath option which I interpreted as the chain file. Damn. Makes sense what you're saying about the independent distribution of root certificates, however I guess I just never really knew this.

My last question then is related to the certificate verification process which I do manually. What is actually openssl verifying if I give the command (either one works):

openssl verify -CAfile ca.pem cert.pem
openssl verify -CAfile ca.pem fullchain.pem

In this example ca.pem is just really the intermediate certificate.

So just to answer what I asked above -- my verification was in correct for acme.sh certificates with an intermediate. What the command should actually be is something like:

openssl verify -verbose -CApath /etc/ssl/certs -untrusted <intermediate certificate or bundle> <cert.pem or fullchain.pem>

*** Usually openssl by default is going to assign -CApath to /etc/ssl/certs. It's a lot more permissive than I thought it was.

I guess I learned a thing or two today.

2 Likes
5

Two slight possible edits to the above:

1- there is a -purpose sslserver flag that might be applicable here. it affects some random things , which i have totally forgotten about.

2- You can use -CApath for a directory OR -CAfile for a specific pem file.

openssl verify -purpose sslserver -CAfile root.pem [[-untrusted intermediate.pem],[-untrusted intermediate.pem],] cert.pem

If you're on an OLDER build of openSSL (hopefully not, because these release may not have important security updates), you have to cat all the intermediates into a single file (it can only take a single -untrusted):

        openssl verify -purpose sslserver -CAfile root.pem -untrusted intermediate.pem cert.pem

IIRC, I think you can ensure the right order of certs in the chain file with this:

        /usr/local/bin/openssl verify -purpose sslserver -partial_chain -trusted {ROOT.pem} {CHAINREVERSED.pem}

I pulled the above from some old code, as I mostly do all this in python now.

however I had no idea the chain file ONLY contained the intermediate certificate.

This confuses a lot of people. I think this forum averages 2 people making that mistake each week. People even file bugreports against ACME Clients and Servers all the time about this.

Edit: Here is the manual for verify, which covers what purpose does: openssl-verification-options - OpenSSL Documentation

6 Likes
6

Additionally part of the confusion was the command too I was using. I'm glad you posted the correct commands however if I used:

openssl verify -CAfile <intermediate.pem> <cert.pem or fullchain.pem>

It was coming back "OK". I had no idea the program was using /etc/ssl/certs path in the background.

Anyway -- Thanks for the clarification

Oh -- I thought about using self-signed certs for this application -- which yes it would be perfect for. However since I'm not using any client certificates, I thought I would just use acme to obtain server certificates. If I had used self-signed certs I don't think I would have ran into this mess, but I also don't think I would have learned about this entire chain.pem only containing the intermediate certificate.

3 Likes