turbot / steampipe-postgres-fdw Goto Github PK

The Steampipe foreign data wrapper (FDW) is a zero-ETL product that provides Postgres foreign tables which translate queries into API calls to cloud services and APIs. It's bundled with Steampipe and also available as a set of standalone extensions for use in your own Postgres database.

Home Page: https://steampipe.io/

License: Apache License 2.0

Shell 6.69% Go 68.11% Makefile 2.91% C 21.37% Dockerfile 0.91%

postgresql postgresql-fdw sql steampipe hacktoberfest aws azure data devsecops gcp golang kubernetes postgres security steampipe-engine

steampipe-postgres-fdw's Introduction

Steampipe Postgres FDW

Overview

The Steampipe Postgres Foreign Data Wrapper (FDW) is a Postgres extension that translates APIs to foreign tables. It does not directly interface with external systems, but instead relies on plugins to implement API- or provider-specific code that returns data in a standard format via gRPC. See the Writing Plugins guide to get started writing Steampipe plugins.

The FDW is part of the Steampipe project. Bundled with the Steampipe CLI, it works with one or more of the plugins you install in Steampipe. You can also install one or more plugin-specific extensions in your own instance of Postgres.

Getting Started

To use the FDW with Steampipe, download Steampipe and use it to install one or more plugins.

You can also use a standalone installer that enables you to choose a plugin and download the FDW for that plugin.

Installation guide →

Developing

Building the FDW for Steampipe

Make sure that you have the following installed in your system:

Postgresql v14
go
gcc for Linux

For instructions on how to install PostgreSQL, please visit: https://www.postgresql.org/download/

For instruction on how to install golang, please visit: https://go.dev/dl/

Steps:

Clone this repository onto your system
Change to the cloned directory
Run the following commands:

$ make

This will compile the FDW (steampipe_postgres_fdw.so) along with the control and sql file in the build-$PLATFORM directory. This will install the compiled FDW into the default Steampipe installation directory (~/.steampipe) - if it exists.

Building the FDW as a standalone extension

To build the FDW for one particular plugin, and run it as a standalone extension in any PostgreSQL database without relying on Steampipe:

Make sure that you have the following installed in your system:

Postgresql v14
go
gcc for Linux

Steps:

Clone this repository onto your system
Change to the cloned directory
Run the following commands:

$ make standalone plugin="<plugin alias>"

Replace plugin alias with the alias or short name of your plugin.

This command will compile the FDW specifically for the chosen plugin, and the resulting binary, control file, and SQL files will be generated.

Example

Suppose you want to build the FDW for a plugin with an alias aws from a GitHub repository located at https://github.com/turbot/steampipe-plugin-aws. You would run the following command:

$ make standalone plugin="aws"

Open Source & Contributing

This repository is published under the Apache 2.0 license. Please see our code of conduct. We look forward to collaborating with you!

Steampipe is a product produced exclusively by Turbot HQ, Inc. It is distributed under our commercial terms. Others are allowed to make their own distribution of the software, but cannot use any of the Turbot trademarks, cloud services, etc. You can learn more in our Open Source FAQ.

steampipe-postgres-fdw's People

Contributors

Stargazers

Watchers

Forkers

tnelson-doghouse ruiaylin pdecat theotherbrian1 aluki dim-ops ppolydoras ockhamlabs judell jreyesr yc3030 tektite-io jonudell shawndonlin raw-labs datacosmos-br

steampipe-postgres-fdw's Issues

JSON data with \u0000 errors in Postgres with "unsupported Unicode escape sequence"

Describe the bug

Running this query

select * from shodan_search where query = 'ssh -port:22'

Seems to return some JSON data with \u0000, leading to the error:

Error: unsupported Unicode escape sequence

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

To reproduce

> select * from shodan_search where query = 'ssh -port:22'
Error: unsupported Unicode escape sequence

Expected behavior

I'd expect the JSON column handler to either strip the null field \u0000 out to fix the parsing, or to replace the JSON with error content.

Additional context

N/A

Errors when joining on multiple required (list) key columns

Only seems to occur when joining to table that has multiple keyColumns defined for List - I think aws_iam_policy_simulator may be the only such table currently:

select
    u.name as username,
    s.decision,
    jsonb_pretty(s.matched_statements)
from
    aws_iam_user as u,
    aws_iam_policy_simulator as s
where
    s.action = 's3:DeleteBucket'
    and s.resource_arn = '*';

Error: 'List' call requires an '=' qual for all columns: principal_arn,action,resource_arn

Cannot use AnyColumn with a bool qual on a list call?

~/src/steampipe-plugin-alicloud $ steampipe query
Welcome to Steampipe v0.1.0
For more information, type .help
> 
> select * from alicloud_vpc
Error: pq: rpc error: code = Internal desc = 'List' call requires an '=' qual for one of columns: is_default,id
> 
> select * from alicloud_vpc where id = 'vpc-0xink0hecahhfo5h0vy71';
+-------+---------------------------+-----------+-----------+-------------+-----------------+---------------------------+-
| name  | id                        | region_id | status    | cidr_block  | ipv6_cidr_block | vrouter_id                | 
+-------+---------------------------+-----------+-----------+-------------+-----------------+---------------------------+-
| test2 | vpc-0xink0hecahhfo5h0vy71 | us-east-1 | Available | 10.2.0.0/16 |                 | vrt-0xig35dgad2ti8q0n40ke | 
+-------+---------------------------+-----------+-----------+-------------+-----------------+---------------------------+-
> 
> select * from alicloud_vpc where is_default
Error: pq: rpc error: code = Internal desc = 'List' call requires an '=' qual for one of columns: is_default,id
> 
> select * from alicloud_vpc where not is_default
Error: pq: rpc error: code = Internal desc = list call listVpc failed with panic we do not have the quals for a list
> 
> select * from alicloud_vpc where is_default is true
Error: pq: rpc error: code = Internal desc = 'List' call requires an '=' qual for one of columns: is_default,id
> 
> 
> select * from alicloud_vpc where is_default = true
Error: pq: rpc error: code = Internal desc = 'List' call requires an '=' qual for one of columns: is_default,id
>

tables with zero rows hang the TablePlus database client

I am seeing a problem for tables with zero rows. They work fine in the steampipe CLI, but they currently hang the TablePlus database client. Specifically, it seems to be expecting more rows to be returned and is waiting for them. It works well when there are any actual rows. Seems like the zero rows case specifically is causing it to wait indefinitely. I'm not sure why...

Improve error handling and reporting for config parsing failures

add support for steampipe-plugin-sdk version 0.3.0

Error when selecting '*' in join

This does not work:

    select
    *
    from 
    aws_iam_user as u, 
    aws_iam_policy as p
    where u.permissions_boundary_arn = p.arn;

    Error: pq: cannot iterate: there was an error executing scanIterator: rpc error: code = Unknown desc = column 'region' requires hydrate data from listIamUsers but none is available

But this works fine:

> select 
  z.name, z.id, z.private_zone, r.*
from 
 aws_route53_zone as z,
 aws_route53_record as r
where z.id = r.zone_id

join `get` calls that throw errors or have null values in joined KeyColumns cause errors

The plugin should probably just return no rows instead of error:

  select * from aws_iam_policy where arn = 'x'

  Error: InvalidParameter: 1 validation error(s) found.
  - minimum field size of 20, GetPolicyInput.PolicyArn.

but it seems like either the FDW of the SDK might be able to detect when the join key is null and NOT do the nested query???

select 
    u.name as user_name, 
    u.arn as user_arn,  
    u.permissions_boundary_arn, 
    p.name as policy_name,
    p.arn as policy_arn
from 
    aws_morales.aws_iam_user as u, 
    aws_morales.aws_iam_policy as p
where u.permissions_boundary_arn = p.arn

Error: InvalidParameter: 1 validation error(s) found.
- minimum field size of 20, GetPolicyInput.PolicyArn.

Note that this is also potentially solved by NOT passing PathKeys for KeyColumns on Get and continue to do list calls and have Postgres merge them (which may be more efficient in some cases, especially with caching enabled?)

qual error for tables that have required KeyColumns in ListConfig


select repository_full_name, issue_number from github_repository_issue where repository_full_name = 'turbot/steampipe' or  repository_full_name = 'turbot/steampipe-plugin-sdk'
Error: pq: rpc error: code = Internal desc = 'List' call requires an '=' qual for column: repository_full_name

Multi column github join returning too many rows?

I'm trying to get commit data for each tag in a github repo. But, when I do the join it seems to return too many columns each time?

> select commit_sha from github_tag where repository_full_name = 'turbot/steampipe-plugin-aws'
+------------------------------------------+
| commit_sha                               |
+------------------------------------------+
| fa2897d55f312b94176f255d21cf15430e83e9ff |
| c64562b03df0badfb64f0fb5581a829a4bb01087 |
| 29b8c635414dc2268eef1ffb38dd0085164c6a5a |
| 39e6fc4ffb360904cd244a9a637d73313fdfdd67 |
| 7ae796c3e3b1aefbe60a5810ca8f971dbd05745f |
| 91961cd85387ff271a5e720e838ce1c3f55f34c9 |
| 956c8debeb3049e4344f019ef3ecfb0f442ae065 |
| dd02b8ed972e46f88661049d74970c10f0897220 |
| a3c7da8c6b1f190afe155209cf7a77c5b4d703e3 |
| 27a996429a1268ea861cc5cb43d0344c3d592297 |
| 48bd0cd4c3267d36f11926be5678605fb225c16b |
| 4a47bede92f5624c1763025bf43d2ce9a665fa71 |
| 4521aa40811e5dc401f822d769aa6ece541f3fe8 |
| b1f4519a1d013cf239b1f3f180f899a6fa6a6c60 |
| 92676be13febc0e290191fbcb5ab05383fcf9f0f |
| 7872804fb729b88bfda4361d6cf9f626c8a3f19a |
| 3b749b51b41609b42059d2f843bf78dbf6ad98b4 |
| da4ccff31bb1881a2c43f642c8658529a129d899 |
| 7263eb680c8b1799fc4394b159b089db13145ad7 |
| abd8a067d8031b85a50084e9d6af8979371e4e14 |
| d2da467b6182c7c539c4f3020244edd4bed7a122 |
| d2da467b6182c7c539c4f3020244edd4bed7a122 |
| d2da467b6182c7c539c4f3020244edd4bed7a122 |
| e04d29c2d7d41296652c38157115fccfee5bf772 |
+------------------------------------------+
> 
> 
> select count(*) from github_commit where repository_full_name = 'turbot/steampipe-plugin-aws' and sha in (select commit_sha from github_tag where repository_full_name = 'turbot/steampipe-plugin-aws')
+-------+
| count |
+-------+
| 167   |
+-------+
> 
> select count(*) from github_commit where repository_full_name = 'turbot/steampipe-plugin-aws' and sha = any(select commit_sha from github_tag where repository_full_name = 'turbot/steampipe-plugin-aws')
+-------+
| count |
+-------+
| 167   |
+-------+
> 
> 
> select count(*) from github_commit as c, github_tag as t where c.repository_full_name = 'turbot/steampipe-plugin-aws' and t.repository_full_name = 'turbot/steampipe-plugin-aws' and c.sha = t.commit_sha
+-------+
| count |
+-------+
| 4008  |
+-------+

using distinct in query causes steampipe to hang

Describe the bug
Not sure if this is a plugin issue or steampipe issue, but when I add the distinct keyword to my query, steampipe goes into an endless loop that I can't break with ctrl-c (needed to use ctrl-z) and then force quit the steampipe service.

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

To reproduce

select distinct
  group_id,
  cidr_ip
from
  aws_vpc_security_group_rule;

Expected behavior
Query returns a value or error message.

Additional context
Add any other context about the problem here.

Fix environment variables

Not all environment variables are copied from C to Go domain

https://turbothq.slack.com/archives/D6LQM0D5Y/p1603486635003400

A tactical fix is in place of copying all env vars over

timestamp quals not working for key columns

➜  steampipe-plugin-aws git:(issue-33) ✗ steampipe query       
Welcome to Steampipe v0.1.1
For more information, type .help
> select * from aws_cloudtrail_trail_event where event_time='2021-02-03T14:37:27Z';

Error: pq: rpc error: code = Internal desc = 'List' call requires an '=' qual for column: event_time
>

func tableAwsCloudtrailEvent(_ context.Context) *plugin.Table {
	return &plugin.Table{
		Name:        "aws_cloudtrail_trail_event",
		Description: "AWS CloudTrail Trail Event",
		List: &plugin.ListConfig{
			KeyColumns: plugin.SingleColumn("event_time"),
			Hydrate:    listCloudtrailEvents,
		},

branch https://github.com/turbot/steampipe-plugin-aws/tree/issue-33

moved from turbot/steampipe#132

Required qual in where only works when the same field is in the column list

See in the example below how removing the id column from the columns causes the qual in the where clause to stop working.

Interesting to me that adding id to the column list causes a qual on the user_id field to work...

> select id, username from twitter_user_follower where user_id in (select id from twitter_user where username = 'steampipeio')
+------------+---------------+
| id         | username      |
+------------+---------------+
| 853536468  | daleburncock  |
| 130100271  | NLNils        |
| 1455702266 | _cloudkate    |
| 14372877   | dlatkins      |
| 825766640  | HamelHusain   |
| 160129663  | vatsparth     |
| 2413837128 | LtB1991       |
| 115454948  | raj_mohanty   |
| 348925658  | ibm2100       |
| 2469180332 | turbothq      |
| 8092452    | nathanwallace |
| 2389633566 | TheFatJohnny  |
+------------+---------------+
> 
> select uf.id, uf.username from twitter_user_follower as uf where uf.user_id in (select id from twitter_user where username = 'steampipeio')
+------------+---------------+
| id         | username      |
+------------+---------------+
| 853536468  | daleburncock  |
| 130100271  | NLNils        |
| 1455702266 | _cloudkate    |
| 14372877   | dlatkins      |
| 825766640  | HamelHusain   |
| 160129663  | vatsparth     |
| 2413837128 | LtB1991       |
| 115454948  | raj_mohanty   |
| 348925658  | ibm2100       |
| 2469180332 | turbothq      |
| 8092452    | nathanwallace |
| 2389633566 | TheFatJohnny  |
+------------+---------------+
> 
> select uf.username from twitter_user_follower as uf where uf.user_id in (select id from twitter_user where username = 'steampipeio')
Error: 'List' call requires an '=' qual for column: user_id
>

Investigate getting limit through the quals

Boolean env vars should support all truthy values.

Update connection config parsing for new steampipe version

unexpected behavior with a author_id = (select id from foo) query

The join works, but the sub-select does not.
Also interesting is that the log shows a ? as the value:

2021-03-23 23:07:21.363 EDT [12667] LOG:  statement: select id, text from twitter_user_tweet_timeline where author_id = (select '1318177503995985921')
2021-03-23T23:07:21.369-0400 [WARN]  plugin.steampipe-plugin-twitter.plugin: [WARN]  executeListCall we have single key column
2021-03-23T23:07:21.369-0400 [WARN]  plugin.steampipe-plugin-twitter.plugin: [WARN]  listUserTweetTimeline: authorID=?

~/src/steampipe-plugin-twitter $ steampipe query
Welcome to Steampipe v0.3.2
For more information, type .help
> select id, text from twitter_user_tweet_timeline where author_id = '1318177503995985921'
+---------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id                  | text                                                                                                                                                                      |
+---------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 1373023688862408710 | New: Steampipe v0.3.0                                                                                                                                                     |
|                     |                                                                                                                                                                           |
|                     | - Query caching                                                                                                                                                           |
|                     | - Configuration settings                                                                                                                                                  |
|                     | - Improved quals for mind-blowing joins                                                                                                                                   |
|                     |                                                                                                                                                                           |
|                     | https://t.co/GsWqO3k6p7                                                                                                                                                   |
| 1354121537637511168 | RT @turbothq: Introducing Steampipe: 𝚜𝚎𝚕𝚎𝚌𝚝 * 𝚏𝚛𝚘𝚖 𝚌𝚕𝚘𝚞𝚍;                                                                                                                 |
|                     | Open source. Download at https://t.co/fsj9Fy23av https://t.co/6Ct3l8PZht                                                                                                  |
| 1373134228620214275 | 𝚜𝚎𝚕𝚎𝚌𝚝 * 𝚏𝚛𝚘𝚖 𝚌𝚕𝚘𝚞𝚍;                                                                                                                                                      |
|                     |                                                                                                                                                                           |
|                     | 60 seconds of awesome! Watch the Steampipe CLI demo: https://t.co/IPilF4u38R                                                                                              |
| 1374512803063623688 | @JensenKarp @CTCSquares One person's delicacy is another's tweet storm... See the world through our #rowscoloredglasses | https://t.co/FTuBWA9EPV https://t.co/4atmjhoJhM |
+---------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
> 
> select id, text from twitter_user_tweet_timeline where author_id = any(array['1318177503995985921'])
+---------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id                  | text                                                                                                                                                                      |
+---------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 1373023688862408710 | New: Steampipe v0.3.0                                                                                                                                                     |
|                     |                                                                                                                                                                           |
|                     | - Query caching                                                                                                                                                           |
|                     | - Configuration settings                                                                                                                                                  |
|                     | - Improved quals for mind-blowing joins                                                                                                                                   |
|                     |                                                                                                                                                                           |
|                     | https://t.co/GsWqO3k6p7                                                                                                                                                   |
| 1373134228620214275 | 𝚜𝚎𝚕𝚎𝚌𝚝 * 𝚏𝚛𝚘𝚖 𝚌𝚕𝚘𝚞𝚍;                                                                                                                                                      |
|                     |                                                                                                                                                                           |
|                     | 60 seconds of awesome! Watch the Steampipe CLI demo: https://t.co/IPilF4u38R                                                                                              |
| 1374512803063623688 | @JensenKarp @CTCSquares One person's delicacy is another's tweet storm... See the world through our #rowscoloredglasses | https://t.co/FTuBWA9EPV https://t.co/4atmjhoJhM |
| 1354121537637511168 | RT @turbothq: Introducing Steampipe: 𝚜𝚎𝚕𝚎𝚌𝚝 * 𝚏𝚛𝚘𝚖 𝚌𝚕𝚘𝚞𝚍;                                                                                                                 |
|                     | Open source. Download at https://t.co/fsj9Fy23av https://t.co/6Ct3l8PZht                                                                                                  |
+---------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
> 
> select id, text from twitter_user_tweet_timeline where author_id = (select '1318177503995985921')
+----+------+
| id | text |
+----+------+
+----+------+
> 
> select id, text from twitter_user_tweet_timeline where author_id = (select id from twitter_user where username = 'steampipeio')
+----+------+
| id | text |
+----+------+
+----+------+
> 
> select t.id, t.text from twitter_user_tweet_timeline as t, twitter_user as u where t.author_id = u.id and u.username = 'steampipeio'
+---------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id                  | text                                                                                                                                                                      |
+---------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 1373023688862408710 | New: Steampipe v0.3.0                                                                                                                                                     |
|                     |                                                                                                                                                                           |
|                     | - Query caching                                                                                                                                                           |
|                     | - Configuration settings                                                                                                                                                  |
|                     | - Improved quals for mind-blowing joins                                                                                                                                   |
|                     |                                                                                                                                                                           |
|                     | https://t.co/GsWqO3k6p7                                                                                                                                                   |
| 1354121537637511168 | RT @turbothq: Introducing Steampipe: 𝚜𝚎𝚕𝚎𝚌𝚝 * 𝚏𝚛𝚘𝚖 𝚌𝚕𝚘𝚞𝚍;                                                                                                                 |
|                     | Open source. Download at https://t.co/fsj9Fy23av https://t.co/6Ct3l8PZht                                                                                                  |
| 1373134228620214275 | 𝚜𝚎𝚕𝚎𝚌𝚝 * 𝚏𝚛𝚘𝚖 𝚌𝚕𝚘𝚞𝚍;                                                                                                                                                      |
|                     |                                                                                                                                                                           |
|                     | 60 seconds of awesome! Watch the Steampipe CLI demo: https://t.co/IPilF4u38R                                                                                              |
| 1374512803063623688 | @JensenKarp @CTCSquares One person's delicacy is another's tweet storm... See the world through our #rowscoloredglasses | https://t.co/FTuBWA9EPV https://t.co/4atmjhoJhM |
+---------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
>

Incorrect caching of get query results depending on order executed?

Note: I am running Steampipe CLI as at commit 6737f1a6ca3057925a23634e71a3ffca2678a982.

The sequence below shows:

Works - List, get existing, get not found
Works - Get existing, get not found
Does not work - Get not found, get existing

~/src/steampipe-plugin-stripe $ STEAMPIPE_LOG=info steampipe query
Welcome to Steampipe v0.6.0
For more information, type .help
> select id, created from stripe_plan
+--------------------------------+---------------------+
| id                             | created             |
+--------------------------------+---------------------+
| plan_GmRtT3Ksn1l3Z8            | 2020-02-22 08:05:31 |
| plan_Glhgm5HFmajz2Y            | 2020-02-20 08:20:41 |
| plan_Go1Pi7XlFnoINF            | 2020-02-26 12:51:32 |
| plan_Go1hfhkx4hENvN            | 2020-02-26 13:09:27 |
| plan_GoGB23ebPyPjQG            | 2020-02-27 04:08:18 |
| plan_GlhddJMh3ihoMh            | 2020-02-20 08:17:51 |
| plan_GmRuksdi3yyeKC            | 2020-02-22 08:06:37 |
| plan_GlgszozjOqKENk            | 2020-02-20 07:30:22 |
| plan_Go0cbVFaUvqCtq            | 2020-02-26 12:03:08 |
| plan_GmRvVFFZWDNrlj            | 2020-02-22 08:07:42 |
| plan_GlNps9zBgYWfAZ            | 2020-02-19 11:49:55 |
| plan_GlhM2vySdj4Zyl            | 2020-02-20 08:00:57 |
| plan_EuyktPl4NZNDeo            | 2019-04-20 02:19:14 |
| price_1HN6QOCWwOK68BLnJwmOsOT7 | 2020-09-03 00:35:36 |
| plan_GntZePThwRfIs3            | 2020-02-26 04:45:50 |
| plan_Go0lOnmXvF2Y1X            | 2020-02-26 12:12:11 |
| plan_GlhIOXtcOCthlS            | 2020-02-20 07:56:49 |
| plan_GoGIO1fAN9Ic1d            | 2020-02-27 04:14:56 |
| plan_Go0eKhbj6qcXmv            | 2020-02-26 12:05:06 |
| plan_GoG89q5c5Vbwhr            | 2020-02-27 04:04:51 |
| plan_GmRwRr09Wj1F8a            | 2020-02-22 08:08:21 |
| plan_GnaaiosiGyytx3            | 2020-02-25 09:08:30 |
+--------------------------------+---------------------+
> select id, created from stripe_plan where id = 'plan_GlNps9zBgYWfAZ'
+---------------------+---------------------+
| id                  | created             |
+---------------------+---------------------+
| plan_GlNps9zBgYWfAZ | 2020-02-19 11:49:55 |
+---------------------+---------------------+
> select id, created from stripe_plan where id = 'plan_GlNps9zBgYWfAC'
+----+---------+
| id | created |
+----+---------+
+----+---------+
> 
~/src/steampipe-plugin-stripe $ STEAMPIPE_LOG=info steampipe query
Welcome to Steampipe v0.6.0
For more information, type .help
> select id, created from stripe_plan where id = 'plan_GlNps9zBgYWfAZ'
+---------------------+---------------------+
| id                  | created             |
+---------------------+---------------------+
| plan_GlNps9zBgYWfAZ | 2020-02-19 11:49:55 |
+---------------------+---------------------+
> select id, created from stripe_plan where id = 'plan_GlNps9zBgYWfAC'
+----+---------+
| id | created |
+----+---------+
+----+---------+
> 
~/src/steampipe-plugin-stripe $ 
~/src/steampipe-plugin-stripe $ STEAMPIPE_LOG=info steampipe query
Welcome to Steampipe v0.6.0
For more information, type .help
> select id, created from stripe_plan where id = 'plan_GlNps9zBgYWfAC'
+----+---------+
| id | created |
+----+---------+
+----+---------+
> select id, created from stripe_plan where id = 'plan_GlNps9zBgYWfAZ'
+----+---------+
| id | created |
+----+---------+
+----+---------+
> 
~/src/steampipe-plugin-stripe $

Here are logs for the final part showing the unexpected cache hit for the existing item after getting a non-existing item:

2021-06-19 08:59:32.169 EDT [1638] LOG:  statement: COMMIT
2021-06-19 08:59:33.690 EDT [1638] LOG:  statement: BEGIN READ WRITE
2021-06-19 08:59:33.690 EDT [1638] LOG:  statement: select id, created from stripe_plan where id = 'plan_GlNps9zBgYWfAC'
2021/06/19 06:59:33 [INFO] 
******************************************************

		steampipe postgres fdw init

******************************************************
2021/06/19 06:59:33 [INFO] Log level info
2021-06-19T06:59:33.717-0600 [INFO]  hub: query cache created
2021-06-19T06:59:33.739-0600 [INFO]  hub: GetPathKeys for connection 'stripe`, table `stripe_plan` returning [{[active] 10} {[active id] 1} {[active nickname] 1} {[active aggregate_usage] 1} {[active amount] 1} {[active amount_decimal] 1} {[active billing_scheme] 1} {[active created] 1} {[active currency] 1} {[active deleted] 1} {[active interval] 1} {[active interval_count] 1} {[active livemode] 1} {[active metadata] 1} {[active product_id] 1} {[active tiers] 1} {[active tiers_mode] 1} {[active transform_usage] 1} {[active trial_period_days] 1} {[active usage_type] 1} {[product] 10} {[product id] 1} {[product nickname] 1} {[product active] 1} {[product aggregate_usage] 1} {[product amount] 1} {[product amount_decimal] 1} {[product billing_scheme] 1} {[product created] 1} {[product currency] 1} {[product deleted] 1} {[product interval] 1} {[product interval_count] 1} {[product livemode] 1} {[product metadata] 1} {[product product_id] 1} {[product tiers] 1} {[product tiers_mode] 1} {[product transform_usage] 1} {[product trial_period_days] 1} {[product usage_type] 1}]
2021-06-19T06:59:33.740-0600 [INFO]  hub: goFdwBeginForeignScan, connection 'stripe', table 'stripe_plan'
2021-06-19T06:59:33.740-0600 [INFO]  hub: RestrictionsToQuals: converted postgres restrictions protobuf quals
2021-06-19T06:59:33.740-0600 [INFO]  hub: executing query for connection stripe, caching ENABLED with TTL 300 seconds
2021-06-19T06:59:33.740-0600 [INFO]  hub: connection 'stripe', table 'stripe_plan', quals 
----------------------------------------------------------------
	Column: id, Operator: '=', Value: 'plan_GlNps9zBgYWfAC'
----------------------------------------------------------------
2021-06-19T06:59:33.740-0600 [INFO]  hub: CACHE MISS - no index
2021-06-19T06:59:33.740-0600 [INFO]  hub: StartScan
  table: stripe_plan
  columns: [id created]
2021-06-19T06:59:33.741-0600 [INFO]  plugin.steampipe-plugin-stripe.plugin: [INFO] get quals - this is a get call  map[id:string_value:"plan_GlNps9zBgYWfAC"]
2021-06-19T06:59:34.336-0600 [ERROR] plugin.steampipe-plugin-stripe.plugin: [ERROR] stripe_customer.getPlan: query_error="{"code":"resource_missing","doc_url":"https://stripe.com/docs/error-codes/resource-missing","status":404,"message":"No such plan: 'plan_GlNps9zBgYWfAC'","param":"plan","request_id":"req_17ntQFwMCtpeTa","type":"invalid_request_error"}" id=plan_GlNps9zBgYWfAC
2021-06-19T06:59:34.336-0600 [INFO]  hub: scan complete
2021-06-19T06:59:34.336-0600 [INFO]  hub: adding 0 rows to cache
2021-06-19 08:59:34.337 EDT [1638] LOG:  statement: COMMIT
2021-06-19 08:59:36.399 EDT [1638] LOG:  statement: BEGIN READ WRITE
2021-06-19 08:59:36.399 EDT [1638] LOG:  statement: select id, created from stripe_plan where id = 'plan_GlNps9zBgYWfAZ'
2021-06-19T06:59:36.399-0600 [INFO]  hub: GetPathKeys for connection 'stripe`, table `stripe_plan` returning [{[active] 10} {[active id] 1} {[active nickname] 1} {[active aggregate_usage] 1} {[active amount] 1} {[active amount_decimal] 1} {[active billing_scheme] 1} {[active created] 1} {[active currency] 1} {[active deleted] 1} {[active interval] 1} {[active interval_count] 1} {[active livemode] 1} {[active metadata] 1} {[active product_id] 1} {[active tiers] 1} {[active tiers_mode] 1} {[active transform_usage] 1} {[active trial_period_days] 1} {[active usage_type] 1} {[product] 10} {[product id] 1} {[product nickname] 1} {[product active] 1} {[product aggregate_usage] 1} {[product amount] 1} {[product amount_decimal] 1} {[product billing_scheme] 1} {[product created] 1} {[product currency] 1} {[product deleted] 1} {[product interval] 1} {[product interval_count] 1} {[product livemode] 1} {[product metadata] 1} {[product product_id] 1} {[product tiers] 1} {[product tiers_mode] 1} {[product transform_usage] 1} {[product trial_period_days] 1} {[product usage_type] 1}]
2021-06-19T06:59:36.399-0600 [INFO]  hub: goFdwBeginForeignScan, connection 'stripe', table 'stripe_plan'
2021-06-19T06:59:36.400-0600 [INFO]  hub: RestrictionsToQuals: converted postgres restrictions protobuf quals
2021-06-19T06:59:36.400-0600 [INFO]  hub: executing query for connection stripe, caching ENABLED with TTL 300 seconds
2021-06-19T06:59:36.400-0600 [INFO]  hub: connection 'stripe', table 'stripe_plan', quals 
----------------------------------------------------------------
	Column: id, Operator: '=', Value: 'plan_GlNps9zBgYWfAZ'
----------------------------------------------------------------
2021-06-19T06:59:36.400-0600 [INFO]  hub: CACHE HIT
2021-06-19 08:59:36.400 EDT [1638] LOG:  statement: COMMIT
2021-06-19 08:59:38.432 EDT [1622] LOG:  received smart shutdown request
2021-06-19 08:59:38.433 EDT [1622] LOG:  background worker "logical replication launcher" (PID 1630) exited with exit code 1
2021-06-19 08:59:38.442 EDT [1626] LOG:  shutting down
2021-06-19 08:59:38.450 EDT [1622] LOG:  database system is shut down

Add support for connection config

v0.0.24

Update cache key generation to use more compact version of quals

at present cache key includes the string version of the qual map, which is quite verbose

Update the string function in the sdk to be more compact

'where ... in' doesnt work on table with keycolumns on a ListConfig

This works:

> select repository_full_name, issue_number from github_repository_issue where repository_full_name = 'turbot/steampipe'
+----------------------+--------------+
| repository_full_name | issue_number |
+----------------------+--------------+
| turbot/steampipe     |           81 |

thjs doesnt:

select repository_full_name, issue_number from github_repository_issue where repository_full_name in ('turbot/steampipe', 'turbot/steampipe-plugin-sdk')
+----------------------+--------------+
| repository_full_name | issue_number |
+----------------------+--------------+
+----------------------+--------------+

Nested queries do not pass quals to outer query

this means we do not execute a get call or pass any potential required quals to the list call.

investigate whether we can tell postgres to execute the subquery first

for example ```select
login,
email
from github.github_user
where login in (
select jsonb_array_elements(collaborators) ->> 'login' as login
from github.github_repository
where owner_login = 'turbotio' and name = 'turbot-core'
order by login
);

No quals are passed to the outer query

Possible approach - use array returning functions instead 


see
https://github.com/turbotio/steampipe-plugin-sdk/issues/58
https://github.com/turbotio/steampipe-plugin-sdk/issues/66
https://github.com/turbotio/steampipe-plugin-sdk/issues/34
https://github.com/turbotio/steampipe-plugin-sdk/issues/63

disable caching by default

only enable if STEAMPIPE_CACHE=true

Timestamp quals are not working

cache key built incorrectly when more than 1 qual used

Support cache configuration via Steampipe config

see turbot/steampipe#230

all env vars should have STEAMPIPE_ prefix

Add options to specify whether or not to add PathKeys for `Get` KeyColumns, and possibly for "optional' list columns

We should ALWAYS specify PathKeys for required 'KeyColumns' on 'List' calls, and we should force this path. This is required in order to join these tables at all (must be a nested loop join, with a filtered scan)
We should DEFAULT to NOT specifying PathKeys for Get KeyColumns when a table has a List with no required KeyColumns. This will result in a path where both tables are scanned and then joined/merged by postgres after they are returned by the plugin. For most cases, this is likely more efficient (even more soe with caching enabled)
We may want to add an ability for the plugin to specify pathkeys for Get (or maybe on other "optional" list/get columns, TBD) if the List call is thought to be very expensive compared to a few Get calls.

Crash when doing "is (not) null" checks on JSON fields

Describe the bug

There is a crash when doing null checks against JSON fields:

> select name, tags from aws_s3_bucket where tags is null
+---------------------------+--------+
| name                      | tags   |
+---------------------------+--------+
| turbot-example-us-east-2  | <null> |
+---------------------------+--------+
> 
> select name, tags from aws_s3_bucket where tags->>'Collective' = 'brooklyn'
+----------------------------+------------------------------------------------------------------------------+
| name                       | tags                                                                         |
+----------------------------+------------------------------------------------------------------------------+
| brooklyn-example-us-east-2 | {"Collective":"brooklyn","Drone":"primary","Name":"turbot.brooklyn.primary"} |
+----------------------------+------------------------------------------------------------------------------+
> 
> select name, tags from aws_s3_bucket where tags->>'Collective' is not null
Error: runtime error: invalid memory address or nil pointer dereference
> 
> select name, tags from aws_s3_bucket where tags->>'Collective' is null
Error: runtime error: invalid memory address or nil pointer dereference
> 
>

Steampipe version (steampipe -v)

v0.3.0

To reproduce

See above

Expected behavior

Should match against the query (not crash).

Additional context

From the log:

2021-03-19 15:31:44.729 EDT [6791] LOG:  statement: select * from aws_s3_bucket
2021-03-19 15:31:48.243 EDT [6791] LOG:  duration: 3514.316 ms
2021-03-19 15:32:05.649 EDT [6791] LOG:  statement: select name, tags from aws_s3_bucket
2021-03-19 15:32:17.806 EDT [6791] LOG:  statement: select name, tags from aws_s3_bucket where tags->'Collective' is null
2021-03-19T15:32:17.806-0400 [WARN]  hub: goFdwBeginForeignScan failed with panic: runtime error: invalid memory address or nil pointer dereference
2021-03-19 15:32:17.806 EDT [6791] ERROR:  runtime error: invalid memory address or nil pointer dereference

datumToQualValue does not handle TIMESTAMPOID type

this is causing #24

extractRestrictions does not handle T_BoolExpr

this is the cause of the first issue listed here:

select repository_full_name, issue_number from github_repository_issue where repository_full_name = 'turbot/steampipe' or  repository_full_name = 'turbot/steampipe-plugin-sdk'
Error: pq: rpc error: code = Internal desc = 'List' call requires an '=' qual for column: repository_full_name

It also prevents quals being passed when passing boolean restrictions like

 select * from chaos_all_column_types where boolean_column is true

Optional key col of boolean type is not used when set to false

Note: I'm not sure it matters, but I am testing the lastest version of the SDK:

github.com/turbot/steampipe-plugin-sdk v0.3.0-rc.0.0.20210618102921-1cc4077273a6

I have a table with an optional key col of boolean type:

func tableStripeProduct(ctx context.Context) *plugin.Table {
	return &plugin.Table{
		Name:        "stripe_product",
		Description: "Products available for purchase or subscription.",
		List: &plugin.ListConfig{
			Hydrate:            listProduct,
			OptionalKeyColumns: plugin.AnyColumn([]string{"active", "id", "shippable", "url"}),
		},
		Columns: []*plugin.Column{
			// Top columns
			{Name: "id", Type: proto.ColumnType_STRING, Description: "Unique identifier for the product."},
			{Name: "name", Type: proto.ColumnType_STRING, Description: "The product’s full name or business name."},
			{Name: "type", Type: proto.ColumnType_STRING, Description: "The product type."},
			{Name: "unit_label", Type: proto.ColumnType_STRING, Description: "A label that represents units of this product in Stripe and on customers’ receipts and invoices. When set, this will be included in associated invoice line item descriptions."},
			// Other columns
			{Name: "active", Type: proto.ColumnType_BOOL, Description: "Whether the product is currently available for purchase."},
			{Name: "created", Type: proto.ColumnType_TIMESTAMP, Transform: transform.FromField("Created").Transform(transform.UnixToTimestamp), Description: "Time at which the product was created."},
			{Name: "description", Type: proto.ColumnType_STRING, Description: "An arbitrary string attached to the product. Often useful for displaying to users."},
			{Name: "images", Type: proto.ColumnType_JSON, Description: "A list of up to 8 URLs of images for this product, meant to be displayable to the customer."},
			{Name: "livemode", Type: proto.ColumnType_BOOL, Description: "Has the value true if the product exists in live mode or the value false if the product exists in test mode."},
			{Name: "metadata", Type: proto.ColumnType_JSON, Description: "Set of key-value pairs that you can attach to an product. This can be useful for storing additional information about the product in a structured format."},
			{Name: "package_dimensions", Type: proto.ColumnType_JSON, Description: "The dimensions of this product for shipping purposes."},
			{Name: "shippable", Type: proto.ColumnType_BOOL, Description: "Whether this product is shipped (i.e., physical goods)."},
			{Name: "statement_descriptor", Type: proto.ColumnType_STRING, Description: "Extra information about a product which will appear on your customer’s credit card statement. In the case that multiple products are billed at once, the first statement descriptor will be used."},
			{Name: "updated", Type: proto.ColumnType_TIMESTAMP, Transform: transform.FromField("Updated").Transform(transform.UnixToTimestamp), Description: "Time at which the product was updated."},
			{Name: "url", Type: proto.ColumnType_STRING, Description: "A URL of a publicly-accessible webpage for this product."},
		},
	}
}

When I run a truthy query, the key col is used:

2021-06-18 17:42:09.994 EDT [51228] LOG:  statement: select * from stripe_product where active = true
2021/06/18 15:42:10 [INFO] 
******************************************************

		steampipe postgres fdw init

******************************************************
2021/06/18 15:42:10 [INFO] Log level info
2021-06-18T15:42:10.016-0600 [INFO]  hub: query cache created
2021-06-18T15:42:10.036-0600 [INFO]  hub: GetPathKeys for connection 'stripe`, table `stripe_product` returning [{[active] 10} {[active id] 1} {[active name] 1} {[active type] 1} {[active unit_label] 1} {[active created] 1} {[active description] 1} {[active images] 1} {[active livemode] 1} {[active metadata] 1} {[active package_dimensions] 1} {[active shippable] 1} {[active statement_descriptor] 1} {[active updated] 1} {[active url] 1} {[id] 10} {[id name] 1} {[id type] 1} {[id unit_label] 1} {[id active] 1} {[id created] 1} {[id description] 1} {[id images] 1} {[id livemode] 1} {[id metadata] 1} {[id package_dimensions] 1} {[id shippable] 1} {[id statement_descriptor] 1} {[id updated] 1} {[id url] 1} {[shippable] 10} {[shippable id] 1} {[shippable name] 1} {[shippable type] 1} {[shippable unit_label] 1} {[shippable active] 1} {[shippable created] 1} {[shippable description] 1} {[shippable images] 1} {[shippable livemode] 1} {[shippable metadata] 1} {[shippable package_dimensions] 1} {[shippable statement_descriptor] 1} {[shippable updated] 1} {[shippable url] 1} {[url] 10} {[url id] 1} {[url name] 1} {[url type] 1} {[url unit_label] 1} {[url active] 1} {[url created] 1} {[url description] 1} {[url images] 1} {[url livemode] 1} {[url metadata] 1} {[url package_dimensions] 1} {[url shippable] 1} {[url statement_descriptor] 1} {[url updated] 1}]
2021-06-18T15:42:10.037-0600 [INFO]  hub: goFdwBeginForeignScan, connection 'stripe', table 'stripe_product'
2021-06-18T15:42:10.037-0600 [INFO]  hub: RestrictionsToQuals: converted postgres restrictions protobuf quals
2021-06-18T15:42:10.037-0600 [INFO]  hub: executing query for connection stripe, caching ENABLED with TTL 300 seconds
2021-06-18T15:42:10.037-0600 [INFO]  hub: connection 'stripe', table 'stripe_product', quals 
----------------------------------------------------------------
	Column: active, Operator: '=', Value: 'true'
----------------------------------------------------------------
2021-06-18T15:42:10.037-0600 [INFO]  hub: CACHE MISS - no index
2021-06-18T15:42:10.037-0600 [INFO]  hub: StartScan
  table: stripe_product
  columns: [id name type unit_label active created description images livemode metadata package_dimensions shippable statement_descriptor updated url]
2021-06-18T15:42:10.038-0600 [INFO]  plugin.steampipe-plugin-stripe.plugin: [INFO] list quals - this is list call, list quals: map[], optional list quals: map[active:bool_value:true]
2021-06-18T15:42:10.049-0600 [WARN]  plugin.steampipe-plugin-stripe.plugin: [WARN]  stripe_customer.listProduct: q=map[active:bool_value:true]
2021-06-18T15:42:10.049-0600 [WARN]  plugin.steampipe-plugin-stripe.plugin: [WARN]  stripe_customer.listProduct: params="&{{context.Background.WithValue(type context_key.contextKey, val <not Stringer>) <nil> [] {[]} 0xc00032a040 false <nil> <nil>} 0xc00032a048 <nil> <nil> [] <nil> <nil> <nil>}"
2021-06-18T15:42:10.632-0600 [INFO]  hub: scan complete
2021-06-18T15:42:10.632-0600 [INFO]  hub: adding 12 rows to cache
2021-06-18 17:42:10.633 EDT [51228] LOG:  statement: COMMIT

But when I run the same query with a false-y clause the key col is not used:

2021-06-18 17:41:52.020 EDT [51203] LOG:  statement: select * from stripe_product where active = false
2021/06/18 15:41:52 [INFO] 
******************************************************

		steampipe postgres fdw init

******************************************************
2021/06/18 15:41:52 [INFO] Log level info
2021-06-18T15:41:52.052-0600 [INFO]  hub: query cache created
2021-06-18T15:41:52.075-0600 [INFO]  hub: GetPathKeys for connection 'stripe`, table `stripe_product` returning [{[active] 10} {[active id] 1} {[active name] 1} {[active type] 1} {[active unit_label] 1} {[active created] 1} {[active description] 1} {[active images] 1} {[active livemode] 1} {[active metadata] 1} {[active package_dimensions] 1} {[active shippable] 1} {[active statement_descriptor] 1} {[active updated] 1} {[active url] 1} {[id] 10} {[id name] 1} {[id type] 1} {[id unit_label] 1} {[id active] 1} {[id created] 1} {[id description] 1} {[id images] 1} {[id livemode] 1} {[id metadata] 1} {[id package_dimensions] 1} {[id shippable] 1} {[id statement_descriptor] 1} {[id updated] 1} {[id url] 1} {[shippable] 10} {[shippable id] 1} {[shippable name] 1} {[shippable type] 1} {[shippable unit_label] 1} {[shippable active] 1} {[shippable created] 1} {[shippable description] 1} {[shippable images] 1} {[shippable livemode] 1} {[shippable metadata] 1} {[shippable package_dimensions] 1} {[shippable statement_descriptor] 1} {[shippable updated] 1} {[shippable url] 1} {[url] 10} {[url id] 1} {[url name] 1} {[url type] 1} {[url unit_label] 1} {[url active] 1} {[url created] 1} {[url description] 1} {[url images] 1} {[url livemode] 1} {[url metadata] 1} {[url package_dimensions] 1} {[url shippable] 1} {[url statement_descriptor] 1} {[url updated] 1}]
2021-06-18T15:41:52.076-0600 [INFO]  hub: goFdwBeginForeignScan, connection 'stripe', table 'stripe_product'
2021-06-18T15:41:52.076-0600 [INFO]  hub: RestrictionsToQuals: converted postgres restrictions protobuf quals
2021-06-18T15:41:52.076-0600 [INFO]  hub: executing query for connection stripe, caching ENABLED with TTL 300 seconds
2021-06-18T15:41:52.076-0600 [INFO]  hub: no quals
2021-06-18T15:41:52.076-0600 [INFO]  hub: CACHE MISS - no index
2021-06-18T15:41:52.076-0600 [INFO]  hub: StartScan
  table: stripe_product
  columns: [id name type unit_label active created description images livemode metadata package_dimensions shippable statement_descriptor updated url]
2021-06-18T15:41:52.076-0600 [INFO]  plugin.steampipe-plugin-stripe.plugin: [INFO] table 'stripe_product': list call, with no list quals
2021-06-18T15:41:52.087-0600 [WARN]  plugin.steampipe-plugin-stripe.plugin: [WARN]  stripe_customer.listProduct: q=map[]
2021-06-18T15:41:52.087-0600 [WARN]  plugin.steampipe-plugin-stripe.plugin: [WARN]  stripe_customer.listProduct: params="&{{context.Background.WithValue(type context_key.contextKey, val <not Stringer>) <nil> [] {[]} 0xc00030c348 false <nil> <nil>} <nil> <nil> <nil> [] <nil> <nil> <nil>}"
2021-06-18T15:41:52.754-0600 [INFO]  hub: scan complete
2021-06-18T15:41:52.754-0600 [INFO]  hub: adding 13 rows to cache
2021-06-18 17:41:52.755 EDT [51203] LOG:  statement: COMMIT

Quals issues with multiple tables and `aws_iam_policy_simulator`

Describe the bug
The aws_iam_policy_simulator requires 3 Quals (principal_arn, resource_arn, action). Joining this table to two other tables (e.g. aws_iam_user.arn and aws_s3_bucket.arn) results in the following error message:

Error: 'List' call requires an '=' qual for all columns: principal_arn,action,resource_arn

Using a join for one qual and hard-coded values for the other two quals works, but applying multiple joins does not.

This works

select 
  u.name,
  s.action,
  s.decision
from
  aws_iam_policy_simulator s
  join aws_iam_user u on s.principal_arn = u.arn
where
  resource_arn = 'arn:aws:s3:::dmi-employee-data'
  and action = 's3:ListBucket';

And this works

select 
  b.name,
  s.action,
  s.decision
from
  aws_iam_policy_simulator s
  join aws_s3_bucket b on s.resource_arn = b.arn
where
  s.principal_arn = 'arn:aws:iam::899206412154:user/toby_flenderson'
  and b.name like 'dmi%'
  and s.action = 's3:ListBucket';

This does not work

select
  u.name as user,
  b.name as bucket,
  s.action,
  s.decision
from
  aws_iam_policy_simulator s
  join aws_iam_user u on s.principal_arn = u.arn
  join aws_s3_bucket b on s.resource_arn = b.arn
where
  b.name like 'dmi%'
  and s.action = 's3:ListBucket';

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

Plugin version (steampipe plugin list)
AWS: v0.12.0

To reproduce

Create an iam user
Create a bucket named dmi-test-bucket-xxxxx
Run the query

Expected behavior
The aws_iam_policy_simulator table should return a row result for each combination of IAM user and S3 Bucket

Quals issue with multiple column key in github plugin

I don't understand why the final query in this list doesn't work?

> select * from github_commit where repository_full_name = 'turbot/steampipe-plugin-aws' and sha = '7ae796c3e3b1aefbe60a5810ca8f971dbd05745f'
+-----------------------------+------------------------------------------+--------------+---------------------+--------------------------------------------------------------------------------------------------------------------+----------
| repository_full_name        | sha                                      | author_login | author_date         | comments_url                                                                                                       | commit   
+-----------------------------+------------------------------------------+--------------+---------------------+--------------------------------------------------------------------------------------------------------------------+----------
| turbot/steampipe-plugin-aws | 7ae796c3e3b1aefbe60a5810ca8f971dbd05745f | cbruno10     | 2021-04-15 21:09:43 | https://api.github.com/repos/turbot/steampipe-plugin-aws/commits/7ae796c3e3b1aefbe60a5810ca8f971dbd05745f/comments | {"author"
|                             |                                          |              |                     |                                                                                                                    |          
+-----------------------------+------------------------------------------+--------------+---------------------+--------------------------------------------------------------------------------------------------------------------+----------
> 
> select * from github_commit where repository_full_name = 'turbot/steampipe-plugin-aws' and sha in ('7ae796c3e3b1aefbe60a5810ca8f971dbd05745f')
+-----------------------------+------------------------------------------+--------------+---------------------+--------------------------------------------------------------------------------------------------------------------+----------
| repository_full_name        | sha                                      | author_login | author_date         | comments_url                                                                                                       | commit   
+-----------------------------+------------------------------------------+--------------+---------------------+--------------------------------------------------------------------------------------------------------------------+----------
| turbot/steampipe-plugin-aws | 7ae796c3e3b1aefbe60a5810ca8f971dbd05745f | cbruno10     | 2021-04-15 21:09:43 | https://api.github.com/repos/turbot/steampipe-plugin-aws/commits/7ae796c3e3b1aefbe60a5810ca8f971dbd05745f/comments | {"author"
|                             |                                          |              |                     |                                                                                                                    |          
+-----------------------------+------------------------------------------+--------------+---------------------+--------------------------------------------------------------------------------------------------------------------+----------
> 
> select * from github_commit where repository_full_name = 'turbot/steampipe-plugin-aws' and sha in (select '7ae796c3e3b1aefbe60a5810ca8f971dbd05745f')
+-----------------------------+------------------------------------------+--------------+---------------------+--------------------------------------------------------------------------------------------------------------------+----------
| repository_full_name        | sha                                      | author_login | author_date         | comments_url                                                                                                       | commit   
+-----------------------------+------------------------------------------+--------------+---------------------+--------------------------------------------------------------------------------------------------------------------+----------
| turbot/steampipe-plugin-aws | 7ae796c3e3b1aefbe60a5810ca8f971dbd05745f | cbruno10     | 2021-04-15 21:09:43 | https://api.github.com/repos/turbot/steampipe-plugin-aws/commits/7ae796c3e3b1aefbe60a5810ca8f971dbd05745f/comments | {"author"
|                             |                                          |              |                     |                                                                                                                    |          
+-----------------------------+------------------------------------------+--------------+---------------------+--------------------------------------------------------------------------------------------------------------------+----------
> 
> select * from github_commit where repository_full_name = 'turbot/steampipe-plugin-aws' and sha = any(select '7ae796c3e3b1aefbe60a5810ca8f971dbd05745f')
+-----------------------------+------------------------------------------+--------------+---------------------+--------------------------------------------------------------------------------------------------------------------+----------
| repository_full_name        | sha                                      | author_login | author_date         | comments_url                                                                                                       | commit   
+-----------------------------+------------------------------------------+--------------+---------------------+--------------------------------------------------------------------------------------------------------------------+----------
| turbot/steampipe-plugin-aws | 7ae796c3e3b1aefbe60a5810ca8f971dbd05745f | cbruno10     | 2021-04-15 21:09:43 | https://api.github.com/repos/turbot/steampipe-plugin-aws/commits/7ae796c3e3b1aefbe60a5810ca8f971dbd05745f/comments | {"author"
|                             |                                          |              |                     |                                                                                                                    |          
+-----------------------------+------------------------------------------+--------------+---------------------+--------------------------------------------------------------------------------------------------------------------+----------
> 
> select * from github_commit where repository_full_name = 'turbot/steampipe-plugin-aws' and sha = any(array['7ae796c3e3b1aefbe60a5810ca8f971dbd05745f'])
+-----------------------------+------------------------------------------+--------------+---------------------+--------------------------------------------------------------------------------------------------------------------+----------
| repository_full_name        | sha                                      | author_login | author_date         | comments_url                                                                                                       | commit   
+-----------------------------+------------------------------------------+--------------+---------------------+--------------------------------------------------------------------------------------------------------------------+----------
| turbot/steampipe-plugin-aws | 7ae796c3e3b1aefbe60a5810ca8f971dbd05745f | cbruno10     | 2021-04-15 21:09:43 | https://api.github.com/repos/turbot/steampipe-plugin-aws/commits/7ae796c3e3b1aefbe60a5810ca8f971dbd05745f/comments | {"author"
|                             |                                          |              |                     |                                                                                                                    |          
+-----------------------------+------------------------------------------+--------------+---------------------+--------------------------------------------------------------------------------------------------------------------+----------
> 
> select * from github_commit where repository_full_name = 'turbot/steampipe-plugin-aws' and sha = (select '7ae796c3e3b1aefbe60a5810ca8f971dbd05745f')
+----------------------+-----+--------------+-------------+--------------+--------+-----------------+----------------+-------+----------+---------+---------+---------+-------+-----+----------+
| repository_full_name | sha | author_login | author_date | comments_url | commit | committer_login | committer_date | files | html_url | message | node_id | parents | stats | url | verified |
+----------------------+-----+--------------+-------------+--------------+--------+-----------------+----------------+-------+----------+---------+---------+---------+-------+-----+----------+
+----------------------+-----+--------------+-------------+--------------+--------+-----------------+----------------+-------+----------+---------+---------+---------+-------+-----+----------+
>

Implement lazy loading of connections and add support for active scan cancellations

Add support for query cancellation

SQL `OR` not working for key columns

When querying with an OR in the where clause, the resultset comes back empty:

steampipe query "select * from whois_domain where domain='steampipe.io' or domain='turbot.com'"
+--------+-----------+-----------------+------------------+--------------+--------+--------------+---------+--------------+-----------
| domain | domain_id | domain_punycode | domain_extension | whois_server | status | name_servers | dns_sec | created_date | updated_da
+--------+-----------+-----------------+------------------+--------------+--------+--------------+---------+--------------+-----------
+--------+-----------+-----------------+------------------+--------------+--------+--------------+---------+--------------+-----------

However, the same query, when restructured with IN works just fine:

steampipe query "select * from whois_domain where domain in ('steampipe.io','turbot.com')" 
+--------------+---------------------------+-----------------+------------------+----------------------------+------------------------
| domain       | domain_id                 | domain_punycode | domain_extension | whois_server               | status                 
+--------------+---------------------------+-----------------+------------------+----------------------------+------------------------
| steampipe.io | D503300001187474055-LRMS  | steampipe.io    | io               | whois.gandi.net            | ["clienttransferprohibi
| turbot.com   | 168144065_DOMAIN_COM-VRSN | turbot.com      | com              | whois.registrar.amazon.com | ["clienttransferprohibi
+--------------+---------------------------+-----------------+------------------+----------------------------+------------------------

hub should instantiate a single plugin for all connections and the connection management should be done within the plugin (via sdk provided functionality)

Add caching for query results

cache query results for lifetime of service, or 10(?) minutes, whichever is shorter

Can not join from aws_vpc_security_group_rules to ipstack_ip

Describe the bug
Attempting to join aws_vpc_security_group_rules.cidr_ip to ipstack_ip.ip fails with a quals error: Error: 'List' call requires an '=' qual for column: ip error

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

To reproduce

  select
    i.ip,
    i.region_code,
    i.country_code
  from 
    aws_vpc_security_group_rule r,
    ipstack_ip i
  where
    i.ip = r.cidr_ip
    and masklen(r.cidr_ip) >= 16

select
    ip,
    region_code,
    country_code
  from
    ipstack_ip
  where
    ip in (
      select
        cidr_ip
      from
        aws_vpc_security_group_rule
      where
        masklen(cidr_ip) >= 32
    )

Expected behavior
Query should return a result e.g.:

select 
  city, 
  region_code,
  country_code
from 
  ipstack_ip 
where 
  ip in (inet '8.8.8.8/32', inet '1.1.1.1/32')
+---------------+-------------+--------------+
| city          | region_code | country_code |
+---------------+-------------+--------------+
| Mountain View | CA          | US           |
| Sydney        | NSW         | AU           |
+---------------+-------------+--------------+

Additional context
Add any other context about the problem here.

Add tests

Include unit test, as well as integration tests on all supported platforms (currently darwin_amd64, limux_amd64).

The tests should run on commit/merge to master
The test should be integrated to run in the build/publish job

order by cidr_block causes a hang on the query?

This works great:

> select vpc_id, cidr_block, region from aws_vpc;
+--------------+---------------+-----------+
| vpc_id       | cidr_block    | region    |
+--------------+---------------+-----------+
| vpc-af92a1d4 | 10.85.0.0/16  | us-east-1 |
| vpc-a7025cc0 | 172.30.0.0/24 | us-east-1 |
| vpc-3cd69054 | 10.84.0.0/16  | us-east-2 |
| vpc-12947a7b | 172.31.0.0/16 | eu-west-2 |
| vpc-87389aef | 10.84.0.0/16  | eu-west-2 |
| vpc-7032c619 | 172.31.0.0/16 | us-east-2 |
| vpc-82fc47e7 | 172.31.0.0/16 | us-east-1 |
| vpc-abb765ce | 172.31.0.0/16 | us-west-2 |
| vpc-0434cd61 | 172.31.0.0/16 | us-west-1 |
| vpc-fb848d9e | 172.31.0.0/16 | eu-west-1 |
| vpc-6f2d5c09 | 10.85.0.0/16  | eu-west-1 |
+--------------+---------------+-----------+

But it hangs once I add the order by?

> select vpc_id, cidr_block, region from aws_vpc order by cidr_block;
⠦ Loading results... 
⠇ Loading results... 
⠹ Loading results... 
⠼ Loading results... 
⠋ Loading results... 
⠹ Loading results... 
⠸ Loading results... 
⠏ Loading results...

FYI, order by vpc_id does work:

> select vpc_id, cidr_block, region from aws_vpc order by vpc_id;
+--------------+---------------+-----------+
| vpc_id       | cidr_block    | region    |
+--------------+---------------+-----------+
| vpc-0434cd61 | 172.31.0.0/16 | us-west-1 |
| vpc-12947a7b | 172.31.0.0/16 | eu-west-2 |
| vpc-3cd69054 | 10.84.0.0/16  | us-east-2 |
| vpc-6f2d5c09 | 10.85.0.0/16  | eu-west-1 |
| vpc-7032c619 | 172.31.0.0/16 | us-east-2 |
| vpc-82fc47e7 | 172.31.0.0/16 | us-east-1 |
| vpc-87389aef | 10.84.0.0/16  | eu-west-2 |
| vpc-a7025cc0 | 172.30.0.0/24 | us-east-1 |
| vpc-abb765ce | 172.31.0.0/16 | us-west-2 |
| vpc-af92a1d4 | 10.85.0.0/16  | us-east-1 |
| vpc-fb848d9e | 172.31.0.0/16 | eu-west-1 |
+--------------+---------------+-----------+

Only use key columns quals and optional key columns quals for cache key

v0.0.23

Different results when re-running same query with an extra column filter in the join statement

Running a query with two columns in the join (only 1 is a key qual) can result in different rows failing the join. Specifically, the qualifier data seems to come through as a literal ? char.

Here is the query:

  select
    r.full_name, b.name, b.protected
  from
    github_my_repository as r
    left join github_branch as b on r.full_name = b.repository_full_name and b.name in ('main', 'master')
  where
    r.full_name like 'turbot/steampipe-plugin-%'

The first run returns:

+--------------------------------------+--------+-----------+
| full_name                            | name   | protected |
+--------------------------------------+--------+-----------+
| turbot/steampipe-plugin-digitalocean | main   | true      |
| turbot/steampipe-plugin-aws          | main   | true      |
| turbot/steampipe-plugin-azure        | main   | true      |
| turbot/steampipe-plugin-alicloud     | main   | true      |
| turbot/steampipe-plugin-equinix      | main   | true      |
| turbot/steampipe-plugin-chaos        | main   | true      |
| turbot/steampipe-plugin-finance      | main   | true      |
| turbot/steampipe-plugin-cloudflare   | main   | true      |
| turbot/steampipe-plugin-gcp          | main   | true      |
| turbot/steampipe-plugin-github       | main   | true      |
| turbot/steampipe-plugin-hackernews   | main   | true      |
| turbot/steampipe-plugin-ipstack      | main   | true      |
| turbot/steampipe-plugin-kubernetes   | main   | true      |
| turbot/steampipe-plugin-net          | main   | true      |
| turbot/steampipe-plugin-oci          | main   | true      |
| turbot/steampipe-plugin-rss          | main   | true      |
| turbot/steampipe-plugin-sdk          | main   | true      |
| turbot/steampipe-plugin-whois        | main   | true      |
| turbot/steampipe-plugin-twitter      | main   | true      |
| turbot/steampipe-plugin-updown       | <null> | <null>    |
| turbot/steampipe-plugin-slack        | main   | true      |
| turbot/steampipe-plugin-steampipe    | main   | true      |
| turbot/steampipe-plugin-zendesk      | main   | true      |
| turbot/steampipe-plugin-turbot       | main   | true      |
| turbot/steampipe-plugin-shodan       | main   | true      |
| turbot/steampipe-plugin-zoom         | main   | true      |
+--------------------------------------+--------+-----------+

And the second run returns different results, failing <null> on different rows in the join:

+--------------------------------------+--------+-----------+
| full_name                            | name   | protected |
+--------------------------------------+--------+-----------+
| turbot/steampipe-plugin-digitalocean | <null> | <null>    |
| turbot/steampipe-plugin-aws          | main   | true      |
| turbot/steampipe-plugin-azure        | main   | true      |
| turbot/steampipe-plugin-alicloud     | main   | true      |
| turbot/steampipe-plugin-equinix      | <null> | <null>    |
| turbot/steampipe-plugin-chaos        | <null> | <null>    |
| turbot/steampipe-plugin-finance      | <null> | <null>    |
| turbot/steampipe-plugin-cloudflare   | main   | true      |
| turbot/steampipe-plugin-gcp          | main   | true      |
| turbot/steampipe-plugin-github       | main   | true      |
| turbot/steampipe-plugin-hackernews   | main   | true      |
| turbot/steampipe-plugin-ipstack      | main   | true      |
| turbot/steampipe-plugin-kubernetes   | main   | true      |
| turbot/steampipe-plugin-net          | main   | true      |
| turbot/steampipe-plugin-oci          | main   | true      |
| turbot/steampipe-plugin-rss          | main   | true      |
| turbot/steampipe-plugin-sdk          | main   | true      |
| turbot/steampipe-plugin-whois        | main   | true      |
| turbot/steampipe-plugin-twitter      | main   | true      |
| turbot/steampipe-plugin-updown       | main   | true      |
| turbot/steampipe-plugin-slack        | main   | true      |
| turbot/steampipe-plugin-steampipe    | main   | true      |
| turbot/steampipe-plugin-zendesk      | <null> | <null>    |
| turbot/steampipe-plugin-turbot       | <null> | <null>    |
| turbot/steampipe-plugin-shodan       | main   | true      |
| turbot/steampipe-plugin-zoom         | main   | true      |
+--------------------------------------+--------+-----------+

Removing the second b.name qualifier from the join changes the result, but makes it work:

  select
    r.full_name, b.name, b.protected
  from
    github_my_repository as r
    left join github_branch as b on r.full_name = b.repository_full_name
  where
    r.full_name like 'turbot/steampipe-plugin-%'

Here are redacted results (they are much bigger) ... but there are no null rows:

+--------------------------------------+------------------------------+-----------+
| full_name                            | name                         | protected |
+--------------------------------------+------------------------------+-----------+
| turbot/steampipe-plugin-gcp          | issue-94                     | false     |
| turbot/steampipe-plugin-gcp          | gcp_kms_key                  | false     |
| turbot/steampipe-plugin-gcp          | issue-207                    | false     |
| turbot/steampipe-plugin-gcp          | issue-189                    | false     |
| turbot/steampipe-plugin-gcp          | gcp-v0.3.0-rc.1              | false     |
| turbot/steampipe-plugin-gcp          | issue-209                    | false     |
| turbot/steampipe-plugin-gcp          | main                         | true      |
| turbot/steampipe-plugin-gcp          | issue-124                    | false     |

select 
    eni.network_interface_id,
    eni.description,
    eni.association_public_ip,
    eni.private_ip_address,
    ipstack_ip.*
from 
    aws_ec2_network_interface as eni,
    ipstack_ip
where 
        ipstack_ip.ip = eni.private_ip_address

Note that doing EXPLAIN on the query ALSO crashes.

FYI - this is when using latest main in SDK:

module github.com/turbot/steampipe-plugin-ipstack

go 1.15

require (
	github.com/qioalice/ipstack v1.0.1
	github.com/turbot/steampipe-plugin-sdk v0.2.2
)

replace github.com/turbot/steampipe-plugin-sdk => github.com/turbot/steampipe-plugin-sdk v0.2.0-rc.1.0.20210310144957-7f411b3b94fe

turbot / steampipe-postgres-fdw Goto Github PK

steampipe-postgres-fdw's Introduction

Steampipe Postgres FDW

Overview

Getting Started

Developing

Building the FDW for Steampipe

Building the FDW as a standalone extension

Example

Open Source & Contributing

steampipe-postgres-fdw's People

Contributors

Stargazers

Watchers

Forkers

steampipe-postgres-fdw's Issues

Recommend Projects

Recommend Topics

Recommend Org