Intermediate certificates that might not comply with Mozilla Root Store Policy section 5.3

20210625_intermediates_with_questionable_eku.csv

intermediates_with_questionable_eku.sh

#!/bin/bash
#
# This script uses the public crt.sh database to find intermediate certificates that either don't comply, or that cannot be programatically proven to comply, with the following rules from the Mozilla Root Store Policy:
#
# "Intermediate certificates created after January 1, 2019, with the exception of cross-certificates that share a private key with a corresponding root certificate:
#   - MUST contain an EKU extension; and,
#   - MUST NOT include the anyExtendedKeyUsage KeyPurposeId; and,
#   - MUST NOT include both the id-kp-serverAuth and id-kp-emailProtection KeyPurposeIds in the same certificate."
# https://www.mozilla.org/en-US/about/governance/policies/security-group/certs/policy/#53-intermediate-certificates
#
# psql doesn't support multi-line \COPY statements, so we use the HEREDOC workaround described by https://minhajuddin.com/2017/05/18/how-to-pass-a-multi-line-copy-sql-to-psql/
#
# If any of the following errors occur, simply try running this script again.
#   1. ERROR:  canceling statement due to conflict with recovery
#      DETAIL:  User query might have needed to see row versions that must be removed.
#   2. ERROR:  no more connections allowed (max_client_conn)

cat <<SQL | tr -d '\n' | psql -h crt.sh -p 5432 -U guest -d certwatch
\COPY (
SELECT c.ID AS "crt.sh ID",
       x509_notBefore(c.CERTIFICATE) AS "notBefore",
       get_ca_name_attribute(cac.CA_ID) AS "Subject CA",
       get_ca_name_attribute(c.ISSUER_CA_ID) AS "Issuer CA"
  FROM ca_certificate cac
    JOIN certificate c ON (cac.CERTIFICATE_ID = c.ID)
  WHERE
    /* Only consider trust paths that terminate with an NSS built-in root */
    EXISTS (
      SELECT 1
        FROM ca_trust_purpose ctp
        WHERE ctp.CA_ID = c.ISSUER_CA_ID
          AND ctp.TRUST_CONTEXT_ID = 5  /* Mozilla */
          AND ctp.IS_TIME_VALID
    )
    /* Exclude self-signed certificates, because we are evaluating rules that only apply to intermediate certificates */
    AND cac.CA_ID != c.ISSUER_CA_ID
    /* "Intermediate certificates created after January 1, 2019," */
    AND cac.CERTIFICATE_ID >= (
      /* Certificate notBefore dates can be (and sometimes are) backdated, but we expect SCT timestamps to never be backdated.
         crt.sh IDs are allocated sequentially, and the crt.sh CT log monitor usually stays up-to-date with fetching new log entries.
         Therefore, we can know approximately when an intermediate certificate became known to crt.sh, even if that intermediate certificate has no SCTs from around that time.
         This may produce some false positives, but (unlike relying on notBefore dates) it will avoid false negatives.
         Find the lowest crt.sh ID for any certificate "created after January 1, 2019", and filter out earlier crt.sh IDs. */
      SELECT ctle.CERTIFICATE_ID
        FROM ct_log_entry ctle, certificate c2
        WHERE ctle.ENTRY_TIMESTAMP >= '2019-01-01T00:00:00Z'::date
          AND ctle.certificate_id = c2.ID
        ORDER BY ENTRY_TIMESTAMP
        LIMIT 1
    )
    /* Filter out any historical intermediates certs that had already expired by January 1, 2019 */
    AND coalesce(x509_notAfter(c.CERTIFICATE), 'infinity'::timestamp) >= '2019-01-01T00:00:00Z'::date
    /* "with the exception of cross-certificates that share a private key with a corresponding root certificate:" */
    AND NOT EXISTS (
      SELECT 1
        FROM ca_certificate cac2, certificate c3
        WHERE cac2.CA_ID = cac.CA_ID
          AND cac2.CERTIFICATE_ID = c3.ID
          AND cac2.CA_ID = c3.ISSUER_CA_ID
    )
    AND (
      /* "MUST contain an EKU extension; and," */
      NOT EXISTS (
        SELECT 1
          FROM x509_extensions(c.CERTIFICATE)
          WHERE x509_extensions = '2.5.29.37'  /* extKeyUsage */
      )
      /* "MUST NOT include the anyExtendedKeyUsage KeyPurposeId; and," */
      OR EXISTS (
        SELECT 1
          FROM x509_extkeyusages(c.CERTIFICATE)
          WHERE x509_extkeyusages = '2.5.29.37.0'  /* anyExtendedKeyUsage */
      )
      /* "MUST NOT include both the id-kp-serverAuth and id-kp-emailProtection KeyPurposeIds in the same certificate." */
      OR (
        EXISTS (
          SELECT 1
            FROM x509_extkeyusages(c.CERTIFICATE)
            WHERE x509_extkeyusages = '1.3.6.1.5.5.7.3.1'  /* id-kp-serverAuth */
        )
        AND EXISTS (
          SELECT 1
            FROM x509_extkeyusages(c.CERTIFICATE)
            WHERE x509_extkeyusages = '1.3.6.1.5.5.7.3.4'  /* id-kp-emailProtection */
        )
      )
    )
  ORDER BY "Issuer CA", "Subject CA"
) TO '__temp__intermediates_with_questionable_eku.csv' CSV HEADER
SQL

mv __temp__intermediates_with_questionable_eku.csv $(date +%Y%m%d)_intermediates_with_questionable_eku.csv