Log monitor stopped, and Upcoming Changes

[Rob Stradling]

First, the bad news...

The idea that "2 billion certificates ought to be enough for any PKI" (to misquote Bill Gates) didn't seem that daft to me back when crt.sh was born (June 2015).  Let's Encrypt had not yet launched, there were only a few million active certificates in the WebPKI (https://blog.cloudflare.com/introducing-universal-ssl/), and certificate lifetimes of >=1yr were the norm.  However...

At 05:55 UTC yesterday, the int32 sequence used to assign crt.sh certificate IDs overflowed.  As a result, crt.sh is not currently able to ingest new log entries.  Although this issue was foreseen over 2 years ago (https://github.com/crtsh/certwatch_db/issues/27) and I wanted to address it before we ran out of numbers, unfortunately I was not able to dedicate time to this task until fairly recently.

The good news is that work to address this problem is well underway!

My attempts at updating certificate ID field lengths on the existing crt.sh database didn't go too well, so I abandoned that effort in favour of building a new database and then copying the data into it.  A new database has offered the perfect opportunity to do a technology refresh and to address some other database maintenance concerns.  Here are the headlines of what's coming...

  - Upgrade from PostgreSQL 9.5 to PostgreSQL 12.

  - Upgrade from OpenSSL 1.0.2 to OpenSSL 1.1.1.

  - Partition the "certificate" (by notAfter) and "ct_log_entry" (by SCT timestamp) tables, so that database maintenance is easier and so that we're in a better position to be able to delete long-expired certificates if/when we decide to do so.

  - Drop the "certificate_identity" table, replacing it with Full Text Search.  It's sad that this will break backwards compatibility for some users of crt.sh:5432; however, it approximately halves the size of the database, so I've concluded it needs to happen.

The new database is currently busy importing data and building indexes.  I'm hoping it'll be production-ready and available by early next week (caveat: I am rubbish at predicting ETAs).  I'll update this thread with more details when I have them.

[Chris Hills]

Thanks for your work on this Rob! How is it going?

[Shubham Agarwal]

Hello Rob..

Thanks for the update. Your work is awesome. Can you predict some date when importing task will be completed ?

Regards

Shubham

[Rob Stradling]

Status update:

I finished importing the data and creating the indexes on the new database/schema early last week, as predicted.  However, it's been taking me rather longer than expected to complete the corresponding tweaks to the web application and other crt.sh components.

Our infrastructure team recently decided to move the crt.sh service to a different datacentre, and they took the opportunity to achieve this by spinning up new servers in the new datacentre to run the new front-end slave databases.  They also needed to spin up new load-balancers, configure new storage, etc, which they've been doing in parallel with my efforts on the database/application side.

Although there's still more work for me to do to restore all of the crt.sh functionality that you all know and love(*), we have today made the new crt.sh front-end webservers publicly available.  If you want to reach these new webservers instead of the old ones, for now please add the following entry to your "hosts" file:

91.199.212.73   crt.sh

There's no port 5432 access yet, but this will follow soon.

I don't yet have ETAs for when we'll be ready to flip the A/AAAA records for crt.sh to point to the new servers, or for when we will then decommission the old servers.

I'll post further updates as and when I have more to say.  Thanks everyone for your patience!

(*) I believe that all of the certificate search options in the web interface are working correctly, but please let me know if you see any issues.

The ct_monitor application has been running since Friday last week, but the backlog has grown slightly rather than gone down.  I'm working on profiling the code to figure out what's slow and what can be optimized.

On-demand certificate linting works, but the automated linting and associated reports are currently disabled, pending further work from me.

The various components that monitor CRLs, OCSP, CCADB, trust chains, etc, are all currently disabled, pending further work from me.

[Chris Hills]

Thanks Rob!

The query Identity LIKE '%'; Issuer CA ID = 16418 is failing and throwing an error about missing column ca.no_of_certs_issued

[Rob Stradling]

Fixed.  Thanks for reporting.

The "no_of_certs_issued" column has been renamed on the new crt.sh database.  Each "ca" record now keeps track of:

- the number of certificates issued.

- the number of precertificates issued.

- the number of certificates that have expired.

- the number of precertificates that have expired.

[Rob Stradling]

On Monday, December 9, 2019 at 11:32:35 PM UTC, Rob Stradling wrote:

Status update:

 <snip>

Although there's still more work for me to do to restore all of the crt.sh functionality that you all know and love(*), we have today made the new crt.sh front-end webservers publicly available.  If you want to reach these new webservers instead of the old ones, for now please add the following entry to your "hosts" file:

91.199.212.73   crt.sh

There's no port 5432 access yet, but this will follow soon.

Port 5432 access is now available.

<snip>

The ct_monitor application has been running since Friday last week, but the backlog has grown slightly rather than gone down.  I'm working on profiling the code to figure out what's slow and what can be optimized.

I've optimized some things.  The backlog is now going down, but I expect it'll take a few weeks for it to disappear completely.

[Nathan Goshen]

I hope I'm not interrupting here. How can I look *NOW* for urls that were registered under a certain CA. Thanks

[Rob Stradling]

Hi Nathan.  No need to apologize!  I've just fixed it, so "Identity LIKE '%'; Issuer CA ID = 16418" queries now actually show results again!

[Matt Lewis]

Hi Rob, 

It seems the backlog is continuing to grow- is there any relative timeline to when the backlog should start reduce/crt.sh fully chew through it?

Additionally, do you have an average trailing wait time for crt.sh? (ie. is it currently 7 days delayed, 14 days, etc.)? 

Thanks for all the work, looking forward to hearing back. 

[Rob Stradling]

On Wednesday, December 25, 2019 at 5:41:01 PM UTC, Matt Lewis wrote:

Hi Rob, 

It seems the backlog is continuing to grow- is there any relative timeline to when the backlog should start reduce/crt.sh fully chew through it?

Hi Matt.  I just restarted the ct_monitor application and now the backlog is shrinking again.

I don't know why restarting ct_monitor made a difference.  However, since it clearly helped, I've just configured ct_monitor (and the other long-running applications - crl_monitor and ocsp_monitor) to be automatically restarted every hour.

Additionally, do you have an average trailing wait time for crt.sh? (ie. is it currently 7 days delayed, 14 days, etc.)? 

We can get a pretty good idea of that by looking at the most recent SCT timestamps that crt.sh has ingested for each of the affected logs...

┌───────────────────────────────────────────┬─────────────────────────┬──────────┐
│                    url                    │ latest_entry_timestamp  │ backlog  │
├───────────────────────────────────────────┼─────────────────────────┼──────────┤
│ https://oak.ct.letsencrypt.org/2020       │ 2019-12-09 15:45:10.122 │ 44508992 │
│ https://ct.googleapis.com/logs/xenon2020  │ 2019-12-10 04:01:22.657 │ 41579326 │
│ https://ct.googleapis.com/logs/argon2020  │ 2019-12-07 22:47:45.258 │ 39656366 │
│ https://ct.googleapis.com/rocketeer       │ 2019-12-23 23:45:39.264 │  3777285 │
│ https://ct.googleapis.com/pilot           │ 2019-12-24 21:54:33.989 │  3089800 │
│ https://ct.cloudflare.com/logs/nimbus2020 │ 2019-12-26 08:20:13.105 │  1409520 │
└───────────────────────────────────────────┴─────────────────────────┴──────────┘

This is the query I used to produce the table above...

SELECT ctl.URL,

       latest.ENTRY_TIMESTAMP AS LATEST_ENTRY_TIMESTAMP,

       (coalesce(ctl.TREE_SIZE, 0) - latest_entry.ENTRY_ID - 1) AS BACKLOG

  FROM ct_log ctl

         LEFT JOIN LATERAL (

           SELECT max(ENTRY_ID) ENTRY_ID

             FROM ct_log_entry ctle

             WHERE ctle.CT_LOG_ID = ctl.ID

         ) latest_entry ON TRUE

         LEFT JOIN LATERAL (

           SELECT ctle2.ENTRY_TIMESTAMP

             FROM ct_log_entry ctle2

             WHERE ctle2.CT_LOG_ID = ctl.ID

               AND ctle2.ENTRY_ID = latest_entry.ENTRY_ID

         ) latest ON TRUE

  WHERE ctl.IS_ACTIVE = 't'

    AND (coalesce(ctl.TREE_SIZE, 0) - latest_entry.ENTRY_ID - 1) > 0

  ORDER BY BACKLOG DESC;

[Rob Stradling]

I've just added a "Latest Entry Age" column to https://crt.sh/monitored-logs

[Rob Stradling]

On Friday, December 27, 2019 at 2:53:07 PM UTC, Rob Stradling wrote:

I've just added a "Latest Entry Age" column to https://crt.sh/monitored-logs

To clarify: this is on the new crt.sh website (which currently still needs a "hosts" entry if you want to reach it; see earlier in this thread).

[Matt Lewis]

Hi Rob, 

Thank you very much for the effort here! The backlog is indeed shrinking and I do see the last entry age field on the new website. Thanks again!

[Nummer378]

On the 'new' website, clicking on 'Download Certificate: PEM', when visiting a recent certificate (large ID number) throws the following error:

value "<cert id>" is out of range for type integer

 

SQL statement "SELECT replace(encode(c.CERTIFICATE, 'base64'), chr(10), '') FROM certificate c WHERE c.ID = cert_identifier::integer" PL/pgSQL function download_cert(text) line 12 at SQL statement PL/pgSQL function web_apis(text,text[],text[]) line 177 at RETURN

[Rob Stradling]

Thanks!  Fixed.

[Rob Stradling]

On Monday, December 9, 2019 at 11:32:35 PM UTC, Rob Stradling wrote:

<snip>

I don't yet have ETAs for when we'll be ready to flip the A/AAAA records for crt.sh to point to the new servers, or for when we will then decommission the old servers.

The new crt.sh service is now LIVE!  We just updated the A/AAAA records to the following IP addresses (note: the TTL is 1 hour, so please be patient :-) ):

NEW:

> host crt.sh
crt.sh has address 91.199.212.73
crt.sh has IPv6 address 2a0e:ac00:c7:d449::5bc7:d449

If you've added a "hosts" entry to access the preview of the new crt.sh service, please remember to delete it.

To determine if you're using the old or new https://crt.sh/ service, you can look at the copyright year at the bottom of any webpage: -2019 for old, and -2020 for new.

As I mentioned earlier in this thread, the new crt.sh service uses PostgreSQL's Full Text Search capability.  I'll post another message soon with some more details.

The old crt.sh service is still available at the IP addresses below.  I anticipate that we'll start to decommission the old servers in a month or two.

OLD:

> host crt.sh
crt.sh has address 91.199.212.48
crt.sh has IPv6 address 2a0e:ac00:c7:d430::5bc7:d430

[Chris Hills]

I'm sure I speak on behalf of most subscribers when I say thank you!

[Tom Lancaster]

Hi Rob,

I'm sure you're already aware but the ability to query a certificate by its prefix is no longer available. 

e.g. you can still do:

%www.google.com (useful for finding new certificates belonging to Google)

But you cannot do:

www.google.com% (useful for finding things impersonating Google)

Do you have any plans to support this query type again? Would it be best to attempt such queries via the SQL interface instead? (if so, could you give an example query?)

Cheers,

Tom

[Rob Stradling]

Hi Tom.  This is an artefact of moving to Full Text Search (for which I still owe this forum a detailed post).  TBH, I didn't consider your prefix search use case when I was working on this.  

In the current implementation:

For a dNSName identity of "www.example.com", crt.sh's Full Text Search index includes these lexemes:

  "www.example.com"

  "example.com"

  "com"

And for a dNSName identity of "www.example.com.evil.com", crt.sh's Full Text Search index includes these lexemes:

  "www.example.com.evil.com"

  "example.com.evil.com"

  "com.evil.com"

  "evil.com"

  "com"

AFAICT, postgres's Full Text Search capabilities don't support prefix matching on lexemes.

I'm definitely open to any suggestions on how to do this better!

Thinking aloud...

1. Would it make sense to treat each domain component as a separate lexeme?  e.g.,

  "www"

  "example"

  "com"

  "evil"

This would shrink the size of the Full Text Search index and would enable the prefix searching use case, but it would also increase the number of false positives returned by the index that would then need to be filtered out.  Would that hurt performance too much?

2. Would it make sense to change the implementation so that the Full Text Search index would include all of the following lexemes (for example):

  "www"

  "www.example"

  "www.example.com"

  "www.example.com.evil"

  "www.example.com.evil.com"

  "example.com.evil.com"

  "com.evil.com"

  "evil.com"

  "com"

?

3. On the old postgres 9.5 database, I experimented some time ago with a pg_trgm index on the certificate identities.  It worked, but it wasn't quick enough to use as the main or only indexing method.  (From memory, it took ~8s to search for '%crt.sh%' and ~40s to search for '%paypal.com%').  Maybe it would be worth giving this another shot.

[Tom Lancaster]

Hi Rob,

Many thanks for the response.

I'm far from a DB guru so can't recommend a best approach (perhaps others can?). 

I would _guess_ that approach #1 would be the best, since generally a larger index probably means slower search times and increased requirements on your side for storing all this data.

Cheers,

Tom

--
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/46fb3e9b-c98e-4042-a5e5-9a3fd36ef56b%40googlegroups.com.

[Rob Stradling]

Hi Tom.  Thanks for your thoughts.  I wouldn't call myself a DB guru either.  At some point I'll experiment with one of more of these ideas and see if anything helps.

To unsubscribe from this group and stop receiving emails from it, send an email to crtsh+unsubscribe@googlegroups.com.

[Steven]

How how the following ideas would impact the database or the search performance.

• Stopping lexeme creation at Public Suffix +1
• Adding a Public Suffix +1 index, falling back to the Public Suffix, and then to any non-standard TLD.
• Adding indexing by decoded IDN names

Full lexeme:

 "www.example.com.evil.com"

 "www.example.com.evil.co.uk"

 "www.example.com.evil.香港"

 "www.example.com.evil.xn--j6w193g"

 "www.example.com.eviΙ.com"

 "www.example.com.xn--evi-zuc.com"

Public Suffix +1 lexeme:

 "evil.com"

 "evil.co.uk"

 "evil.香港"

 "evil.xn--j6w193g"

 "eviΙ.com"

 "xn--evi-zuc.com"

Other lexemes:

 "www"

 "www.example"

 "www.example.com"

 "example.com"

 "com"

•  Or only using the Public Suffix:

Full lexeme:

 "www.example.com.evil.com"

 "www.example.com.evil.co.uk"

 "www.example.com.evil.香港"

 "www.example.com.evil.xn--j6w193g"

 "www.example.com.eviΙ.com"

 "www.example.com.xn--evi-zuc.com"

Public Suffix lexeme:

 "com"

 "co.uk"

 "香港"

 "xn--j6w193g"

Other lexemes:

 "www"

 "www.example"

 "www.example.com"

 "www.example.com.evil"

 "example.com.evil"

 "com.evil"

 "www.example.com.eviΙ"

 "example.com.eviΙ"

 "com.eviΙ"

 "www.example.com.xn--evi-zuc"

 "example.com.xn--evi-zuc"

 "com.xn--evi-zuc"

 

Using the domains above as something I would want to find, the following are the query patterns I have used on crt.sh. 

Only the first example currently returns the expected results. I expect that the last two would only succeed if the search was limited to a few days or crt.sh ID ranges, which was only possible using PostgreSQL.

 "%.evil.co.uk"

 "www.example.com.%"

 "www%"

 "%.%.evil.co.uk"

 "www.%.%"

 "www.%.co.uk"

 "www.example%.co.uk"

Steven

To unsubscribe from this group and stop receiving emails from it, send an email to cr...@googlegroups.com.

[Jacob Hoffman-Andrews]

On 1/13/20 3:38 AM, Rob Stradling wrote:
> Thinking aloud...
>
> 1. Would it make sense to treat each domain component as a separate
> lexeme?  e.g.,
>   "www"
>   "example"
>   "com"
>   "evil"
> This would shrink the size of the Full Text Search index and would
> enable the prefix searching use case, but it would also increase the
> number of false positives returned by the index that would then need
> to be filtered out.  Would that hurt performance too much?

I think this makes a lot of sense. You'd want some stopwords, like "www"
and "com", that you wouldn't index at all because they are too common.
Even though without knowing the details of Postgres' implementation, it
seems like intersecting the set of results for "www" and "com" would be
prohibitively expensive. So in the example of
"www.example.com.evil.com", with the existing strategy plus the new new,
plus stopwording, you'd have:

"www.example.com.evil.com"
"example.com.evil.com"
"com.evil.com"
"evil.com"
"example"
"evil"

[Donncha Fahy]

Should be possible to do prefix matching on lexemes using the prefix:* syntax e.g.

SELECT DISTINCT lexeme

FROM certificate, unnest(identities(certificate))

WHERE identities(certificate) @@ 'wikipedia.org:*' and lexeme like '%wikipedia.org%';

[Rob Stradling]

Thanks for pointing this out, Donncha!  Clearly I didn't read the postgres manual closely enough.  :-)

Armed with this new (to me) knowledge, I've just updated https://crt.sh/ so that it can now do prefix matching when searching for identities.

For backwards compatibility, both syntaxes ('identity:*' and 'identity%') are accepted.  https://crt.sh/ will automatically convert the second form to the first form.  So, for example, the following two searches are equivalent (and work once again!):

https://crt.sh/?q=www.google.com.%25

https://crt.sh/?q=www.google.com.%3A*

[Rob Stradling]

Thanks Jacob.  I hadn't thought of doing both strategies together.  Good idea!

I don't have the bandwidth to work on this now, but I've added it to my TODO list.

[Tom Lancaster]

Thanks for the quick fix Rob // Donncha!

Much appreciated.

--
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/532ce964-c1a4-4185-8826-384f45e223d3%40googlegroups.com.

[Steven]

If the query is deconstructed, one or two looser to_tsquery followed by the original strict query returns the expected results, at least for all of my current certificate queries.

For example, for a query on 'www.bad%oogle.com', I believe this works:

WHERE to_tsquery('www.bad:*') @@ identities(cai.CERTIFICATE)
AND to_tsquery('oogle.com') @@ identities(cai.CERTIFICATE)
AND cai.NAME_VALUE ILIKE ('www.bad%oogle.com')

And for 'bad.subdomain.%.%'

WHERE to_tsquery('bad.subdomain:*') @@ identities(cai.CERTIFICATE)
AND cai.NAME_VALUE ILIKE ('bad.subdomain.%.%')

[Steven]

It doesn't work well for a substring in the middle.

For example, for a query on '%adgoogl%', I don't believe this will work:

WHERE to_tsquery('adgoogl:*') @@ identities(cai.CERTIFICATE)
    AND cai.NAME_VALUE ILIKE ('%adgoogl%')

And it doesn't work for partial domain names:

WHERE to_tsquery('vil.com') @@ identities(cai.CERTIFICATE)

    AND cai.NAME_VALUE ILIKE ('%vil.com')

And it doesn't work for common stems:

WHERE to_tsquery('security:*') @@ identities(cai.CERTIFICATE)

[Steven]

Using what Donncha provided, the following appears to give the output that the web interface provides.  I still do not see a way to get the desired results for a query like %adgoogl% or %vil.com.

For queries like %adgoo%e.evil.com, it can be converted into multiple criteria that return expected results quickly.

For "www.badgoogle.evil.com"

querystring = "%adgoo%e.evil.com"

ts_query1 = "evil.com"

ts_query2 = "evil.com"

For www.example.com.evil.com

querystring = "www.exam%.evil.com"

ts_query1 = "www.exam:*"

ts_query2 = "evil.com"

WITH ci AS (

SELECT min(sub.CERTIFICATE_ID) ID,

min(sub.ISSUER_CA_ID) ISSUER_CA_ID,

array_agg(DISTINCT sub.NAME_VALUE) NAME_VALUES,

x509_subjectName(sub.CERTIFICATE) SUBJECT_NAME,

x509_notBefore(sub.CERTIFICATE) NOT_BEFORE,

x509_notAfter(sub.CERTIFICATE) NOT_AFTER

FROM (SELECT DISTINCT

cai.CERTIFICATE_ID CERTIFICATE_ID,

cai.ISSUER_CA_ID ISSUER_CA_ID,

cai.NAME_VALUE NAME_VALUE,

cai.CERTIFICATE CERTIFICATE

FROM certificate ci, unnest(identities(certificate)),

certificate_and_identities cai

WHERE cai.CERTIFICATE_ID = ci.id

AND identities(ci.certificate) @@ %(ts_query1)s

AND identities(ci.certificate) @@ %(ts_query2)s

AND lexeme ILIKE %(querystring)s

AND coalesce(x509_notAfter(cai.CERTIFICATE), 'infinity'::timestamp) > now() AT TIME ZONE 'UTC'

) sub

GROUP BY sub.CERTIFICATE

)

SELECT ci.ISSUER_CA_ID,

ca.NAME ISSUER_NAME,

ci.NAME_VALUES,

ci.ID ID,

le.ENTRY_TIMESTAMP,

ci.NOT_BEFORE,

ci.NOT_AFTER

FROM ci

LEFT JOIN LATERAL (

SELECT min(ctle.ENTRY_TIMESTAMP) ENTRY_TIMESTAMP

FROM ct_log_entry ctle

WHERE ctle.CERTIFICATE_ID = ci.ID

) le ON TRUE,

ca

WHERE ci.ISSUER_CA_ID = ca.ID

ORDER BY le.ENTRY_TIMESTAMP DESC;

[Rob Stradling]

Hi Steven.

Thanks for sharing your thoughts on this.  You're correct that the underlying indexes don't support search terms such as %adgoogl% or %vil.com.  Full Text Search supports prefix matching (thanks Donncha!), but it doesn't support suffix matching (this is relevant: https://dba.stackexchange.com/questions/189084/how-to-make-middle-and-suffix-matching-using-full-text-search).  I did experiment with pg_trgm on the "old" certwatch database a while ago; and if/when I get time, I may give this another go.

Also, yes, some of your use cases could be handled by tweaking the filters in the queries.  When I get time...

[Steven]

Based on the current capabilities, I have created a script to clean the input and split it into one ILIKE and one or two text search strings.
It strips anything that doesn't match [-a-z0-9._%], looks for leading text search strings and trailing text strings. It can also extract strings from the middle that could be used as leading text strings.
This allows a query like %.%.evil.com to match on www.example.com.evil.com and not www.evil.com (%.%.evil.com AND evil.com)
Or for www.example%.com (www.example%.com AND www.example:*)
Or for %w.example.com.% (%w.example.com.% AND example.com.:*)
I have not tested it against queries that include underscores, but I don't think the web UI appropriately respects them either.

[Rob Stradling]

On Wednesday, January 8, 2020 at 3:42:58 PM UTC, Rob Stradling wrote:

<snip>

The old crt.sh service is still available at the IP addresses below.  I anticipate that we'll start to decommission the old servers in a month or two.

OLD:

> host crt.sh
crt.sh has address 91.199.212.48
crt.sh has IPv6 address 2a0e:ac00:c7:d430::5bc7:d430

We plan to decommission the old crt.sh servers on Monday 9th March.