robstradling · May 9, 2025 08:20
diff --git a/compare_root_information.sh b/compare_root_information.sh
 #!/bin/bash

 # This script uses the public crt.sh database to compare Root Certificate information in crt.sh against the same information in the CCADB AllCertificateRecordsCSVFormatv2 and AllIncludedRootCertsCSV reports.
 #
 # 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/
 #
 # Since long-running crt.sh:5432 queries are often killed off for various reasons, this script makes multiple attempts.  If all of the attempts fail, try running this script again later.

 ERRORFILE=`mktemp`

 TIMESTAMP=`date -Iseconds | sed "s/+00:00$/Z/g"`

 for i in {1..10}; do
  cat <<SQL | tr -d '\n' | psql -h crt.sh -p 5432 -U guest -d certwatch -v ON_ERROR_STOP=1 -X 2>$ERRORFILE
 \COPY (
 SELECT /* Record identifiers */
       cc.CCADB_RECORD_ID, c.ID AS CRTSH_ID,
       /* Discrepancy tracking */
       (
         (cc.CERT_NAME != get_ca_name_attribute(ca.ID))
         OR (NOT x509_verify(c.CERTIFICATE, ca.PUBLIC_KEY))
         OR (cc.CERT_SHA256 != digest(c.CERTIFICATE, 'sha256'))
         OR (to_date(cc.VALID_TO, 'YYYY.MM.DD') != date_trunc('day', x509_notAfter(c.CERTIFICATE)))
         OR (coalesce(sort_delimited_list(cc.APPLE_TRUST_BITS, ';'), '') != coalesce(apple.PURPOSES, ''))
         OR ((CASE WHEN cc.CHROME_STATUS = 'Included' THEN 'Server Authentication' ELSE '' END) != coalesce(chrome.PURPOSES, ''))
         OR (coalesce(sort_delimited_list(cc.MICROSOFT_EKUS, ';'), '') != coalesce(microsoft.PURPOSES, ''))
         OR (coalesce(sort_delimited_list(nullif(cc.MOZILLA_TRUST_BITS, 'All Trust Bits Turned Off'), ';'), '') != coalesce(mozilla.PURPOSES, ''))
       ) AS HAS_ANY_DISCREPANCIES,
       (cc.CERT_NAME != get_ca_name_attribute(ca.ID)) AS HAS_CERT_NAME_MISMATCH,
       (NOT x509_verify(c.CERTIFICATE, ca.PUBLIC_KEY)) AS IS_NOT_SELF_SIGNED,
       (cc.CERT_SHA256 != digest(c.CERTIFICATE, 'sha256')) AS HAS_CERT_SHA256_MISMATCH,
       (to_date(cc.VALID_TO, 'YYYY.MM.DD') != date_trunc('day', x509_notAfter(c.CERTIFICATE))) AS HAS_NOTAFTER_MISMATCH,
       (coalesce(sort_delimited_list(cc.APPLE_TRUST_BITS, ';'), '') != coalesce(apple.PURPOSES, '')) AS HAS_APPLE_TRUST_BITS_MISMATCH,
       ((CASE WHEN cc.CHROME_STATUS = 'Included' THEN 'Server Authentication' ELSE '' END) != coalesce(chrome.PURPOSES, '')) AS HAS_CHROME_TRUST_BITS_MISMATCH,
       (coalesce(sort_delimited_list(cc.MICROSOFT_EKUS, ';'), '') != coalesce(microsoft.PURPOSES, '')) AS HAS_MICROSOFT_EKUS_MISMATCH,
       (coalesce(sort_delimited_list(nullif(cc.MOZILLA_TRUST_BITS, 'All Trust Bits Turned Off'), ';'), '') != coalesce(mozilla.PURPOSES, '')) AS HAS_MOZILLA_TRUST_BITS_MISMATCH,
       /* CA Owners (sourced from CCADB only) */
       cc.INCLUDED_CERTIFICATE_OWNER, nullif(cc.SUBORDINATE_CA_OWNER, '') SUBORDINATE_CA_OWNER,
       /* Certificate Name */
       cc.CERT_NAME AS CERT_NAME_CCADB, get_ca_name_attribute(ca.ID) AS CERT_NAME_CRTSH,
       /* Certificate Type */
       cc.CERT_RECORD_TYPE AS CERT_RECORD_TYPE_CCADB, CASE WHEN x509_verify(c.CERTIFICATE, ca.PUBLIC_KEY) THEN 'Root Certificate' ELSE 'Intermediate Certificate' END AS CERT_RECORD_TYPE_CRTSH,
       /* SHA-256 Fingerprint */
       upper(encode(cc.CERT_SHA256, 'hex')) AS CERT_SHA256_CCADB, upper(encode(digest(c.CERTIFICATE, 'sha256'), 'hex')) AS CERT_SHA256_CRTSH,
       /* Valid To */
       cc.VALID_TO AS VALID_TO_CCADB, x509_notAfter(c.CERTIFICATE) AS VALID_TO_CRTSH,
       /* Root Store Statuses */
       sort_delimited_list(cc.APPLE_TRUST_BITS, ';') AS APPLE_TRUST_BITS_CCADB, apple.PURPOSES AS APPLE_TRUST_BITS_CRTSH,
       CASE WHEN cc.CHROME_STATUS = 'Included' THEN 'Server Authentication' ELSE NULL END AS CHROME_TRUST_BITS_CCADB, chrome.PURPOSES AS CHROME_TRUST_BITS_CRTSH,
       sort_delimited_list(cc.MICROSOFT_EKUS, ';') AS MICROSOFT_EKUS_CCADB, microsoft.PURPOSES AS MICROSOFT_EKUS_CRTSH,
       sort_delimited_list(nullif(cc.MOZILLA_TRUST_BITS, 'All Trust Bits Turned Off'), ';') AS MOZILLA_TRUST_BITS_CCADB, mozilla.PURPOSES AS MOZILLA_TRUST_BITS_CRTSH
  FROM ccadb_certificate cc
         JOIN ca_certificate cac ON (cc.CERTIFICATE_ID = cac.CERTIFICATE_ID)
         JOIN ca ON (cac.CA_ID = ca.ID)
         JOIN certificate c ON (cac.CERTIFICATE_ID = c.ID)
         LEFT JOIN LATERAL (
           SELECT array_to_string(array_agg(coalesce(tp.OID_NAME, tp.PURPOSE) ORDER BY coalesce(tp.OID_NAME, tp.PURPOSE)), ';') AS PURPOSES
             FROM root_trust_purpose rtp
                    JOIN trust_purpose tp ON (rtp.TRUST_PURPOSE_ID = tp.ID)
             WHERE rtp.CERTIFICATE_ID = c.ID
               AND rtp.TRUST_CONTEXT_ID = 12
               AND coalesce(rtp.DISABLED_FROM, 'infinity'::timestamp) > now() AT TIME ZONE 'UTC'
               AND coalesce(rtp.NOTBEFORE_UNTIL, 'infinity'::timestamp) > now() AT TIME ZONE 'UTC'
         ) apple ON TRUE
         LEFT JOIN LATERAL (
           SELECT array_to_string(array_agg(tp.PURPOSE ORDER BY tp.PURPOSE), ';') AS PURPOSES
             FROM root_trust_purpose rtp
                    JOIN trust_purpose tp ON (rtp.TRUST_PURPOSE_ID = tp.ID)
             WHERE rtp.CERTIFICATE_ID = c.ID
               AND rtp.TRUST_CONTEXT_ID = 6
               AND coalesce(rtp.DISABLED_FROM, 'infinity'::timestamp) > now() AT TIME ZONE 'UTC'
               AND coalesce(rtp.NOTBEFORE_UNTIL, 'infinity'::timestamp) > now() AT TIME ZONE 'UTC'
         ) chrome ON TRUE
         LEFT JOIN LATERAL (
           SELECT array_to_string(array_agg(tp.PURPOSE ORDER BY tp.PURPOSE), ';') AS PURPOSES
             FROM root_trust_purpose rtp
                    JOIN trust_purpose tp ON (rtp.TRUST_PURPOSE_ID = tp.ID)
             WHERE rtp.CERTIFICATE_ID = c.ID
               AND rtp.TRUST_CONTEXT_ID = 1
               AND coalesce(rtp.DISABLED_FROM, 'infinity'::timestamp) > now() AT TIME ZONE 'UTC'
               AND coalesce(rtp.NOTBEFORE_UNTIL, 'infinity'::timestamp) > now() AT TIME ZONE 'UTC'
         ) microsoft ON TRUE
         LEFT JOIN LATERAL (
           SELECT array_to_string(array_agg(CASE rtp.TRUST_PURPOSE_ID WHEN 1 THEN 'Websites' WHEN 3 THEN 'Email' END ORDER BY rtp.TRUST_PURPOSE_ID DESC), ';') AS PURPOSES
             FROM root_trust_purpose rtp
             WHERE rtp.CERTIFICATE_ID = c.ID
               AND rtp.TRUST_CONTEXT_ID = 5
               AND coalesce(rtp.DISABLED_FROM, 'infinity'::timestamp) > now() AT TIME ZONE 'UTC'
               AND coalesce(rtp.NOTBEFORE_UNTIL, 'infinity'::timestamp) > now() AT TIME ZONE 'UTC'
         ) mozilla ON TRUE
  WHERE cc.CERT_RECORD_TYPE = 'Root Certificate'
  ORDER BY HAS_ANY_DISCREPANCIES, cc.INCLUDED_CERTIFICATE_OWNER, cc.SUBORDINATE_CA_OWNER, cc.CERT_NAME
 ) TO 'roots_compared.csv' CSV HEADER
 SQL

  RESULT=$?
  cat $ERRORFILE
  echo "[Attempt $i]: psql returned $RESULT (expecting 0)."
  if [ "$RESULT" -eq "0" ]; then
    grep ERROR $ERRORFILE >/dev/null
    RESULT=$?
    echo "[Attempt $i]: \"grep ERROR\" returned $RESULT (expecting !=0)."
    if [ "$RESULT" -ne "0" ]; then
      sed -i "s/<timestamp>/$TIMESTAMP/g" roots_compared.csv
      break
    fi
  fi
  echo
 done

 rm -f $ERRORFILE
diff --git a/roots_compared.csv b/roots_compared.csv
	#!/bin/bash

	# This script uses the public crt.sh database to compare Root Certificate information in crt.sh against the same information in the CCADB AllCertificateRecordsCSVFormatv2 and AllIncludedRootCertsCSV reports.
	#
	# 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/
	#
	# Since long-running crt.sh:5432 queries are often killed off for various reasons, this script makes multiple attempts. If all of the attempts fail, try running this script again later.

	ERRORFILE=`mktemp`

	TIMESTAMP=`date -Iseconds \| sed "s/+00:00$/Z/g"`

	for i in {1..10}; do
	cat <<SQL \| tr -d '\n' \| psql -h crt.sh -p 5432 -U guest -d certwatch -v ON_ERROR_STOP=1 -X 2>$ERRORFILE
	\COPY (
	SELECT /* Record identifiers */
	cc.CCADB_RECORD_ID, c.ID AS CRTSH_ID,
	/* Discrepancy tracking */
	(
	(cc.CERT_NAME != get_ca_name_attribute(ca.ID))
	OR (NOT x509_verify(c.CERTIFICATE, ca.PUBLIC_KEY))
	OR (cc.CERT_SHA256 != digest(c.CERTIFICATE, 'sha256'))
	OR (to_date(cc.VALID_TO, 'YYYY.MM.DD') != date_trunc('day', x509_notAfter(c.CERTIFICATE)))
	OR (coalesce(sort_delimited_list(cc.APPLE_TRUST_BITS, ';'), '') != coalesce(apple.PURPOSES, ''))
	OR ((CASE WHEN cc.CHROME_STATUS = 'Included' THEN 'Server Authentication' ELSE '' END) != coalesce(chrome.PURPOSES, ''))
	OR (coalesce(sort_delimited_list(cc.MICROSOFT_EKUS, ';'), '') != coalesce(microsoft.PURPOSES, ''))
	OR (coalesce(sort_delimited_list(nullif(cc.MOZILLA_TRUST_BITS, 'All Trust Bits Turned Off'), ';'), '') != coalesce(mozilla.PURPOSES, ''))
	) AS HAS_ANY_DISCREPANCIES,
	(cc.CERT_NAME != get_ca_name_attribute(ca.ID)) AS HAS_CERT_NAME_MISMATCH,
	(NOT x509_verify(c.CERTIFICATE, ca.PUBLIC_KEY)) AS IS_NOT_SELF_SIGNED,
	(cc.CERT_SHA256 != digest(c.CERTIFICATE, 'sha256')) AS HAS_CERT_SHA256_MISMATCH,
	(to_date(cc.VALID_TO, 'YYYY.MM.DD') != date_trunc('day', x509_notAfter(c.CERTIFICATE))) AS HAS_NOTAFTER_MISMATCH,
	(coalesce(sort_delimited_list(cc.APPLE_TRUST_BITS, ';'), '') != coalesce(apple.PURPOSES, '')) AS HAS_APPLE_TRUST_BITS_MISMATCH,
	((CASE WHEN cc.CHROME_STATUS = 'Included' THEN 'Server Authentication' ELSE '' END) != coalesce(chrome.PURPOSES, '')) AS HAS_CHROME_TRUST_BITS_MISMATCH,
	(coalesce(sort_delimited_list(cc.MICROSOFT_EKUS, ';'), '') != coalesce(microsoft.PURPOSES, '')) AS HAS_MICROSOFT_EKUS_MISMATCH,
	(coalesce(sort_delimited_list(nullif(cc.MOZILLA_TRUST_BITS, 'All Trust Bits Turned Off'), ';'), '') != coalesce(mozilla.PURPOSES, '')) AS HAS_MOZILLA_TRUST_BITS_MISMATCH,
	/* CA Owners (sourced from CCADB only) */
	cc.INCLUDED_CERTIFICATE_OWNER, nullif(cc.SUBORDINATE_CA_OWNER, '') SUBORDINATE_CA_OWNER,
	/* Certificate Name */
	cc.CERT_NAME AS CERT_NAME_CCADB, get_ca_name_attribute(ca.ID) AS CERT_NAME_CRTSH,
	/* Certificate Type */
	cc.CERT_RECORD_TYPE AS CERT_RECORD_TYPE_CCADB, CASE WHEN x509_verify(c.CERTIFICATE, ca.PUBLIC_KEY) THEN 'Root Certificate' ELSE 'Intermediate Certificate' END AS CERT_RECORD_TYPE_CRTSH,
	/* SHA-256 Fingerprint */
	upper(encode(cc.CERT_SHA256, 'hex')) AS CERT_SHA256_CCADB, upper(encode(digest(c.CERTIFICATE, 'sha256'), 'hex')) AS CERT_SHA256_CRTSH,
	/* Valid To */
	cc.VALID_TO AS VALID_TO_CCADB, x509_notAfter(c.CERTIFICATE) AS VALID_TO_CRTSH,
	/* Root Store Statuses */
	sort_delimited_list(cc.APPLE_TRUST_BITS, ';') AS APPLE_TRUST_BITS_CCADB, apple.PURPOSES AS APPLE_TRUST_BITS_CRTSH,
	CASE WHEN cc.CHROME_STATUS = 'Included' THEN 'Server Authentication' ELSE NULL END AS CHROME_TRUST_BITS_CCADB, chrome.PURPOSES AS CHROME_TRUST_BITS_CRTSH,
	sort_delimited_list(cc.MICROSOFT_EKUS, ';') AS MICROSOFT_EKUS_CCADB, microsoft.PURPOSES AS MICROSOFT_EKUS_CRTSH,
	sort_delimited_list(nullif(cc.MOZILLA_TRUST_BITS, 'All Trust Bits Turned Off'), ';') AS MOZILLA_TRUST_BITS_CCADB, mozilla.PURPOSES AS MOZILLA_TRUST_BITS_CRTSH
	FROM ccadb_certificate cc
	JOIN ca_certificate cac ON (cc.CERTIFICATE_ID = cac.CERTIFICATE_ID)
	JOIN ca ON (cac.CA_ID = ca.ID)
	JOIN certificate c ON (cac.CERTIFICATE_ID = c.ID)
	LEFT JOIN LATERAL (
	SELECT array_to_string(array_agg(coalesce(tp.OID_NAME, tp.PURPOSE) ORDER BY coalesce(tp.OID_NAME, tp.PURPOSE)), ';') AS PURPOSES
	FROM root_trust_purpose rtp
	JOIN trust_purpose tp ON (rtp.TRUST_PURPOSE_ID = tp.ID)
	WHERE rtp.CERTIFICATE_ID = c.ID
	AND rtp.TRUST_CONTEXT_ID = 12
	AND coalesce(rtp.DISABLED_FROM, 'infinity'::timestamp) > now() AT TIME ZONE 'UTC'
	AND coalesce(rtp.NOTBEFORE_UNTIL, 'infinity'::timestamp) > now() AT TIME ZONE 'UTC'
	) apple ON TRUE
	LEFT JOIN LATERAL (
	SELECT array_to_string(array_agg(tp.PURPOSE ORDER BY tp.PURPOSE), ';') AS PURPOSES
	FROM root_trust_purpose rtp
	JOIN trust_purpose tp ON (rtp.TRUST_PURPOSE_ID = tp.ID)
	WHERE rtp.CERTIFICATE_ID = c.ID
	AND rtp.TRUST_CONTEXT_ID = 6
	AND coalesce(rtp.DISABLED_FROM, 'infinity'::timestamp) > now() AT TIME ZONE 'UTC'
	AND coalesce(rtp.NOTBEFORE_UNTIL, 'infinity'::timestamp) > now() AT TIME ZONE 'UTC'
	) chrome ON TRUE
	LEFT JOIN LATERAL (
	SELECT array_to_string(array_agg(tp.PURPOSE ORDER BY tp.PURPOSE), ';') AS PURPOSES
	FROM root_trust_purpose rtp
	JOIN trust_purpose tp ON (rtp.TRUST_PURPOSE_ID = tp.ID)
	WHERE rtp.CERTIFICATE_ID = c.ID
	AND rtp.TRUST_CONTEXT_ID = 1
	AND coalesce(rtp.DISABLED_FROM, 'infinity'::timestamp) > now() AT TIME ZONE 'UTC'
	AND coalesce(rtp.NOTBEFORE_UNTIL, 'infinity'::timestamp) > now() AT TIME ZONE 'UTC'
	) microsoft ON TRUE
	LEFT JOIN LATERAL (
	SELECT array_to_string(array_agg(CASE rtp.TRUST_PURPOSE_ID WHEN 1 THEN 'Websites' WHEN 3 THEN 'Email' END ORDER BY rtp.TRUST_PURPOSE_ID DESC), ';') AS PURPOSES
	FROM root_trust_purpose rtp
	WHERE rtp.CERTIFICATE_ID = c.ID
	AND rtp.TRUST_CONTEXT_ID = 5
	AND coalesce(rtp.DISABLED_FROM, 'infinity'::timestamp) > now() AT TIME ZONE 'UTC'
	AND coalesce(rtp.NOTBEFORE_UNTIL, 'infinity'::timestamp) > now() AT TIME ZONE 'UTC'
	) mozilla ON TRUE
	WHERE cc.CERT_RECORD_TYPE = 'Root Certificate'
	ORDER BY HAS_ANY_DISCREPANCIES, cc.INCLUDED_CERTIFICATE_OWNER, cc.SUBORDINATE_CA_OWNER, cc.CERT_NAME
	) TO 'roots_compared.csv' CSV HEADER
	SQL

	RESULT=$?
	cat $ERRORFILE
	echo "[Attempt $i]: psql returned $RESULT (expecting 0)."
	if [ "$RESULT" -eq "0" ]; then
	grep ERROR $ERRORFILE >/dev/null
	RESULT=$?
	echo "[Attempt $i]: \"grep ERROR\" returned $RESULT (expecting !=0)."
	if [ "$RESULT" -ne "0" ]; then
	sed -i "s/<timestamp>/$TIMESTAMP/g" roots_compared.csv
	break
	fi
	fi
	echo
	done

	rm -f $ERRORFILE