Last active
March 31, 2023 01:12
-
-
Save robstradling/e494d1f8ff561812782f6616be373ff4 to your computer and use it in GitHub Desktop.
Find certificates with embedded SCTs signed by Dodo's private key
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- 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$$; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment