Query your Entire AWS Org using Steampipe

Recently decided to deep dive into using Steampipe.io CLI.

The application requires a set of connection definitions per AWS account to be present in its config file (found in ~/.steampipe/config/aws.spc) and fortunately comes with an aggregator that lets you query multiple accounts in one query.

This is actually a pretty damn cool tool I must admit. I was able to generate a dashboard for the top 10 Security issues across two AWS organisations fairly simply using this tool. So massive shout out to the team writing steampipe! Thanks heaps!

Steampipe relies on the AWS CLI config for authentication to AWS and thus you have two config files for two CLIs you need to worry about having in alignment.

In this blog I will show case how I used the config creator to make steampipe work across multiple accounts using the permission sets assigned to me by the AWS Identity Access Centre in my Org. Again, this example is only going to focus on the AWS Identity Access Center (formerly known as AWS SSO) federated login approach, as that is what both of my orgs have setup.

Also worth noting is that I’ve had to update my python script to output steampipe credentials 🙂 Any bugs please let me know.

Primary tool that helped ease my pain: https://github.com/stormlrd/aws-sso-cli-config-creator

git clone https://github.com/stormlrd/aws-sso-cli-config-creator
cd aws-sso-cli-config-creator
vi source_config

Default Looks Like This:
[profile primary]
sso_start_url = https://my-sso-portal.awsapps.com/start
sso_region = us-east-1
sso_account_id = 123456789011
sso_role_name = ReadAccess
region = us-west-2
output = json

I edited the source_config file to update the sso_start_url, sso_region, sso_account_id, sso_role_name with the correct information.

I then need to append this to my existing AWS CLI config file (or make it fresh up to you):

cat source_config >> ~/.aws/config

Then using AWS CLI undertake a federated login using the new profile; called primary.

aws sso login --profile primary

This will cause the following output and fire up a Browser (though in my case as I was using Ubuntu on Windows I had to CTL+Click the link and copy and paste the code):

Click Authorize and Continue:

Click Allow and you should see this:

You should now have a set of cached credentials under ~./aws/sso/cache

With python3 correctly installed I then execute the aws_sso_cli_config_creator python script from the aws_sso_cli_config_creator folder:

python3 ./create_config.py

You should get the following with a prompt to select a cached credential file. I did this because I was getting multiples in the folder so just to be safe I’m requesting you select one.

you can now review the config files and remove any roles / permission sets you don’t want. This can be where things are a little messy if you have lots of permission sets. For example I have two permission sets on every account in my Org:

• AWSAdministratorAccess
• AWSReadOnlyAccess

So if I want steampipe to just use the AWSReadOnlyAccess role I will need to modify the sp_conf.json file to delete connections and remove them from the aggregator at the bottom of the file.

Once you’re happy just append these to the configs or copy them over the top, up to you:

cat config.new >> ~/.aws/config
cat sp_conf.json >> ~/.steampipe/config/aws.spc

Now you can fire up steampipe, modify your search path to be all_aws which is the aggregator created in the sp_conf.json from the script and the run a query against all the accounts in your org!

steampipe query

once the tool has loaded:
.search_path all_aws
select * from aws_account

Should give you a list of all your accounts!

If you get “Error: relation “aws_account” does not exist (SQLSTATE 42P01)” then it’s likely your search path is stuffed.

In order to make this two orgs friendly all I did was follow the same process for generating the config files and made one aggregator for all the credentials in the steampipe config.

My second org need a profile of its own in the aws cli config so I called that primary2 profile for the SSO.

Then I logged into both orgs at the same time using the AWScli, and ran the steampipe query when doing a Select * From aws_account it returned the results for both orgs! Nifty!

You can also get the same crawling for 2 orgs when you use steampipe dashboard for the nice web dashboards it outputs. v cool! :)_

Hope this helps you. Highly recommend you look into using steampipe for audit / assurance checks in realtime across your estate. It’s not perfect but its damn handy out of the box!

If you want to ask me any questions you can find me in the AWS User Group Aotearoa slack or discord.