AWS CIS Benchmark in 2026: Automated Compliance with SQL-Based Policies

kind: source
spec:
  name: aws
  path: cloudquery/aws
  version: 'v28.0.0' # check /hub/plugins/source/cloudquery/aws for latest
  tables:
    - aws_iam_users
    - aws_iam_root_summary
    - aws_iam_virtual_mfa_devices
    - aws_iam_password_policies
    - aws_cloudtrail_trails
    - aws_s3_buckets
    - aws_ec2_vpcs
    - aws_ec2_security_groups
    - aws_kms_keys
    - aws_cloudwatch_alarms
    - aws_guardduty_detectors
  destinations:
    - postgresql
---
kind: destination
spec:
  name: postgresql
  path: cloudquery/postgresql
  version: 'v8.0.0' # check /hub/plugins/destination/cloudquery/postgresql for latest
  spec:
    connection_string: '${CQ_DSN}'

SELECT account_id
FROM aws_iam_root_summary
WHERE access_key_1_last_used_date > NOW() - INTERVAL '30 days'
   OR access_key_2_last_used_date > NOW() - INTERVAL '30 days';

SELECT name, account_id, region
FROM aws_s3_buckets
WHERE block_public_acls IS NOT TRUE
   OR block_public_policy IS NOT TRUE
   OR ignore_public_acls IS NOT TRUE
   OR restrict_public_buckets IS NOT TRUE;

SELECT account_id, name, home_region
FROM aws_cloudtrail_trails
WHERE is_multi_region_trail IS NOT TRUE
   OR include_global_service_events IS NOT TRUE;

SELECT key_id, account_id, region
FROM aws_kms_keys
WHERE key_rotation_enabled IS NOT TRUE
  AND key_manager = 'CUSTOMER';

-- Create your exceptions tracking table
CREATE TABLE cis_exceptions (
  resource_id TEXT,
  control_id  TEXT,
  reason      TEXT,
  approved_by TEXT,
  expires_at  TIMESTAMP
);

-- Log an approved exception
INSERT INTO cis_exceptions VALUES (
  'arn:aws:s3:::my-public-assets-bucket',
  'CIS-2.1.5',
  'Public static asset bucket - engineering review on 2026-04-10',
  'platform-security-team',
  '2026-12-31'
);

-- CIS 2.1.5 with exceptions applied
SELECT b.name, b.account_id, b.region
FROM aws_s3_buckets b
LEFT JOIN cis_exceptions e
  ON e.resource_id = b.arn
  AND e.control_id = 'CIS-2.1.5'
  AND (e.expires_at IS NULL OR e.expires_at > NOW())
WHERE (
  b.block_public_acls IS NOT TRUE
  OR b.block_public_policy IS NOT TRUE
  OR b.ignore_public_acls IS NOT TRUE
  OR b.restrict_public_buckets IS NOT TRUE
)
AND e.resource_id IS NULL;

SELECT
  account_id,
  COUNT(*) AS total_buckets,
  COUNT(*) FILTER (
    WHERE block_public_acls IS TRUE
      AND block_public_policy IS TRUE
      AND ignore_public_acls IS TRUE
      AND restrict_public_buckets IS TRUE
  ) AS compliant_buckets,
  ROUND(
    100.0 * COUNT(*) FILTER (
      WHERE block_public_acls IS TRUE
        AND block_public_policy IS TRUE
        AND ignore_public_acls IS TRUE
        AND restrict_public_buckets IS TRUE
    ) / NULLIF(COUNT(*), 0), 1
  ) AS pct_compliant
FROM aws_s3_buckets
GROUP BY account_id
ORDER BY pct_compliant ASC;