Enhance your cloud security with CloudQuery’s powerful SQL 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.
