1. Overview

In my previous blog, I discussed how to quickly set up a TLS connection between a PostgreSQL server and a psql client. In this blog, I will guide you through the process of setting up a TLS connection using OCSP Stapling, which can help improve the security of PostgreSQL.

2. What is OCSP Stapling?

Before diving into how to enable OCSP Stapling with PostgreSQL, we need to understand some basic concepts:

*) The Online Certificate Status Protocol (OCSP) is a protocol used to check the validity of a certificate in real-time.

*) An OCSP Responder is a server that receives requests from clients to verify the status of certificates using OCSP, providing real-time validation by determining the current status (good, revoked or unknown) of the certificate and sending back an OCSP response.

*) An OCSP Response is the reply from an OCSP responder server to a client’s request for the status of a certificate.

*) An OCSP Stapling is a method used in SSL/TLS protocols where the server proactively sends a recent OCSP response along with its own certificate during the handshake, allowing the client to verify the certificate’s status without contacting the OCSP responder directly.

3. The Benefits of using OCSP Stapling

*) Enhanced user trust and real-time certificate verification
OCSP Stapling enabled users to check the certificate status of the PostgreSQL server in real-time. This ensures that users can validate the trustworthiness of the server’s certificate without relying on potentially outdated certificate revocation lists (CRLs).

*) Improved privacy
OCSP Stapling helps address privacy concerns associated with traditional OCSP checks, where the client contacts the OCSP responder directly. With OCSP Stapling, the server takes responsibility for obtaining the OCSP response, ensuring that the client’s IP address and queried certificate details are not exposed to the OCSP responder.

*) Reduced latency
OCSP Stapling allows the server to provide the client with a pre-signed OCSP response during the SSL/TLS handshake, eliminating the need for the client to perform an additional round-trip to the OCSP responder.

*) Efficient resource utilization
OCSP Stapling minimizes the load on OCSP responders and Certificate Authorities (CAs) by allowing the server to cache and reuse OCSP responses. This results in more efficient resource utilization and reduces the overall impact on the CA infrastructure.

4. Setup OCSP environment

Here is a simple network diagram for the OCSP stapling practice in below sections.

(1) A root or sub CA signed the certificates for OCSP responder, PostgreSQL server and a psql client.

(2) PostgreSQL server periodically connects to OCSP responder to request its’ certificate OCSP response and save it as OCSP stapling.

(3) A psql client wants to verify PostgreSQL server’s certificate status in “real-time”, and PostgreSQL server provides certificate plus OCSP stapling during handshake process.

To experience OCSP Stapling, OpenSSL tools are required to simulate the OCSP responder for generating OCSP responses. Additionally, initial certificate generation, including a self-signed root CA, OCSP response signing certificate, and PostgreSQL server certificate, is needed.

The openssl has been verified and the difference compared to the original openssl configuration file:

$ openssl version
OpenSSL 3.0.2 15 Mar 2022 (Library: OpenSSL 3.0.2 15 Mar 2022)


$ diff openssl-ocsp.cnf /etc/ssl/openssl.cnf 
204d203
< authorityInfoAccess = OCSP;URI:http://127.0.0.1:6655
232,235d230
< [ v3_ocsp ]
< basicConstraints = CA:FALSE
< keyUsage = nonRepudiation, digitalSignature, keyEncipherment
< extendedKeyUsage = OCSPSigning
255c250
< keyUsage = critical, cRLSign, digitalSignature, keyCertSign
---
> 

4.1 Create Certificates

mkdir -p demoCA/newcerts
touch demoCA/index.txt
echo '01' > demoCA/serial

*) Create a self-signed root CA
openssl req -new -nodes -out rootCA.csr -keyout rootCA.key -subj "/C=CA/ST=BC/L=VAN/O=IDO/OU=DEV/CN=rootCA"
openssl x509 -req -in rootCA.csr -days 3650 -extfile openssl-ocsp.cnf -extensions v3_ca -signkey rootCA.key -out rootCA.crt

*) Create a certificate for OCSP responder
openssl req -new -nodes -out ocspSigning.csr -keyout ocspSigning.key -subj "/C=CA/ST=BC/L=VAN/O=IDO/OU=DEV/CN=ocspSigner"
openssl ca -keyfile rootCA.key -cert rootCA.crt -in ocspSigning.csr -out ocspSigning.crt -config openssl-ocsp.cnf -extensions v3_ocsp
    Sign the certificate? [y/n]:y
    1 out of 1 certificate requests certified, commit? [y/n]y

*) Create a certificate for PostgreSQL server
openssl req -new -nodes -out server.csr -keyout server.key -subj "/C=CA/ST=BC/L=VAN/O=IDO/OU=DEV/CN=server"
openssl ca -batch -days 365 -keyfile rootCA.key -cert rootCA.crt -config openssl-ocsp.cnf -out server.crt -infiles server.csr

4.2 Start OCSP Responder and Generate OCSP Response

openssl ocsp -index demoCA/index.txt -port 6655 -rsigner ocspSigning.crt -rkey ocspSigning.key -CA rootCA.crt -text

*) Make sure PostgreSQL server's certificate is 'good'
openssl ocsp -issuer rootCA.crt -url http://127.0.0.1:6655 -resp_text -noverify -cert server.crt

*) Generate OCSP Response when certificate status is 'good' for PostgreSQL server and save as _server.resp-good:
openssl ocsp -issuer rootCA.crt -cert server.crt -url http://127.0.0.1:6655 -respout _server.resp-good


*) Revoke PostgreSQL server's certificate
openssl ca -keyfile rootCA.key -cert rootCA.crt -revoke server.crt

*) Make sure PostgreSQL server's certificate is 'revoked'
openssl ocsp -issuer rootCA.crt -url http://127.0.0.1:6655 -resp_text -noverify -cert server.crt

*) Generate OCSP Response when certificate status is 'revoked' for PostgreSQL server and save as _server.resp-revoked:
openssl ocsp -issuer rootCA.crt -cert server.crt -url http://127.0.0.1:6655 -respout _server.resp-revoked

4.3 Setup OCSP stapling on PostgreSQL Server

To experience this OCSP Stapling enhancement, you need to get the patch from here, and apply it to the more recent PostgreSQL master branch.

git clone https://github.com/postgres/postgres.git
cd postgres
git checkout 1ae5ace7558ea949d2f94af2fd5eb145d5558659 -b ocsp-stapling
git apply v1-0001-support-certificate-status-check-using-OCSP-stapling.patch
./configure --enable-tap-tests --with-openssl
make -j && make install

If PostgreSQL compiled without any issue, then update PostgreSQL server configuration by specifying ssl_ocsp_file = ‘ocsp/_server.resp’, where ‘_server.resp’ is either a copy of ‘_server.resp-good’ or ‘_server.resp-revoked’ depending on the test case. For example,

listen_addresses = '*'
ssl = on
ssl_ca_file = 'rootCA.crt'
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'
ssl_ocsp_file = '_server.resp'

5. Verify the TLS Connection Using psql as Client

5.1) PostgreSQL server’s certificate status is ‘good’

cp -pr _server.resp-good _server.resp

$ psql -d "sslmode=verify-ca sslrootcert=rootCA.crt user=david dbname=postgres ssl_ocsp_stapling=1" -h 127.0.0.1 -p 5432
psql (17devel)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, compression: off)
Type "help" for help.

postgres=#

5.2) PostgreSQL server’s certificate status is ‘revoked’

cp -pr  _server.resp-revoked _server.resp

$ psql -d "sslmode=verify-ca sslrootcert=rootCA.crt user=david dbname=postgres ssl_ocsp_stapling=1" -h 127.0.0.1 -p 5432
psql: error: connection to server at "127.0.0.1", port 5432 failed: SSL error: ocsp callback failure

5.3) PostgreSQL server’s certificate status is ‘revoked’ but OCSP stapling is not required by client:

$ psql -d "sslmode=verify-ca sslrootcert=rootCA.crt user=david dbname=postgres ssl_ocsp_stapling=0" -h 127.0.0.1 -p 5432
psql (17devel)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, compression: off)
Type "help" for help.

postgres=# 

6. Summary

In this blog post, I demonstrated how to use OCSP Stapling to enhance the TLS connection between a psql client and a PostgreSQL server, and I hope this feature can be helpful.

Again, the openssl.cnf file and the OCSP Stapling patch used in this experimental exercise are available here.

David Zhang

A software developer specialized in C/C++ programming with experience in hardware, firmware, software, database, network, and system architecture. Now, working in HighGo Software Inc, as a senior PostgreSQL architect.