query your entire AWS account with plain SQL

your aws account is basically a database, you just haven't queried it yet

if you've ever clicked through 40 s3 buckets in the console trying to figure out which one is public, you already know the console is not built for auditing. it's built for clicking one thing at a time. steampipe fixes that by turning your entire cloud account into a set of sql tables you can query directly. iam users, ec2 instances, security groups, s3 buckets, all of it becomes rows and columns.

this post is about using that power defensively, to find your own exposure before someone else does.

what steampipe actually is

steampipe is an open source tool that connects to aws (and gcp, azure, github, and a bunch of other services) and exposes the api responses as postgres tables. under the hood it's still calling the same aws apis the console uses, it just hands you the results as sql instead of a webpage full of pagination.

once it's installed and you've added the aws plugin, you get a local postgres-compatible shell where things like aws_s3_bucket, aws_iam_user, and aws_security_group are just tables sitting there waiting for a select statement.

breaking down the query

select name from aws_s3_bucket where bucket_policy_is_public;

this is about as simple as sql gets, but let's walk through it anyway:

select name means we only want the bucket name back, not every column steampipe knows about a bucket (and it knows a lot, region, versioning, encryption, logging config, all of it).

from aws_s3_bucket tells steampipe which resource type to pull. behind the scenes this triggers the list and describe api calls against s3 for every bucket in your account.

where bucket_policy_is_public filters down to buckets whose policy actually grants public access, not just buckets that theoretically could be misconfigured. this column is computed by steampipe evaluating the bucket policy for you, which is the part that would take forever to do by hand across dozens of buckets.

run that one line and you get a clean list of every bucket that's currently exposed to the internet. no clicking, no manually opening policy json for each one.

going further than one query

the real value shows up when you start chaining checks together. a few useful ones to run against your own account:

select user_name, mfa_enabled from aws_iam_user where not mfa_enabled;

finds every iam user without mfa turned on, which is one of the highest-value fixes you can make in an afternoon.

select group_name from aws_ec2_security_group
where ip_permissions is not null
and ip_permissions::text like '%0.0.0.0/0%';

surfaces security groups open to the entire internet, a classic way people accidentally expose ssh or rdp.

select access_key_id, user_name, create_date
from aws_iam_access_key
where status = 'Active'
and create_date < now() - interval '90 days';

flags old access keys that should've been rotated ages ago. stale keys are a favorite target because nobody remembers they exist.

none of these queries change anything, they just report. that's the point, you're building visibility before you build a fix.

making this a habit, not a one time thing

the danger with any audit tool is running it once, feeling good, and never touching it again. treat these queries like a recurring health check instead:

save the ones that matter to your team into a folder of .sql files and run them on a schedule, weekly is reasonable for most small teams.

pipe the output into a slack webhook or a simple cron job that emails you when a new public bucket or mfa-less user shows up. steampipe's cli output is script-friendly, so this isn't much extra work.

if you manage multiple aws accounts, steampipe can query across them with aggregator connections, so you're not logging into five different consoles to do the same checks five times.

the takeaway

the console makes it easy to miss things because it forces you to look at one resource at a time. steampipe lets you ask your whole account a direct question and get a direct answer. start with the public bucket check and the mfa check, those two alone catch a huge chunk of the misconfigurations that actually get accounts compromised. run them today, then put them on a schedule so future-you doesn't have to find out the hard way.