Reclaiming CSPM: How I Learned to Stop Worrying and Query the Cloud

When I think of Cloud Security Posture Management — CSPM — I think of simple, necessary checks.

Things like:

• Is this EBS volume encrypted?

• Does this IAM user have MFA enabled?

• Is logging turned on for this S3 bucket?

It’s not complicated work — it’s config hygiene. But despite how basic some of this is, the default reaction is almost always: which vendor do I need for that?

Not: How would I check this myself?
Not: Is there an open source way?
Just: What platform handles this for me?

That’s not how AppSec works. That’s not how Kubernetes security works. Both of those have rich ecosystems of open source tools. But CSPM? It seems to be mostly a closed conversation — even when the logic behind the checks is simple.

You can try to go manual with AWS CLI, but it gets painful fast. Every service has its own command structure, its own quirks. You end up with jq pipelines and bash loops that are barely maintainable.

I even tried CloudQuery, thinking I could plug it into my workflow. But as far as I could tell, the open source part stops at the data collection — there’s no way to query or visualize anything unless you use their commercial UI. That’s not what I wanted.

I wanted a tool that let me explore cloud posture like a real data problem — something I could query, filter, and automate without giving up visibility or control.

This isn’t a dig at cloud security vendors. Most of them do excellent work. But we should still have options — and open source should be one of them.

What Is CSPM (imo)?

Cloud Security Posture Management is always going to be our fancy way of saying:
“Are your cloud settings sane?”

It’s about continuously checking your environment for misconfigurations — the kind that might not trip alarms today, but will absolutely come up in your next audit, breach report, or awkward Slack message from the security team.

Most of our CSPM checks fall into a few predictable buckets:

• Access:
  Is this IAM user missing MFA? Did someone attach AdministratorAccess to a service account because... well whatever

• Exposure:
  Is this S3 bucket public? Is this security group open to the internet? Is someone running a managed database that’s wide open to the world?

• Logging:
  Do you have CloudTrail enabled in all regions? Are you capturing S3 access logs?

• Backup & Recovery:
  Do you have EBS snapshots configured? Can anyone delete them without guardrails?

• Baseline Hygiene:
  Is encryption enabled at rest? In transit? Or is it one of those things everyone agreed to “circle back to” three quarters ago?

These aren’t complicated questions. They’re config flags. But obviously it’s necessary with the ease of hacks we've seen since the advent of cloud.

But being simple configs, shouldn’t that mean we can check and audit them ourselves?

Shouldn’t we be able to do DIY CSPM?

This feels less like building a security data platform and more like building a small end table — with pocket screws, not mortise and tenon joints.

It doesn’t have to be beautiful. It just has to stand up on its own.

---

The AWS CLI: Does Everything, Sorta

For now, we’re keeping our focus on AWS. That’s not because the other clouds don’t have issues worth exploring, it’s just that AWS is simply the account I have set up with a few things.

So let’s say you want to start checking posture yourself. No vendors. No tools. Just raw AWS CLI. Here are some progressively more complex queries, although none of them are too crazy.

Example 1: Which EBS volumes aren’t encrypted?

This one is in AWS Foundational Security Best Practices, as are most of the following. I think it is probably one of the least important security configs out there, but let's proceed. Chalk that up to one for compliance. But at least it is genuinely simple. The following is the AWS query I ran:

aws ec2 describe-volumes \
  --query 'Volumes[?Encrypted==`false`].[VolumeId,AvailabilityZone]' \
  --output table
-------------------------------------------
|            DescribeVolumes              |
+------------------------+----------------+
|  vol-12345abcd6789efgh |  us-west-2b    |
+------------------------+----------------+

That’s it. One call. You get a table of unencrypted EBS volumes and where they live. It’s readable, fast, and you don’t have to write a shell script.

Example 2: Which S3 buckets are public?

This one’s not quite as straightforward. But if you’ve ever heard people talk about CSPM, this is the part they all bring up. Then they argue over it, cite a few high-profile breaches, and suddenly you remember why CSPM feels like a headache disguised as a product category. And there are more than one Foundational Best Practices!

But I digress.

A quick loop to scan all your S3 buckets for bucket policies that allow public read access, specifically Principal: "*" and Action: "s3:GetObject":

for b in $(aws s3api list-buckets --query 'Buckets[*].Name' --output text); do
  aws s3api get-bucket-policy --bucket "$b" --query Policy --output text 2>/dev/null | \
    jq -e '.Statement[] | select(.Principal=="*" and .Action=="s3:GetObject")' >/dev/null && echo "$b is public"
done

You’ll get something like:

public-policy-bucket-1111111111 is public

Not bad, right? Except — of course — that doesn’t catch buckets with public-read ACLs.

So here’s another script:

aws s3api list-buckets --output json | jq -r '.Buckets[].Name' | while read bucket; do
  if aws s3api get-bucket-acl --bucket "$bucket" \
      --query 'Grants[*].Grantee.URI' --output text 2>/dev/null | grep -q "AllUsers"; then
    echo "$bucket is public via ACL"
  fi
done

You’ll get something like:

public-acl-bucket-1111111111 is public via ACL

And just like that, you’re in AWS permission hell. I always think these are going to be easy… then I end up crying and hacking away more than I ever wanted.

Example 3: Which IAM users don’t have MFA enabled?

Let’s just start what seems like the simplest question of all:

Do Bob and Matt have MFA?

You start simple:

aws iam list-users

Okay, cool. A list of usernames. But to actually check MFA, you have to loop over each user and query their MFA devices one by one (yes my output formatting is a bit inconsistent throughout):

aws iam list-users --query 'Users[*].UserName' --output text | tr '\t' '\n' | while read user; do
  mfa=$(aws iam list-mfa-devices --user-name "$user" --query 'MFADevices' --output json)
  if [[ "$mfa" == "[]" ]]; then
    echo "$user has NO MFA"
  else
    echo "$user has MFA configured"
  fi
done

You’ll get something like:

Matt has NO MFA
Bob has NO MFA

It works. You’ll get an answer.

But this doesn't even say anything else about the user. Like when their credentials were last rotated, whether they’re in privileged groups, or if they have access keys lying around from the Bush (pick one) administration. Oh paid CSPM where art thou?

Example 4: Which EC2 instances are running without an IAM role attached?

No, let’s not.

You can technically make all of this work. But it’s just posture management by brute force with bash and cli.

---

What Is Steampipe and Why Should I Care?

After going through that AWS CLI gauntlet, you're wondering:
“Surely there’s a better way?”

There is, otherwise I wouldn't be posting this.

Steampipe is an open source tool that lets you query your cloud accounts like a database. No ETL, no bash, no endless scripting. Just SQL, which is why their tagline seems to be select * from cloud;. It has 149 plugins, which allow it to not just query AWS, but a host of other services like Okta, Semgrep, and Tailscale.

The AWS plugin wraps the AWS API in a local Postgres-like interface, where each resource — IAM users, S3 buckets, EC2 instances — is a table. You write SQL queries, and Steampipe figures out the API calls behind the scenes.

Just write:

select * from aws_cloudtrail_trail;

Yes. That’s it. You now have all your cloudtrails. But what is really cool is that if you don't just select name you get a lot of info combined and thus are not making a bunch of extra calls. There information is information about selectors, time ran, etc. It has a lot more than aws cloudtrail list-trails because it is a sort of virtual table combining various cloudtrail cli calls.

Yay SQL?

Why It Works So Well

• You already know SQL, or can pick it up faster than a dozen AWS API idiosyncrasies. Or hello GPT4o (but that is for another post).

• You can join tables and leverage virtual tables, which means no more “get this from here, then look that up over there.”

• You can scan whole accounts quickly, not just one user or bucket at a time.

• And best of all — it’s OSS. You install it locally, point it at your AWS creds, and go.

We’re not even talking dashboards yet (which it has). Just fast, understandable, ad hoc security checks.

Let’s get it installed and see it in action.

---

Setting Up Steampipe

Getting started with Steampipe is simply lovely. Out of the helm chart territory for now. It runs locally and uses your existing AWS credentials.

Step 1: Install Steampipe

If you’re on macOS and use Homebrew:

brew install steampipe

You’re ready to roll.

If you're not using macOS, just follow the appropriate instructions.

Step 2: Install the AWS Plugin

Steampipe works through plugins. To query AWS resources, you’ll need the AWS plugin:

steampipe plugin install aws

Step 3: Use Your AWS Credentials

Steampipe will pick up whatever AWS creds you'd normally use — including:

• AWS_PROFILE environment variable

• Static credentials in ~/.aws/credentials

• And other ways

If you can run aws sts get-caller-identity, Steampipe can use it.

Step 4: Run Your First Query

Fire up the interactive shell:

steampipe query

You’ll drop into a SQL prompt.

Welcome to Steampipe v2.0.1
For more information, type .help
>

Hello mariadb command line.

From here you can check out a few helpful things, including the auto complete as you type. Use .tables and you get a nice list of all the tables available based on the plugins you installed. Then use .inspect to find the schema for a specific. For example, you can find the schema of iam_user with the following:

> .inspect aws_iam_user
+---------------------------+--------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------+
| column                    | type                     | description                                                                                                                                              |
+---------------------------+--------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------+
| _ctx                      | jsonb                    | Steampipe context in JSON form.                                                                                                                          |
| account_id                | text                     | The AWS Account ID in which the resource is located.                                                                                                     |
| akas                      | jsonb                    | Array of globally unique identifier strings (also known as) for the resource.                                                                            |
| arn                       | text                     | The Amazon Resource Name (ARN) that identifies the user.                                                                                                 |
| attached_policy_arns      | jsonb                    | A list of managed policies attached to the user.                                                                                                         |
| create_date               | timestamp with time zone | The date and time, when the user was created.                                                                                                            |
| groups                    | jsonb                    | A list of groups attached to the user.                                                                                                                   |
| inline_policies           | jsonb                    | A list of policy documents that are embedded as inline policies for the user.                                                                            |
| inline_policies_std       | jsonb                    | Inline policies in canonical form for the user.                                                                                                          |
| login_profile             | jsonb                    | Contains the user name and password create date for a user.                                                                                              |
| mfa_devices               | jsonb                    | A list of MFA devices attached to the user.                                                                                                              |
| mfa_enabled               | boolean                  | The MFA status of the user.                                                                                                                              |
| name                      | text                     | The friendly name identifying the user.                                                                                                                  |
| partition                 | text                     | The AWS partition in which the resource is located (aws, aws-cn, or aws-us-gov).                                                                         |
| password_last_used        | timestamp with time zone | The date and time, when the user's password was last used to sign in to an AWS website.                                                                  |
| path                      | text                     | The path to the user.                                                                                                                                    |
| permissions_boundary_arn  | text                     | The ARN of the policy used to set the permissions boundary for the user.                                                                                 |
| permissions_boundary_type | text                     | The permissions boundary usage type that indicates what type of IAM resource is used as the permissions boundary for an entity. This data type can only  |
|                           |                          | have a value of Policy.                                                                                                                                  |
| region                    | text                     | The AWS Region in which the resource is located.                                                                                                         |
| sp_connection_name        | text                     | Steampipe connection name.                                                                                                                               |
| sp_ctx                    | jsonb                    | Steampipe context in JSON form.                                                                                                                          |
| tags                      | jsonb                    | A map of tags for the resource.                                                                                                                          |
| tags_src                  | jsonb                    | A list of tags that are attached to the user.                                                                                                            |
| title                     | text                     | Title of the resource.                                                                                                                                   |
| user_id                   | text                     | The stable and unique string identifying the user.                                                                                                       |
+---------------------------+--------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------+
>

I think I notice an MFA column! Now to just grab some data, try this:

select name from aws_iam_user;

And you should see

+--------+
| name   |
+--------+
| matt   |
| bob    |
+--------+

If that works — congrats. No more aws cli.

Let’s rebuild the rest of our examples in Steampipe next.

---

Rewriting Our Examples with Steampipe

Let's do it the easy way. We'll rework all our earlier queries the Steampipe way. You'll see how easy these are as they have now been simplified into querying a single virtual table in each example.

Example 1: Which EBS volumes aren’t encrypted?

Simple one-liner:

select volume_id from aws_ebs_volume where encrypted=false;

And you'll see:

+-----------------------+
| volume_id             |
+-----------------------+
| vol-abc69af7b29881234 |
+-----------------------+

Nice.

Example 2: Which S3 buckets are public?

Instead of chasing down ACLs and policies across multiple API calls:

select name from aws_s3_bucket where bucket_policy_is_public=true or block_public_acls=false;

And you'll see:

+------------------------------------+
| name                               |
+------------------------------------+
| my-open-acl-bucket-1000987107      |
| my-open-policy-bucket-1000987107   |
+------------------------------------+

Too easy. This checks both bucket policies and ACLs with one keystroke.

Example 3: Which IAM users don’t have MFA enabled?

The CLI loop using two distinct calls becomes a single query:

select name from aws_iam_user where mfa_enabled=false;

And you'll see:

+------------------------------------+
| name                               |
+------------------------------------+
| matt                               |
| bob                                |
+------------------------------------+

Matt...

Example 4: EC2 Instances Without IAM Role

Remember when we skipped this earlier because it was “too annoying”?

Now it’s a simple query:

select instance_id, title, iam_instance_profile_arn from aws_ec2_instance where iam_instance_profile_arn is null;

And you'll see:

+---------------------+-------+--------------------------+
| instance_id         | title | iam_instance_profile_arn |
+---------------------+-------+--------------------------+
| i-11116d1177b5334a2a | Test  | <null>                   |

That is plus one for Steampipe. Wait... what are we comparing here?

---

Steampipe Beyond the Terminal

You don’t have to live inside interactive mode forever. Once your setup is working, you can interact with it several different ways.

steampipe query x

Run the queries in line via steampipe query x, where x is you query. Maybe useful for a cronjob or pipeline alerting workflows, which will look at in a later post.

steampipe query "select name from aws_iam_user where mfa_enabled=false;"

And you'll see the following from your terminal:

+------------------------------------+
| name                               |
+------------------------------------+
| matt                               |
| bob                                |
+------------------------------------+

Nothing different really, just a simpler way to do it provided you know the tables and columns.

Connect with any SQL client

If you prefer to use another SQL tool, you can run Steampipe as a service and set the config flags so you can use said tool.

The following is an example service config for localhost on port 9191 with a custom password:

steampipe service start --port 9191 --database-password password123

Once connected, you can run any of the same queries from this post.

---

Wrapping Up: Have It Your Way

None of the checks we walked through are revolutionary. You’re not writing detection rules, instrumenting syscall monitors, or hunting threats in a dark SOC corner. You’re asking basic questions:

• Is EBS encryption enabled?

• Are any S3 buckets public?

• Do my users have MFA?

• Did someone forget to attach an IAM role?

These are table stakes in CSPM — and Steampipe answers them pretty damn well, using the natural language of a certain type (SQL, not English).

And that’s the real win here: not “look at this fancy new tool,” but being able to ask simple cloud security questions without hating your life.

With Steampipe, you get:

• Live access to your cloud environment

• Queryable interfaces for things that usually require glue code

• Immediate visibility — no data warehouse, no dashboard logins, no waiting

You still have to think. You still have to write the queries.

But now, you get to have it your way? Ok I was looking for a reason to drop that in, no judgement.

---

In the next posts, we’ll dive into more advanced examples and capabilities — joins, cross-account checks, dashboards, agents.

But for now?

You installed Steampipe.
You ran your first queries.
You escaped the AWS CLI

Not too bad.