# cloudquery-config.yml
kind: source
spec:
  name: aws
  path: cloudquery/aws
  version: v28.2.0
  destinations: [postgresql]
  tables:
    - aws_ec2_instances
    - aws_autoscaling_groups
    - aws_lambda_functions
  spec:
    accounts:
      - id: "123456789012"

-- Query using native AWS attributes
SELECT
  instance_id,
  instance_type,
  vpc_id,
  security_groups,
  iam_instance_profile,
  tags->>'Environment' as environment,
  tags->>'Owner' as owner,
  public_ip_address
FROM aws_ec2_instances
WHERE state_name = 'running'
  AND tags->>'Encryption' != 'true'
  AND public_ip_address IS NOT NULL;

-- Find public S3 buckets instantly
SELECT
  account_id,
  bucket_name,
  region,
  public_access_block_configuration,
  acl
FROM aws_s3_buckets
WHERE public_access_block_configuration->>'BlockPublicAcls' = 'false'
   OR public_access_block_configuration->>'BlockPublicPolicy' = 'false'
   OR acl @> '[{"Grantee": {"Type": "Group", "URI": "http://acs.amazonaws.com/groups/global/AllUsers"}}]'::jsonb;

SELECT
  i.instance_id,
  i.public_ip_address,
  sg.group_id,
  sg.group_name,
  sg.ip_permissions
FROM aws_ec2_instances i
JOIN aws_ec2_security_groups sg
  ON sg.group_id = ANY(i.security_groups)
WHERE i.public_ip_address IS NOT NULL
  AND sg.ip_permissions @> '[{"FromPort": 22, "ToPort": 22, "IpRanges": [{"CidrIp": "0.0.0.0/0"}]}]'::jsonb;

-- Find unencrypted resources across AWS and GCP
SELECT
  'AWS S3' as platform,
  bucket_name as resource_name,
  account_id,
  region
FROM aws_s3_buckets
WHERE encryption_configuration IS NULL

UNION ALL

SELECT
  'GCP Storage' as platform,
  name as resource_name,
  project_id,
  location
FROM gcp_storage_buckets
WHERE encryption->>'defaultKmsKeyName' IS NULL;

SELECT
  r.full_name as repository,
  r.visibility,
  c.login as collaborator,
  o.email as okta_email,
  o.status as okta_status
FROM github_repositories r
JOIN github_repository_collaborators c ON r.full_name = c.repository_full_name
JOIN okta_users o ON c.email = o.email
WHERE o.status = 'DEPROVISIONED'
  AND c.permission IN ('admin', 'maintain');