plugin error after a few queries

[tinder-tder]

Describe the bug plugin seems to keep connections open and crash. After a few queries the plugin seems to error out (and return no results) and the only fix seems to be restarting steampipe

Steampipe version (steampipe -v) Example: v0.23.1

Plugin version (steampipe plugin list) Example: v0.4.0

To reproduce

> select id,dns_names, issuer ->> 'Organization', subject from crtsh_certificate where query = '<redacted>'   and not_before < now()
  and not_after > now();
+-------------+-----------------------------------------------------------+----------------------+----------------------------------------------------------------------------------->
| id          | dns_names                                                 | ?column?             | subject                                                                           >
+-------------+-----------------------------------------------------------+----------------------+----------------------------------------------------------------------------------->
...<redacted>
+-------------+-----------------------------------------------------------+----------------------+----------------------------------------------------------------------------------->
> select id,dns_names, issuer ->> 'Organization', subject from crtsh_certificate where query = '<redacted too>'   and not_before < now()
  and not_after > now();

Error: crtsh: pq: no more connections allowed (max_client_conn) (SQLSTATE HV000)

+----+-----------+----------+---------+
| id | dns_names | ?column? | subject |
+----+-----------+----------+---------+
+----+-----------+----------+---------+

Expected behavior Query run

Additional context plugin log

2024-05-29 19:25:41.979 UTC [ERROR] steampipe-plugin-crtsh.plugin: [ERROR] 1717010739328: crtsh_ca_issuer.listCaIssuer: connection_error="pq: no more connections allowed (max_client_conn)"
2024-05-29 19:25:41.980 UTC [ERROR] steampipe-plugin-crtsh.plugin: [ERROR] 1717010739328: crtsh_certificate.listCertificate: connection_error="pq: no more connections allowed (max_client_conn)"
2024-05-29 19:25:41.980 UTC [WARN]  steampipe-plugin-crtsh.plugin: [WARN]  1717010739328: doList callHydrateWithRetries (crtsh-1717010739328) returned err pq: no more connections allowed (max_client_conn)
2024-05-29 19:25:41.980 UTC [WARN]  steampipe-plugin-crtsh.plugin: [WARN]  1717010739328: QueryData StreamError pq: no more connections allowed (max_client_conn) (crtsh-1717010739328)
2024-05-29 19:25:41.980 UTC [WARN]  steampipe-plugin-crtsh.plugin: [WARN]  1717010739328: streamRows execution has failed: crtsh-1717010739328 - calling queryCache.AbortSet (crtsh: pq: no more connections allowed (max_client_conn))
2024-05-29 19:25:41.981 UTC [WARN]  steampipe-plugin-crtsh.plugin: [WARN]  1717010739328: QueryCache AbortSet - aborting request  with error crtsh: pq: no more connections allowed (max_client_conn) (1 subscriber) (crtsh-1717010739328)
2024-05-29 19:25:41.981 UTC [WARN]  steampipe-plugin-crtsh.plugin: [WARN]  1717010739328: queryData.streamRows returned error: crtsh: pq: no more connections allowed (max_client_conn)
2024-05-29 19:25:41.981 UTC [WARN]  steampipe-plugin-crtsh.plugin: [WARN]  1717010739328: executeForConnection crtsh returned error crtsh: pq: no more connections allowed (max_client_conn), writing to CHAN
2024-05-29 19:25:41.981 UTC [WARN]  steampipe-plugin-crtsh.plugin: [WARN]  1717010739328: error channel received crtsh: pq: no more connections allowed (max_client_conn)
2024-05-29 19:25:42.004 UTC [WARN]  steampipe-plugin-crtsh.plugin: [WARN]  1717010739328: readAndStreamAsync failed to read previous rows from cache: crtsh: pq: no more connections allowed (max_client_conn) publisher crtsh-1717010739328 (crtsh-1717010739328)

database log

2024-05-29 19:25:39.085 UTC [WARN]  hub: RestrictionsToQuals: failed to convert 2 restrictions to quals
2024-05-29 19:25:41.981 UTC [WARN]  hub: stream receive error rpc error: code = Unknown desc = crtsh: pq: no more connections allowed (max_client_conn) (0xc000aebb20)
2024-05-29 19:25:41.981 UTC [64283] ERROR:  rpc error: code = Unknown desc = crtsh: pq: no more connections allowed (max_client_conn)
2024-05-29 19:25:41.981 UTC [64283] STATEMENT:  select id,dns_names, issuer ->> 'Organization', subject from crtsh_certificate where query = '<redacted too>'   and not_before < now()
     and not_after > now();

[ParthaI]

Hi @tinder-tder,

Thanks for raising the issue. I have a couple of follow-up questions:

• When you say "after a few queries," do you recall approximately how many attempts it takes before the issue occurs?
• In the plugin, we are connecting to the database using the connection string postgres://guest@crt.sh:5432/certwatch?binary_parameters=yes, which establishes a connection with the database.
• The database at crt.sh:5432 can only handle a certain number of concurrent connections, and sometimes all of the connection slots are in use. I found an article in a public forum that discusses this issue: Google Groups.
• Could you please confirm if you are getting this error consistently after a few attempts or only occasionally?

Thanks!

[tinder-tder]

@ParthaI consistently, it only takes one query really with a follow up not long after (assuming its not cached). If its a rate limit itssue maybe the error can be clearer. or it can try and hold the connection open for cache period (dunno if that fits in the acceptable use policy)?

[ParthaI]

Hello @tinder-tder, I tried the following queries multiple times. However, I could not replicate the error. All the time I am getting the result successfully. It would be great if you could share the actual query you are trying to run. That may help us with replicating the error.

Queries:

1.

select
  dns_names,
  not_after
from
  crtsh_certificate
where
  query = 'steampipe.io';

2.

select id,dns_names, issuer ->> 'Organization', subject from crtsh_certificate where query = 'steampipe.io'   and not_before < now()
  and not_after > now();

If its a rate limit itssue maybe the error can be clearer. or it can try and hold the connection open for cache period (dunno if that fits in the acceptable use policy)

• I don't think it is a rate limit error.
• By the way, I have adjusted the client creation logic for establishing the connection with the following changes:
  • Set the maximum number of concurrently open connections to 10.
  • Set the maximum number of idle connections to 5.
  • Set the maximum amount of time a connection may be reused to 30 minutes.

I have hardcoded these values for now and pushed the changes to the issue-28 branch.

Please take a stab at it in the issue-28 branch and share your observations with us to see if it helps.

Steps to try it out in the issue-28 branch:

• Clone the repository: git clone https://github.com/turbot/steampipe-plugin-crtsh.git
• Navigate to the project directory: cd steampipe-plugin-crtsh
• Check out the issue-28 branch: git checkout issue-28
• Build the project: make
• Run the query to test the changes.

If it helps, we can consider making those hardcoded values dynamic by configuring them in the connection config.

Thank you!

[tinder-tder]

@ParthaI I tried the same queries with the current version and couldnt hit the error today too (maybe it was on their end). i tested the issue-28 branch too and it didnt error out the domains tested yesterday and today were tinder ops.net match security.net tinder security.net

I think we can close this or just update error handling to try to give a better error message, thanks for the quick turnaround!

[ParthaI]

Thanks @tinder-tder, for the confirmation. I am closing the issue since it is resolved. Please don't hesitate to reopen the issue/create a new one if you encounter any more issues. Thanks!