Querying with psycopg

[Andrew Carr]

Hi,

I'm currently working on a Python script that will query crt.sh for certificates created for certain domains that we manage. 

I have a working solution using 'https://crt.sh/?q=' + domain + '&output=json' but the rate limiting causes some issues (even with generous gaps between domains). From reading posts here, I can see there is a PostgreSQL option so have investigated that. 

Using the showSQL=Y parameter, I have the query I need, and it kind of works. It returns all the certificates I'm expecting (about 55) before erroring with the following:

psycopg.errors.InvalidSqlStatementName: prepared statement "_pg3_0" does not exist

Does anyone have advice on this error, and how to get a clean exit from the query?

In case it helps, the relevant parts of the code:

db_conn_str = 'postgresql://gu...@crt.sh:5432/certwatch'

db_connection = psycopg.connect(db_conn_str, autocommit=True)

db_cursor = db_connection.cursor()

db_cursor.execute("<query from crt.sh goes here, with $1 changed to a domain I'm interested in>;")

for query in db_cursor:

  print(str(query))

I'm not a professional developer - my Python skills are probably intermediate, and I've never worked with PSQL before (my MSSQL is basic!).

Many thanks

[r...@sectigo.com]

Hi Andrew.  PgBouncer, the database connection pooler that sits in front of the crt.sh:5432 servers, is not compatible with prepared statements.  Please see https://www.psycopg.org/psycopg3/docs/advanced/prepare.html for advice on how to disable prepared statements.

(Hmm, actually it appears that newer versions of PgBouncer do now support prepared statements in "transaction" mode - see https://www.crunchydata.com/blog/prepared-statements-in-transaction-mode-for-pgbouncer.  However, crt.sh's set-up is running PgBouncer in the more restrictive "statement" mode).