Security-focused queries

Secure your cloud with SQL queries

Ensuring cloud security requires continuous monitoring and proactive detection of risks. CloudQuery allows you to run SQL queries directly on your cloud asset inventory, making it easy to spot vulnerabilities and misconfigurations in real time.

Security queries in action

Here are some powerful SQL queries to help you secure your cloud environment and eliminate risks before they become incidents.

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;

Want more?

These are just a few examples of how CloudQuery turns your cloud into an instantly queryable database—giving you unparalleled visibility into security risks.

Check out the main query examples page for more ways to audit, secure, and optimize your cloud — all with SQL!