Instantly query AWS with SQL in CloudShell

AWS CloudShell makes it easy to spin up a terminal right in your AWS account. CloudShell comes preinstalled with the AWS CLI and your credentials within the applicable account. Since CloudShell is just like any other terminal, you have the ability to bootstrap other tools without the need to spin up an instance. I personally find CloudShell useful for ad hoc actions I need to take in AWS CLI or with other open source tools.

As a lead on our open source tool Steampipe.io, I am a heavy user of the Steampipe CLI often running Steampipe on my local machine to run aggregated queries & reports across an AWS multi-account environment and other cloud accounts. When I am working with clients in their AWS accounts, I find it easy to work within their account structure vs setting up a local profile. In this case I find AWS CloudShell a quick win for being a guest in another account to bootstrap my tools in a temporary environment under their control.

In this post will walk through how to install Steampipe in your AWS CloudShell.

Steampipe Background

With Steampipe, you can instantly query your AWS APIs using SQL right in your terminal.

select
  title,
  create_date,
  mfa_enabled
from
  aws_iam_user

+-----------------+---------------------+-------------+
| title           | create_date         | mfa_enabled |
+-----------------+---------------------+-------------+
| pam_beesly      | 2005-03-24 21:30:00 | false       |
| creed_bratton   | 2005-03-24 21:30:00 | true        |
| stanley_hudson  | 2005-03-24 21:30:00 | false       |
| michael_scott   | 2005-03-24 21:30:00 | false       |
| dwight_schrute  | 2005-03-24 21:30:00 | true        |
+-----------------+---------------------+-------------+

It takes just a few seconds to install Steampipe itself, along with the AWS plugin that maps AWS API calls to Postgres tables.

Steampipe will resolve your region and credentials using the same mechanism as the AWS CLI (AWS environment variables, default profile, etc). Note: more can be extended for querying multiple accounts, regions, configuring credentials from your AWS Profiles, SSO, aws-vault etc.

AWS CloudShell + Steampipe

Alternatively you can use CloudShell to install Steampipe directly in your AWS Account. With CloudShell your credentials you use to sign into the AWS console are already forwarded to CloudShell. Since Steampipe will default to your local AWS credentials, from a cold start, you're querying AWS APIs with SQL in a matter of seconds.

Install Steampipe in CloudShell

Go to your AWS CloudShell, install Steampipe:

$ sudo /bin/sh -c "$(curl -fsSL https://raw.githubusercontent.com/turbot/steampipe/main/install.sh)"
...
Installing
Applying necessary permissions
Steampipe was installed successfully to /usr/local/bin/steampipe

Install the AWS plugin

Now with Steampipe installed, you can install the AWS plugin:

$ steampipe plugin install aws

Installed plugin: aws v0.57.0
Documentation:    https://hub.steampipe.io/plugins/turbot/aws

Run a SQL query!

Now you are ready to run a SQL query. Since CloudShell already has the credentials in place, you can simply get started:

$ steampipe query
Welcome to Steampipe v0.13.6
For more information, type .help
> select * from aws_s3_bucket
+--------------------------------------+
| name                                 |
+--------------------------------------+
| jon-turbot-test-bucket-01            |
| cf-templates-1s5tzrjxv4j52-us-west-1 |
+--------------------------------------+

CloudShell takes full advantage of the Steampipe CLI components so you can inspect tables, configure environment variables, visualize syntax highlighting, select autofill suggestions, etc.

Final Thoughts

I enjoy using AWS CloudShell for ad hoc actions with AWS CLI and Steampipe within a specific AWS Account. Interested to learn from others on how you use CloudShell in your environment; what are your use cases and how often do you use CloudShell?