Find certificates with embedded SCTs signed by Dodo's private key

find_embedded_dodo_scts.sql

-- CREATE TABLE temp_dodo_embedded_scts ( certificate_id bigint, issuer_ca_id integer );
-- GRANT SELECT ON temp_dodo_embedded_scts TO GUEST;

\timing on

\set ON_ERROR_STOP on

DO
$$DECLARE
	t_minCertificateID	certificate.ID%TYPE;
	t_maxCertificateID	certificate.ID%TYPE;
	t_start				certificate.ID%TYPE;
	t_end				certificate.ID%TYPE;
	t_rowCount			certificate.ID%TYPE;
BEGIN
	-- 8892800071 is a conservatively estimated lower bound ID that is known to have existed before the misconfigured Sabre private key incident.
	SELECT coalesce(max(tdes.CERTIFICATE_ID), 8892800071)
		INTO t_minCertificateID
		FROM temp_dodo_embedded_scts tdes;

	SELECT max(c.ID)
		INTO t_maxCertificateID
		FROM certificate c;

	t_start := t_minCertificateID;
	t_end := t_minCertificateID;
	WHILE t_end < t_maxCertificateID LOOP
		t_end := t_start + 10000;

		-- Scan all the certificates in this batch, looking for Dodo's LogID.
		INSERT INTO temp_dodo_embedded_scts ( CERTIFICATE_ID, ISSUER_CA_ID )
			SELECT c.ID, c.ISSUER_CA_ID
				FROM certificate c
				WHERE c.ID BETWEEN t_start AND t_end
					AND position(E'\\xdb76fdadac65e7d09508886e2159bd8b90352f5fead3e3dc5e22eb350acc7b98' IN c.CERTIFICATE) > 0;
		GET DIAGNOSTICS t_rowCount = ROW_COUNT;
		COMMIT;

		RAISE NOTICE 'Processed %..% (max=%); found %', t_start, t_end, t_maxCertificateID, t_rowCount;

		t_start := t_end + 1;
	END LOOP;
END$$;

list_embedded_dodo_scts.sql

SELECT count(*), get_ca_name_attribute(ca.ID) AS CA_NAME, cc.CA_OWNER
  FROM temp_dodo_embedded_scts tdes, ca, ca_certificate cac
         LEFT JOIN LATERAL (
           SELECT array_to_string(array_agg(coalesce(nullif(cc.SUBORDINATE_CA_OWNER, ''), cc.INCLUDED_CERTIFICATE_OWNER)), ',') CA_OWNER
             FROM ccadb_certificate cc
             WHERE cac.CERTIFICATE_ID = cc.CERTIFICATE_ID
         ) cc ON TRUE
  WHERE tdes.ISSUER_CA_ID = ca.ID
    AND ca.ID = cac.CA_ID
  GROUP BY ca.ID, cc.CA_OWNER
  ORDER BY cc.CA_OWNER, ca.NAME;