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:

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

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:

Important

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:

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

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

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

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

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:

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

You can now query the tables in your new schema:

You can even create views to aggregate them:

Editing the Configuration

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

Removing the configuration

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

Caching

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

or disable caching with the STEAMPIPE_CACHE:

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.