mozilla_ca_certs_with_ocspsigning_eku.sql

WITH mozilla_server_cas AS (
  SELECT DISTINCT ctp.CA_ID
    FROM ca_trust_purpose ctp
    WHERE ctp.TRUST_CONTEXT_ID = 5  /* Mozilla */
      AND ctp.TRUST_PURPOSE_ID = 1  /* Server Authentication */
)
SELECT c.ID,
       coalesce(cc.CA_OWNER, cc.INCLUDED_CERTIFICATE_OWNER) as CA_OWNER,
       CASE WHEN mo.CERTIFICATE_ID IS NOT NULL THEN 'Revoked' ELSE 'Unrevoked' END as ONECRL_STATUS,
       x509_signatureHashAlgorithm(c.CERTIFICATE) as SIG_HASH_ALG,
       x509_subjectName(c.CERTIFICATE) SUBJECT_DN
  FROM ca_certificate cac,
       certificate c,
       mozilla_server_cas msc,
       ccadb_certificate cc
         LEFT OUTER JOIN mozilla_onecrl mo ON (
           cc.CERTIFICATE_ID = mo.CERTIFICATE_ID
         )
  WHERE cac.CERTIFICATE_ID = c.ID
    AND c.ISSUER_CA_ID = msc.CA_ID
    AND c.ID = cc.CERTIFICATE_ID
    AND EXISTS (
      SELECT 1
        FROM x509_extKeyUsages(c.CERTIFICATE)
        WHERE x509_extKeyUsages = '1.3.6.1.5.5.7.3.9'  /* id-kp-OCSPSigning */
    )
    AND NOT x509_hasExtension(c.CERTIFICATE, '1.3.6.1.5.5.7.48.1.5')  /* id-pkix-ocsp-nocheck */
    AND x509_notAfter(c.CERTIFICATE) > now() AT TIME ZONE 'UTC'  /* Exclude CA certs that have already expired */
  ORDER BY coalesce(cc.CA_OWNER, cc.INCLUDED_CERTIFICATE_OWNER), c.ID;

mozilla_ca_certs_with_ocspsigning_eku.tsv

unrevoked_only.tsv