aidansteele commented 3 years ago

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?

e-gineer commented 3 years ago

@aidansteele Great idea for a table!

I researched this idea a while back actually ... will share some of my (incomplete) work below.

Here are a list of possible sources for certificate transparency I found:

While I loved the idea of a certificate transparency table, none of these options jumped out at me as being great.

It's fascinating that crt.sh works as a public postgres database, but I have seen variable performance when connecting to it during testing. In the end it seemed best to just go with the simplest steampipe wrapper FDW. (I agree that using other FDW's in steampipe could be awesome - e.g. postgres_fdw, sqlite_fdw).

e-gineer commented 3 years ago

I did go further, but unfortunately never finished. Here is the rough code I did for crt.sh via HTTP:

package crtsh

import (
    "context"
    "encoding/json"
    "io/ioutil"
    "net/http"
    "net/url"
    "time"

    "github.com/turbot/steampipe-plugin-sdk/grpc/proto"
    "github.com/turbot/steampipe-plugin-sdk/plugin"
    "github.com/turbot/steampipe-plugin-sdk/plugin/transform"
)

type certificateRecord struct {
    IssuerCaID     int    `json:"issuer_ca_id"`
    IssuerName     string `json:"issuer_name"`
    CommonName     string `json:"common_name"`
    NameValue      string `json:"name_value"`
    EntryTimestamp string `json:"entry_timestamp"`
    NotBefore      string `json:"not_before"`
    NotAfter       string `json:"not_after"`
    ID             int    `json:"id"`
    SerialNumber   string `json:"serial_number"`
}

func tableCrtshLog() *plugin.Table {
    return &plugin.Table{
        Name:        "crtsh_log",
        Description: "TODO",
        List: &plugin.ListConfig{
            Hydrate:    listLog,
            KeyColumns: plugin.SingleColumn("query"),
        },
        Columns: []*plugin.Column{
            // Top columns
            {Name: "query", Type: proto.ColumnType_STRING, Hydrate: queryString, Transform: transform.FromValue(), Description: "The search query."},
            {Name: "id", Type: proto.ColumnType_INT, Description: "Unique identifier for the certificate."},
            {Name: "common_name", Type: proto.ColumnType_STRING, Description: "Common name (primary domain) for the certificate."},
            {Name: "subject_alternate_names", Type: proto.ColumnType_JSON, Transform: transform.FromField("NameValue").Transform(linesToStringArray), Description: "Subject alternate names (max 2) from the certificate."},
            {Name: "not_before", Type: proto.ColumnType_TIMESTAMP, Description: "Timestamp when the certificate becomes valid."},
            {Name: "not_after", Type: proto.ColumnType_TIMESTAMP, Description: "Timestamp when the certificate expires."},
            // Other columns
            {Name: "entry_timestamp", Type: proto.ColumnType_TIMESTAMP, Description: "Timestamp when the certificate entry was created."},
            {Name: "issuer_ca_id", Type: proto.ColumnType_STRING, Description: "Unique identifier of the certificate authority that issued the certificate."},
            {Name: "issuer_name", Type: proto.ColumnType_STRING, Description: "Name of the certificate authority that issued the certificate."},
            {Name: "serial_number", Type: proto.ColumnType_STRING, Description: "Serial number for the certificate."},
        },
    }
}

func queryString(ctx context.Context, d *plugin.QueryData, _ *plugin.HydrateData) (interface{}, error) {
    quals := d.KeyColumnQuals
    q := quals["query"].GetStringValue()
    return q, nil
}

func listLog(ctx context.Context, d *plugin.QueryData, _ *plugin.HydrateData) (interface{}, error) {
    // Derived from (MIT license): https://github.com/knqyf263/crtsh/blob/44aa37a00d0c54a9cb28ac24995246025148c596/cmd/search.go#L53
    quals := d.KeyColumnQuals
    query := quals["query"].GetStringValue()
    crtURL := "https://crt.sh/"
    // Set a reasonable timeout
    values := url.Values{}
    values.Add("q", query)
    values.Add("output", "json")
    values.Add("match", "ILIKE")
    // Build the request
    req, err := http.NewRequest("GET", crtURL, nil)
    if err != nil {
        return nil, err
    }
    req.URL.RawQuery = values.Encode()
    // Do the request
    client := &http.Client{Timeout: time.Duration(10) * time.Second}
    resp, err := client.Do(req)
    if err != nil {
        return nil, err
    }
    defer resp.Body.Close()
    b, err := ioutil.ReadAll(resp.Body)
    if err != nil {
        return nil, err
    }
    var results []certificateRecord
    err = json.Unmarshal(b, &results)
    if err != nil {
        plugin.Logger(ctx).Error("crtsh_log.listLog", "result_error", err, "crtURL", crtURL, "body", string(b), "results", results)
        return nil, err
    }
    for _, i := range results {
        d.StreamListItem(ctx, i)
    }
    return nil, nil
}

Which gives me results like this when run in steampipe:

aidansteele commented 3 years ago

That's a great point regarding reliability and performance. You wouldn't want your rules to start failing because of third-party dependencies. Tricky 🤔

github-actions[bot] commented 2 years ago

This issue is stale because it has been open 60 days with no activity. Remove stale label or comment or this will be closed in 30 days.

github-actions[bot] commented 2 years ago

This issue was closed because it has been stalled for 90 days with no activity.

turbot / steampipe-plugin-net

Add table net_certificate_logs #10

General background / value proposition

Implementation questions