Steampipe is an fantastic tool from Turbot. Steampipe allows you to easily ask questions about your Cloud and SaaS Infrastructure. Whether its AWS, GCP or even Slack. You can ask anything you like from these platforms; users, assets, identity, access, logs and even deployed assets. Steampipe empowers you to understand every part of your Cloud infrastructure.
Steampipe uses an SQL (Structured Query Language) abstraction to give granular access to your system. Effectively allowing you to query your cloud providers and SaaS applications, just like you would a database.
SELECT title, instance_id, instance_state, instance_status, subnet_id, vpc_id, public_dns_name, public_ip_address, placement_availability_zone, launch_time
FROM aws_ec2_instance
ORDER BY launch_time DESC
LIMIT 100;Just like that you can retrieve the most recently launched EC2 instances within your AWS environment. However, Steampipe’s power really comes from its ability to join tables of abstracted data. Filter it and transform it into anything you like within the SQLite’s syntax. Its really powerful. A full list of Steampipe’s schema is available here.
How do I get started?
You can download Steampipe from https://steampipe.io/downloads. Once installed (or you can run it directly from the command line) you’ll need to first install some plugins. These plugins will allow Steampipe to interact with a variety of cloud services and retrieve the data required by the query. A full list of the available plugins can be found here.
steampipe plugin install awsYou can also re-run the command to update already installed plugins. Once installed, the AWS plugin will automatically use the default credentials stored in ~/.aws/credentials . However, you can configure each plugin with its own configuration as per the plugin spec which can be found here.
nano ~/.steampipe/config/aws.spcThe above configuration file should already exist. Its highly recommended that you create a new IAM user with the role ReadOnlyAccess.
Then storing the new API credentials within ~/.aws/credentials like so:
[zercurity_ro]
aws_access_key_id=AZA...
aws_secret_access_key==btD...If there are already credentials in this file simply append the new AWS API keys and then update your ~/.steampipe/config/aws.spc with the new profile like so:
connection "aws" {
plugin = "aws"
region = ["eu-west-1"]
profile = "zercurity_ro"
}You’ll need to provide a list of the regions you’d like your queries to be run against. Once done you’ll be able to run the following query on your AWS environment:
steampipe query "select instance_id, title, instance_state FROM aws_ec2_instance ORDER BY launch_time DESC LIMIT 10;"+---------------------+----------+----------------+
| instance_id | title | instance_state |
+---------------------+----------+----------------+
| i-1234567890abcdef0 | test | running |
+---------------------+----------+----------------+
If nothing is returned double check your aws.spc file to make sure the correct AWS regions are specified.
Steampipe Mods
A common use case for Steampipe is compliance. Fortunately, Steampipe provides thousands of pre-written queries in the form of Mods. Which themselves can contain a collection pre-defined compliance benchmarks such as CIS, HIPPA, NIST, PCI and SOC to name a few. To get started you’ll need to initalise the mod configuration file.
steampipe mod initYou can then install the AWS compliance mod. A full list of mods are available here.
steampipe mod install github.com/turbot/steampipe-mod-aws-complianceOnce your mod is installed you can then run your compliance benchmark. In order to figure out the resource name. Check the compliance documentation here. The documentation will provide you with the usage query.
steampipe check aws_compliance.benchmark.cis_v140The command will take a moment to process all the bundled controls. You can also add the --dry-run argument to check all the controls that will be queried before the queries are run. There is the additional where parameter to filter specific queries.
Steampipe also offers a web based dashboard of the installed mods. Once you visit a mod Steampipe will automatically start scheduling queries in the background.
steampipe dashboard
Steampipe internals
Hows does all this work then? At its core the Steampipe CLI tool is a wrapper around an embedded PostgreSQL database which is started and stopped with the tool. The steampipe application manages the PostgreSQL service and handles the installation and configuration of mods, plugins.
The primary element in the inner working of Steampipe is the PostgreSQL foreign data wrapper or FDW. Which is an extension for PostgreSQL. Connects to all the various plugins via GRPC. Loading in the defined schemas and making PostgreSQL aware of what virtual tables that are available to it. Once a query is executed. The query planner requests the relevant data, which the FDW handles and its translated into the various API calls for each cloud service. The data is then sent back to be processed and presented back as the final result.
Steampipe service
You may want to build Steampipe into your own service and require API access. Whilst Steampipe dosen’t provide a REST API. It will give you direct access to the PostgreSQL backend to query directly. Allowing you to build any application around it.
steampipe service startYou’ll then get back the PostgreSQL database connection endpoint. Which you can then connect to directly like so:
psql -U steampipe -h localhost -p 9193steampipe=> SELECT instance_id FROM aws_ec2_instance; instance_id
---------------------
i-1234567890abcdef0
(1 row)
Or use a GUI tool such as DataGrip or even connect to directly via the programming language of your choice.
Want to schedule and automate queries via the web?
Zercurity provides a SaaS platform to query all of your cloud services from one single interface. It’ll also automatically enrich your results for you. It’s free to signup and you can query as many providers as you like.
