How to Build an Open Source CSPM with CloudQuery, PostgreSQL and Grafana
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.
- ETL (Extract-Transform-Load) ingestion layer: CloudQuery (opens in a new tab)
- Datastore: PostgreSQL
- Policies: Standard SQL Policies to be executed via
- Data Visualization and Exploration Platform: Grafana
- Raw SQL access to all your cloud asset inventory, open source SQL based policies.
- 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.
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 to AWS on RDS Aurora and EKS at github.com/cludquery/terraform-aws-cloudquery (opens in a new tab) , or GCP and Cloud SQL at https://github.com/cloudquery/terraform-gcp-cloudquery (opens in a new tab)
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:
- Self-hosted (local, docker, k8s): Official guide. (opens in a new tab)
- SaaS/managed: Grafana.com (opens in a new tab)
- AWS Managed Grafana: https://aws.amazon.com/grafana/ (opens in a new tab)
CloudQuery policies and rules are implemented in pure SQL and they store results in a single table that you can easily query and visualize. Here is a link to all available policies and compliance frameworks. In this section we will go quickly through how to run multiple benchmarks for AWS.
git clone https://github.com/cloudquery/cloudquery.git cd cloudquery/plugins/source/aws/policies # change the DSN to your PostgreSQL instance populated by CloudQuery psql postgres://postgres:pass@localhost:5432/postgres -f policy.sql
This should run all available compliance framework and store the results in aws_policy_results. Now you can query the table 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. We prepared a pre-built dashboard for Grafana that you can check out here (opens in a new tab):
That’s it! Now you have fully functional CSPM (KSPM, or any other SPM) with those nice bonuses:
Access to raw data available and stored in your PostgreSQL.
Policies easily customizable and defined in pure SQL.