AWS Steampipe SQL Queries

Steampipe lets you query your AWS infrastructure with plain SQL. Instead of clicking through the console or chaining AWS CLI commands with jq, you write a SELECT statement and get back exactly what you need.

Below is a collection of queries I use regularly. Each one targets a specific resource type and answers a specific question you'd ask during a security review, audit, or just trying to understand what's running in an account.

IAM Users and Roles

Start here. You want to know who has access to the account and what roles exist. Old IAM users that haven't been touched in months are a red flag. Roles with overly permissive assume-role policies are another.

SELECT
  name,
  arn,
  create_date,
  path
FROM aws_iam_user;

SELECT
  name,
  arn,
  assume_role_policy,
  create_date
FROM aws_iam_role;

Managed Policies

This filters out AWS-managed policies and shows only the custom ones your team created. Custom policies are where misconfigurations hide, since nobody reviews them as carefully as the AWS defaults.

SELECT
  name AS policy_name,
  arn AS policy_arn,
  description,
  path,
  create_date
FROM aws_iam_policy
WHERE is_aws_managed = false;

Networking: VPCs and Subnets

Understanding the network layout is fundamental. Which VPCs exist, what CIDR ranges are in use, and which subnets auto-assign public IPs (that map_public_ip_on_launch column is the one to watch).

SELECT
  vpc_id,
  cidr_block,
  is_default,
  state,
  region
FROM aws_vpc;

SELECT
  subnet_id,
  vpc_id,
  availability_zone,
  cidr_block,
  map_public_ip_on_launch
FROM aws_vpc_subnet;

SELECT
  vpc_id,
  cidr_block,
  is_default,
  state,
  region
FROM aws_vpc
ORDER BY
  region, vpc_id;

Security Groups

Security groups are your instance-level firewall. You're looking for groups with overly broad rules, groups attached to nothing (orphaned), or groups with descriptions that don't match what they actually allow.

SELECT
  group_id,
  group_name,
  description,
  vpc_id,
  region
FROM aws_vpc_security_group;

S3 Buckets

Quick inventory of all buckets. From here you'd typically dig deeper into bucket policies, ACLs, and encryption settings, but this gives you the starting list.

SELECT
  name,
  region,
  creation_date
FROM aws_s3_bucket;

EC2 Instances

The basics: what's running, where, and is it publicly accessible? The public_ip_address column tells you immediately which instances are internet-facing.

SELECT
  instance_id,
  instance_type,
  placement_availability_zone as availability_zone,
  instance_state,
  public_ip_address,
  private_ip_address,
  vpc_id,
  region
FROM
  aws_ec2_instance
LIMIT 10;

EC2 Instances with Security Groups

This is where it gets useful. Joining instances to their security groups tells you which firewall rules apply to which machines. The LATERAL join unpacks the JSON array of security groups attached to each instance.

SELECT
  i.instance_id,
  sg_obj ->> 'GroupId' AS group_id,
  sg_obj ->> 'GroupName' AS group_name
FROM
  aws_ec2_instance i,
  LATERAL jsonb_array_elements(i.security_groups) AS sg_obj;

SELECT
  i.instance_id,
  i.instance_type,
  i.placement_availability_zone AS availability_zone,
  i.vpc_id,
  sg_obj ->> 'GroupId' AS security_group_id,
  sg_obj ->> 'GroupName' AS security_group_name
FROM
  aws_ec2_instance i,
  LATERAL jsonb_array_elements(i.security_groups) AS sg_obj
ORDER BY
  i.instance_id;

EC2 Schema Discovery

Not sure what columns are available for EC2 instances? Query the information schema. Handy when you're building new queries and want to know what data Steampipe exposes.

SELECT
  column_name,
  data_type
FROM information_schema.columns
WHERE table_name = 'aws_ec2_instance';

Lambda Functions

Serverless doesn't mean invisible. You still need to know what's deployed, what runtime it's using (old runtimes are a security risk), and when it was last updated.

SELECT
  name,
  runtime,
  handler,
  last_modified,
  region
FROM aws_lambda_function;

API Gateway REST APIs

This joins stages to their parent APIs and constructs the actual invoke URL. Useful for mapping out the API surface area of an account, especially when you're looking for endpoints that might be publicly accessible.

SELECT
  stage.rest_api_id,
  api.name AS api_name,
  stage.name AS stage_name,
  stage.region,
  'https://' || stage.rest_api_id || '.execute-api.' || stage.region || '.amazonaws.com/' || stage.name AS invoke_url
FROM
  aws_api_gateway_stage AS stage
JOIN
  aws_api_gateway_rest_api AS api
    ON stage.rest_api_id = api.api_id;

Load Balancers

ALBs and NLBs are often the front door to your applications. The scheme column tells you if they're internet-facing or internal. The DNS name is what resolves publicly.

SELECT
  name,
  arn,
  dns_name,
  scheme,
  region
FROM aws_ec2_application_load_balancer;

SELECT
  name,
  arn,
  dns_name,
  scheme,
  region
FROM aws_ec2_network_load_balancer;

← Back to all posts