crt.sh & Steampipe

[Andrew Dean]

Hi!

I've made an internal app for my organization to help give visibility into our certificate ecosystem, along with email subscriptions to get notified when certificates will expire within a given period and a given subdomain/hostname. It relies on Steampipe's crtsh connection/plugin as the primary data source, and caches responses in a local DB for ~24h so that there is only 1 query per day, per subdomain to the public DB.

However, our root domain has ~16,000 total subdomains/hostnames (which crt.sh will never load), and at least a couple of our subdomains have ~800 related hostnames (many of which are registered to two different Let's Encrypt CNs simultaneously, doubling the amount of certificates compared to hostnames) which also fail fairly consistently.

In both cases, I receive errors like:

• Error: crtsh: pq: canceling statement due to statement timeout (SQLSTATE HV000)
• Error: crtsh: pq: unexpected message 'E'; expected ReadyForQuery (SQLSTATE HV000)
• Error: crtsh: pq: canceling statement due to conflict with recovery (SQLSTATE HV000)
• no error, but an empty table as a return (headers/column names only) when there are definitely certificates.
  

Some of our subdomains have ~500 hostnames/certificates, and those generally get responses without issue, but I'm worried if they continue to grow.

I understand that there is no SLA for crt.sh/responses are "best effort", but is there any way to get better results from crt.sh?

Would connecting directly with the DB(i.e. with Psycopg2), rather than using Steampipe as an intermediary, potentially solve some issues here?

[Tamim]

Hi Andrew, any luck? We want to do the same.