crt.sh
1#!/usr/bin/env sh
2
3query="""
4WITH ci AS (
5 SELECT min(sub.CERTIFICATE_ID) ID,
6 min(sub.ISSUER_CA_ID) ISSUER_CA_ID,
7 array_agg(DISTINCT sub.NAME_VALUE) NAME_VALUES,
8 x509_commonName(sub.CERTIFICATE) COMMON_NAME,
9 x509_notBefore(sub.CERTIFICATE) NOT_BEFORE,
10 x509_notAfter(sub.CERTIFICATE) NOT_AFTER,
11 encode(x509_serialNumber(sub.CERTIFICATE), 'hex') SERIAL_NUMBER,
12 count(sub.CERTIFICATE_ID)::bigint RESULT_COUNT
13 FROM (SELECT cai.*
14 FROM certificate_and_identities cai
15 WHERE plainto_tsquery('certwatch', '$1') @@ identities(cai.CERTIFICATE)
16 AND cai.NAME_VALUE ILIKE ('%' || '$1' || '%')
17 LIMIT 10000
18 ) sub
19 GROUP BY sub.CERTIFICATE
20)
21SELECT ci.ISSUER_CA_ID,
22 ca.NAME ISSUER_NAME,
23 ci.COMMON_NAME,
24 array_to_string(ci.NAME_VALUES, chr(10)) NAME_VALUE,
25 ci.ID ID,
26 le.ENTRY_TIMESTAMP,
27 ci.NOT_BEFORE,
28 ci.NOT_AFTER,
29 ci.SERIAL_NUMBER,
30 ci.RESULT_COUNT
31 FROM ci
32 LEFT JOIN LATERAL (
33 SELECT min(ctle.ENTRY_TIMESTAMP) ENTRY_TIMESTAMP
34 FROM ct_log_entry ctle
35 WHERE ctle.CERTIFICATE_ID = ci.ID
36 ) le ON TRUE,
37 ca
38 WHERE ci.ISSUER_CA_ID = ca.ID
39 ORDER BY le.ENTRY_TIMESTAMP DESC NULLS LAST;
40"""
41
42echo "$query" | psql -t -h crt.sh -p 5432 -U guest certwatch