Ingest AWS Resource Metadata into Postgres Using CloudQuery for Security and Governance Analysis

Turn live AWS configuration into a queryable database. This blueprint uses CloudQuery to extract AWS resource metadata (S3 buckets in this example), loads it into PostgreSQL, and runs SQL to surface security and governance risks such as buckets that allow public access. It replaces ad-hoc console clicks and one-off CLI checks with a repeatable, version-controlled cloud asset inventory pipeline that any team can schedule, audit, and extend.

How it works

1. The cloudquery task (io.kestra.plugin.cloudquery.CloudQueryCLI) writes an inline config.yml via inputFiles, defining an aws source (pinned to cloudquery/aws v22.4.0, tables aws_s3*) and a postgresql destination (cloudquery/postgresql v5.0.3) with write_mode: overwrite-delete-stale.
2. The task runs cloudquery sync config.yml --log-console, pulling AWS S3 metadata and writing it into Postgres. AWS credentials, the CloudQuery API key, and the Postgres connection string are injected through env so no secrets are hardcoded.
3. The query_s3_metadata_in_postgres task (io.kestra.plugin.jdbc.postgresql.Query) connects with fetchType: FETCH and runs SQL against public.aws_s3_buckets, returning the arn and region of every bucket where block_public_acls, block_public_policy, ignore_public_acls, or restrict_public_buckets is not properly set.

What you get

• A continuously refreshable inventory of AWS resources stored in PostgreSQL.
• SQL-driven detection of publicly exposed S3 buckets.
• A clean separation between extraction (CloudQuery) and analysis (SQL).
• Outputs you can route into alerts, dashboards, or downstream tasks.

Who it's for

• Cloud security and CSPM engineers auditing AWS posture.
• Platform and DevOps teams maintaining cloud asset inventories.
• Compliance and governance teams that need repeatable evidence.
• Data engineers who prefer querying cloud config with plain SQL.

Why orchestrate this with Kestra

CloudQuery syncs data, but it has no built-in scheduler, retry semantics, or downstream branching. Kestra wraps the sync in declarative YAML, so you can attach event or schedule triggers, add automatic retries when the AWS API or Postgres hiccups, capture full execution lineage and logs, and chain the SQL analysis (and any alerting) as dependent tasks. The result is an auditable pipeline rather than a cron-driven script the sync tool cannot manage on its own.

Prerequisites

• A running PostgreSQL instance reachable at host.docker.internal:5432.
• AWS credentials with read access to the resources you want to inventory.
• A CloudQuery API key.

Secrets

• AWS_ACCESS_KEY_ID
• AWS_SECRET_ACCESS_KEY
• AWS_DEFAULT_REGION
• CLOUDQUERY_API_KEY
• DB_PASSWORD

Quick start

1. Start PostgreSQL locally with Docker:

   docker run -d --name mypostgres \
     -v mypostgresdb:/var/lib/postgresql/data \
     -p 5432:5432 \
     -e POSTGRES_USER=postgres \
     -e POSTGRES_PASSWORD=yourPassword1234 \
     -e POSTGRES_DB=postgres \
     postgres:latest
   

2. Add the secrets listed above to your Kestra instance.
3. Add the blueprint and execute the flow.
4. Inspect the query_s3_metadata_in_postgres output for any non-compliant buckets.

How to extend

• Broaden the inventory by adding source tables such as aws_ec2*, aws_iam*, aws_ecs*, aws_glue*, or aws_dynamodb*.
• Add a schedule trigger to refresh the inventory on a cadence.
• Branch on the SQL results to send a Slack or email alert when public buckets are found.
• Swap the PostgreSQL destination for another CloudQuery-supported warehouse.

Links

• CloudQuery plugin docs