Computing Mozilla Trusted CAs

instructions.md

What this is

Absolutely terrible SQL queries against crt.sh to generate the links and nodes of the Mozilla Trusted CA Certificate graph.

How to use it

It's SQL, it should be obvious ;)

You can connect to https://crt.sh via psql using the following:

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

From there, you'll want to make sure your JSON output doesn't have the leading/trailing junk added, and so for a single session, you'll need to run both \t on and \pset format unaligned to turn those off.

links.sql

SELECT
  array_to_json(array_agg(row_to_json(t)))
FROM (
  SELECT
    ca_c.CA_ID as source,
    c.ISSUER_CA_ID as target,
    ca_c.CERTIFICATE_ID as certificate_id
  FROM
    ca_certificate AS ca_c
    INNER JOIN certificate AS c
      ON ca_c.CERTIFICATE_ID = c.ID
    INNER JOIN ca_trust_purpose AS ca_t_p
      ON ca_c.CA_ID = ca_t_p.CA_ID
  WHERE
    -- Trusted by Mozilla
    ca_t_p.TRUST_CONTEXT_ID = 5
    -- For TLS
    AND ca_t_p.TRUST_PURPOSE_ID = 1
    AND ca_t_p.IS_TIME_VALID
    -- At least one of the chains is valid
    AND NOT(ca_t_p.ALL_CHAINS_REVOKED_IN_SALESFORCE OR ca_t_p.ALL_CHAINS_REVOKED_VIA_ONECRL)
    -- Not self-issued (lazy optimization)
    AND ca_c.CA_ID != c.ISSUER_CA_ID
    -- Not expired
    AND x509_notAfter(c.CERTIFICATE) > NOW()
    -- Not revoked by (Mozilla OneCRL, CCADB, CRL)
    AND NOT (
      EXISTS(SELECT 1 FROM mozilla_onecrl mo WHERE mo.CERTIFICATE_ID = ca_c.CERTIFICATE_ID) 
      OR EXISTS(SELECT 1 FROM ccadb_certificate cc WHERE cc.CERTIFICATE_ID = ca_c.CERTIFICATE_ID AND cc.REVOCATION_STATUS IN ('Revoked', 'Parent Cert Revoked'))
      OR EXISTS(SELECT 1 FROM crl_revoked crl WHERE crl.CA_ID = c.ISSUER_CA_ID AND crl.SERIAL_NUMBER = x509_serialNumber(c.CERTIFICATE))
    )
    -- And the parent also has a valid chain
    AND EXISTS(
      SELECT 1
      FROM ca_trust_purpose AS ca_t_p2
      WHERE
        ca_t_p2.TRUST_CONTEXT_ID = 5
        AND ca_t_p2.TRUST_PURPOSE_ID = 1
        AND ca_t_p2.IS_TIME_VALID
        AND NOT(ca_t_p2.ALL_CHAINS_REVOKED_IN_SALESFORCE OR ca_t_p2.ALL_CHAINS_REVOKED_VIA_ONECRL)
        AND ca_t_p2.CA_ID = c.ISSUER_CA_ID
    )
) t \g links.json

nodes.sql

SELECT
  array_to_json(array_agg(row_to_json(t)))
FROM (
  SELECT
    ca.ID,
    ca.NAME
  FROM
    ca
    INNER JOIN ca_trust_purpose AS ca_t_p
      ON ca_t_p.CA_ID = ca.ID
  WHERE
    ca_t_p.TRUST_CONTEXT_ID = 5
    AND ca_t_p.TRUST_PURPOSE_ID = 1
    AND ca_t_p.IS_TIME_VALID
    AND NOT(ca_t_p.ALL_CHAINS_REVOKED_IN_SALESFORCE OR ca_t_p.ALL_CHAINS_REVOKED_VIA_ONECRL)
) t \g nodes.json