Steampipe for Security and Compliance | GCP Security and Compliance Dashboards

7 min readOct 16, 2022

Steampipe — An API Query Swiss Army Knife

Steampipe is without a doubt one of the best open source tools for security compliance, operations and API orchestration. This project has evolved a lot in the past few years and I am one of the recent open source contributors to Steampipe’s GCP Plugin.

It is no longer just a cloud security and compliance tool but you can do much more with it, Ah! Threat Hunting :-)

Don’t miss reading the complete article, I have covered tons of things that you can do with just few keywords and clicks in Steampipe.

Doesn’t matter if you are a Red or Blue Teamer. You’re gonna love it!

Let’s discuss a little about Steampipe and its components before we dive in.

What is Steampipe.?

A simple to install tool that exposes the 3rd party APIs as a high-performance relational database that can be queried using SQL queries.

Steampipe Components:

Plugins: It enables connections for integrations like GCP, AWS, Github, Cloudflare, AbuseIPDB, Hacker News and describes the blueprint of the data.

Steampipe have tons of plugins that can be explored using https://hub.steampipe.io/plugins

2. Mods: Integrations on steroids with OOB content like Dashboards, Reports, Controls and Benchmarks. Mods are built using Hashicorp’s HCL query.

For example: After GCP Plugin setup, you can enable GCP mods like GCP Compliance.

This mode generates CIS benchmark report of your GCP environment in seconds.

Are you using CSPM just for this. :-P

3. Tables: It is part of plugins but important to know the tables right. As this will enable you to optimize your queries accordingly.

Steampipe Architecture:

Steampipe Foreign Data Wrapper (FDW) that is based on Postgres foreign data wrapper that is used here to present the external data as a standardized database table.

Steampipe FDW lets the Plugins act as a middleware to pull the data that is then standardised by FDW via gRPC.

You can query the APIs in two ways.

Via Steampipe CLI queries: $ steampipe query "<syntax>”
Run backend queries in the form of Dashboards & reports: $ steampipe dashboard

Steampipe Installation:

Steampipe can be used both as CLI and as Docker container. So easy to implement but need to decide on it’s implementation.

In the below steps we will download the installation binary, will install it into /usr/local/bin. It will also create a home directory .steampipe with all the required libs and configs.

Download “Steampipe” binary.

$ sudo /bin/sh -c “$(curl -fsSL https://raw.githubusercontent.com/turbot/steampipe/main/install.sh)"

2. First plugin installation: $ steampipe plugin install steampipe

Install and Configure GCP Plugin:

Configure credentials in Steampipe’s specification file:

Create a Service Account in GCP with view rights, download key file in json format and move it under the specified path on your host where Steampipe is configured.

2. Make required changed to GCP credentials file as per following:

$ sudo vi ~/.steampipe/config/gcp.spc

Add Project = <Project-ID> and credentials = <downloaded-GCP-Key>

`Project-ID and Key addition to gcp.spc`

You can search and install the required Plugins, as per Steampipe’s plugin section.

View installed plugins as per below:

$ steampipe plugin list

View Steampipe live connections:

$ steampipe query

$ .connections

View available Tables in your Steampipe configuration: (Obviously after plugin setup)

$ steampipe query

$ .tables

To get information about particular table:

select * from <table_name>

Get Metadata(columns) or key fields for any Steampipe Plugin Tables:

$ steampipe query

$ .inspect <table_name>

Steampipe auto-complete and recommendations: (Use Tab and UP or Down arrow to select the option)

Recommendation snippet from GCP Cloud instance: (Use .help option as well)

To come of query mode: Type $ .quit

By now we have already setup GCP Plugin, so lets try few queries.

Audit GCP’s Project service accounts:

$ steampipe query "select * from gcp_service_account"

Audit GCP Instances via Steampipe query:

$ steampipe query$ select zone_name, count(*) from gcp_compute_instance group by zone_name order by count desc;

Take the output of SQL query and save export it to .json file.

$ steampipe query “select
 zone_name,
 count(*)
from
 gcp_compute_instance
group by
 zone_name
order by
 count desc;” — output json > instance_Gcp_audit.json

Verify export of file and cat

GCP Mods: Check on few amazing GCP mods for CIS Benchmarks, GCP Insights and check for labelling standards across your GCP infra.

Check it under Stampipe’s Mod page.

Let’s configure few GCP Steampipe mods:

Installation of GCP Insights Mod:

1. $ steampipe plugin update gcp2. $ git clone https://github.com/turbot/steampipe-mod-gcp-insights3. Go to mod folder $cd steampipe-mod-gcp-insights4. Start server: $  steampipe dashboard5. You can also run for all checks and report generation $ steampipe check all

Steampipe Dashboard: After running this command in the mod folder, the dashboard interface will automatically get launched in browser as https://localhost:<port>. You select the required options from these list.

Dashboard View:

CIS Benchmarks:

Google’s Foreseti Security Dashboard: An open source GCP Governance and policy enforcement project by Google that seems to be killed.
The project might be dead, but seems like Google is bundling it with it’s SCC premium offering.

Look for unused resources , it can help identify cost impact.

Identify untagged resources in GCP:

You can also use CLI to generate few quick report as per following:

$ steampipe check all ##export (Generates default HTML)$ steampipe check all — output=json > GCP_compliance report

Output: Default console output is text, however you can select the console output format as Csv, Html, Json, Md or Text.

Run a single benchmark:

$ steampipe check benchmark.cis_v130

Run a specific control:

$ steampipe check control.cis_v130_2_1

Written by Ashish Bansal

No responses yet