How to monitor all possibilities

[lkip91]

Hello folks, I would like to ask, how to find all posibilities of issued certificates which contains my domain name. Now my script using data from this:

• https://crt.sh/?q=mydomain.com
• https://crt.sh/?q=mydomain%25

However I have noticed there was issued certificate for xy.mydomain.otherdomain.com, and this certificates is not in list on sites which I have mentioned. Could someone help me, how to find or which search term I have to use, to find issued certificates like this xy.mydomain.otherdomain.com? Or is there some better way, how to monitoring issued certificates which cointains my domain name? Thank you in advance.

[Rob Stradling]

Hi.  Please try this: https://crt.sh/?q=mydomain

[lkip91]

Hi, thank you for your quick response, but with your link I get

Certificates	None found

:/

Dňa streda, 16. decembra 2020 14:23:25 UTC+1 Rob Stradling napísal(a):

[r...@sectigo.com]

Ah, sorry, I misremembered how this works.  :-)

I think you're probably running into the same issue described in this thread: https://groups.google.com/g/crtsh/c/hsv9m0837hw

https://crt.sh/ has to limit the size of result sets for performance reasons; and since I haven't found any way to get Full Text Search to sort the results, it only gives you a random subset of the full result set.

Browse to https://crt.sh/?q=mydomain%25&showSQL=y and look at the bottom of the page to see the SQL query it uses.  ($1 is replaced with 'mydomain:*').

To get the IDs of all certificates in the full result set for "mydomain", try this:

psql -h crt.sh -p 5432 -U guest -d certwatch -q -c "SELECT c.ID FROM certificate c WHERE to_tsquery('certwatch', 'mydomain:*') @@ identities(c.CERTIFICATE)" -o output.txt

[lkip91]

Thank you very much for your answer. I have only one more question, I would like to use this solution and I think, I will write a script for pairing cert ID from psql query with common name. I would like to only know, can I rely on this psql query? Will it be available? Thank you in advance :)

Dňa streda, 16. decembra 2020 17:44:12 UTC+1 r...@sectigo.com napísal(a):

[r...@sectigo.com]

> pairing cert ID from psql query with common name

You can do that like this:

psql -h crt.sh -p 5432 -U guest -d certwatch -q -c "SELECT c.ID, x509_commonName(c.CERTIFICATE) FROM certificate c WHERE to_tsquery('certwatch', 'mydomain:*') @@ identities(c.CERTIFICATE)" -o output.txt

> I would like to only know, can I rely on this psql query? Will it be available?

Access to crt.sh:5432 has been available for several years already and we have no plans to discontinue this service.

Long-running queries tend to be terminated early by the postgres replication system.  If you experience this, try repeating the query.  I've found that often the first attempt will have primed the cache, meaning that the second attempt will run to completion quickly.

[lkip91]

Hi, thank you very much for your reply as well as for query, it works great :)

Dňa pondelok, 21. decembra 2020 15:01:34 UTC+1 r...@sectigo.com napísal(a):

[lkip91]

Hi, I have one more question. Sometimes I get error:

psql: ERROR: no more connections allowed (max_client_conn)

It that only time, when there is a lot of queries, or problem is on my side? Thank you :)

Dňa streda, 16. decembra 2020 14:09:55 UTC+1 lkip91 napísal(a):

[r...@sectigo.com]

That's not a problem at your side.

crt.sh:5432 can only handle a certain number of concurrent connections, and sometimes all of the connection slots are in use.

[cbartle]

How is it possible to modify this query to return everything with 'mydomain' regardless of whether it's a prefix or not? Admittedly, I don't know Postgres very well, but from what I gather, `to_tsquery('certwatch', 'mydomain')` should work, but it returns far fewer matches, including some that don't contain `mydomain` at all.

[r...@sectigo.com]

I'm afraid there aren't currently any suitable indexes to support that kind of search.

[cbartle]

Darn, thanks.