Is your feature request related to a problem? Please describe.
Sometimes DNS requests timeout, so I'd like to have more reliable query results

Describe the solution you'd like
Adding backoff-retry (using Steampipe SDK's backoff retry functionality) and a secondary DNS server should allow for more resiliency when making requests.

Describe alternatives you've considered
Running queries again can sometimes return results for failed queries.

Additional context
Add any other context or screenshots about the feature request here.

Describe the bug
net_certificate queries completely fail when one of the hosts queried don't exist in DNS

Steampipe version (steampipe -v)
v0.16.1

Plugin version (steampipe plugin list)
v0.7.0

To reproduce
Run a query with at least one host that doesn't have a DNS entry:
echo "select * from net_certificate where domain in ('www.google.com', 'nodnsforthis.site');" | steampipe query
Warning: executeQueries: query 1 of 1 failed: ERROR: rpc error: code = Unknown desc = TLS connection failed: dial tcp: lookup nodnsforthis.site on 128.194.254.1:53: no such host (SQLSTATE HV000)

Expected behavior
Just like when no service exists on port 443, the host should be ignored, and the rest of the query should complete.

Additional context
None

Describe the bug
Using a LEFT JOIN on the net_dns_record table (from the net plugin), while doing a csv-based lookup on a text file results in an "unexpcted EOF" error

Warning: executeQueries: query 1 of 1 failed: unexpected EOF

Steampipe version (steampipe -v)
steampipe version 0.15.2

To reproduce

• Create a simple csv file in the relevant folder where the csv plugin reads the files, called "domains.csv" with the following contents

domain
steampipe.io

• In this example, look for the dmarc record in DNS using the following SQL query

SELECT
    Q.domain,
    N.value
FROM
    domains Q
LEFT JOIN
    net_dns_record N
    ON N.domain = '_dmarc.' || Q.domain 
    AND N.type = 'TXT'

Expected behavior
I am expecting the LEFT JOIN to respect the concatenation statement, and do a lookup of the TXT record in DNS of _dmarc.steampipe.io (as per the CSV file).

Additional context
In another example, I was able to pass a simple SELET 'steampipe.io' as domain query, which worked fine. It would appear the combination of using net_dns_record and csv in the join with the concatenation is a problem. A direct join without the concatenation works fine.

sdk version v1.3.0 does not support d.QueryContext.Quals, need to update it as d.QueryContext.UnsafeQuals.

Make it easier to do basic TCP queries now we have optional key quals.

Describe the bug
Fix example query
https://hub-steampipe-io-git-staging-turbot.vercel.app/plugins/turbot/net/tables/net_connection > Test a TCP connection to steampipe.io on port 443

The below query is correct in the repo, however, it's rendered differently in hub with the condition as in

In .md file

select
  *
from
  net_connection
where
  protocol = 'tcp'
  and address = 'steampipe.io:443'

In Hub

select
  *
from
  net_connection
where
  protocol = 'tcp'
  and address in 'steampipe.io:443'

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

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

To reproduce
Steps to reproduce the behavior (please include relevant code and/or commands).

Expected behavior
A clear and concise description of what you expected to happen.

Additional context
Add any other context about the problem here.

From this thread, an example like this would be good on the net_http_request table:

The trick is to cast the response into JSONB . For example:

> select response_body::jsonb ->> 'number' as num_people_in_space from net_http_request where url = 'http://api.open-notify.org/astros.json'

+---------------------+
| num_people_in_space |
+---------------------+
| 10                  |
+---------------------+

Describe the bug
I am running net_certificate to identify domains with SSL issues. If a domain does not have an SSL certificate, the plugin simply fails, and does not process anything.

Warning: executeQueries: query 1 of 1 failed: ERROR: rpc error: code = Unknown desc = TLS connection failed: dial tcp 164.92.154.37:443: i/o timeout (SQLSTATE HV000)

Steampipe version (steampipe -v)
steampipe version 0.15.3

Plugin version (steampipe plugin list)
| hub.steampipe.io/plugins/turbot/net@latest | 0.6.0 | net |

To reproduce

with domains as (
    SELECT 'steampipe.io' as domain
    UNION ALL SELECT 'massyn.com' as domain
)

SELECT
    N.domain,
    N.revoked
FROM
  net_certificate N
where
  N.domain IN (SELECT * FROM domains)

Expected behavior
In this example, I would expect the domain massyn.com to come up with a null value, instead of failing the script with the error message.

Additional context
None

General background / value proposition

Certificate transparency logs provide a publicly-accessible record of almost all SSL certificates issued on the Internet. It could be helpful to be able to query these tables from Steampipe to e.g. validate that no certificates have been issued for my domains that weren't in AWS ACM for one of my accounts.

Implementation questions

The question becomes: which data source should this query? A popular one is https://crt.sh. In fact, they even provide a publicly available Postgres instance (!!) for querying CT logs. You can get a sample of the SQL at this URL: https://crt.sh/?q=%25.steampipe.io&showSQL=y.

That page uses this SQL query:

WITH ci AS (
    SELECT min(sub.CERTIFICATE_ID) ID,
           min(sub.ISSUER_CA_ID) ISSUER_CA_ID,
           array_agg(DISTINCT sub.NAME_VALUE) NAME_VALUES,
           x509_commonName(sub.CERTIFICATE) COMMON_NAME,
           x509_notBefore(sub.CERTIFICATE) NOT_BEFORE,
           x509_notAfter(sub.CERTIFICATE) NOT_AFTER,
           encode(x509_serialNumber(sub.CERTIFICATE), 'hex') SERIAL_NUMBER
        FROM (SELECT *
                  FROM certificate_and_identities cai
                  WHERE plainto_tsquery('certwatch', 'steampipe.io') @@ identities(cai.CERTIFICATE)
                      AND cai.NAME_VALUE ILIKE ('%' || 'steampipe.io' || '%')
                  LIMIT 10000
             ) sub
        GROUP BY sub.CERTIFICATE
)
SELECT ci.ISSUER_CA_ID,
        ca.NAME ISSUER_NAME,
        ci.COMMON_NAME,
        array_to_string(ci.NAME_VALUES, chr(10)) NAME_VALUE,
        ci.ID ID,
        le.ENTRY_TIMESTAMP,
        ci.NOT_BEFORE,
        ci.NOT_AFTER,
        ci.SERIAL_NUMBER
    FROM ci
            LEFT JOIN LATERAL (
                SELECT min(ctle.ENTRY_TIMESTAMP) ENTRY_TIMESTAMP
                    FROM ct_log_entry ctle
                    WHERE ctle.CERTIFICATE_ID = ci.ID
            ) le ON TRUE,
         ca
    WHERE ci.ISSUER_CA_ID = ca.ID
    ORDER BY le.ENTRY_TIMESTAMP DESC NULLS LAST;

That yields (at the time of writing) these results:

That presents us with a few questions:

• Would you just use the Postgres->Postgres postgres_fdw with host crt.sh, user guest and DB certwatch?
• Or should Steampipe attempt to simplify the (quite complex!) query above with some kind of view?
• Maybe it's not worth using the postgres_fdw because it doesn't work with Steampipe's architecture, e.g. caching (I say this from a point of ignorance, I haven't looked into how that works)

Either way, I feel this could be useful. Thoughts?

Describe the bug
Selecting specific columns from net_dns_reverse appears to break with the error:

Error: rpc error: code = Internal desc = 'List' call for table 'net_dns_reverse' is missing 1 required qual: column:'ip_address' operator: =
 (SQLSTATE HV000)

Steampipe version (steampipe -v)

$ steampipe -v
Steampipe v0.20.5

Plugin version (steampipe plugin list)

| hub.steampipe.io/plugins/turbot/net@latest        | 0.9.0   | net         |

To reproduce

Write a query such as this:

query "dns_records_reverse" {
  sql = <<-EOQ
    select
      ip_address,
      jsonb_array_elements_text(domains) as domain
    from
      net_dns_reverse
    where
      ip_address in (
        select
          ip
        from
          net_dns_record
        where
          domain = $1
          and ip is not null
      )
  EOQ

  param "domain" {
    description = "Domain to lookup"
  }
}

Using the steampipe query tool, run the query like this:

Welcome to Steampipe v0.20.5
For more information, type .help
> query.dns_records_reverse("google.com")

Error: rpc error: code = Internal desc = 'List' call for table 'net_dns_reverse' is missing 1 required qual: column:'ip_address' operator: =
 (SQLSTATE HV000)

+------------+--------+
| ip_address | domain |
+------------+--------+
+------------+--------+
>

Expected behavior
The query should return results without an error.

Additional context
The error can be avoided by adding the wildcard column selector (aka: *). For exaxmple

query "dns_records_reverse" {
  sql = <<-EOQ
    select
      *,
      ip_address,
      jsonb_array_elements_text(domains) as domain
    from
      net_dns_reverse
    where
      ip_address in (
        select
          ip
        from
          net_dns_record
        where
          domain = $1
          and ip is not null
      )
  EOQ

  param "domain" {
    description = "Domain to lookup"
  }
}

In this case, the results are successful, but with more columns than intended.

> query.dns_records_reverse("google.com")
+----------------+--------------------------------+---------------------------+----------------+----------------------------+
| ip_address     | domains                        | _ctx                      | ip_address     | domain                     |
+----------------+--------------------------------+---------------------------+----------------+----------------------------+
| 172.217.170.46 | ["jnb02s03-in-f14.1e100.net."] | {"connection_name":"net"} | 172.217.170.46 | jnb02s03-in-f14.1e100.net. |
+----------------+--------------------------------+---------------------------+----------------+----------------------------+
>

References
Validate a TLS connection to the given network address by initiating a TLS handshake, and return all the possible TLS protocol-cipher combinations for which a TLS connection could be established.
https://pkg.go.dev/crypto/tls

Is your feature request related to a problem? Please describe.
I would like to be able to monitor certificate expiration dates on other commonly used TLS ports such as 636, 8443, etc. Using an alternative product to monitor other ports means that I can't easily have a single dashboard for all of my certs if I want to use Steampipe.

Describe the solution you'd like
"port" should be added as a field to the net_certificate table, which would enable certificates on arbitrary ports to be monitored. I assume that it's not there right now so that it doesn't need to be specified for most cases.

Describe alternatives you've considered
Use an alternative product to monitor these ports.

Additional context
None

Describe the bug
net_dns_record not listing correct results for consecutive queries when used filter for type field

> select * from net_dns_record where domain = 'turbot.com' and type = 'NS'
+------------+------+--------+--------------------------+----------+--------+--------+
| domain     | type | ip     | target                   | priority | value  | ttl    |
+------------+------+--------+--------------------------+----------+--------+--------+
| turbot.com | NS   | <null> | ns-1312.awsdns-36.org.   | <null>   | <null> | 172800 |
| turbot.com | NS   | <null> | ns-287.awsdns-35.com.    | <null>   | <null> | 172800 |
| turbot.com | NS   | <null> | ns-2039.awsdns-62.co.uk. | <null>   | <null> | 172800 |
| turbot.com | NS   | <null> | ns-517.awsdns-00.net.    | <null>   | <null> | 172800 |
+------------+------+--------+--------------------------+----------+--------+--------+

Time: 85.174741ms
> select * from net_dns_record where domain = 'turbot.com' and type = 'A'
+--------+------+----+--------+----------+-------+-----+
| domain | type | ip | target | priority | value | ttl |
+--------+------+----+--------+----------+-------+-----+
+--------+------+----+--------+----------+-------+-----+

Time: 4.52282ms
> select * from net_dns_record where domain = 'turbot.com' and type = 'SOA'
+--------+------+----+--------+----------+-------+-----+
| domain | type | ip | target | priority | value | ttl |
+--------+------+----+--------+----------+-------+-----+
+--------+------+----+--------+----------+-------+-----+

Time: 4.568473ms
> select * from net_dns_record where domain = 'turbot.com' and type = 'MX'
+--------+------+----+--------+----------+-------+-----+
| domain | type | ip | target | priority | value | ttl |
+--------+------+----+--------+----------+-------+-----+
+--------+------+----+--------+----------+-------+-----+

Time: 5.069768ms
> select * from net_dns_record where domain = 'turbot.com'
+------------+------+--------+--------------------------+----------+--------+--------+
| domain     | type | ip     | target                   | priority | value  | ttl    |
+------------+------+--------+--------------------------+----------+--------+--------+
| turbot.com | NS   | <null> | ns-1312.awsdns-36.org.   | <null>   | <null> | 172800 |
| turbot.com | NS   | <null> | ns-287.awsdns-35.com.    | <null>   | <null> | 172800 |
| turbot.com | NS   | <null> | ns-2039.awsdns-62.co.uk. | <null>   | <null> | 172800 |
| turbot.com | NS   | <null> | ns-517.awsdns-00.net.    | <null>   | <null> | 172800 |
+------------+------+--------+--------------------------+----------+--------+--------+

Time: 5.259996ms
> .cache clear
> select * from net_dns_record
Error: rpc error: code = Internal desc = 'List' call for table 'net_dns_record' is missing 1 required qual: column:'domain' operator: =
 (SQLSTATE HV000)
> select * from net_dns_record where domain = 'turbot.com'
+------------+------+--------------+--------------------------+----------+--------+--------+
| domain     | type | ip           | target                   | priority | value  | ttl    |
+------------+------+--------------+--------------------------+----------+--------+--------+
| turbot.com | A    | 54.182.0.62  | <null>                   | <null>   | <null> | 60     |
| turbot.com | A    | 54.182.0.126 | <null>                   | <null>   | <null> | 60     |
| turbot.com | A    | 54.182.0.125 | <null>                   | <null>   | <null> | 60     |
| turbot.com | A    | 54.182.0.32  | <null>                   | <null>   | <null> | 60     |
| turbot.com | MX   | <null>       | alt3.aspmx.l.google.com. | 10       | <null> | 300    |
| turbot.com | MX   | <null>       | alt2.aspmx.l.google.com. | 5        | <null> | 300    |
| turbot.com | MX   | <null>       | aspmx.l.google.com.      | 1        | <null> | 300    |
| turbot.com | MX   | <null>       | alt1.aspmx.l.google.com. | 5        | <null> | 300    |
| turbot.com | MX   | <null>       | alt4.aspmx.l.google.com. | 10       | <null> | 300    |
| turbot.com | NS   | <null>       | ns-2039.awsdns-62.co.uk. | <null>   | <null> | 172800 |
| turbot.com | NS   | <null>       | ns-287.awsdns-35.com.    | <null>   | <null> | 172800 |
| turbot.com | NS   | <null>       | ns-517.awsdns-00.net.    | <null>   | <null> | 172800 |
| turbot.com | NS   | <null>       | ns-1312.awsdns-36.org.   | <null>   | <null> | 172800 |
| turbot.com | SOA  | <null>       | ns-1312.awsdns-36.org.   | <null>   | <null> | 900    |
+------------+------+--------------+--------------------------+----------+--------+--------+

Steampipe version (steampipe -v)
v0.13.2

Plugin version (steampipe plugin list)
v0.1.1

To reproduce
witch cache ON execute below queries in order they appear here:

1. select * from net_dns_record where domain = 'turbot.com' and type = 'NS'
2. select * from net_dns_record where domain = 'turbot.com' and type = 'A'
3. .cache clear
4. select * from net_dns_record where domain = 'turbot.com'

Expected behavior
A clear and concise description of what you expected to happen.

Additional context
Add any other context about the problem here.

Query HTTP response headers of various sites using SQL

Update readme and index.md to new formats

Describe the bug
The net_tls_connection connection table can possibly have incorrect values for cipher_suite_name when connecting via TLS 1.3.
Looking at the code, it is returning the requested cipher suite, while the docs say it is the "Negotiated cipher suite". This would be true up until this change made by the Go team for TLS 1.3 (and remains true for TLS 1.2 and below). This causes the net plugin to report that ciphers such as TLS_CHACHA20_POLY1305_SHA256 were able to complete a handshake, when in fact they were not, and Go automatically negotiated another cipher that the server supports such as TLS_AES_128_GCM_SHA256 to make the connection. This is largely a big problem for compliance environments where reports are needed for compliant TLS ciphers for production web servers.

Steampipe version (steampipe -v)
v0.23.2

Plugin version (steampipe plugin list)
v0.12.0

To reproduce
Place a Linux web server with openssl in FIPS mode and run a simple Nginx server. It will not support TLS_CHACHA20_POLY1305_SHA256 as its not FIPS validated. This can be verified with openssl ciphers -v

Run the steampipe query

select
  address,
  version,
  cipher_suite_name,
  handshake_completed
from
  net_tls_connection
where
  address = 'YOUR_NGINX_SERVER:443'
  and handshake_completed;

and notice results such as:

+-----------------+----------+---------------------------------------+-----------------+---------------------+----------------+
| address         | version  | cipher_suite_name                     | cipher_suite_id | handshake_completed | alpn_supported |
+-----------------+----------+---------------------------------------+-----------------+---------------------+----------------+
| 10.0.84.132:443 | TLS v1.3 | TLS_AES_256_GCM_SHA384                | 0x1302          | true                | true           |
| 10.0.84.132:443 | TLS v1.2 | TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 | 0xc02f          | true                | true           |
| 10.0.84.132:443 | TLS v1.3 | TLS_AES_128_GCM_SHA256                | 0x1301          | true                | true           |
| 10.0.84.132:443 | TLS v1.3 | TLS_CHACHA20_POLY1305_SHA256          | 0x1303          | true                | true           |
| 10.0.84.132:443 | TLS v1.2 | TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 | 0xc030          | true                | true           |
+-----------------+----------+---------------------------------------+-----------------+---------------------+----------------+

Where steampipe is claiming it connected to that server which doesn't support TLS_CHACHA20_POLY1305_SHA256 using the TLS_CHACHA20_POLY1305_SHA256 cipher.

Expected behavior
Either replace the cipher_suite_name and ciper_suite_id with the real negotiated cipher instead of the requested one or add a new column for a negotiated cipher to distinguish the difference.

Additional context
Helpful Go test code for this issue.
https://go.dev/blog/tls-cipher-suites
golang/go#29349

A table to run / summarize a traceroute to a specific IP / host would be a handy utility, providing statistics and information about the reachability.

It could leverage a library like https://github.com/aeden/traceroute

A table to ping a specific IP / host would be a handy utility, providing statistics and information about the reachability.

It could leverage a library like https://github.com/go-ping/ping

Describe the bug
When querying TXT records, some domains do not return any data.

Steampipe version (steampipe -v)
steampipe version 0.18.1

Plugin version (steampipe plugin list)

To reproduce
Steps to reproduce the behavior (please include relevant code and/or commands).

Run the following query in Steampipe

select
  value,
  ttl
from
  net_dns_record
where
  domain = 'github.com'
  and type = 'TXT' and dns_server = '8.8.8.8';

Expected behavior
Expecting the result to be similar to nslookup -query=txt github.com 8.8.8.8, however no data is being returned.

Additional context
While running the same query on my private domain massyn.net, the result is being returned as expected. It would appear that there may be some characters or text in the output result of the DNS query that is causing the plugin to drop it.

Describe the bug
select * from net_certificate query fails with an error table 'net_certificate' column 'transparent' requires hydrate data from getCertificateTransparencyLogs, which failed with error invalid character '<' looking for beginning of value.

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

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

To reproduce
Try to execute following query

select * from net_certificate

Expected behavior
Error message should describe the reason.

Additional context
Add any other context about the problem here.