Enhancement: Connect with PostgreSQL

[malvidin]

A lot more functionality can be included by using a connection to the crt.sh database with PostgreSQL. Only some filters are exposed through the web interface.

For example, this query: https://crt.sh/?q=%.github.com&showSQL=Y&exclude=expired Can be performed with this PostgreSQL command: psql -h crt.sh -p 5432 -U guest certwatch

certwatch=> SELECT ci.ISSUER_CA_ID,
        ca.NAME ISSUER_NAME,
        ci.NAME_VALUE NAME_VALUE,
        min(c.ID) MIN_CERT_ID,
        min(ctle.ENTRY_TIMESTAMP) MIN_ENTRY_TIMESTAMP,
        x509_notBefore(c.CERTIFICATE) NOT_BEFORE,
        x509_notAfter(c.CERTIFICATE) NOT_AFTER
    FROM ca,
        ct_log_entry ctle,
        certificate_identity ci,
        certificate c
    WHERE ci.ISSUER_CA_ID = ca.ID
        AND c.ID = ctle.CERTIFICATE_ID
        AND reverse(lower(ci.NAME_VALUE)) LIKE reverse(lower('%.github.com'))
        AND ci.CERTIFICATE_ID = c.ID
        AND x509_notAfter(c.CERTIFICATE) > statement_timestamp()
    GROUP BY c.ID, ci.ISSUER_CA_ID, ISSUER_NAME, NAME_VALUE
    ORDER BY MIN_ENTRY_TIMESTAMP DESC, NAME_VALUE, ISSUER_NAME;

I have not yet had success executing this command with psycopg2.

psycopg2.DatabaseError: long transactions not allowed
SSL connection has been closed unexpectedly

[justinsteven]

@malvidin I stumbled upon this when trying to get psycopg2 playing nice with crt.sh

Regarding "I have not yet had success executing this command with psycopg2" I think I've worked it out and figured it might help you or someone else.

I'm guessing crt.sh's Postgres interface is being fronted by pgbouncer. https://www.endpoint.com/blog/2015/05/18/connected-to-pgbouncer-or-postgres suggests you can detect pgbouncer by trying to connect to an invalid database name using psql and examing the exact error that's returned. In my testing, I get the same error as endpoint.com gets for a Postgres that's being fronted by pgbouncer.

endpoint.com goes on to explain that the "Long transactions not allowed" error message may be specific to pgbouncer when you attempt to use transactions.

http://initd.org/psycopg/docs/faq.html suggests you can disable psycopg2's use of transactions:

you can use a connection in autocommit mode to avoid a new transaction to be started at the first command.

By doing the following, I no longer get the "Long transactions not allowed" error:

conn = psycopg2.connect("dbname=certwatch user=guest host=crt.sh")
conn.set_session(autocommit=True)

[shubham8agar]

I am running command psql -h crt.sh -p 5432 -U guest certwatch and waiting for psql prompt but it does not appear. Is there any tweak to perform, which I am missing ?

Also I tries python way to use psycopg2 to connect with crt.sh database. But failed !! I am using crt.sh script too and trying to parse complete .com TLD but new entries does not appear and therefore looking to connect with database directly. Can you please suggest how I can use the same and what is the casue I am not getting db prompt !!

[malvidin]

This is how I connect to the crt.sh with psycopg2. I'll be putting more on my fork later this week.

conn = psycopg2.connect(dbname="certwatch", user="guest", host="crt.sh", cursor_factory=RealDictCursor)
conn.set_session(readonly=True, autocommit=True)

[thiezn]

@justinsteven Thank you very much!!! I was bouncing my head against the wall for this for quite some time!

For others with the same issue: when trying to connect to crt.sh I was getting the following error:

psycopg2.errors.ProtocolViolation: transaction blocks not allowed in statement pooling mode

Couldn't find anything online for that specific error message and my DB skills are lacking actually understanding the message..