AWS Quick Wins

kind: source
spec:
  name: aws
  path: cloudquery/source/aws
  version: 'vX.Y.Z' # pin a recent version
  # Keep it tight: only the tables we query
  tables:
    - aws_ec2_ebs_volumes
    - aws_ec2_eips
    - aws_ec2_security_groups
    - aws_ec2_network_interfaces
  destinations: [postgres]

---
kind: destination
spec:
  name: postgres
  path: cloudquery/destination/postgresql
  version: 'vA.B.C' # whatever your postgres version is
  spec:
    connection_string: ${POSTGRES_URL} # e.g. postgres://user:pass@host:5432/db

cq sync aws-safe-deletes.yaml

aws_ec2_ebs_volumes
Rows: ~hundreds (depends on account)
Columns:
- volume_id (utf8)          e.g. "vol-xxxxxxx"
- state (utf8)              e.g. "in-use", "available"
- size (int64)              e.g. 100 (GB)
- volume_type (utf8)        e.g. "gp3"
- attachments (json)        e.g. '[{"InstanceId":"i-0abc123..."}]'
- region (utf8)             e.g. "us-east-1"
- tags (json)               e.g. '{"Name":"db-data","Owner":"TeamA"}'

aws_ec2_eips
Rows: usually small (<50)
Columns:
- allocation_id (utf8)      e.g. "eipalloc-0123456789abcdef"
- public_ip (utf8)          e.g. "1.11.22.333"
- association_id (utf8)     e.g. NULL if unattached
- instance_id (utf8)        e.g. "i-0abc123def456"
- region (utf8)             e.g. "us-east-1"
- tags (json)               e.g. '{"Env":"prod"}'

aws_ec2_security_groups
Rows: varies widely (dozens → hundreds)
Columns:
- group_id (utf8)           e.g. "sg-0abc123def456"
- group_name (utf8)         e.g. "default", "web-sg"
- description (utf8)        e.g. "Web tier security group"
- vpc_id (utf8)             e.g. "vpc-0abcd1234ef567"
- ip_permissions (json)     inbound rules (array of objects)
- ip_permissions_egress (json) outbound rules
- region (utf8)             e.g. "us-east-1"
- tags (json)               e.g. '{"Owner":"sec-team"}'

-- Value: Volumes with no attachments (state='available' or empty attachments array) cost $/GB-mo without use.
-- Heuristic: gp3 ~ $0.08/GB-mo; adjust if you prefer.
SELECT
  v.volume_id,
  v.region,
  v.volume_type,
  v.state,
  v.size::int AS size_gb,
  (v.size * 0.08)::numeric(12,2) AS est_monthly_cost_usd,
  v.tags
FROM aws_ec2_ebs_volumes v
WHERE v.state = 'available'
   OR (
        v.attachments IS NOT NULL
        AND jsonb_typeof(v.attachments) = 'array'
        AND jsonb_array_length(v.attachments) = 0
      )
ORDER BY est_monthly_cost_usd DESC NULLS LAST, v.size DESC;

-- Value: EIPs with no association_id incur hourly charges (~$3.65/mo at ~$0.005/hr).
SELECT
  e.public_ip,
  e.allocation_id,
  e.region,
  e.association_id,
  CASE WHEN e.association_id IS NULL THEN 3.65 ELSE 0 END::numeric(10,2) AS est_monthly_cost_usd,
  e.tags
FROM aws_ec2_eips e
WHERE e.association_id IS NULL
ORDER BY est_monthly_cost_usd DESC, e.public_ip;

-- Value: Security groups not referenced by any ENI (excluding the literal 'default') are cleanup candidates.
-- Note: aws_ec2_network_interfaces.groups is JSON (array of objects). We extract group_id robustly, handling key variants.
WITH attached AS (
  SELECT DISTINCT
         COALESCE( g.elem->>'group_id',
                   g.elem->>'GroupId',
                   g.elem->>'groupId' ) AS group_id
  FROM aws_ec2_network_interfaces eni
  CROSS JOIN LATERAL jsonb_array_elements(enit.groups) AS g(elem)
  WHERE eni.groups IS NOT NULL
    AND jsonb_typeof(eni.groups) = 'array'
)
SELECT
  sg.group_id,
  sg.group_name,
  sg.vpc_id,
  sg.description,
  sg.region,
  sg.tags
FROM aws_ec2_security_groups sg
LEFT JOIN attached a ON a.group_id = sg.group_id
WHERE a.group_id IS NULL
  AND sg.group_name <> 'default'
ORDER BY sg.vpc_id, sg.group_name;