Checking PostgreSQL cluster certificates

During installation, during a renewal, or simply to find out when the PostgreSQL cluster certificates expire, it can be useful to examine the content of the generated certificates.

The certificates used by the PostgreSQL cluster are the following:

/etc/etcd/
├── ca.crt
├── client.crt
├── client.key
├── peer.crt
└── peer.key

/etc/patroni/
├── ca.crt
├── client.crt
└── client.key

ca.crt

Certificate of the root certification authority. The same certificate is present in the /etc/patroni/ and /etc/etcd/ directories.

client.crt

Certificate of the PostgreSQL node. The same certificate is present in the /etc/patroni/ and /etc/etcd/ directories. It is used by patroni and etcd for client communications between the nodes.

client.key

Key associated with the client.crt certificate.

peer.crt

Same certificate as client.crt. It is used for the internal etcd communications between the nodes.

peer.key

Same key as client.key.

There are therefore three items in all:

• the certificate of the root certification authority (/etc/etcd/ca.crt, /etc/patroni/ca.crt);
• the certificate of the PostgreSQL node (/etc/etcd/client.crt, /etc/etcd/peer.crt, /etc/patroni/client.crt);
• the key associated with the certificate of the PostgreSQL node (/etc/etcd/client.key, /etc/etcd/peer.key, /etc/patroni/client.key).

Checking the expiration date of a certificate

To check the expiration date of a certificate, use the command below.

You can customize the variable for the following command:

Custom value	Variable	Comment
	EXPIRY_CERT	Path of the certificate whose expiration date to check.

Or fill the field with one of the cluster certificates:

/etc/etcd/ca.crt /etc/etcd/client.crt /etc/etcd/peer.crt /etc/patroni/ca.crt /etc/patroni/client.crt

openssl x509 -in EXPIRY_CERT -noout -enddate

If the expiration date has passed, the certificate is considered invalid.

Warning

If the certification authority that issued the certificate has expired, the certificate is also considered invalid. Therefore also check the expiration date of the certification authority.

Example

To check the expiration date of the /etc/patroni/client.crt certificate, we use the following command:

openssl x509 -in /etc/patroni/client.crt -noout -enddate

The output is similar to this one:

notAfter=Dec 18 14:04:09 2030 GMT

Here, the certificate expires on December 18, 2030 at 14:04:09.

Checking the Subject Alternative Names of a certificate

To check the SANs (Subject Alternative Names) of a certificate, that is, the names and IP addresses for which it is valid, use the command below.

You can customize the variable for the following command:

Custom value	Variable	Comment
	SAN_CERT	Path of the certificate whose SANs to check.

Or fill the field with one of the node certificates:

/etc/etcd/client.crt /etc/etcd/peer.crt /etc/patroni/client.crt

openssl x509 -in SAN_CERT -noout -ext subjectAltName

If the DNS name or IP address used to connect to a node is not in the SANs of its certificate, the connection is considered invalid. Likewise, when a node opens a connection to another node, if the certificate it presents does not match its own DNS name or IP address, the remote node rejects the connection.

Example

To check the SANs of the /etc/etcd/peer.crt certificate, we use the following command:

openssl x509 -in /etc/etcd/peer.crt -noout -ext subjectAltName

The command returns a result similar to this one:

X509v3 Subject Alternative Name:
    DNS:PSQL_VIP, DNS:PSQL_2, IP Address:192.168.1.4, IP Address:192.168.1.2

Here, the certificate covers the DNS names PSQL_VIP and PSQL_2, as well as the IP addresses 192.168.1.4 and 192.168.1.2.

Checking which certification authority issued a certificate

When a certificate is generated, the X509v3 Authority Key Identifier extension can be added to it. This extension can store the hash of its issuer's public key, which matches the content of the issuer's X509v3 Subject Key Identifier extension.

Info

Certificates generated by following the PostgreSQL cluster installation documentation have the Authority Key Identifier and Subject Key Identifier extensions.

If these extensions are present, you can retrieve the hash of the public key of a certificate's issuer, then compare it with the hash in the Subject Key Identifier extension of the CA that is supposed to have issued the certificate, to confirm that it is the right one.

To retrieve the value of the Authority Key Identifier extension of a certificate, use the command below.

You can customize the variable for the following command:

Custom value	Variable	Comment
	ISSUED_CERT	Path of the certificate whose issuer to check.

Or fill the field with one of the node certificates:

/etc/etcd/client.crt /etc/etcd/peer.crt /etc/patroni/client.crt

openssl x509 -in ISSUED_CERT -noout -issuer -ext authorityKeyIdentifier

Info

The -issuer option retrieves the CN of the issuing certificate.

To retrieve the value of the Subject Key Identifier extension of a certificate, use the command below.

You can customize the variable for the following command:

Custom value	Variable	Comment
	ISSUER_CA	Path of the certificate of the certification authority assumed to be the issuer.

Or fill the field with one of the certification authority certificates:

/etc/etcd/ca.crt /etc/patroni/ca.crt

openssl x509 -in ISSUER_CA -noout -subject -ext subjectKeyIdentifier

Info

The -subject option retrieves the CN of the certificate.

Example

We want to check that the CA that signed the certificate in the /etc/patroni/client.crt file is indeed the one in the /etc/patroni/ca.crt file.
We first retrieve the content of the X509v3 Authority Key Identifier extension of the /etc/patroni/client.crt certificate with the following command:

openssl x509 -in /etc/patroni/client.crt -noout -issuer -ext authorityKeyIdentifier

Which returns:

issuer=CN = BDD-ROOT-CA
X509v3 Authority Key Identifier:
    38:B3:EC:CD:20:90:80:A4:B3:60:F8:E5:7B:98:92:3B:7D:26:06:2B

We then retrieve the content of the X509v3 Subject Key Identifier extension of the /etc/patroni/ca.crt CA with the following command:

openssl x509 -in /etc/patroni/ca.crt -noout -subject -ext subjectKeyIdentifier

Which returns:

subject=CN = BDD-ROOT-CA
X509v3 Subject Key Identifier:
    38:B3:EC:CD:20:90:80:A4:B3:60:F8:E5:7B:98:92:3B:7D:26:06:2B

Comparing the two outputs shows that the Key Identifier and the CN are identical: the certificate in the /etc/patroni/ca.crt file is indeed the CA that signed the certificate in the /etc/patroni/client.crt file.