Steampipe Review: Query Your Cloud Infrastructure Like a Database

Security audits and cloud inventory management usually involve one of two things: clicking through the AWS console for hours, or writing Python scripts that call AWS APIs and parsing JSON. Both approaches are painful.

Steampipe offers a third option: query your cloud infrastructure with SQL.

SELECT instance_id, instance_type, state, region
FROM aws_ec2_instance
WHERE state = 'running'
  AND instance_type NOT IN ('t3.micro', 't3.small')
  AND tags ->> 'Environment' = 'production';

That query returns all non-trivial EC2 instances in production, instantly. No SDK setup, no pagination handling, no JSON parsing. I've been using Steampipe for about two months across AWS, Kubernetes, and GitHub. Here's what I actually found.

What Steampipe Is

Steampipe is a CLI tool that runs an embedded PostgreSQL server. Plugins (called "mods") connect to cloud providers and APIs, translate their data into SQL tables, and let you query them with standard SQL.

Supported sources include: AWS, Azure, GCP, Kubernetes, GitHub, Terraform, Docker, Splunk, Datadog, Slack, and ~100 more.

The plugin model is the key: each plugin maps API resources to tables. The AWS plugin has tables like aws_ec2_instance, aws_s3_bucket, aws_iam_user. You query those tables with SQL, and Steampipe calls the relevant APIs behind the scenes.

Installation and Setup

# Install Steampipe
brew tap turbot/tap
brew install steampipe
 
# Install AWS plugin
steampipe plugin install aws
 
# Authenticate (uses your existing AWS credentials)
# ~/.aws/credentials or environment variables
 
# Start querying
steampipe query

-- First query: how many running EC2 instances do I have?
SELECT COUNT(*) FROM aws_ec2_instance WHERE state = 'running';

The initial startup takes a few seconds while Steampipe initializes the embedded PostgreSQL. After that, queries are fast for small-to-medium accounts.

Real-World Use Cases

Security Audit: Find Public S3 Buckets

SELECT
  name,
  region,
  bucket_policy_is_public,
  block_public_acls,
  block_public_policy
FROM aws_s3_bucket
WHERE bucket_policy_is_public = TRUE
   OR block_public_acls = FALSE
   OR block_public_policy = FALSE;

This would take 20+ minutes manually through the console. With Steampipe, it runs in 30 seconds.

IAM Audit: Users Without MFA

SELECT
  user_name,
  create_date,
  password_last_used,
  mfa_enabled
FROM aws_iam_user
WHERE mfa_enabled = FALSE
  AND password_enabled = TRUE
ORDER BY password_last_used DESC;

Kubernetes: Pods Without Resource Limits

SELECT
  name,
  namespace,
  jsonb_array_elements(containers) -> 'resources' -> 'limits' AS limits
FROM kubernetes_pod
WHERE (containers -> 0 -> 'resources' -> 'limits') IS NULL
  AND namespace NOT IN ('kube-system', 'kube-public');

Cross-Service Analysis: EC2 Instances Not in Any Security Group Review

-- Find EC2 instances with default security group (common oversight)
SELECT 
  i.instance_id,
  i.instance_type,
  sg.group_name,
  i.region
FROM aws_ec2_instance i
JOIN aws_vpc_security_group sg ON sg.group_id = ANY(
  ARRAY(SELECT jsonb_array_elements_text(i.security_groups -> 'GroupId'))
)
WHERE sg.group_name = 'default'
  AND i.state = 'running';

This cross-table join across two different AWS services is exactly where Steampipe shines — it would require multiple API calls and Python code to replicate.

Steampipe Benchmarks: Pre-Built Compliance Checks

Beyond ad-hoc queries, Steampipe has a benchmark system with pre-built compliance checks:

# Install the AWS Compliance mod
steampipe mod install github.com/turbot/steampipe-mod-aws-compliance
 
# Run CIS AWS Foundations checks
steampipe check benchmark.cis_aws_foundations_benchmark_v150
 
# Run specific controls
steampipe check control.cis_v150_1_4  # Root account access key check

These generate HTML or JSON reports with pass/fail status for each control. For teams that need to demonstrate compliance posture, this is significantly faster than commercial tools like Prisma Cloud or Wiz for basic compliance reporting.

Where Steampipe Struggles

Performance on large accounts. If you have thousands of EC2 instances or millions of S3 objects, queries slow down significantly. The plugin has to make paginated API calls, and there's no pre-aggregated data store. A query scanning all S3 objects in a 5TB account can take 20+ minutes.

Steampipe provides caching, but it's per-session. There's no persistent cache between CLI sessions, so you're always making fresh API calls.

Complex joins get messy. SQL is great for relational data, but cloud resources are often deeply nested JSON. Querying nested attributes requires PostgreSQL's jsonb functions (->>, @>, jsonb_array_elements), which aren't beginner-friendly.

No alerting or scheduling. Steampipe is a query tool, not a monitoring platform. To run queries on a schedule or trigger alerts, you need to pipe it into something else (cron + email, GitHub Actions, etc.). This isn't necessarily bad — it's a focused tool — but it means Steampipe alone doesn't replace something like AWS Config.

Multi-account setup is manual. Querying across 20+ AWS accounts requires configuring each account in the plugin config and using connection aggregators. It works, but the setup is tedious.

Steampipe vs. Alternatives

Verdict

Steampipe does exactly what it says: it makes cloud infrastructure queryable with SQL. For DevOps engineers who already think in SQL, this is genuinely useful for ad-hoc analysis, security audits, and answering "what do we have running?" questions across multiple services simultaneously.

The limitations are real — performance at scale, no built-in scheduling, manual multi-account setup — but they're appropriate for what Steampipe is: a query tool, not a full CSPM platform.

If you spend 30+ minutes per week digging through the AWS console or writing one-off Python scripts to answer inventory questions, Steampipe will save you time immediately. The learning curve for the jsonb syntax is the main barrier, but for anyone comfortable with SQL it's manageable within a few hours.

Score: 8/10 — A genuinely useful tool with a clear, well-executed purpose. Limited by performance at large scale, but excellent for most DevOps teams.

More security tooling? Check out our Trivy vs Grype vs Snyk container scanning comparison and software supply chain security guide.