Multi-Cloud Security Compliance Auditing as Code using Steampipe

One of the major challenges of cloud security is that each cloud provider provides its own set of tools and services to handle the compliance auditing of the cloud environment. Learning each tool and its features can be challenging and demanding. Additionally, most of the tools are GUI-based and can’t be coded for reusability. Hence, it is critical to have tools that can be used across different cloud providers which provide standardization for multi-cloud environments.

Steampipe is a fantastic solution for the same!

Steampipe is an open-source tool for querying cloud APIs using Structured Query Language (SQL) and is written in Golang. It organizes cloud metadata into tables and fields that are simple for users to find and read. It is an exquisite and effective tool that makes interacting with many APIs easily. The software gives the team an equal playing field, while seamlessly interacting with other systems and speeding up delivery. Its live tables give you the current view of any resource right now.

Steampipe supports a plethora of plugins. In our blog, we will explore how Steampipe’s Compliance module uses query infrastructure to check for compliance with regulatory frameworks for different cloud providers and kubernetes. Additionally, steampipe provides strong community support.

Steampipe leverages PostgreSQL Foreign Data Wrappers to provide a SQL interface to external services and systems. It uses an embedded PostgreSQL database (currently, version 14.2.0), and you can use standard Postgres syntax to query Steampipe.​ By default, when you run the steampipe query, Steampipe will start the database and shut it down at the end of the query command or session.

The database only listens to the loopback address (127.0.0.1). During this time, Steampipe runs in service mode. Steampipe listens on port 9193 and should be allowed on the firewall list of the host from which the query is executed.

When a user writes a Steampipe SQL query, Steampipe translates it into API calls that are executed in real-time across one or more cloud service APIs. The data returned is organized into tables using a PostgreSQL Foreign Data Wrapper (FDW); allowing the user to join, filter, and aggregate the data just like any other database table.

Steampipe isn’t just a compliance auditing tool. It has many plugins to handle different use cases to fetch information and inventory.

How Steampipe works

The purpose of Steampipe is to streamline the process of finding and requesting configuration data stored in the cloud. Your cloud configuration is exposed by Steampipe as a high-performance relational database.

This allows you to investigate the live configuration of operating cloud assets without leaving the current environment. SQL tables in Steampipe represent complicated cloud resources such as Compute, IAM policies, network security groups, databases, storage buckets, SSL certificates, and so on.

How Steampipe Query works

To get the query working, we need to first install the corresponding cloud plugin and then get the authentication to the respective cloud provider enabled.

• Each plugin contains the tables ​
• To List, all the available tables, use “.tables” in the steampipe query.​
• To inspect the fields in the tables, use “.inspect <tablename>” ​
• To query from a specific table

select * from oci_core_instance
select * from aws_ec2_instance where instance_type=’t2.micro’ and instance_state=’running‘

Install Steampipe

Installation of steampipe is straightforward and please refer to this link for installation steps.

Authentication and Authorization with a cloud provider

• Steampipe uses the default methods of getting credentials from the credential file and/or environment variables.
• Uses the keys present at the default location (.aws/config, .oci/config) or environment variables preset in the current user’s account.
• It uses the privilege of the API keys and runs the SQL queries against the cloud account.
• If the API keys have limited privilege towards the cloud, Steampipe compliance checks will fail on the specific queries due to authorization issues.

Steampipe Mods

Steampipe Mods are collections of named queries and coded controls that can be used to test the current configuration of your cloud resources against the desired configuration.

Please find the list of compliance security mods for the major cloud providers

AWS

• AWS Audit Manager Control Tower Guardrails
• CIS v1.3.0
• CIS v1.4.0
• CISA Cyber Essentials
• FedRAMP Low Revision 4
• FedRAMP Moderate Revision 4
• Federal Financial Institutions Examination Council (FFIEC)
• AWS Foundational Security Best Practices
• General Data Protection Regulation (GDPR)
• GxP 21 CFR Part 11
• GxP EU Annex 11
• HIPAA
• NIST 800–171 Revision 2
• NIST 800–53 Revision 4
• NIST 800–53 Revision 5
• NIST Cybersecurity Framework (CSF) v1.1
• Other Compliance Checks
• PCI v3.2.1
• RBI Cyber Security Framework
• SOC 2

Please refer to the link for aws steampipe compliance git repo

Azure

• CIS v1.3.0
• CIS v1.4.0
• HIPAA HITRUST 9.2
• NIST SP 800–53 Revision 5

Please check the link for azure steampipe compliance git repo

Kubernetes

• CIS Kubernetes v1.20
• Extra Checks
• NSA and CISA Kubernetes Hardening Guidance v1.0

Please browse this link for kubernetes steampipe compliance git repo

GCP

• CFT Scorecard v1
• CIS v1.2.0
• CIS v1.3.0
• Forseti Security v2.26.0

Please refer to the link for GCP steampipe compliance git repo

OCI

• CIS v1.1.0

OCI Cloud has lesser compliance checks. Checks for services like OKE are missing. As part of our project, we conduct additional compliance checks.

Please refer to the link for oci steampipe compliance git repo

SnowFlake DataCloud

• Snowflake Security Overview and Best Practices

Please refer to the link for snowflake steampipe compliance git repo

Terraform with OCI Cloud

• Block Storage
• Cloud Guard
• Compute
• Database
• File Storage
• Identity and Access Management
• Object Storage
• VCN

Integration with Different Cloud Providers

Please refer to the below links on how to get Steampipe integrated with different cloud providers to run the queries.

AWS

Azure

ORACLE CLOUD

Terraform

Unfortunately, due to missing features, Terraform compliance checks will happen only against raw Tf files and won’t work for Terraform modules.

Kubernetes

GCP

Snowflake Data Cloud

Steampipe Queries Type

List

List all the columns

select * from oci_core_instance

Required List

List required columns

select display_name, id, lifecycle_state from oci_core_instance;

Filter

Filter the query with a matching value

select display_name, id, lifecycle_state from oci_core_instance where shape = ‘VM.Standard.E3.Flex

Range

Filter the query with a range of values

select display_name, id, lifecycle_state from oci_core_instance where shape in (‘VM.Standard.E3.Flex’, ‘VM.Standard.E2.8’);

And/OR

Filter multiple columns — Using “and” or “or”

select display_name, id, lifecycle_state from oci_core_instance where shape = ‘VM.Standard.E3.Flex’ and lifecycle_state = ‘RUNNING;

Sort The Results

select display_name,lifecycle_state,shape from oci_core_instance where lifecycle_state = ‘RUNNING’ and fault_domain = ‘FAULT-DOMAIN-3’ order by display_name;

Sort By Asc/Desc: Sort by column ascending or descending

select display_name, id, kms_key_id from oci_core_boot_volume order by display_name desc;

Count the results

select user_name,count(*) from oci_identity_api_key where lifecycle_state = ‘ACTIVE’ group by user_name;

Left Join

select
display_name,
region,
coalesce(oci_identity_compartment.name, ‘root’) as compartment
from oci_core_boot_volume
left join oci_identity_compartment
on oci_core_boot_volume.compartment_id =
oci_identity_compartment.id

Steampipe Demo — Reference Cloud

To explain the concept around Steampipe, we will be leveraging the OCI cloud platform.

> select
— Required Columns
m.id as resource,
case
when s.prohibit_public_ip_on_vnic is false then ‘alarm’
else ‘ok’
end as status,
case
when s.prohibit_public_ip_on_vnic is false then ‘DB system subnet is not private’
else ‘DB system subnet is private’
end as reason,
— Additional Dimensions
m.display_name
from
oci_mysql_db_system as m
left join oci_core_subnet as s on m.subnet_id = s.id
where
m.lifecycle_state = ‘ACTIVE’;
+ — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — + — — — — + — — — — — — — — — — — — — — -+ — — — — — — — — — — — — — — — — — -+
| resource | status | reason | display_name |
+ — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — + — — — — + — — — — — — — — — — — — — — -+ — — — — — — — — — — — — — — — — — -+
| ocid1.mysqldbsystem.oc1.me-jeddah-1.bbbbbbbbkrt7chnq6ccvspdcaeslrcrof425z65ycsn7uifevdekgajqeuva | ok | DB system subnet is private | data-test-tgs-mysql-db |
| ocid1.mysqldbsystem.oc1.me-jeddah-1.bbbbbbbbhg32pgmpvp6qnsusufevps5vm6mrovnspqpqruw6tiowe4c2vnbq | ok | DB system subnet is private | data-test-tgs-mysql-spinnakerdb |
| ocid1.mysqldbsystem.oc1.me-jeddah-1.bbbbbbbbtxshwjlzredz62syvrmgbtyhgv4gnmxdrwyzo72yecg3j5ck324q | ok | DB system subnet is private | data-bld-tgs-mysql-keycloak-db |
| ocid1.mysqldbsystem.oc1.me-jeddah-1.bbbbbbbbhfaisuhqpmk2j5xqm4xusxs37lcoxr3slmh4ztgjlbgyh5wmfmaa | ok | DB system subnet is private | data-bld-tgs-mysql-db |
+ — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — + — — — — + — — — — — — — — — — — — — — -+ — — — — — — — — — — — — — — — — — -+

Steampipe enables query parallelism in three ways

• across different sub-APIs. In this case, a primary API call gets resources and a secondary API call gets status. The two calls happen in parallel.
• across regions. When your tenancy is configured for multiple regions, Steampipe queries across them in parallel.
• Across accounts. You can bundle a set of OCI tenancy and query across those in parallel. Please refer to the Sample.

All these mechanisms come into play when you install and use the Compliance mod.

How to execute the corresponding benchmarks

Please clone the corresponding cloud “mod” compliance repo

git clone https://github.com/turbot/steampipe-mod-oci-compliance
cd steampipe-mod-oci-compliance

This workspace directory contains the resources — named queries, controls, and benchmarks. When executed, Steampipe will discover the resources and use them to check against the corresponding compliance framework.

In order to execute the compliance check for OCI CIS v1.1.0 below command needs to be invoked. The report will be generated in HTML format.

steampipe check control.cis_v110_2_1 — export=cis_v110_2_1.html

Alternatively, we can use — export=cis_v110_2_1.md for Markdown.

Steampipe HTML Report

Additionally, we can use — export=cis_v110_2_1.csv or — export=cis_v110_2_1.json to capture the results in raw data format.

Nitty Gritties of a Steampipe Compliance Benchmark Check

The compliance repo (in the case of oci cloud, it is steampipe-mod-oci-compliance and refers to other cloud providers repo) contains multiple sub-directories, each referring to different security standards and frameworks. Each of the sub-directories contains a set of .sp files which contain the benchmarks and the control definition. The .sp files are written in the HashiCorp configuration language.

Each control runs queries to check a specific compliance condition like “Ensure IAM password policy requires minimum length of 14 or greater”.

Under cis_v110 directory, the file cis.sp enumerates over the benchmarks that are defined in the files section_1.sp, section_2.sp, etc.

Each file further defines the set of controls

locals {
cis_v110_1_common_tags = merge(local.cis_v110_common_tags, {
cis_section_id = “1”
})
}
benchmark “cis_v110_1” {
title = “1 Identity and Access Management”
documentation = file(“./cis_v110/docs/cis_v110_1.md”)
children = [
control.cis_v110_1_1,
control.cis_v110_1_4,
control.cis_v110_1_7,
control.cis_v110_1_8,
control.cis_v110_1_9,
control.cis_v110_1_10,
control.cis_v110_1_11,
control.cis_v110_1_12,
]
tags = merge(local.cis_v110_1_common_tags, {
service = “OCI/Identity”
type = “Benchmark”
})
}

Here are the key elements of one of those controls.

control “cis_v110_1_4” {
title = “1.4 Ensure IAM password policy requires minimum length of 14 or greater”
description = “Password policies are used to enforce password complexity requirements. IAM password policies can be used to ensure passwords are at least a certain length and are composed of certain characters. It is recommended the password policy requires a minimum password length of 14 characters and contains 1 non-alphabetic character (Number or ‘Special Character’).”
sql = query.identity_authentication_password_policy_strong_min_length_14.sql
documentation = file(“./cis_v110/docs/cis_v110_1_4.md”)
tags = merge(local.cis_v110_1_common_tags, {
cis_item_id = “1.4”
cis_level = “1”
cis_type = “manual”
service = “OCI/Identity”
})
}

Controls can include SQL queries directly, but most often they refer to named queries that live in the query subdirectory of the mod’s repo. Here’s a query-

query/identity/identity_authentication_password_policy_strong_min_length_14.sql.

Running Compliance checks at scale against different cloud environments

The Compliance mod comprises multiple frameworks for each cloud provider. They define multiple benchmarks, which in turn define multiple controls that refer to named queries. The numbers keep growing. When running one or more benchmarks, an additional level of parallelism happens. Steampipe will run up to 5 controls in parallel. When controls need the same query results, as is typical, the first control to run a query caches the results for others that follow.

Run all benchmarks

steampipe check all

To run a single benchmark

steampipe check oci_compliance.benchmark.cis_v110

Run a specific control

steampipe check control.cis_v110_2_1

Additionally, you can run the Steampipe dashboard to run the benchmarks

steampipe dashboard

Alternatively, the dashboard interface will then be launched in a new browser window at https://localhost:9194. you can run benchmarks by selecting one or searching for a specific one from the dashboard.

Kubernetes Compliance Check

CIS Kubernetes Benchmarks provide a predefined set of compliance and security best-practice checks for Kubernetes clusters.

Creating custom controls

We are trying to create a custom control to check if the image policy is enabled for the OKE cluster. Please check the link.

create okecontrols.sp in the corresponding benchmark folder

control “cis_kube_adc_v1_1_2” {
title = “1.2 Images should be verified against the configured policy at runtime”
sql = query.is_image_policy_config_enabled.sql
}

Under the query folder, create the is_image_policy_config_enabled.sql

select
— Required Columns
ce.id as resource,
case
when ce.image_policy_config_enabled is false then ‘alarm’
else ‘ok’
end as status,
case
when ce.image_policy_config_enabled is false then ‘Image Policy is not enabled’
else ‘Image Policy is enabled’
end as reason,
— Additional Dimensions
ce.name
from
oci_containerengine_cluster as ce
where
ce.lifecycle_state = ‘ACTIVE’;

We can run the test-

steampipe check control.okeControls.sp — export okeControls.html

Here’s the HTML report

Steampipe’s Pros and Cons

Pros

• It is open source
• No code, easily written SQL queries.
• Multi-format support such as JSON and CSV.
• Custom Benchmarks
• Strong community support ​
• Output in table format
• Support for Multi-cloud compliance​

Cons

• While using steampipe, you can unintentionally hit the API rate limits of the web services that you were querying.
• It doesn’t support inserting or updating queries to remediate the state of the resource

Conclusion

Steampipe is an open-source tool that helps in the standardization of auditing for multi-cloud environments. It reduces the learning curve for several platforms by working across multi-cloud environments. Steampipe leverages the power of SQL and provides ease of experience. It is the best way to enable users to query cloud APIs and build compliance controls. It organizes cloud metadata into tables and fields that are simple to find and read.

Usually, one needs to comply with a variety of security and governance frameworks for different cloud workloads. Steampipe comes to the rescue! The software and its plugins provide the data to support compliance checks. The Compliance mod defines hundreds of controls that use the data to check compliance. On top of it, one can query the different cloud provider plugins with SQL, run named SQL queries from the command line, and embed SQL in controls.

Thus, by using Steampipe and its plugins, one can smoothly work on multi-cloud environments with ease, using a simplified language to fulfill their varied tasks.

Additional Reference

Intro to steampipe

Intro to steampipe dashboard

Steampipe plugins

Intro to steampipe on oci