Add a page that displays a matrix of which roots are currently accepted by which logs.

I'm wondering, how often do you update the information in crt.sh with the information in CCADB? I did some queries in crt.sh on the information coming from CCADB and it turned out, that the completeness of the data is not very satisfying. Now I'm wondering, if the data is not in CCADB (which I can't access) or if there is a problem in the import of CCADB data.

Results are already paged for some types of query (e.g. https://crt.sh/?Identity=%25&iCAID=7395), but not all.

To increase the speed of a search for the first time a certificate showed up in any CT log, it would be (hopefully) helpful to have an additional index:

CREATE INDEX ctle_et_ci ON ct_log_entry (ENTRY_TIMESTAMP, CERTIFICATE_ID);

Could you please add this? Honestly I have to admit, I'm not 100% sure it will help, but I'm running out of ideas how to speed up my query otherwise.

Since deploying the new ct_monitor application (a few weeks ago) until just now, SCT timestamps were recorded on the crt.sh database 1hr fast.

The problem was that ct_monitor was assuming that the TimestampToTime() function in github.com/google/certificate-transparency-go was returning timestamps in UTC, when in fact they were in local time. Local time on the crt.sh servers is BST (i.e., GMT+1).

I just fixed ct_monitor (by adding an explicit conversion to UTC). This ticket is to track the additional task of fixing all of the (1hr fast) SCT timestamps on the database.

Timing out https://crt.sh/ searches with an HTTP 404 has confused lots of people. It's intended behaviour, but it seems like a bug.

It would be better to return a webpage with a suitably descriptive error message. I don't think it's possible to do this in the PL/pgSQL code, so this will probably have to be implemented in https://github.com/crtsh/mod_certwatch.

Use the Problem Reporting Mechanism data gathered by Mozilla and published at:
https://ccadb-public.secure.force.com/mozilla/CAInformationReport
and
https://ccadb-public.secure.force.com/mozilla/CAInformationReportCSVFormat

It can be a bit difficult to read the monitored logs table, since the rows all sort of blend together.

A common UI pattern to solve this is to stripe the rows with alternating colors: https://getbootstrap.com/css/#tables-striped

Currently these recompute hourly I believe; it'd be great if they recompute as soon as new CA:TRUE certs are inserted into the DB.

Add a "?host=" search type that will list certs with any identity that a browser would match to a URL on the host indicated by the search value.

e.g. ?host=www.example.com would match [CN or dNSName]=www.example.com, [CN or dNSName]=*.example.com, but not [CN or dNSName]=secure.example.com, etc.

Searches such as https://crt.sh/?q=%25.google.com tend to take too long nowadays.

Need to figure out a way to improve performance.

I'm developing a little tool (https://github.com/RufusJWB/crt.sh-monitor) to monitor the presence of our CA (https://crt.sh/?caid=52410 and possibly every other) at crt.sh. While implementing it, I have observed various time outs and connection drops. I'm wondering if my query kills the performance of your DB. If you want, you can use the tool under https://eo0kjkxapi.execute-api.eu-central-1.amazonaws.com/prod/crtsh-monitor?caID=52410&excludeExpired=true&onlyLINTErrors=true&excludeRevoked=true&verbose=true to check it.

I think there is a problem with the access rights to the table has_debian_weak_key. Could you please check this? Thank you!

https://twitter.com/fotisl/status/794306766242807812

As operator of the OCSP monitoring service at Siemens CA

I would like to have the possibility to manually trigger the check of my OCSP responder to unknown serial numbers on https://crt.sh/ocsp-response?caID=52410&url=http%3A%2F%2Focsp1.pki-services.siemens.com&request=randomserial

to reproduce crt.sh queries to ensure that I filter them out correctly

[Suggested by @gerv]

On each certificate page, kick off with the key information in a summary line.
Some examples:

• Cert for www.example.com and other names, issued by "CA ABC", valid now and until YYYY-MM-DD, not revoked by anyone.
• Root cert "CA XYZ", not valid until YYYY-MM-DD, revoked by CA, Google and Microsoft.

Hi,

Let's Encrypt is no longer displayed in 1 week summary screen, e.g.:
https://crt.sh/?cablint=1+week&dir=^&sort=3

Although any other search displays them correctly.

Some tips here:
http://stackoverflow.com/questions/1301392/pagination-in-feeds-like-atom-and-rss

https://crt.sh/?chain=<crtshID1>,<crtshID2>,etc

Show whether or not each crt.sh ID in the comma-separated list is a valid (or valid-but-expired, or invalid) issuer certificate for the preceding certificate in the list.

https://crt.sh/?id=721305519 doesn't seem to be associated with a CA ("Subject" is not a link). I believe it should be associated with https://crt.sh/?caid=28

It appears I'm hitting this when trying to submit a certificate generated using the certificate submission assistant. The curl POST seemed to work fine when using https://ct.googleapis.com/pilot/ct/v1/add-chain.

$ curl -X POST https://crt.sh/ct/v1/add-chain --data-binary "@add-chain-1c32fa4eb6430ee7e31551068a70b3c169c17294df923d9a4c0b943bbc2bab32.json"
<BR><BR>Unsupported output type: ct/v1/add-chain</BODY>
</HTML>

Add some new parameters that will filter the results based on the notBefore and notAfter dates in the certificate:
minNotBefore
maxNotBefore
minNotAfter
maxNotAfter

minNotBefore already exists for some searches (just the linting stuff, IIRC).

Add "notBefore range" and "notAfter range" to "Select search options:" on https://crt.sh/?a=1

I think, there is a problem with parsing precertificates for this certificate: https://crt.sh/?id=501788710

Both are dating back to Sept. 2017 but only one is shown in the overview:

Is it possible, that there is a problem in parsing CT logs?

I'm not sure whenever this is the right spot to report the problem on the website https://crt.sh

Problem description

incompatible library "/usr/lib64/postgresql-9.5/lib64/libx509pq.so": version mismatch

PL/pgSQL function web_apis(text,text[],text[]) line 3084 at assignment

Steps to reproduce

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

To increase the speed of a search for revoked certificates of a specific CA it would be helpful to have a foreign key relation between the table crl_revoked to the table ca

CONSTRAINT crl_revoked_ca_fk
		FOREIGN KEY (CA_ID)
		REFERENCES ca(ID)

For compliance purposes, it would be very handy to be able to filter all of a CA Owner's CAs based on the "Mozilla Owner" field (i.e. the way that the CCADB tracks CA compliance). For instance, Digicert acquired some Cybertrust CAs from Verizon, so the Issuer "O" field is not as useful for obtaining a comprehensive snapshot. If I could see all of them on one screen, then it would be easier for me to remediate issues with external Sub CAs that issue non-compliant certificates.

For science and fun I am trying to run crt.sh on my own hardware however it looks like I am running against some bottlenecks regarding performance. I am wondering if I am missing something. It looks like most time is spent inside the import_ct_cert function (obviously 🤣 ) however trying to find out which query inside (or insede import_cert) might be hauling this amount of rows has not been acomplished yet (which looks to be the biggest bottleneck).

When I started with an empty database (right now I have about 60M certs imported) it went a lot faster and was mostly bottlenecked by disk I/O however now it looks like to be CPU (the import query hogging a Postgres thread).

I was wondering if you have any idea why this is happening and what I could do to optimize since right now the google log is growing faster then I can import.

Also wondering what kind of hardware / setup crt.sh is using (figured a 4 SSD RAID10 with plenty of cores and ram would do the trick easily) 👀

When a user pastes a PEM-encoded cert and clicks a button, automatically construct the JSON for the /ct/v1/add-chain calls for whichever logs might accept the cert. Then let the page provide a Submit button for each applicable log. Use AJAX to submit the chain from the webpage.

To simplify the search for unrevokes, unexpired certificates of a specific CA (e.g. to find all certificates with weak Debian keys that are not revoked nor expired) I would like to propose the creation of a view, that encapsulates the search for some typical lifecycle data of a certificate:

create view CERTIFICATE_LIFECYCLE as
select 
             C.ID CERTIFICATE_ID,
             C.ISSUER_CA_ID CA_ID,
             encode(X509_SERIALNUMBER(C.CERTIFICATE), 'hex') SERIAL_NUMBER,
             X509_SUBJECTNAME(C.CERTIFICATE) SUBJECT_DISTINGUISHED_NAME,
             (CASE WHEN (x509_print(C.CERTIFICATE) LIKE '%CT Precertificate Poison%') THEN
                 'Precertificate'
             ELSE
                 'Certificate'
             END) CERTIFICATE_TYPE,
             X509_NOTBEFORE(C.CERTIFICATE) NOT_BEFORE,
             X509_NOTAFTER(C.CERTIFICATE) NOT_AFTER,
             CTLE.FIRST_SEEN FIRST_SEEN,
             COALESCE(CRL.REVOKED, 0) REVOKED,
             COALESCE(LCI.LINT_ERRORS, 0) LINT_ERRORS,
             X509_NOTAFTER(C.CERTIFICATE) < now() EXPIRED
     from CERTIFICATE C
     join lateral
         (select MIN(CTLE.ENTRY_TIMESTAMP) FIRST_SEEN,
                 CTLE.CERTIFICATE_ID
          from CT_LOG_ENTRY CTLE
          where CTLE.CERTIFICATE_ID = C.ID
          group by CTLE.CERTIFICATE_ID) CTLE on true
     left join lateral
         (select COUNT(CRL.CA_ID) REVOKED,
                 CRL.SERIAL_NUMBER
          from CRL_REVOKED CRL
          where CRL.CA_ID = C.ISSUER_CA_ID
              and CRL.SERIAL_NUMBER = X509_SERIALNUMBER(C.CERTIFICATE)
          group by CRL.SERIAL_NUMBER) CRL on true
     left join lateral
         (select COUNT(LCI.CERTIFICATE_ID) LINT_ERRORS,
                 LCI.CERTIFICATE_ID
          from LINT_CERT_ISSUE LCI
          where LCI.CERTIFICATE_ID = C.ID
          group by LCI.CERTIFICATE_ID) LCI on true

It'd be nice if there were an "Upload" button on the lintcert HTML form.

Sample code e.g. https://twitter.com/ericlaw/status/916342499790204930

This file:
https://valid.apple.com/g3/v0

Code to parse it:
https://opensource.apple.com/source/Security/Security-58286.41.2/OSX/sec/securityd/SecRevocationDb.c.auto.html

You can add a URL associated with the repo. It'd be nice to add https://crt.sh to it, to better establish that this repo powers the main site.

When trying to view https://crt.sh/?cablint=226&iCAID=1687, I receive the following:

For certificates with notBefore >= 2016-02-02:

There is no parameter $3

PL/pgSQL function web_apis(text,text[],text[]) line 1347 at EXECUTE statement

SELECT c.ID, x509_subjectName(c.CERTIFICATE) SUBJECT_NAME, x509_notBefore(c.CERTIFICATE) NOT_BEFORE, x509_notAfter(c.CERTIFICATE) NOT_AFTER FROM certificate c , cablint_cert_issue cci WHERE c.ISSUER_CA_ID = $1::integer AND c.ID = cci.CERTIFICATE_ID AND cci.ISSUER_CA_ID = $1::integer AND cci.NOT_BEFORE >= $3 AND cci.CABLINT_ISSUE_ID = $2::integer GROUP BY c.ID, SUBJECT_NAME, NOT_BEFORE, NOT_AFTER ORDER BY NOT_BEFORE DESC OFFSET 0 LIMIT 100

It looks like the notBefore is not associating with the correct variable/parameter (?)

Add new parameter(s) to filter the results by signature (hash and/or key) algorithm.

Add this to "Select search options:" on https://crt.sh/?a=1

Example:

~ ❯❯❯ http head 'https://crt.sh/?sha256=BB146B5C18893D4AE14356BC2ED2AA78EB6E162D9DE2520AA4F9DA97FE06B0AA'
HTTP/1.1 200 OK
Cache-Control: max-age=300
Connection: Keep-Alive
Content-Length: 3025
Content-Type: text/html; charset=UTF-8
Date: Wed, 10 May 2017 21:42:09 GMT
Expect-CT: preload
Keep-Alive: timeout=15, max=100
Server: Apache
Strict-Transport-Security: max-age=15768000; includeSubDomains; preload

that ought to be a 404, since there's no certificate at that URL

Add a search option to only show matching certs that chain to a particular root certificate.

When supplying output=JSON, you get JSON data with the content type text/html. This should be application/json.

https://crt.sh/?q=%25.crt.sh&output=json

Show CRL revocation status on each certificate page.

Could a JSON feed be added for the cablint page?

Hello,

I would like to create a SQL JOIN statement to take fields from multiple tables such as Certfificate_table and CA_table in the PostgreSQL.. What is the best way to do this? I would greatly appreciate your help!

Due to range of accepted values it might be worth replacing

• integer -> bigint
• serial -> bigserial

for record ID and foreigh indices as documented in the PostgreSQL manual.

Given we currently handle about 100-200 million certificates, this might soon reach the constraints imposed by the smaller int32-based data types.

It would be great, if you could make the OCSP responder test queries for unknown certificates somehow identifiable, e.g. by letting the random serial number always start with '0f0f0f'. The background for this request is, that our OCSP monitoring always sends an alert when a request for an unknown certificate have been logged. This logging can't be easily filtered for the IP address of the requesting address and would need always manual digging in other log files. If you would embed such a magic string in the serial number, we could easily filter for them.

What do you think?