README.md

CloudQuery Multi-Cloud Demo

This demo showcases how to build a multi-cloud asset inventory using CloudQuery, integrating data sources from AWS and GCP, storing results in a PostgreSQL database, and running sample SQL queries on the collected data.

Slides

You can view the accompanying slides for this demo here.

Prerequisites

Before running this demo, make sure you have the following:

1. Download multi_cloud_demo.sh and demo-magic.sh:

   mkdir multi-cloud-demo
   cd multi-cloud-demo
   curl -O https://raw.githubusercontent.com/paxtonhare/demo-magic/master/demo-magic.sh
   curl -O https://gist.githubusercontent.com/JoeKarlsson/ab73f106a4b2f4c01ce46a48358d84dd/raw/cbc768f0fc887b2e8fe32b4959a6e41c9b7cddd4/multi_cloud_demo.sh
   chmod +x multi_cloud_demo.sh

2. AWS Access: Obtain updated AWS access keys (AWS_ACCESS_KEY_ID, AWS_SECRET_ACCESS_KEY, and AWS_SESSION_TOKEN), and export them to your environment:

   export AWS_ACCESS=YOUR_AWS_ACCESS_KEY
   export AWS_SECRET=YOUR_AWS_SECRET_ACCESS_KEY
   export AWS_SESSION=YOUR_AWS_SESSION

3. GCP Authentication: Install and authenticate using the GCP CLI:

4. Docker: Ensure Docker is installed and running on your local machine.

5. Make sure you can launch VS Code from your terminal: Launching from the command line.

Running the Demo

1. Be sure to reexport your AWS keys before running

2. Run the demo script:

   ./multi_cloud.sh

3. Press Enter to run each command in the script.

multi_cloud_demo.sh

#!/usr/bin/env bash

#################################
# include the -=magic=-
# you can pass command line args
#
# example:
# to disable simulated typing
# . ../demo-magic.sh -d
#
# pass -h to see all options
#################################
. ./demo-magic.sh


########################
# Configure the options
########################

#
# speed at which to simulate typing. bigger num = faster
#
TYPE_SPEED=100

#
# custom prompt
#
# see http://www.tldp.org/HOWTO/Bash-Prompt-HOWTO/bash-prompt-escape-sequences.html for escape sequences
#
DEMO_PROMPT="${GREEN}➜ ${CYAN}\W ${COLOR_RESET}"

# text color
# DEMO_CMD_COLOR=$BLACK

# enters interactive mode and allows newly typed command to be executed
cmd

# Clean up previous demo and hide the evidence
ei "[ -f 'aws_to_postgresql.yaml' ] && rm 'aws_to_postgresql.yaml'"
ei "[ -f 'gcp_to_postgresql.yaml' ] && rm 'gcp_to_postgresql.yaml'"
clear

wait

pei "brew install cloudquery/tap/cloudquery"

pei "cloudquery login"

p "Initialize the project with an AWS to PostgreSQL sync"

pei "cloudquery init --source=aws --destination=postgresql"

wait

p "Change CloudQuery config so it syncs aws_s3_buckets"

pei "sed -i '' 's/tables: \[\"aws_ec2_instances\"\]/tables: \[\"aws_s3_buckets\"\]/' aws_to_postgresql.yaml"

pei "code aws_to_postgresql.yaml"

wait

clear

pei "docker run --name postgres_container \
  --restart unless-stopped \
  --env POSTGRES_USER=postgres \
  --env POSTGRES_PASSWORD=postgres \
  --env POSTGRES_HOST=db \
  --env POSTGRES_DB=asset_inventory \
  --publish 5432:5432 \
  --volume pgdata:/var/lib/postgresql/data \
  postgres"

pei "docker exec -it postgres_container psql -U postgres -c 'CREATE DATABASE asset_inventory;'"

pei "export POSTGRESQL_CONNECTION_STRING='postgresql://postgres:postgres@localhost:5432/asset_inventory'"

clear

pe "cloudquery sync aws_to_postgresql.yaml"

wait

p "List the first 10 S3 buckets in the database"

pe "docker exec -it postgres_container psql -U postgres -d asset_inventory -c 'SELECT name, region FROM aws_s3_buckets LIMIT 10;'"

wait

clear

p "List all buckets created in the last 30 days, grouped by region"

pe "docker exec -it postgres_container psql -U postgres -d asset_inventory -c \"
SELECT 
    region, 
    COUNT(*) AS bucket_count 
FROM 
    aws_s3_buckets 
WHERE 
    creation_date >= NOW() - INTERVAL '30 days'
GROUP BY 
    region
ORDER BY 
    bucket_count DESC;
\""

wait

clear

p "Identify buckets with public access policies"

pe "docker exec -it postgres_container psql -U postgres -d asset_inventory -c \"
SELECT 
    name, 
    arn, 
    policy_status 
FROM 
    aws_s3_buckets 
WHERE 
    policy_status->>'isPublic' = 'true';
\""

# ~/google-cloud-sdk/bin/gcloud init # gcloud init 

p "Next, let's add GCP to our Asset Inventory - initialize the project with a GCP to PostgreSQL sync"

pe "cloudquery init --source=gcp --destination=postgresql"

wait

# pei "~/google-cloud-sdk/bin/gcloud auth application-default login" # gcloud auth application-default login

pei "sed -i '' '/project_ids: \[\"my-project\"\]/d' gcp_to_postgresql.yaml"

pei "cloudquery sync gcp_to_postgresql.yaml"

wait

p "Count the number of storage buckets per location in GCP"

pe 'docker exec -it postgres_container psql -U postgres -d asset_inventory -c "
SELECT 
    location, 
    COUNT(*) AS bucket_count, 
    SUM(CASE WHEN public_access_prevention = 1 THEN 1 ELSE 0 END) AS public_access_prevention_enabled
FROM 
    gcp_storage_buckets 
GROUP BY 
    location
ORDER BY 
    bucket_count DESC;"
'

wait

clear

p "Count buckets by location and identify how many have public access prevention enabled"

pe "docker exec -it postgres_container psql -U postgres -d asset_inventory -c \"
SELECT 
    location, 
    COUNT(*) AS bucket_count, 
    SUM(CASE WHEN public_access_prevention = 1 THEN 1 ELSE 0 END) AS public_access_prevention_enabled
FROM 
    gcp_storage_buckets 
GROUP BY 
    location
ORDER BY 
    bucket_count DESC;
\""

wait

clear

p "Retrieve bucket names, their creation dates, regions/locations, and cloud provider information from both AWS and GCP."

pe "docker exec -it postgres_container psql -U postgres -d asset_inventory -c \"
SELECT 
    name AS bucket_name,
    creation_date AS created_date,
    region AS location,
    'AWS' AS cloud_provider
FROM 
    aws_s3_buckets

UNION ALL

SELECT 
    name AS bucket_name,
    created AS created_date,
    location AS location,
    'GCP' AS cloud_provider
FROM 
    gcp_storage_buckets
ORDER BY 
    created_date DESC;
\""

wait

p "Done!"

wait

p "Press ENTER to reset..."

wait

clear

pei "[ -f 'aws_to_postgresql.yaml' ] && rm 'aws_to_postgresql.yaml'"
pei "[ -f 'gcp_to_postgresql.yaml' ] && rm 'gcp_to_postgresql.yaml'"