crt.sh certwatch DB query performance: date range

[rodi...@gmail.com]

I'm trying to understand what causes my query to slow down if I uncomment the upper bound for the date condition:

--AND x509_notBefore(c.CERTIFICATE) < '2018-04-02T00:00:00.000Z'

Here's the query:

SELECT ci.CERTIFICATE_ID, ci.ISSUER_CA_ID, ci.NAME_VALUE,
  x509_notBefore(c.CERTIFICATE) AS not_before,
  x509_notAfter(c.CERTIFICATE) AS not_after,
  x509_issuerName(c.CERTIFICATE)
FROM certificate_identity ci,
     certificate c
WHERE ci.CERTIFICATE_ID = c.ID
  AND lower(ci.NAME_VALUE) LIKE lower('bank%of%america%')
  AND ci.NAME_TYPE = 'commonName'
  --AND ci.ISSUER_CA_ID = 16418
  AND x509_notBefore(c.CERTIFICATE) >= '2018-04-01T00:00:00.000Z'
  --AND x509_notBefore(c.CERTIFICATE) < '2018-04-02T00:00:00.000Z'
  LIMIT 5

The query identifies (possible) phishing sites.

The indexes appear to be in place according to schema 
https://github.com/crtsh/certwatch_db/blob/master/create_schema.sql

CREATE INDEX c_ica_notbefore
    ON certificate (ISSUER_CA_ID, x509_notBefore(CERTIFICATE));

CREATE INDEX c_notbefore_ica
    ON certificate (x509_notBefore(CERTIFICATE), ISSUER_CA_ID);


I also tried to run GROUP BY ci.ISSUER_CA_ID queries to count the number of certificates issued by CA for a given date.
None of the queries complete.

Regards,


Sergei

[rodi...@gmail.com]

Here's a workaround to trick the optimizer when the date range is 1 day.

AND CAST(x509_notBefore(c.CERTIFICATE) AS Date) = '2018-04-01'

Still, my query to retrieve certificates issued by CA is very slow.
I'm not sure it will ever complete.

SELECT ci.ISSUER_CA_ID AS ca_id, 
  COUNT(*) AS cert_count

FROM certificate_identity ci,
     certificate c
WHERE ci.CERTIFICATE_ID = c.ID

  --AND lower(ci.NAME_VALUE) LIKE lower('bank%of%america%')
  AND ci.NAME_TYPE = 'commonName'
  AND CAST(x509_notBefore(c.CERTIFICATE) AS Date) = '2018-04-01'
  GROUP BY ci.ISSUER_CA_ID

Is there a better way to approach this?

[Rob Stradling]

I'm not sure why your query is slow.

Perhaps you could execute this...

SELECT DISTINCT ci.CERTIFICATE_ID
  FROM certificate_identity ci
  WHERE lower(ci.NAME_VALUE) LIKE lower('bank%of%america%')
    AND ci.NAME_TYPE = 'commonName';

...and then, for each returned row, execute this...

SELECT ci.CERTIFICATE_ID, ci.ISSUER_CA_ID, ci.NAME_VALUE,

       x509_notBefore(c.CERTIFICATE) AS not_before,
       x509_notAfter(c.CERTIFICATE) AS not_after,

       x509_issuerName(c.CERTIFICATE)
  FROM certificate c, certificate_identity ci
  WHERE c.ID = :cert_id
    AND c.ID = ci.CERTIFICATE_ID

    AND lower(ci.NAME_VALUE) LIKE lower('bank%of%america%')
    AND ci.NAME_TYPE = 'commonName'

    AND x509_notBefore(c.CERTIFICATE) >= '2018-04-01T00:00:00.000Z'

    AND x509_notBefore(c.CERTIFICATE) < '2018-04-02T00:00:00.000Z';

[by li]

hi Rob,

    when i use psql to access crt.sh DB, there are error occur:

ERROR:  incompatible library "/usr/lib64/postgresql-9.5/lib64/libx509pq.so": version mismatch
DETAIL:  Server is version 9.5, library is version 10.0.

a month ago, there is no problem.

在 2018年4月13日星期五 UTC+8下午11:52:08，Rob Stradling写道：

[by li]

these is my script:

echo "SELECT  x509_notbefore(c.CERTIFICATE),

    x509_notafter(c.CERTIFICATE),
    x509_serialnumber(c.CERTIFICATE),
    digest(c.CERTIFICATE,'sha256') SHA256

    FROM certificate_identity ci,
        certificate c

    WHERE reverse(lower(ci.NAME_VALUE)) LIKE reverse(lower('$domainname'))
        AND c.ID = ci.CERTIFICATE_ID
        AND x509_notAfter(c.CERTIFICATE) > statement_timestamp()
    GROUP BY c.ID
    ORDER BY c.ID DESC;" >$temp

psql -h crt.sh -p 5432 -U guest certwatch <$temp >$file_name

//============================

i use it to request some request, there will error occur, but if i only request one time use this script, there is no problem.

can you tell me why. thank you very much.

在 2018年7月15日星期日 UTC+8下午2:16:54，by li写道：

[Rob Stradling]

Thanks for reporting this error.  We deployed some new front-end servers last week, and a few things weren't configured correctly.  I thought I'd fixed them all, but (thanks to your report) I just found one other misconfigured server that's serving crt.sh:5432 traffic.  It's fixed now.