psql fails with an error (ERROR: could not connect to server "old")

175 views
Skip to first unread message

Israel Thomas

unread,
Jun 26, 2024, 9:29:42 AM6/26/24
to crt.sh
Hi Team,

I get the following error while trying to fetch domain names via psql.

Query used =
SELECT ci.NAME_VALUE NAME_VALUE FROM certificate_identity ci WHERE ci.NAME_TYPE = 'dNSName' AND reverse(lower(ci.NAME_VALUE)) LIKE reverse(lower('withgoogle.com'));


The above query fails with the following error:

ERROR:  could not connect to server "old"
DETAIL:  could not connect to server: Connection refused
       Is the server running on host "bddccwsql.brad.dc.comodoca.net" (10.0.16.55) and accepting
       TCP/IP connections on port 5432?

Can someone help? :_

Regards,
Israel

Israel Thomas

unread,
Jun 26, 2024, 1:09:22 PM6/26/24
to crt.sh
Issue fixed.

You'll have to replace `certificate_identity` with `certificate_and_identities`.

r...@sectigo.com

unread,
Jul 29, 2024, 8:00:27 AM7/29/24
to crt.sh
The "certificate_identity" table existed in the original crt.sh database that ran out of 32-bit certificate IDs in late 2019 (see https://groups.google.com/g/crtsh/c/DM8SI-qsE8E/m/J_ndSkroBgAJ).  To avoid breaking anyone's applications that were using "certificate_identity", we kept one instance of the old DB lying around, which was connected to the new DB via postgres_fdw.  "certificate_identity" became a view on the new DB that accessed the data from the old DB.  The intention was to announce a deprecation date for "certificate_identity", but...oops...I never got around to it.

So this isn't the orderly deprecation I had intended, but...given that "certificate_identity" has not been getting any new data since late 2019, and given that it's been broken for several days, I'm not going to resurrect it now.

Instead, "certificate_identity" will now be a function-based view that raises an exception with the following message:

Sorry, the "certificate_identity" table has been superseded by a Full Text Search index on the "certificate" table.
Reply all
Reply to author
Forward
0 new messages