A PostgreSQL extension that exposes OpenSSL's X.509 certificate parser as a set of SQL functions. Pass in a DER-encoded certificate as bytea and get back issuer/subject names, validity dates, key material, extensions, fingerprints, and more — without leaving the database.

This is the parsing engine that powers crt.sh, Sectigo's public Certificate Transparency log search.

• PostgreSQL 12 or later (developed and tested against PG 18)
• OpenSSL 1.1.1 or later (3.x and 4.x supported)
• A C toolchain plus the PostgreSQL server headers (postgresql-server-dev-<n> on Debian/Ubuntu, postgresql<n>-devel on RHEL/Fedora)

make
sudo make install

The build uses PGXS; point it at a non-default cluster with PG_CONFIG:

make PG_CONFIG=/path/to/pg_config
sudo make install PG_CONFIG=/path/to/pg_config

To build against an OpenSSL installed outside the default system paths (e.g. /usr/local/openssl-4.0.1), set OPENSSL_HOME:

make OPENSSL_HOME=/usr/local/openssl-4.0.1
sudo make install OPENSSL_HOME=/usr/local/openssl-4.0.1

The Makefile will look for headers in $OPENSSL_HOME/include and for libcrypto.so in $OPENSSL_HOME/lib64 (falling back to $OPENSSL_HOME/lib). An RPATH is embedded in the resulting .so so the correct libcrypto is found at runtime without requiring LD_LIBRARY_PATH or changes to the system linker cache.

Both variables can be combined:

make PG_CONFIG=/path/to/pg_config OPENSSL_HOME=/usr/local/openssl-4.0.1

You can verify the linked library after building:

objdump -p libx509pq.so | grep NEEDED   # should show libcrypto.so.4
ldd libx509pq.so                        # should resolve to OPENSSL_HOME

In a database:

CREATE EXTENSION libx509pq;

Upgrading from an older install:

ALTER EXTENSION libx509pq UPDATE;

CREATE EXTENSION libx509pq;

-- Load a DER-encoded certificate (e.g. produced by
--   openssl x509 -in cert.pem -outform der | xxd -p | tr -d '\n'
-- and pasted as a hex literal):
WITH c(der) AS (VALUES (decode('3082...', 'hex')))
SELECT x509_commonName(der)                  AS cn,
       x509_issuerName(der)                  AS issuer,
       x509_notBefore(der)                   AS not_before,
       x509_notAfter(der)                    AS not_after,
       x509_keyAlgorithm(der)                AS key_alg,
       x509_keySize(der)                     AS key_bits,
       encode(x509_serialNumber(der), 'hex') AS serial_hex
FROM c;

See test/sql/basic.sql for a runnable example using two well-known root certificates.

All functions are IMMUTABLE PARALLEL SAFE. Functions that take only a bytea argument are STRICT (NULL in → NULL out); functions that accept an optional flags argument cannot be STRICT because they treat NULL as "use the default flags".

When a function's job is to return text and the input cannot be parsed as a certificate, it returns a short error sentinel (e.g. the string (error)) rather than NULL — this matches the long-standing crt.sh behaviour. The pure binary/numeric extractors (x509_publicKey, x509_serialNumber, the timestamps, x509_keySize, etc.) return NULL on parse failure.

To avoid re-parsing the certificate once per field, x509_basic_info() returns the cheap-to-extract fields as a single composite row:

SELECT (x509_basic_info(der)).*
FROM   certs
WHERE  id = $1;

The composite type:

x509_basic_info_type(
    issuer_name              text,
    subject_name             text,
    common_name              text,
    serial_number            bytea,
    not_before               timestamp,
    not_after                timestamp,
    key_algorithm            text,
    key_size                 integer,
    signature_hash_algorithm text,
    signature_key_algorithm  text,
    subject_key_identifier   bytea,
    authority_key_identifier bytea
)

Note: in the boolean arguments below that pick between numeric OID strings and long names, TRUE selects the numeric OID (OBJ_obj2txt(..., 1)) and FALSE selects the long name. The defaults are TRUE, so by default these functions return dotted-decimal OIDs.

A minimal pg_regress smoke test lives under test/sql/ with expected output in test/expected/. Run it against a running cluster with:

make installcheck

Note: pg_regress runs as the PostgreSQL OS user (typically postgres), so the checkout must be in a directory that user can read and write. If your repo is under /home/<you>/... (not traversable by postgres), copy the tree to a shared location first, e.g.:

cp -r . /tmp/libx509pq && cd /tmp/libx509pq && sudo -u postgres make installcheck

GNU GPL v3. See LICENSE.