Auditing AWS compliance using Steampipe and SQL

Auditing AWS compliance using Steampipe and SQL

Steampipe is an open-source tool under the AGPLv3 license for querying cloud APIs in a universal way and reasoning about the data in SQL. In our first post we explored the AWS plugin which maps the suite of AWS APIs to (currently) 274 Postgres tables. You can use these to query individual AWS services, join across them (spanning regions and accounts), and join AWS data with queries against many 3rd-party APIs.

In this post we’ll explore how Steampipe’s Compliance “mod” uses that query infrastructure to check for compliance with regulatory frameworks. It provides 448 controls and 233 named queries that deliver comprehensive support for these standards:

(Pic Credit - Steampipe)

Steampipe is an amazing open-source querying tool and an asset inventory dashboard. The tool helps us understand and manage a diverse infrastructure, by allowing us to find where who and what are the cloud resources in a location-independent manner.

I personally find Steampipe an amazing open-source querying tool and dashboard.

Steampipe gives us a centralized place to view cloud resources that can be visualized with interactive dashboards or in tabular form. It also enables non-coders to get insights into cloud environments.

This product solves the context-switching problem, where we have to bounce back and forth between different tools and tabs to understand our environment. But most importantly it gives us a central point of access for data visualization, where we can visualize all the data in one place!

It requires only basic SQL knowledge to query data and visualize it in a graphical way, by providing an interactive dashboard.

In this blog post, we will cover an open-source tool named Steampipe used to monitor cloud infrastructure.

Cloud asset management provides complete visibility and control over the assets and infrastructure that make up the cloud environment. It enables us to keep track of all the services used in the cloud environment, as well as monitor and manage each level of infrastructure usage in real time.

Installation of steampipe

STEP 1: Copy and paste into your Linux shell

sudo /bin/sh -c "$(curl -fsSL https://raw.githubusercontent.com/turbot/steampipe/main/install.sh)"

STEP 2: Version check

steampipe -v

Output:

dipak.patil@BETWA:~$ steampipe -v
steampipe version 0.15.4

STEP 3: Install your first plugin

steampipe plugin install aws

Run a steampipe query to open an interactive query session:

steampipe query

Output:

dipak.patil@BETWA:~$ steampipe query

+-------------------------------------------------------------------+
|                                                                   |
| A new version of Steampipe is available! 0.15.4 → 0.18.1          |
| You can update by downloading from https://steampipe.io/downloads |
|                                                                   |
|                                                                   |
| Updated versions of the following plugins are available:          |
|                                                                   |
|   turbot/aws       @ latest               0.78.0 → 0.92.0         |
|   turbot/steampipe @ latest                0.5.0 → 0.6.0          |
|                                                                   |
| You can update by running steampipe plugin update --all           |
|                                                                   |
+-------------------------------------------------------------------+

Welcome to Steampipe v0.15.4
For more information, type .help

Now run the .tables meta-command to list the available tables:

> .tables
 ==> aws
+--------------------------------------------------------------+--------------------------------------------------------------------+
| table                                                        | description                                                        |
+--------------------------------------------------------------+--------------------------------------------------------------------+
| aws_accessanalyzer_analyzer                                  | AWS Access Analyzer                                                |
| aws_account                                                  | AWS Account                                                        |
| aws_account_alternate_contact                                | AWS Account Alternate Contact                                      |
| aws_account_contact                                          | AWS Account Contact                                                |
| aws_acm_certificate                                          | AWS ACM Certificate                                                |
| aws_amplify_app                                              | AWS Amplify App                                                    |
| aws_api_gateway_api_key                                      | AWS API Gateway API Key                                            |
| aws_api_gateway_authorizer                                   | AWS API Gateway Authorizer                                         |
| aws_api_gateway_rest_api                                     | AWS API Gateway Rest API                                           |
| aws_api_gateway_stage                                        | AWS API Gateway Stage                                              |
| aws_api_gateway_usage_plan                                   | AWS API Gateway Usage Plan                                         |
| aws_api_gatewayv2_api                                        | AWS API Gateway Version 2 API                                      |
| aws_api_gatewayv2_domain_name                                | AWS API Gateway Version 2 Domain Name                              |
| aws_api_gatewayv2_integration                                | AWS API Gateway Version 2 Integration                              |
| aws_api_gatewayv2_stage                                      | AWS API Gateway Version 2 Stage                                    |
| aws_appautoscaling_target                                    | AWS Application Auto Scaling Target                                |
| aws_appconfig_application                                    | AWS AppConfig Application                                          |
| aws_auditmanager_assessment                                  | AWS Audit Manager Assessment                                       |
| aws_auditmanager_control                                     | AWS Audit Manager Control                                          |
| aws_auditmanager_evidence                                    | AWS Audit Manager Evidence                                         |
| aws_auditmanager_evidence_folder                             | AWS Audit Manager Evidence Folder                                  |
| aws_auditmanager_framework                                   | AWS Audit Manager Framework                                        |
| aws_availability_zone                                        | AWS Availability Zone                                              |

Managing Steampipe Multi-Region(AWS) Connections

A Steampipe connection represents a set of tables for a single data source. Each connection is represented as a distinct Postgres schema.

A connection is associated with a single plugin type. The boundary/scope of the connection varies by plugin, but is typically aligned with the vendor's cli tool and/or api. For example:

An azure connection contains tables for a single Azure subscription A google connection contains tables for a single GCP project An aws connection contains tables for a single AWS account

All standard regions>>

sudo vim  ~/.steampipe/config/aws.spc

Paste below mentioned configurations in ~/.steampipe/config/aws.spc file

connection "aws" {
  plugin  = "aws"
  regions = ["*"]
}

All standard US and EU regions >>

Paste below mentioned configurations in ~/.steampipe/config/aws.spc file

connection "aws" {
  plugin  = "aws"
  regions = ["us-*", "eu-*"]
}

Multi-Account Connections for AWS and Streampipe

When you configure Steampipe for multiple accounts, each account must have an AWS profile associated with it. Each profile contains a unique set of configuration variables that are specific to that account.

AWS credential file:

The shared AWS config and credentials files are plaintext files that reside by default in a folder named .aws that is placed in the "home" folder on your computer.

On Linux and macOS, this is typically shown as ~/.aws

Default location of the file:

~/.aws/config

~/.aws/credentials

AWS credential file configuration :

[account_a]
aws_access_key_id = your AWS account access key id
aws_secret_access_key = your AWS account secret access key id
region = us-west-2

[account_b]
aws_access_key_id = AKIA4YFAKEKEYJ7HS98F
aws_secret_access_key = Apf938vDKd8ThisIsNotRealzTiEUwXj9KliWP9mg4

aws.spc:

The AWS plugin will install the ~/.steampipe/config/aws.spc configuration file. This file contains a single AWS connection definition that configures the plugin to use the same configuration as the AWS CLI.

connection "aws_account_a" {
  plugin  = "aws"
  profile = "account_a"
  regions = ["us-east-1", "us-west-2"]
}

connection "aws_account_b" {
  plugin  = "aws"
  profile = "account_b"
  regions = ["ap-southeast-1", "ap-southeast-2"]
}

AWS Compliance:-

Steampipe is a cloud security solution that allows you to easily manage AWS compliance. With Steampipe, you can easily leverage powerful underlying monitoring and analytics tools to gain visibility into your AWS resources, track usage and monitor compliance with regulatory obligations.

The AWS Compliance mod includes 360+ queries:

hub.steampipe.io/mods/turbot/aws_compliance..

Example 1: Root account MFA is enabled or not?

Query: iam_root_user_mfa_enabled

aws_iam_account_summary

SQL Query - You have to run the below query in the steampipe terminal

select
  -- Required Columns
  'arn:' || partition || ':::' || account_id as resource,
  case
    when account_mfa_enabled then 'ok'
    else 'alarm'
  end status,
  case
    when account_mfa_enabled then 'MFA enabled for root account.'
    else 'MFA not enabled for root account.'
  end reason,
  -- Additional Dimensions
  account_id
from
  aws_iam_account_summary;

Output:

+------------------------+--------+-------------------------------+--------------+
| resource               | status | reason                        | account_id   |
+------------------------+--------+-------------------------------+--------------+
| arn:aws:::123456789101 | ok     | MFA enabled for root account. | 123456789101 |
+------------------------+--------+-------------------------------+--------------+

Example 2: IAM user MFA is enabled or not?

Query: iam_user_mfa_enabled

select
  -- Required Columns
  user_arn as resource,
  case
    when not mfa_active then 'alarm'
    else 'ok'
  end as status,
  case
    when not mfa_active then user_name || ' MFA device not configured.'
    else user_name || ' MFA device configured.'
  end as reason,
  -- Additional Dimensions
  account_id
from
  aws_iam_credential_report;

Output:

+---------------------------------------------------------------+--------+----------------------------------------------------------+---------->
| resource                                                      | status | reason                                                   | account_i>
+---------------------------------------------------------------+--------+----------------------------------------------------------+---------->
| arn:aws:iam::123456789101:user/Deepak.Patil                   | alarm  | Deepak.Patil MFA device not configured.                  | 123456789>
| arn:aws:iam::123456789101:user/Piyush.Chaudhari               | alarm  | Piyush.Chaudhari MFA device not configured.              | 123456789>
| arn:aws:iam::123456789101:user/Akash.Melkari                  | ok     | Akash.Melkari  MFA device configured.                    | 123456789>
+------------------------------------------------------------------------------------------------------------------------------------------------

For more IAM-related queries visit:hub.steampipe.io/mods/turbot/aws_compliance..

Example 3: Check whether the default EBS encryption is enabled or disabled

Query: ec2_ebs_default_encryption_enabled

select
  -- Required Columns
  'arn:' || partition || '::' || region || ':' || account_id as resource,
  case
    when not default_ebs_encryption_enabled then 'alarm'
    else 'ok'
  end as status,
  case
    when not default_ebs_encryption_enabled then region || ' default EBS encryption disabled.'
    else region || ' default EBS encryption enabled.'
  end as reason,
  -- Additional Dimensions
  region,
  account_id
from
  aws_ec2_regional_settings;

Output:

+----------------------------------+--------+---------------------------------------------+------------+--------------+
| resource                         | status | reason                                      | region     | account_id   |
+----------------------------------+--------+---------------------------------------------+------------+--------------+
| arn:aws::ap-south-1:123456789101 | alarm  | ap-south-1 default EBS encryption disabled. | ap-south-1 | 123456789101 |
+----------------------------------+--------+---------------------------------------------+------------+--------------+

Note:

  • When a state is disabled and is not in the enabled state, it is declared to be in an alarm state.

  • When the state is enabled, then the status is considered to be a normal info state.

Example 3: Is EC2 instance termination protection enabled or not?

Query: ec2_instance_termination_protection_enabled

select
  -- Required Columns
  arn as resource,
  case
    when disable_api_termination then 'ok'
    else 'alarm'
  end status,
  case
    when disable_api_termination then instance_id || ' termination protection enabled.'
    else instance_id || ' termination protection disabled.'
  end reason,
  -- Additional Dimensions
  region,
  account_id
from
  aws_ec2_instance;

Output:

+------------------------------------------------------------------+--------+------------------------------------------------------+----------->
| resource                                                         | status | reason                                               | region    >
+------------------------------------------------------------------+--------+------------------------------------------------------+----------->
| arn:aws:ec2:ap-south-1:123456789101:instance/i-04dbb3d1881b7880b | alarm  | i-04dbb3d1881b7888b termination protection disabled. | ap-south-1>
| arn:aws:ec2:ap-south-1:123456789101:instance/i-0b98d1ae49bce04d0 | ok     | i-0b98d1ae49bce4440 termination protection enabled.  | ap-south-1>
+-----------------------------------------------------------------------------------------------------------------------------------------------

For more EC2-related queries visit:hub.steampipe.io/mods/turbot/aws_compliance..

Example 4:Is S3 bucket MFA deletes enabled or not?

Query: s3_bucket_mfa_delete_enabled

select
  -- Required Columns
  arn as resource,
  case
    when versioning_mfa_delete then 'ok'
    else 'alarm'
  end status,
  case
    when versioning_mfa_delete then name || ' MFA delete enabled.'
    else name || ' MFA delete disabled.'
  end reason,
  -- Additional Dimensions
  region,
  account_id
from
  aws_s3_bucket;

Output:

+---------------------------------------------------------------+--------+--------------------------------------------------------------------->
| resource                                                      | status | reason                                                              >
+---------------------------------------------------------------+--------+--------------------------------------------------------------------->
| arn:aws:s3:::my-test-bucket123                                | alarm  | my-test-bucket123 MFA delete disabled.                              >
| arn:aws:s3:::my-test-bucket456                                | alarm  | my-test-bucket456 MFA delete disabled.                              >
+---------------------------------------------------------------------------------------------------------------------------------------------->

For more S3 quires visit :hub.steampipe.io/mods/turbot/aws_compliance..

Steampipe helps us understand our cloud environment better and make our environment more efficient by analyzing usage data in real-time.

AWS has five mods present in Steampipe. These mods are on the Steampipe Hub and can be shared with anyone through any git repository. You can start the dashboard server and view dashboards with the steampipe dashboard command.

Steampipe will start the dashboard server on your local machine on the following URL:

http://localhost:9194/

To install the AWS compliance mod, run the command:

  1. Clone the repository using the command: git clonegithub.com/turbot/steampipe-mod-aws-complia..

  2. Change the directory using the command: cd steampipe-mod-aws-compliance/

  3. Then run the steampipe dashboard command to start the dashboard.

dipak.patil@BETWA:~/steampipe-mod-aws-compliance$ steampipe dashboard
[ Wait    ] Loading Workspace
[ Wait    ] Starting Dashboard Server
[ Message ] Workspace loaded
[ Message ] Initialization complete
[ Ready   ] Dashboard server started on 9194 and listening on local
[ Message ] Visit http://localhost:9194
[ Message ] Press Ctrl+C to exit
[ Wait    ] Dashboard execution started: aws_compliance.benchmark.soc_2
[ Ready   ] Execution complete: aws_compliance.benchmark.soc_2

We must be in the directory where the mod files are present (In this case, the steampipe-mod-aws-compliance directory). The dashboard will run from there.

Let’s run a quick CIS benchmark test against our AWS account.

Errors and Troubleshooting:

Error: 1 mod plugin requirement not satisfied.

dipak.patil@BETWA:~$ cd steampipe-mod-aws-compliance/
dipak.patil@BETWA:~/steampipe-mod-aws-compliance$ steampipe dashboard
[ Wait    ] Loading Workspace
Error: 1 mod plugin requirement not satisfied. 

  turbot/aws  0.78.0  →  0.92.0

Solution - Uninstall the older plugin - aws@0.78.0

dipak.patil@BETWA:~/steampipe-mod-aws-compliance$ steampipe plugin uninstall aws@0.78.0

Uninstalled plugin:
* turbot/aws

We are now using the steampipe plugin version aws@0.92.0. This version has been updated to support dashboards.

dipak.patil@BETWA:~/steampipe-mod-aws-compliance$ steampipe plugin uninstall aws@0.92.0

Uninstalled plugin:
* turbot/aws

Use the below command to check the plugin list.

steampipe plugin list

dipak.patil@BETWA:~/steampipe-mod-aws-compliance$ steampipe plugin list
+--------------------------------------------------+---------+-------------+
| Name                                             | Version | Connections |
+--------------------------------------------------+---------+-------------+
| hub.steampipe.io/plugins/turbot/aws@0.92.0       | 0.92.0  |             |
| hub.steampipe.io/plugins/turbot/steampipe@latest | 0.5.0   | steampipe   |
+--------------------------------------------------+---------+-------------+

Visit:steampipe.io

hub.steampipe.io/mods/turbot/aws_compliance

Conclusion:

Steampipe’s AWS plugin provides the data to support compliance checks. The Compliance mod defines hundreds of controls that use that data to check compliance with all the major frameworks.

  • Happy Learning...!!

  • Thank you for taking the time to read this blog...!!

  • If you find this blog helpful share it with your connection.

  • Follow me for more content like this.

Linkedin - Deepak Patil (DevOps Associate @ Aurochs Software)

Did you find this article valuable?

Support DEEPAK PATIL by becoming a sponsor. Any amount is appreciated!