How to Build an Open Source CSPM with CloudQuery, PostgreSQL and Grafana

Yevgeny Pats
Name
Yevgeny Pats
Twitter
@yevgenypats

In this guide, we will walk you through how to set up CloudQuery (opens in a new tab) to build your own customizable compliance, CSPM (Cloud Security Posture Management) dashboard with PostgreSQL and Grafana.

CSPMs are probably the biggest offenders of yet-another-dashboard syndrome, and at CloudQuery we believe it’s time to unbundle those, applying the best practices in data engineering and the modern data stack to cloud security.

General Architecture

What you will get

  • Raw SQL access to all your cloud asset inventory.
  • Multi-Cloud Asset Inventory: Ingest configuration from all your clouds to a single datastore with a unified structure.
  • Avoid yet-another-dashboard fatigue: Reuse your existing BI/Visualization stack (Grafana in this example) to build an open source CSPM.

Building the CSPM step by step

Step 1: Install or deploy CloudQuery

If it’s your first time using CloudQuery we suggest you first run it locally to get familiar with the tool. Take a look at our quick start guide.

If you are already familiar with CloudQuery, take a look at how to deploy it with Kubernetes, on AWS ECS using Fargate or with Google Cloud Run. For more deployment guides, see our Deployment Overview.

Step 2: Install Grafana

Grafana is a well-known open source observability and visualization tool. It is open source, so there are a number of ways to deploy it:

Step 3: Install dbt

CloudQuery policies and rules are implemented using dbt (opens in a new tab), a popular open source tool for data transformation.

After the installation, you will need to provide dbt with a profile which defines how to connect to relevant databases. By default, it searches for a profiles.yml file in the local directory and falls back to ~/.dbt/. Read more about profiles in the dbt documentation (opens in a new tab).

Your profiles.yml file should look like this:

config:
  send_anonymous_usage_stats: False
  use_colors: True

aws_compliance: # this should match the profile name in your dbt_project.yml, see step 5.
  target: postgres
  outputs:
    postgres:
      type: postgres
      host: "your postgres host"
      user: "postgres user name"
      pass: "postgres password"
      port: 5432
      dbname: "database name"
      schema: public
      threads: 4

Step 4: Configure and run CloudQuery sync

You will need to configure CloudQuery to sync your cloud assets to your PostgreSQL instance. For AWS assets, you will need the AWS Plugin (opens in a new tab). Policies are also available for GCP (opens in a new tab), Azure (opens in a new tab), and Kubernetes (opens in a new tab).

To fully define the configuration, you will need to specify what tables to sync. This depends on the policy you want to use. For example, to use AWS Compliance (free) (opens in a new tab), you will need to sync the following tables:

    - aws_cloudwatch_alarms
    - aws_cloudwatchlogs_metric_filters
    - aws_ec2_network_acls
    - aws_ec2_security_groups
    - aws_sns_subscriptions
    - aws_iam_credential_reports
    - aws_iam_password_policies
    - aws_iam_user_access_keys
    - aws_iam_users
    - aws_autoscaling_groups
    - aws_cloudtrail_trail_event_selectors
    - aws_cloudtrail_trails
    - aws_codebuild_projects
    - aws_config_configuration_recorders
    - aws_apigateway_rest_api_stages
    - aws_apigateway_rest_apis
    - aws_apigatewayv2_api_routes
    - aws_apigatewayv2_api_stages
    - aws_apigatewayv2_apis
    - aws_cloudfront_distributions
    - aws_efs_access_points
    - aws_elasticbeanstalk_environments
    - aws_elbv1_load_balancers
    - aws_elbv2_load_balancer_attributes
    - aws_elbv2_load_balancers
    - aws_iam_accounts
    - aws_rds_clusters
    - aws_s3_accounts

To see what other transformations are available and what tables are required, visit the individual Transformations (opens in a new tab) documentation pages.

Step 5: Run policies (CSPM - Cloud Security Posture Management)

Download the AWS Compliance (free) (opens in a new tab) and extract the package.

Navigate to the extracted directory where the dbt project file (dbt_project.yml) resides. Check that the profile: aws_compliance matches the profile name in your profiles.yml file that you configured in step 3.

Before executing the dbt run command, it might be useful to check for any potential issues:

dbt compile

If everything compiles without errors, you can then execute:

dbt run

This command will run all the dbt models and create views in your destination database as defined in the models.

Now you can query the views directly and export in various formats such as CSV or HTML, all with standard psql, and of course visualize them in your favorite BI tool.

You can repeat this for other cloud providers by adding additional plugins to your syncs and running the relevant transformations.

Step 6: Visualize in Grafana

For AWS Transformations, we offer free Grafana dashboards that you can use as a starting point.

Download AWS Compliance Dashboard (opens in a new tab) and extract the zip file. Find the dashboard.json file in the extracted directory (in aws_compliance/grafana/postgres) and import (opens in a new tab) it into your Grafana instance.

At the top of the dashboard, select the data source to be the PostgreSQL database with data synced by CloudQuery.

Now you should see a dashboard similar to this:

See our Hub (opens in a new tab) for additional dashboards.

Summary

That’s it! Now you have fully functional CSPM (KSPM, or any other SPM) with access to raw data available and stored in your PostgreSQL. Don't forget to run the syncs regularly to get fresh data. You only need to run the transformations when you want to update to a newer version.