AWS EC2 instances authorizing SSH from anywhere

Queries & Insights
, ,
1

EC2 instances with an open port 22 are a major security risk that could allow unauthorized access from any IP address. To find all instances with this port open, run a CloudQuery sync with AWS source plugins for these tables: aws_ec2_instances, aws_ec2_security_groups.

The use this PostgreSQL query to find which instances have the port 22 open:

WITH security_group_expanded AS (
    SELECT 
        e.arn as instance_arn,
        e.private_dns_name  as name,
        e.region as instance_region,
        e.account_id as account_id,
        jsonb_array_elements(COALESCE(e.security_groups, '[]'::jsonb)) AS security_group
    FROM aws_ec2_instances as e 
),
ip_permissions_expanded AS (
    SELECT 
        sge.*,
        security_group->>'GroupId' AS instance_group_id,
        jsonb_array_elements(COALESCE(s.ip_permissions, '[]'::jsonb)) as ip_permission
    FROM security_group_expanded sge
    JOIN aws_ec2_security_groups as s 
    ON (security_group->>'GroupId') = s.group_id
),
ip_ranges_expanded AS (
    SELECT 
        ipe.*,
        (ip_permission->>'FromPort')::integer AS from_port,
        (ip_permission->>'ToPort')::integer AS to_port,
        COALESCE(ip_permission->'IpRanges', '[]'::jsonb) AS ip_ranges,
        COALESCE(ip_permission->'Ipv6Ranges', '[]'::jsonb) AS ipv6_ranges
    FROM ip_permissions_expanded ipe
),
ip_ranges_lateral AS (
    SELECT 
        ire.*,
        ip_range.value as ip_range
    FROM ip_ranges_expanded ire
    LEFT JOIN LATERAL jsonb_array_elements(
        CASE WHEN jsonb_array_length(ip_ranges) = 0 THEN '[""]'::jsonb ELSE ip_ranges END
    ) AS ip_range(value) ON true
),
final_expansion AS (
    SELECT 
        irl.*,
        ipv6_range.value as ipv6_range
    FROM ip_ranges_lateral irl
    LEFT JOIN LATERAL jsonb_array_elements(
        CASE WHEN jsonb_array_length(ipv6_ranges) = 0 THEN '[""]'::jsonb ELSE ipv6_ranges END
    ) AS ipv6_range(value) ON true
)
SELECT 
    instance_arn,
    name,
    instance_region,
    account_id,
    from_port,
    to_port,
    ip_range->>'CidrIp' as cidr,
    ipv6_range->>'CidrIp' as ipv6_cidr
FROM final_expansion
WHERE 
    from_port = 22 
    AND to_port = 22 
    AND (
        ip_range->>'CidrIp' = '0.0.0.0/0' 
        OR ipv6_range->>'CidrIp' = '::/0'
    );