Pre-existing S/MIME Sub-CA Certificates that cannot comply with the certificate policy requirements in SMIME BR 7.1.6.3

affected_smime_subca_certs.csv

list_affected_smime_subca_certs.sql

SELECT 'https://crt.sh/?id=' || c.ID,
       cc.INCLUDED_CERTIFICATE_OWNER,
       cc.CERT_NAME,
       array_to_string(array_agg(DISTINCT tc.CTX ORDER BY tc.CTX), ',') TRUSTED_BY
  FROM ca_trust_purpose ctp, trust_context tc, ca_certificate cac, certificate c, ccadb_certificate cc
  WHERE ctp.TRUST_CONTEXT_ID IN (1, 5, 12)  -- Microsoft, Mozilla, Apple.
    AND ctp.TRUST_PURPOSE_ID = 3  -- Secure Email.
    AND ctp.IS_TIME_VALID
    AND coalesce(ctp.DISABLED_FROM, 'infinity'::timestamp) > '2023-09-01T00:00:00Z'
    AND coalesce(ctp.NOTBEFORE_UNTIL, 'infinity'::timestamp) > '2023-09-01T00:00:00Z'
    AND ctp.TRUST_CONTEXT_ID = tc.ID
    AND ctp.CA_ID = cac.CA_ID
    AND cac.CERTIFICATE_ID = c.ID
    AND x509_notAfter(c.CERTIFICATE) > now() AT TIME ZONE 'UTC'  -- Unexpired.
    AND x509_isEKUPermitted(c.CERTIFICATE, '1.3.6.1.5.5.7.3.4')  -- Secure Email.
    AND NOT (
      x509_isPolicyPermitted(c.CERTIFICATE, '2.23.140.1.5.1.1')
      OR x509_isPolicyPermitted(c.CERTIFICATE, '2.23.140.1.5.1.2')
      OR x509_isPolicyPermitted(c.CERTIFICATE, '2.23.140.1.5.1.3')
      OR x509_isPolicyPermitted(c.CERTIFICATE, '2.23.140.1.5.2.1')
      OR x509_isPolicyPermitted(c.CERTIFICATE, '2.23.140.1.5.2.2')
      OR x509_isPolicyPermitted(c.CERTIFICATE, '2.23.140.1.5.2.3')
      OR x509_isPolicyPermitted(c.CERTIFICATE, '2.23.140.1.5.3.1')
      OR x509_isPolicyPermitted(c.CERTIFICATE, '2.23.140.1.5.3.2')
      OR x509_isPolicyPermitted(c.CERTIFICATE, '2.23.140.1.5.3.3')
      OR x509_isPolicyPermitted(c.CERTIFICATE, '2.23.140.1.5.4.1')
      OR x509_isPolicyPermitted(c.CERTIFICATE, '2.23.140.1.5.4.2')
      OR x509_isPolicyPermitted(c.CERTIFICATE, '2.23.140.1.5.4.3')
    )
    AND c.ID = cc.CERTIFICATE_ID
    AND cc.REVOCATION_STATUS NOT IN ('Revoked', 'Parent Cert Revoked')  -- Exclude when marked as revoked in CCADB.
  GROUP BY c.ID, cc.INCLUDED_CERTIFICATE_OWNER, cc.CERT_NAME
  ORDER BY cc.INCLUDED_CERTIFICATE_OWNER, cc.CERT_NAME, c.ID;