Operations | Monitoring | ITSM | DevOps | Cloud

Clickhouse is an incredible database. Here at Certkit, we’ve long worked in the world of “No SQL” databases like Elasticsearch precisely for their ability to query large amounts of data. But for every database, there’s an amount of data that’s “Too big”. Too big to query quickly or too big to store affordably. Clickhouse manages to thread the needle by efficiently storing truly ridiculous amounts of data while still providing impressive query performance.

In the last post we discussed why we’re building our own Certificate Transparency (CT) search tool. There’s good background on the CT ecosystem in that post, so check it out if you haven’t. This post assumes a certain understanding of terminology covered previously. Now that we know where the CT logs live, and the different kinds of logs, we need to start reading them.

Every TLS certificate issued by a root Certificate Authority (CA) ends up in one more more publicly accessible logs. These logs, collectively, make up the Certificate Transparency (CT) ecosystem. Unfortunately the logs are not very searchable. You can’t easily type in a domain and find all associated certificates. At CertKit we’re building CT monitoring capabilities to notify our customers when a new certificate is issued.

Last week, someone commented on my post about 47-day certificates: This perfectly captures our collective delusion that SSL certificate revocation works. You click a button, the certificate stops working. And why wouldn’t we believe that? Every CA has a big “Revoke Certificate” button right there in the dashboard. It must do something, right? Here’s the dirty truth: most revoked certificates keep working.

Turns out the scariest thing about SSL certificates isn’t when they expire. It’s when they don’t. I wrote about the CA/Browser fight that led to the 47-day certificate mandate. CAs crying about lost revenue, browsers flexing their root program authority, enterprises stuck in the middle. But nobody talks about the security research that started it all: BygoneSSL at DEFCON 2018. Two researchers mining Certificate Transparency logs found something surprising.

For twenty years, Certificate Authorities ran the perfect protection racket. The CAs had a beautiful monopoly. Browsers needed them to keep users safe. Websites needed them to look legitimate. Everyone paid up, nobody asked too many questions. Then the cryptography of most certificates (SHA-1) got shattered, and the browsers realized they’d been played.

You were tired of renewing all those certificates, and Certbot looked so easy. Now you have scripts thousands of lines long filled with command line incantations you have to Google every time you open it. The script is running on all the critical servers. And some of the printers. If someone looks at it the wrong way, a certificate expires.

SSL Certificates have always been a pain in the butt. From the magical OpenSSL incantations to generate a CSR to the various formats that each webserver requires. Remembering what hardware needs which certificates. Managing scheduled renewals and runbooks for which file goes where. Screw anything up and your site is “Not Secure”. And now Apple wants us to do it every 47 days. Remember when we had HTTP-only websites? Or when certificates lasted three years? Then one?