CT Logs question

Help
#1

Hello everyone,

I have a question about CT logs and I can’t find much info. I’m trying do make some statistics based on CT log query (for my thesis) but I’m struggling.

I found different results on censys.io than crt.sh (more results on crt.sh) but “wildcard” is not totally working on crt.sh

I tried to get info based on “wildcard query” like %word% on censys.io it works but not on crt. On crt.sh only word% seems to work.

So my question is, is it possible to do that on crt.sh (it seems to have more info there than on Censys) ? If yes, how ? I’m asking because the site seems to crash every time I ran a quite big query.

I also have a second question. I saw on crt.sh that Let’s Encrypt has 2 CAID (7395 and 16418). Does Let’s Encrypt have more than these 2 ?

Thanks for your help !

#2

Hi @hjacquemin

crt.sh has both - pre- and leaf certificates. May be censys.io shows only the leaf certificates.

Do you use raw Sql-code? If yes, you have a lot of options.

I don’t know. But there is a CA-table you can check.

#3

The most recent redesign of crt.sh broke some types of wildcard queries via the website, however, they are still available if you connect to the Postgres database directly:

$ docker run --rm -it -e PGSSLMODE=allow postgres psql -U guest -h crt.sh -p 5432 certwatch
certwatch=> select certificate_id, name_value from certificate_and_identities cai where cai.NAME_VALUE ILIKE ('%' || '.vic.gov.' || '%') limit 5;
certificate_id |      name_value
----------------+-----------------------
    2378339922 | buloke.vic.gov.au
    2378339922 | buloke.vic.gov.au
    2378350050 | portal.esv.vic.gov.au
    2378350050 | portal.esv.vic.gov.au
    2378380114 | login.vla.vic.gov.au

However, you may find that creating such queries may use the database indexes suboptimally and take too long. It could be better to download a cold dataset from scans.io or rapid7 sonar, or crawl CT logs on your own without using an aggregator.

Let’s Encrypt currently only issues from 16418, which is Let’s Encrypt Authority X3.

7395 is Let’s Encrypt Authority X1, and will never issue another certificate.

The next intermediate is Let’s Encrypt Authority X4 (16429), which would be used as a backup.

You can see how they all relate to each other on: https://letsencrypt.org/certificates/

My observation over the past few years has been that censys is usually more up-to-date than crt.sh. But it’s to be expected, they are a largely commercial offering, while crt.sh has all us freeloaders hammering their database, not requiring any kind of registration.

1 Like
#4

Assuming https://censys.io/reports/ctstatus is the current list of CT logs that Censys crawls, they crawl a somewhat different set of logs than crt.sh does: crt.sh crawls ~all ~current production logs (including proposed logs that are in testing) while Censys also monitors some test logs but leaves out some production logs (like Let’s Encrypt Oak!).

Since precertificates are normally logged to multiple logs, Censys probably doesn’t have much less information than crt.sh does, though.

#5

Thanks for the tips !

I’m actually checking the db table to query the columns I need but I can’t find the “cai” table as you used in your query ? I’m not a db expert but

“from certificate_and_identities cai” it should be a table right ? Why it’s not appearing when I do \dt ?

#6

I tripped up on that as well!

It’s a view, not a table. You can see it with \dv - or you can see everything with \d.

Also \d+ certificate_and_identities will show what the view does.

#7

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.