When crt.sh's website is down, query its public database instead

I maintain a small tool that searches Certificate Transparency logs for new certificates on a domain. It reads crt.sh's JSON output, and it already retries the 404s and 502s that crt.sh returns when it's busy.

This week retrying stopped being enough. For more than an hour, every request to the website failed: a 502 in under a second, or no answer at all until my 40-second timeout. The platform I run the tool on tests it once a day with its default input, and it had failed 5 of its last 27 daily tests, which was enough to get it flagged as under maintenance.

crt.sh has a second door, though. Its database is open to the public, read-only:

psql -h crt.sh -p 5432 -U guest certwatch

While the website was returning 502s, the database answered in 9 to 35 seconds. That's slower than the website on a good day, but it's an answer. So the tool now tries the website three times and then falls back to the database. Getting that fallback right took four fixes.

1. The pooler rejects some connection options

My first connection set a server-side statement timeout:

new pg.Client({ host: 'crt.sh', port: 5432, user: 'guest', database: 'certwatch', statement_timeout: 60000 });

It failed straight away with unsupported startup parameter: statement_timeout. The database sits behind a connection pooler that only accepts a few startup parameters. node-postgres has a client-side query_timeout that does the same job without asking the server:

const client = new pg.Client({
    host: 'crt.sh', port: 5432, user: 'guest', database: 'certwatch',
    connectionTimeoutMillis: 20000,
    query_timeout: 60000,
});

2. The query that works, and the one that gets cancelled

crt.sh searches by identity. Its schema has a certificate_and_identities view with a full-text index, so this finds every unexpired certificate for a domain and its subdomains:

SELECT c.id,
       x509_commonName(c.certificate) AS common_name,
       ca.name AS issuer_name,
       encode(x509_serialNumber(c.certificate), 'hex') AS serial_number,
       to_char(x509_notBefore(c.certificate), 'YYYY-MM-DD"T"HH24:MI:SS') AS not_before,
       to_char(x509_notAfter(c.certificate), 'YYYY-MM-DD"T"HH24:MI:SS') AS not_after
FROM certificate c
JOIN ca ON ca.id = c.issuer_ca_id
WHERE c.id IN (
    SELECT cai.certificate_id
    FROM certificate_and_identities cai
    WHERE plainto_tsquery('certwatch', $1) @@ identities(cai.certificate)
      AND (cai.name_value ILIKE '%.' || $1 OR lower(cai.name_value) = lower($1))
    LIMIT 5000)
  AND x509_notAfter(c.certificate) > now()
  AND x509_notBefore(c.certificate) >= $2::timestamp
ORDER BY x509_notBefore(c.certificate) DESC
LIMIT $3

For a mid-sized domain this took about 35 seconds, and for a small one about 9.

I tried to make the 5,000-match cap keep the newest certificates by adding ORDER BY cai.certificate_id DESC inside the subquery. That version never finished. crt.sh cancelled it with canceling statement due to statement timeout after about 54 seconds: the server has its own limit, and sorting every match goes over it. So the subquery stays unordered, which means a very large domain can miss some recent certificates on this path. For a fallback, that's a fair trade.

3. A dropped connection kills the whole process

After a cancelled query, my test script crashed with an error I never caught:

error: client_idle_timeout
Emitted 'error' event on Client instance

The pooler closes idle and cancelled connections, and node-postgres reports that as an error event on the client. In Node, an error event with no listener is an uncaught exception, so the process dies, retry logic and all. One line fixes it:

client.on('error', (err) => console.debug('crt.sh connection closed:', err.message));

4. The timestamps are UTC, but they don't say so

The first fallback run on my laptop said a certificate became valid at 07:08:23. The same code in the cloud said 10:08:23. To find out which was right, I decoded the certificate itself:

import { X509Certificate } from 'node:crypto';

const { rows } = await client.query('SELECT certificate FROM certificate WHERE id = $1', [id]);
console.log(new X509Certificate(rows[0].certificate).validFrom); // Oct  3 10:08:23 2026 GMT

x509_notBefore() returns a timestamp without time zone that holds UTC. node-postgres turns that type into a JavaScript Date in the machine's local time zone. The cloud runs on UTC, so it was right by luck. My laptop is on UTC+3, so it was three hours off. Formatting the value in SQL with to_char, as in the query above, avoids the driver's guess entirely, and when I compare dates in JavaScript I add the Z myself:

const utc = (crtshTime) => new Date(`${crtshTime}Z`);

Fitting it into a time budget

The daily test expects a finished run with results within five minutes, and a monitor running on a schedule shouldn't hang either. So everything shares one 4-minute budget:

• The website gets three attempts with a 20-second timeout each, capped at 75 seconds in total.
• The database gets whatever is left, with one retry if at least 30 seconds remain.
• If both fail, the run fails with a message that names both errors, instead of quietly reporting zero certificates.

One more cause: the example domain

The default input searched example.com for certificates from the last 30 days. Looking at 180 days of history in the database showed that example.com once went 59 days without a new certificate. So on some days the default run correctly found nothing, and an empty result counts as a failed test too. I switched the default to a domain with lots of subdomains on 90-day certificates, whose longest gap was 19.5 days.

The result

With the website still returning 502s, the fallback found four recent certificates in 32 seconds on my laptop and 40 seconds in the cloud.

What I'd pass on:

• If a free service has a second way in (here, a public read-only database), wire it up as a fallback before you need it.
• Behind a connection pooler, set timeouts on the client, not the server.
• Always attach an error listener to database clients in Node.
• Don't let a driver guess the time zone of a timestamp without time zone. Format it yourself.
• Pick test inputs that can't legitimately come back empty.