Leveraging Steampipe and the AWS Plugin for Security and Compliance

In today’s cloud-first environment, ensuring your AWS infrastructure is secure and compliant is more critical than ever. Manual audits simply can’t keep pace with the rapid growth and complexity of cloud resources. Steampipe—an open-source tool that transforms cloud APIs into SQL tables—offers a modern, efficient approach to continuously monitor and audit your AWS environment.

In this post, we’ll explore how to get started with Steampipe and the AWS plugin, identify security gaps, and collect comprehensive inventory details to meet compliance requirements.

What Is Steampipe?

Steampipe is an innovative tool that enables you to query cloud services and APIs using familiar SQL syntax. By converting cloud APIs into queryable relational tables, Steampipe allows you to:

• Quickly identify security misconfigurations: Run queries to detect open security groups, publicly accessible S3 buckets, and more.

• Streamline inventory management: Pull detailed resource inventories for audits and compliance.

• Integrate multiple data sources: Extend your queries beyond AWS with additional plugins (e.g., GitHub, Kubernetes).

Why Use Steampipe with AWS?

Integrating Steampipe with the AWS plugin offers several powerful benefits:

• Unified Visibility: Query data across services such as EC2, S3, IAM, RDS, and more from a single interface.

• Rapid Insights: Identify misconfigurations and vulnerabilities without sifting through multiple dashboards.

• Audit-Ready Reporting: Generate detailed, SQL-driven reports for compliance audits.

• Scalability: Easily extend your monitoring to additional cloud platforms or services with minimal setup.

Getting Started with Steampipe

Before diving into queries and reports, let’s cover the basics of installation, configuration, and initial setup.

1. Prerequisites

Ensure you have the following ready:

• AWS Account: With sufficient permissions to read the resources you wish to audit.

• AWS CLI: Installed and configured (optional but helpful for credential management).

• Basic SQL Knowledge: Familiarity with SQL will help you create and customize queries.

2. Installing Steampipe

Steampipe is available for macOS, Linux, and Windows. Choose the installation method for your operating system:

macOS (via Homebrew):

brew tap turbot/tap
brew install steampipe

Linux:

Use the installation script:

curl -sL https://steampipe.io/install | bash

Windows:

Download the installer from the Steampipe Downloads page and follow the provided instructions.

Verify the installation with:

steampipe --version

3. Installing the AWS Plugin

With Steampipe installed, add the AWS plugin to transform AWS API data into SQL tables:

steampipe plugin install aws

This command fetches the latest AWS plugin, making AWS data available for querying.

4. Configuring AWS Credentials

Steampipe utilizes the same credentials as the AWS CLI. Configure your credentials using one of the following methods:

• Environment Variables:

    export AWS_ACCESS_KEY_ID=your_access_key_id
    export AWS_SECRET_ACCESS_KEY=your_secret_access_key
    export AWS_DEFAULT_REGION=your_preferred_region
  

• AWS CLI Configuration Files:

  Run:

    aws configure
  

• IAM Roles:

  If operating from an EC2 instance or a role-enabled environment, ensure the instance has the appropriate IAM role attached.

5. Launching Steampipe

Start the interactive query console by running:

steampipe query

You will now see a prompt where you can begin executing SQL queries against your AWS resources.

Identifying Security Gaps with SQL Queries

One of Steampipe’s greatest strengths is its ability to quickly surface security issues. Here are several example queries to help you get started.

A. Identifying Open Security Groups

Open ingress rules can leave your infrastructure vulnerable. Run this query to list security groups that allow inbound access from any IP address:

select
  group_id,
  group_name,
  array_agg(ingress) as ingress_rules
from
  aws_security_group,
  lateral flatten(ingress_permissions) as ingress
where
  ingress.cidr_ip = '0.0.0.0/0'
group by
  group_id, group_name;

B. Finding buckets that do not block public access

Identify instances where AWS S3 buckets may be vulnerable due to not blocking public access. This query is useful for assessing potential security risks associated with unrestricted public access to your data:

select
  name,
  block_public_acls,
  block_public_policy,
  ignore_public_acls,
  restrict_public_buckets
from
  aws_s3_bucket
where
  not block_public_acls
  or not block_public_policy
  or not ignore_public_acls
  or not restrict_public_buckets;

Note: Depending on your AWS setup, you might need to adjust the query syntax to properly parse JSON fields.

C. Detecting IAM Users Without MFA

Multi-factor authentication (MFA) is crucial for security. Identify IAM users who have not enabled MFA:

select
  name,
  user_id,
  mfa_enabled
from
  aws_iam_user
where
  not mfa_enabled;

---

Collecting Inventory Details for Compliance

A comprehensive resource inventory is vital for both internal audits and regulatory compliance. Steampipe simplifies this process with flexible SQL queries.

A. Listing EC2 Instances

Retrieve an overview of your EC2 instances:

SELECT
    *
FROM
    aws_ec2_instance

B. Finding instances which have default security group attached

Discover the segments that have the default security group attached to them in order to identify potential security risks. This is useful for maintaining optimal security practices and ensuring that instances are not using default settings, which may be more vulnerable:

select
  instance_id,
  sg ->> 'GroupId' as group_id,
  sg ->> 'GroupName' as group_name
from
  aws_ec2_instance
  cross join jsonb_array_elements(security_groups) as sg
where
  sg ->> 'GroupName' = 'default';

C. Additional Inventory Examples

You can extend your inventory queries to cover other AWS services, such as RDS databases, Lambda functions, or CloudTrail configurations, to ensure a holistic view of your environment.

Advanced: Automating Compliance Reporting

Combine your security gap analysis and inventory queries to generate audit-ready compliance reports. Here’s how to integrate and automate this process:

1. Schedule Regular Queries:

   • Use cron jobs (or other schedulers) to run Steampipe queries at set intervals.

   • Export the results to CSV, JSON, or directly to a BI tool for further analysis.

2. Integrate with CI/CD Pipelines:

   • Embed Steampipe queries in your CI/CD pipeline to enforce security and compliance checks during deployments.

   • Automatically fail builds if critical misconfigurations are detected.

3. Alerting and Notifications:

   • Integrate with tools like Slack, Opsgenie, PagerDuty, or email notifications to alert your security team when anomalies are detected.

4. Historical Data Collection:

   • Archive query outputs to build a historical record. This audit trail can be invaluable during compliance reviews or forensic investigations.

Best Practices and Troubleshooting

Best Practices:

• Least Privilege:
  Ensure that the IAM user or role used by Steampipe has only the permissions necessary to perform read operations.

• Environment Segmentation:
  If managing multiple AWS accounts or environments (dev, test, production), use AWS Organizations and run separate Steampipe instances or queries for each account.

• Regular Updates:
  Keep both Steampipe and its plugins updated to leverage the latest features and security improvements.

• Query Optimization:
  As your queries become more complex, consider optimizing them to reduce API calls and speed up results.

Troubleshooting:

• Credential Issues:
  If queries fail, double-check your AWS credentials and region configuration. Running aws sts get-caller-identity via the AWS CLI can help verify permissions.

• Plugin Errors:
  Ensure you’re using the latest version of the AWS plugin. You can update it by running:

    steampipe plugin update aws
  

• Query Performance:
  If you experience slow query responses, consider narrowing the query scope or filtering results more aggressively.

Next Steps and Additional Resources

• Explore More Plugins:
  Steampipe supports a range of plugins for GitHub, Kubernetes, and more—expand your visibility across your entire tech stack.

• Community Engagement:
  Join the Steampipe Community to share queries, best practices, and get support from fellow users.

• Official Documentation:
  For detailed guidance on query syntax, plugin configuration, and advanced features, refer to the SteampipeDocumentation.

• Automation Examples:
  Look for open-source projects or sample scripts that demonstrate integrating Steampipe into CI/CD pipelines and compliance reporting workflows.

Conclusion

Steampipe, combined with the AWS plugin, offers a transformative way to manage and monitor your cloud infrastructure. By using SQL to query cloud APIs, you can swiftly identify security gaps, build comprehensive resource inventories, and generate audit-ready compliance reports. Whether you’re a security professional, auditor, or DevOps engineer, integrating Steampipe into your workflow provides powerful insights that keep your AWS environment secure and compliant.

Take the next step—install Steampipe, run your first query, and start securing your cloud environment, one SQL query at a time!