Rough analysis of CAs embedding too few SCTs to comply with the 2021-04-21 update to the Apple CT Policy

apple_insufficient_scts.txt

-- Step 1: Find the lowest/earliest certificate ID affected by the updated Apple CT Policy.
-- (Assumptions: (i) Certificate notBefore dates are never forward-dated and (ii) Log entry timestamps are always accurate).

certwatch@certwatch=> SELECT min(c.ID)
  FROM certificate c
  WHERE x509_notBefore(c.CERTIFICATE) >= '2021-04-21'::timestamp
    AND c.ID IN (
      SELECT ctle.CERTIFICATE_ID
        FROM ct_log_entry_2021 ctle
        WHERE ctle.ENTRY_TIMESTAMP >= '2021-04-21'::timestamp
        ORDER BY ctle.ENTRY_TIMESTAMP
        LIMIT 10000
    );
┌────────────┐
│    min     │
├────────────┤
│ 4407299627 │
└────────────┘
(1 row)

Time: 216.808 ms


-- Step 2: Look for precertificates affected by the updated Apple CT Policy for which crt.sh has observed less than 3 SCTs.
certwatch@certwatch=> CREATE TABLE temp_insufficient_scts AS
[more] - > SELECT ctle.CERTIFICATE_ID
[more] - >   FROM certificate c, ct_log_entry ctle
[more] - >   WHERE c.ID >= 4407299627
[more] - >     AND c.ID = ctle.CERTIFICATE_ID
[more] - >     -- Precertificates only.
[more] - >     AND x509_hasExtension(c.CERTIFICATE, '1.3.6.1.4.1.11129.2.4.3', TRUE)
[more] - >     -- Affected by the updated Apple CT Policy.
[more] - >     AND x509_notBefore(c.CERTIFICATE) >= '2021-04-21'::timestamp
[more] - >     AND x509_notAfter(c.CERTIFICATE) - x509_notBefore(c.CERTIFICATE) >= interval '180 days'
[more] - >     -- Optimization: Avoid checking pre-2021 partitions of the "certificate" and "ct_log_entry" tables.
[more] - >     AND coalesce(x509_notAfter(c.CERTIFICATE), 'infinity'::timestamp) >= '2021-01-01'::timestamp AT TIME ZONE 'UTC'
[more] - >     AND ctle.ENTRY_TIMESTAMP >= '2021-01-01'::timestamp
[more] - >   GROUP BY ctle.CERTIFICATE_ID
[more] - >   HAVING count(DISTINCT ctle.CT_LOG_ID) < 3
[more] - >     -- To avoid false positives, only consider log entries older than the MMD.
[more] - >     AND min(ctle.ENTRY_TIMESTAMP) < (now() AT TIME ZONE 'UTC' - interval '24 hours');
SELECT 78766
Time: 2739550.028 ms (45:39.550)


-- Step 3: Filter out CAs that aren't in the Apple Root Certificate Program, and output a CSV summary.
\COPY ( SELECT count(*), ca.ID, ca.NAME, min(c.ID) EXAMPLE_ID_1, max(c.ID) EXAMPLE_ID_2 FROM temp_insufficient_scts tis, certificate c, ca WHERE tis.CERTIFICATE_ID = c.ID AND c.ISSUER_CA_ID = ca.ID AND EXISTS ( SELECT 1 FROM ca_trust_purpose ctp WHERE ctp.CA_ID = c.ISSUER_CA_ID AND ctp.TRUST_CONTEXT_ID = 12 AND ctp.TRUST_PURPOSE_ID = 1 ) GROUP BY ca.ID, ca.NAME ORDER BY count(*) DESC) to 'apple_insufficient_scts.csv' (FORMAT CSV);

apple_insufficient_scts_20210427.csv