> ## Content Index > Fetch the complete content index at: https://devopscube.com/llms.txt > Use this file to discover other available public pages before exploring further. # Steampipe Tutorial: Query AWS and Kubernetes Infrastructure With SQL - URL: https://devopscube.com/steampipe-tutorial/ - Published: 2026-09-15T10:19:49.000Z - Updated: 2026-09-15T10:20:14.000Z - Author: Bibin Wilson - Tags: #blog, DEVSECOPS, DEVOPS, #syntax-highlight How many times have you opened the AWS console and other tools just to answer a simple question like: - *Which resources don't have the required labels?* - *Are there any storage buckets that can be accessed publicly?* - *Which Kubernetes namespaces don't have NetworkPolicies?* - Which Terraform resources are missing encryption settings? The answers are there, but finding them often means jumping between services, checking multiple accounts, or writing scripts. What if you could answer these questions using a simple SQL query? That is exactly what [**Steampipe**](https://github.com/turbot/steampipe?ref=devopscube.com) helps you do. I have used **Steampipe** for a security project, and it worked very well for my use case. In this blog, I have covered how to use **Steampipe** and its key use cases/ Let's get started. ## What is Steampipe? [Steampipe](https://steampipe.io/?utm%5Fcampaign=query-your-cloud-kubernetes-like-a-database&utm%5Fmedium=referral&utm%5Fsource=newsletter.devopscube.com) is an open-source tool that lets you **query your cloud infrastructure using SQL** as shown in the image below. ![](https://storage.ghost.io/c/5f/2f/5f2f4d20-2abf-4534-8d40-7aa233aedd43/content/images/2026/09/68747470733a2f2f737465616d706970652e696f2f696d616765732f737465616d706970652d73716c2d64656d6f2e676966--1-.gif) Instead of navigating through multiple pages in a cloud console, you can ask questions about your infrastructure using simple SQL queries. For example, you can quickly find **publicly accessible S3 buckets** across your AWS accounts. ## Who is Steampipe for? Steampipe is mainly useful for people who need to answer questions about cloud infrastructure rather than constantly monitor it. - **Cloud security engineers** can use it for quick security audits, compliance checks, and evidence gathering without manually navigating the AWS console. - **DevOps and platform engineers** can use it to get a quick inventory of infrastructure across regions and accounts, or build simple checks into their workflows. - **FinOps teams** can use it to identify resources that cost money without providing much value and to combine cost data with information about the resources themselves. It is also useful for consultants, auditors, or anyone who has just inherited a cloud account and needs to understand what is actually running in it. ## **How Does Steampipe Work?** The following diagram illustrates how Steampipe enables querying cloud resources using SQL. ![Steampipe architecture showing how SQL queries use PostgreSQL and FDW plugins to retrieve cloud and Kubernetes data as virtual table rows and columns.](https://storage.ghost.io/c/5f/2f/5f2f4d20-2abf-4534-8d40-7aa233aedd43/content/images/2026/09/image-75.png) Steampipe runs a lightweight PostgreSQL database locally and connects to your cloud using your **existing credentials**. When you run a SQL query, 1. Steampipe receives the SQL query. 2. The relevant plugin translates it into cloud API calls. 3. The cloud provider returns the requested resource data. 4. Steampipe maps the data into rows and columns. 5. PostgreSQL returns the result to your terminal. Under the hood, Steampipe plugins use PostgreSQL [**Foreign Data Wrappers (FDWs)**](https://www.percona.com/blog/foreign-data-wrappers-in-postgresql-databases-postgres%5Ffdw-dblink/?utm%5Fcampaign=query-your-cloud-kubernetes-like-a-database&utm%5Fmedium=referral&utm%5Fsource=newsletter.devopscube.com). This allows external resources such as AWS, Azure, GCP, and [Kubernetes](https://devopscube.com/kubernetes-architecture-explained/) to appear as regular database tables. For example, the AWS plugin translates your SQL query into calls to the relevant AWS APIs. ## Installation and setup ### macOS The recommended way on macOS is by using Homebrew. If you don’t have Homebrew installed, you can get it at [brew.sh](http://brew.sh/?ref=devopscube.com). ``` brew install turbot/tap/steampipe ``` Verify installation: ``` steampipe -v ``` ### Linux Run the one-step installer script: ``` sudo /bin/sh -c "$(curl -fsSL https://steampipe.io/install/steampipe.sh)" ``` This downloads the Steampipe binary, installs it to `/usr/local/bin`, and then creates a `.steampipe` directory in your home folder that contains all supporting libraries and configuration. ### Windows Steampipe on Windows requires WSL2 (Windows Subsystem for Linux). Once WSL2 is set up, you can either use the Linux installer above in your WSL terminal, or run the native Windows installer using PowerShell: ``` iwr -useb https://steampipe.io/install/steampipe.ps1 | iex ``` ### Installing Plugins Steampipe does not inherently know about AWS, GCP, or any cloud provider. You need to install a plugin for the service you want to query. Plugins are available for GCP, Azure, GitHub, [Kubernetes](https://devopscube.com/setup-kubernetes-cluster-kubeadm/), and many more. You can check them out at [hub.steampipe.io](http://hub.steampipe.io/?ref=devopscube.com). For example, I am using [AWS](https://devopscube.com/aws-vpc-design/) and Kubernetes. You can install the plugins using the following commands. ```bash steampipe plugin install aws teampipe plugin install kubernetes ``` ## Credentials and permissions One thing worth clarifying is how Steampipe actually connects to AWS. The AWS plugin uses the AWS SDK, not the [AWS CLI](https://devopscube.com/install-configure-aws-cli-linux/). But it uses the same standard AWS credential chain, so it can pick up credentials from environment variables, AWS profiles, SSO, and IAM roles depending on where it is running. In practice, the rule of thumb is simple: if aws `ec2 describe-vpcs` works in your terminal, you’re already most of the way there. Steampipe can use the same AWS credentials to query your account. You don’t need to create a separate set of credentials just for Steampipe. The AWS plugin also exposes hundreds of AWS tables, including [EC2](https://devopscube.com/use-aws-cli-create-ec2-instance/), S3, IAM, EBS, [CloudWatch](https://devopscube.com/how-to-setup-and-push-serverapplication-logs-to-aws-cloudwatch/), and Cost Explorer data. ## Querying Multiple Accounts at Once Most people don't have one AWS account; they have several. Steampipe lets you define a connection per profile and group them into an aggregator in `~/.steampipe/config/aws.spc`: ```hcl connection "aws_all" { plugin = "aws" type = "aggregator" connections = ["aws_prod", "aws_staging", "aws_dev"] } ``` Now you can query all of them together: ```sql select account_id, name, region from aws_all.aws_s3_bucket; ``` One query per account; no looping over profiles yourself. ## How to use Steampipe? 💡 ****Important Note:** You can find the query syntax in the Steampipe plugin documentation. For example, for AWS, refer to the AWS tables, and for Kubernetes, refer to the Kubernetes tables. **Running a Query Directly:** You can run a one-off query straight from your terminal: ```sql steampipe query "select name, region from aws_s3_bucket" ``` **Using the Interactive Shell:** For exploring your infrastructure, the interactive shell is more convenient. It supports tab-completion and query history: ```bash steampipe query ``` This drops you a prompt where you can type queries and press Enter to run them: ```sql > select instance_id, instance_type, region from aws_ec2_instance where instance_state = 'running'; ``` Press Ctrl+D or type .exit to leave. **Running a Query From a File:** For queries you want to save and reuse, put them in a .sql file and pass it to Steampipe. For example: ```bash steampipe query my_checks.sql ``` You can also save the output as JSON or CSV, making it easier to use the results in scripts or other tools. ``` steampipe query my_checks.sql --output json ``` ## **Practical Steampipe Examples** Now, let’s look at some practical use cases for Steampipe. ### **1\. Cloud Security Checks** In cloud security, questions like “is there anything exposed that shouldn’t be?” are very common. Steampipe is built to answer these kinds of questions. Finding all publicly accessible buckets across your account almost always means checking each one individually or setting up AWS Config. With Steampipe: ```sql > select name, region from aws_s3_bucket where bucket_policy_is_public = true; +-------------------------------------------+-----------+ | name | region | +-------------------------------------------+-----------+ | static-web-arun-637423664276-us-west-2-an | us-west-2 | +-------------------------------------------+-----------+ 1 row ``` Similarly, to **find users without multi-factor authentication,** you can run the following query. ```sql select name, mfa_enabled from aws_iam_user where mfa_enabled = false; ``` These kinds of checks are run regularly by security teams. ### 2\. Cross-Service Questions Without Custom Scripts Suppose you want to identify which running EC2 instances are associated with a security group that allows unrestricted inbound traffic. That question spans two services, EC2 and VPC security groups. Answering it normally means either writing a script that calls both APIs and combines the results, or doing it manually. With Steampipe, you can write one query that joins the two tables: ```sql select i.instance_id, i.instance_type, sg.group_name from aws_ec2_instance as i cross join lateral jsonb_array_elements(i.security_groups) as s join aws_vpc_security_group as sg on sg.group_id = s->>'GroupId' where i.instance_state = 'running'; ``` This is where Steampipe starts becoming really useful. You’re not just querying one AWS service anymore. You’re combining information from different parts of your infrastructure with normal SQL. ### 3\. Tagging Compliance Tags are labels you attach to cloud resources, things like Environment: Production or Team: Backend. They’re used for cost tracking, access control, and general organization. Sometimes people forget to tag resources, and you end up with infrastructure that nobody can clearly identify. Finding the untagged ones without a tool like Steampipe means going service by service. With Steampipe: ``` select instance_id, instance_type, region from aws_ec2_instance where tags is null or tags = '{}'; ``` You can run the same idea against other resource types and get a list of what is missing. AWS Tag Policy can help [enforce tagging rules](https://devopscube.com/aws-tag-policy/) natively, but Steampipe is useful for quickly inspecting what is actually in the account. ### 4\. FinOps and Cost Visibility Steampipe’s AWS plugin exposes Cost Explorer data as tables, including costs by account, service, region, and tag, as well as forecasts and usage. For example, you can find which services are driving the most cost: ```sql select service, period_start, unblended_cost_amount from aws_cost_by_service_monthly where period_start > now() - interval '3 months' order by unblended_cost_amount desc limit 10; ``` 💡 One thing worth knowing: Cost Explorer API calls cost $0.01 each, unlike the resource tables which are free. Don't put one of these in a loop. You can also look for obvious waste, such as EBS volumes that exist but aren’t attached to anything: ``` select volume_id, size, create_time from aws_ebs_volume where state = 'available'; ``` An available [EBS volume](https://devopscube.com/mount-ebs-volume-ec2-instance/) means it exists, you’re paying for it, and nothing is using it. The interesting part is when you combine cost and infrastructure data. For example, you can find running EC2 instances that have not been doing much: ```sql select i.instance_id, i.instance_type, i.tags ->> 'Team' as team, avg(m.average) as avg_cpu from aws_ec2_instance i inner join aws_ec2_instance_metric_cpu_utilization_daily m on i.instance_id = m.instance_id where i.instance_state = 'running' and m.timestamp > now() - interval '14 days' group by i.instance_id, i.instance_type, i.tags ->> 'Team' having avg(m.average) < 5; ``` Output shows, ```bash +---------------------+---------------+--------+--------------------+ | instance_id | instance_type | team | avg_cpu | +---------------------+---------------+--------+--------------------+ | i-0a8f3af65dc9f88a7 | t3.medium | | 3.9168023811790946 | | i-0be76f42014452666 | t3.medium | | 4.479497081730911 | +---------------------+---------------+--------+--------------------+ 2 rows ``` Now you’re looking at instances that have averaged less than 5% CPU for two weeks, grouped by the team that owns them. That’s not just an infrastructure query anymore. That’s something a [FinOps](https://devopscube.com/best-finops-certifications/) team can actually use to find resources that might be wasting money. The AWS plugin also exposes `aws_costoptimizationhub_recommendation`, so AWS’s own cost optimization recommendations can be queried alongside your infrastructure data. ### 5\. Kubernetes Configuration Auditing In a [Kubernetes cluster](https://devopscube.com/production-ready-kubernetes-cluster/), you may want to know **which containers are running without CPU or memory limits**. For example, to find [containers](https://devopscube.com/kubernetes-init-containers/) without memory limits: ```sql select name as pod_name, namespace, c ->> 'name' as container_name from kubernetes_pod, jsonb_array_elements(containers) as c where c -> 'resources' -> 'limits' ->> 'memory' is null; ``` ## Is Steampipe Worth Using? Based on our testing, Steampipe is most useful when you need a quick answer from live cloud data and do not want to write a custom script. Its strongest use cases that we found useful are cross-service investigation, multi-account inventory, one-time security checks, and [FinOps](https://devopscube.com/finops-certified-practitioner-study-guide/) exploration. We would not use it alone for continuous compliance, configuration history, alerting, or automated remediation. ## Conclusion The useful thing about Steampipe is that you can ask questions about your infrastructure without having to build a script every time you need an answer. Need to find public S3 buckets? Write a query. Need to check which instances are missing tags? Query it. Need to look across multiple AWS accounts or combine data from different services? It’s the same SQL interface. It’s not trying to replace [AWS Config](https://aws.amazon.com/config/?ref=devopscube.com), a CSPM platform, or your monitoring stack. It’s just a much easier way to investigate what’s actually in your cloud when you need an answer.