Security-Focused Queries

Secure Your Cloud with SQL Queries

CloudQuery lets you query your cloud asset inventory directly to identify vulnerabilities and misconfigurations.

Security Queries in Action

The following queries help you identify and address common security risks.

Find public S3 buckets (AWS)

Why it matters: Public S3 buckets can expose sensitive data and lead to breaches.


SELECT
  name, JSONExtractBool(policy_status, 'IsPublic') as is_public
FROM
  aws_s3_buckets
WHERE
  is_public = TRUE

Detect AWS lambda functions using deprecated runtimes (AWS)

Why it matters: Deprecated runtimes introduce security risks and compatibility issues.


SELECT
  arn, region, tags, JSONExtractString(configuration, 'Runtime') as runtime
FROM
  aws_lambda_functions
WHERE
  runtime in ('nodejs10.x', 'python2.7', 'dotnetcore2.1', 'ruby2.5', 'go1.x')

Identify expired SSL certificates (AWS)

Why it matters: Expired SSL certificates can lead to service disruptions and security vulnerabilities.


SELECT
  account_id,
  arn,
  domain_name,
  not_after AS expiry_date,
  status,
  NOW() AS current_time,
  not_after < NOW() AS is_expired
FROM
  aws_acm_certificates
ORDER BY
  not_after ASC;

List EC2 instances authorizing SSH from anywhere (AWS)

Why it matters: Exposed SSH ports allow unauthorized access and increase security risks.


SELECT
  e.arn as instance_arn,
  e._cq_name as name,
  e.region as instance_region,
  e.account_id as account_id,
  arrayJoin(JSONExtractArrayRaw(assumeNotNull(security_groups))) AS security_group,
  JSONExtractString(security_group, 'GroupId') AS instance_group_id,
  arrayJoin(JSONExtractArrayRaw(assumeNotNull(s.ip_permissions))) as ip_permission,
  JSONExtractInt(ip_permission, 'FromPort') AS from_port,
  JSONExtractInt(ip_permission, 'ToPort') AS to_port,
  JSONExtractArrayRaw(ip_permission, 'IpRanges') AS ip_ranges,
  JSONExtractArrayRaw(ip_permission, 'Ipv6Ranges') AS ipv6_ranges,
  arrayJoin(empty(ip_ranges) ? [''] : ip_ranges) as ip_range,
  arrayJoin(empty(ipv6_ranges) ? [''] : ipv6_ranges) as ipv6_range,
  JSONExtractString(ip_range, 'CidrIp') as cidr,
  JSONExtractString(ipv6_range, 'CidrIp') as ipv6_cidr
FROM
  aws_ec2_instances as e
JOIN
  aws_ec2_security_groups as s
ON
  instance_group_id = s.group_id
WHERE
  from_port = 22 AND to_port = 22 AND (cidr = '0.0.0.0/0' OR ipv6_cidr = '::/0')

Find IAM users without MFA enabled (AWS)

Why it matters: Lack of MFA increases the risk of account compromise.


SELECT
  u.*
FROM
  aws_iam_users AS u
LEFT JOIN
  aws_iam_mfa_devices AS m
ON
  u.user_name = m.user_name
WHERE
  m.user_name IS NULL;

More query examples

See the query examples overview for compliance and FinOps-focused queries.

Next Steps

Compliance-Focused Queries - Compliance monitoring queries
FinOps-Focused Queries - Cost optimization queries
Policies - Turn security queries into automated compliance rules
Alerts - Get notified when security conditions are detected