security
tutorial

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

Yevgeny Pats

Yevgeny Pats Mar 12, 2023

In this guide, we will walk you through how to set up CloudQuery 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

  • ETL (Extract-Transform-Load) ingestion layer: CloudQuery
  • Datastore: PostgreSQL
  • Transformations (policies): dbt Transformations to be executed via dbt
  • Data Visualization and Exploration Platform: Grafana

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

You can get started with CloudQuery without any installation or deployment using our Managed Syncs.
If you prefer to 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 run a , 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, 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.
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. Policies are also available for GCP, Azure, and Kubernetes.
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), 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 documentation pages.

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

Download the AWS Compliance (free) 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 and extract the zip file. Find the dashboard.json file in the extracted directory (in aws_compliance/grafana/postgres) and import 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 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.
Subscribe to product updates

Be the first to know about new features.