De-duplication of public keys in cert query
David Champion
I’m starting a new thread because I made a very puzzling (concerning?) discovery while testing public-key de-duplication logic, and it’d be great if someone can sanity-check it.
First off, with regard to public-key de-duplication, this is the query I have arrived at:
WITH no_dups AS (
SELECT min(ID) ID,
x509_publicKey(CERTIFICATE) PUBKEY
FROM certificate
WHERE ID BETWEEN LAST_SCANNED_ID+1 AND LAST_SCANNED_ID+10000
GROUP BY PUBKEY
)
SELECT regexp_replace(encode(CERTIFICATE, 'base64'), E'\\n', '', 'g'),
ID
FROM certificate
WHERE ID IN (SELECT ID FROM no_dups)
As mentioned in the previous thread, there is no shortage of duplicate public keys in a given range of 10k CRT IDs (about 7.5% contain duplicate keys). So, this seems to me the way to go. Thanks Rob for bringing the x509_publicKey function to my attention.
Second, with regard the puzzling (concerning?) discovery I made while testing the above, I found many instances (well, at least one, but I’m pretty sure there are others) in which the same public key is present in the certificates of several, seemingly unrelated, entities. For example, pycon.org, ndnminh.com and mlaursen.com all have currently-valid certificates containing the same public key. If you visit these three sites, you’ll find no apparent connection between them (at least I didn’t).
This cannot be a good thing, right? Please explain if I've missed something.
Matthew McPherrin
--
You received this message because you are subscribed to the Google Groups "crt.sh" group.
To unsubscribe from this group and stop receiving emails from it, send an email to crtsh+un...@googlegroups.com.
To view this discussion on the web, visit https://groups.google.com/d/msgid/crtsh/080875a0-bdf1-481e-b573-675e114a293en%40googlegroups.com.