Security-focused queries

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.

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