robstradling · July 3, 2020 14:36
diff --git a/mozilla_ca_certs_with_ocspsigning_eku.sql b/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;
diff --git a/mozilla_ca_certs_with_ocspsigning_eku.tsv b/mozilla_ca_certs_with_ocspsigning_eku.tsv
diff --git a/unrevoked_only.tsv b/unrevoked_only.tsv
	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;