Configuring Steampipe Postgres FDW

To use the Steampipe Postgres FDW, you first have to create the foreign server and import the foreign schema.

Login to Postgres as a superuser and create the extension:

DROP EXTENSION IF EXISTS steampipe_postgres_aws CASCADE;
CREATE EXTENSION IF NOT EXISTS steampipe_postgres_aws;

If you want, you can verify the extension was created:

select * from pg_extension

Now create a foreign server. Many plugins include a default configuration that may "just work", but more often you will want to explicitly set the configuration by passing the config option to specify the plugin-specific configuration:

DROP SERVER IF EXISTS steampipe_aws_01;
CREATE SERVER steampipe_aws_01 FOREIGN DATA WRAPPER steampipe_postgres_aws OPTIONS (config 'profile = "my_aws_profile"');

Note: Many plugins use environment variables or configuration files from the user's $HOME directory for some configuration options. Be aware that the user context is whichever user Postgres is running as!

The config option takes an HCL string with the plugin connection arguments. These arguments vary per plugin. You can view the available options and syntax for the plugin in the Steampipe hub.

Note that HCL is newline-sensitive. To specify multiple arguments, you must include the line break inside the string:

CREATE SERVER steampipe_aws_01 FOREIGN DATA WRAPPER steampipe_postgres_aws OPTIONS (config 'access_key="AKIA4YFAKEKEYT99999"
  secret_key="A32As+zuuBFThisIsAFakeSecretNb77HSLmcB"
  regions = ["*"]');

If you want, you can verify the foreign server was created:

select * from information_schema.foreign_servers
select * from information_schema.foreign_server_options

Now that the server has been set up, create a schema and import the foreign tables:

DROP SCHEMA IF EXISTS aws_01 CASCADE;
CREATE SCHEMA aws_01;
COMMENT ON SCHEMA aws_01 IS 'steampipe aws fdw';
IMPORT FOREIGN SCHEMA aws_01 FROM SERVER steampipe_aws_01 INTO aws_01;

You can query the information schema to see the foreign tables that have been added to your schema:

select
  foreign_table_name
from
  information_schema.foreign_tables
where
  foreign_table_schema = 'aws_01'

--------------------------------------------------------------+
| foreign_table_name                                           |
|--------------------------------------------------------------|
| aws_wellarchitected_workload                                 |
| aws_guardduty_finding                                        |
| aws_vpc_verified_access_instance                             |
| aws_cloudformation_stack_set                                 |
| aws_route53_resolver_rule                                    |
| aws_securityhub_insight                                      |
| aws_securityhub_member                                       |
...

Your FDW is now configured! You should now be able to run queries!

select * from aws_01.aws_account;

You can install as many Steampipe Postgres FDWs as you like. The installation process is the same for all plugins, though the config arguments vary.

Multiple Foreign Servers

You can create multiple foreign servers for the same extension (plugin type). For instance, you can add a foreign server and schema for each of your AWS accounts.

Because the configuration is set on the foreign server, you need to create a new foreign server for each distinct instance. You will re-use the extension that you created for the first AWS foreign server:

DROP SERVER IF EXISTS steampipe_aws_02;
CREATE SERVER steampipe_aws_02 FOREIGN DATA WRAPPER steampipe_postgres_aws OPTIONS (config 'profile = "my_aws_profile_2"');

Now that the server has been set up, create a schema and import the foreign tables:

DROP SCHEMA IF EXISTS aws_02 CASCADE;
CREATE SCHEMA aws_02;
COMMENT ON SCHEMA aws_02 IS 'steampipe aws fdw - aws_02';
IMPORT FOREIGN SCHEMA aws_02 FROM SERVER steampipe_aws_02 INTO aws_02;

You can now query the tables in your new schema:

select * from aws_02.aws_account

You can even create views to aggregate them:

CREATE VIEW aws_account AS
  select * from aws_01.aws_account 
  union all select * from aws_02.aws_account

select * from aws_account

Editing the Configuration

If desired, you can change the foreign server configuration by editing the config option:

ALTER SERVER steampipe_aws_01 OPTIONS (SET config 'profile = "my_new_profile"
 regions = ["*"]');

Removing the configuration

You can remove the FDW configuration by dropping the relevant objects:

DROP SCHEMA IF EXISTS aws01 CASCADE;
DROP SERVER IF EXISTS steampipe_aws_01;
DROP EXTENSION IF EXISTS steampipe_postgres_aws CASCADE;

Caching

By default, query results are cached for 5 minutes. You can change the duration with the STEAMPIPE_CACHE_MAX_TTL:

export STEAMPIPE_CACHE_MAX_TTL=600  # 10 minutes

or disable caching with the STEAMPIPE_CACHE:

export STEAMPIPE_CACHE=false

Logging

You can set the logging level with the STEAMPIPE_LOG_LEVEL environment variable. By default, the log level is set to warn. Logs are written to the Postgres database logs.

export STEAMPIPE_LOG_LEVEL=DEBUG