Detecting and excluding Let's Encrypt renewals

[Craig Ringer]

TL;DR: Wrote a lets-encrypt quota usage monitoring query to run on the crt.sh db, see https://github.com/ringerc/letsencrypt-quota-crtsh/blob/main/letsencrypt-quota-report.sql

My organisation has been trying to use the crt.sh database that tracks the Let's Encrypt public certificate transparency log to compute our Let's Encrypt quota usage for each domain. Let's Encrypt does not expose any public interface for checking quota limits or usage, so tracking the public certificate transparency log seems to be the only way to monitor it.

The queries they were using were timing out and failing, at which point I became involved.

The timeouts turned out to be due to some bugs introduced by changes made to the original crt.sh-derived queries, but I'm sure we're not the only ones with these issues so I wanted to share what I've found and done.

The Let's Encrypt certificatesPerNameLimit quota defaults to 50 certs per week issued with a distinct set of subject fully qualified domain names. "Renewals" are not counted, where a renewal is a new cert issued with the exact same set of domain names as a previously-issued cert. Renewals have a separate certificatesPerFQDNSetLimit quota of 5 per week per unique set of domains. See https://letsencrypt.org/docs/rate-limits/ . Pre-certificates are not counted against this limit, only the final certificates issued are counted. Internally in Let's Encrypt's backend the quota is bucketed into 1 hour blocks by requesting next-to-top-level domain e.g. myorg.com .

I tried to express this as an SQL query against the crt.sh certificate log, which I've published to https://github.com/ringerc/letsencrypt-quota-crtsh rather than worry about mail clients mangling attachments etc.

Thanks very much for the database - it's astounding how well it copes with what must be a huge load, even across replicas, especially since it's denormalized and there's very little pre-computed info for the certs built up in expression indexes. The custom full-text search trick it uses is extremely clever.

[r...@sectigo.com]

https://github.com/ringerc/letsencrypt-quota-crtsh gives me a 404 error.  Is this repository marked "private", perhaps?

> Thanks very much for the database - it's astounding how well it copes with what must be a huge load, even across replicas, especially since it's denormalized and there's very little pre-computed info for the certs built up in expression indexes. The custom full-text search trick it uses is extremely clever.

Thanks!

[Craig Ringer]

On Friday, July 21, 2023 at 4:26:56 AM UTC+12 r...@sectigo.com wrote:

https://github.com/ringerc/letsencrypt-quota-crtsh gives me a 404 error.  Is this repository marked "private", perhaps?

Whoops! I didn't think I could even create private repos on my personal github account. Fixed now.

Meanwhile per your reply in https://groups.google.com/g/crtsh/c/VcIC2YHlwl4/m/TK51UlPTAwAJ other readers should be aware that the assumptions I make in this query are certainly incorrect for some issuers other than Let's Encrypt, and may not be correct for Let's Encrypt for all circumstances. In particular my assumptions that a pre-certificate will always be present in the CT log and will become visible in a timely manner are invalid for certificate transparency log holders.

I might need to go back to Let's Encrypt and see if they'd be willing to consider exposing an API for querying the quota limit and the current usage for a domain as a longer-term solution. I've read a bunch of the relevant Boulder backend code-base and it's clear that the internal APIs are already there and quite conveniently available, so exposing them as a web-service-endpoint API looks surprisingly straightforward in purely technical terms. Probably something I can put together my own PR for. Whether they'd be willing to accept a patch to do so is another matter - and whether that new API endpoint would in turn need its own rate-limiting... but I think I'd best explore that direction. Using the CT log for the purpose is a bit of a hack at best. If I get the time to raise a request and PR with them I'll try to remember to link it here.

[Matthew McPherrin]

We (Let's Encrypt) are currently re-implementing our rate-limits, and the new version will hopefully have ways of exposing ratelimit data in the API.

The first PR for this new system has landed, though there's still a lot of work left to do:  https://github.com/letsencrypt/boulder/pull/6947

You can follow along with the wider effort at https://github.com/letsencrypt/boulder/issues/5545

--
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/06f181e8-7cd5-419d-9cd3-33991afe1316n%40googlegroups.com.

[Craig Ringer]

On Monday, July 24, 2023 at 12:01:56 PM UTC+12 Matthew McPherrin wrote:

We (Let's Encrypt) are currently re-implementing our rate-limits, and the new version will hopefully have ways of exposing ratelimit data in the API.

The first PR for this new system has landed, though there's still a lot of work left to do:  https://github.com/letsencrypt/boulder/pull/6947

You can follow along with the wider effort at https://github.com/letsencrypt/boulder/issues/5545

Brilliant! Thanks so much for letting me (and anyone else who finds this thread) know. All I found when researching this was a number of very old questions on the LE community discussion pages about quota checking, all of which pretty much mentioned that there was no API to expose it and pointed to crt.sh for monitoring the certificate transparency log as a fallback.

Looks like it's very early days, and quite new. The proposal makes a lot of sense though - I was quite surprised to see that LE was using upserts on a MySQL table to track unique-fqdn-set quota and duplicate-certs-for-fqdn-set quota given how DB-expensive and contention-heavy that logic is likely to be. I'll comment to link to this thread and a couple of past requests from others about quota visibility - both "what's the quota assignment for domain X" and "what's the current quota usage for domain X". It's not presently called out explicitly as a goal in the issue and I don't want to drive-by scope-creep it, but it sounds like there's no point even trying to propose such quota-visibility APIs until this quota rework is done, whether as part of that ticket or a follow-on one.

I'll see if I can convince my org to let me get more involved on this.