AWS
Tutorials
Exploring AWS EC2 Security Groups - Best Practices and Query Examples
AWS Security groups are a critical part of managing access to your AWS resources. However, working with the deeply nested JSON data in AWS security groups can be challenging, especially for non-technical users. This blog post will guide you through the aws_ec2_security_groups table, helping you understand its structure and providing example queries to make the most of your data.
To begin, you first need to sync your AWS security groups using CloudQuery's AWS Source Plugin to any destination available in our destination plugins. In the query examples below, we’ll use the PostgreSQL Destination Plugin.
To get started, download the CloudQuery CLI, and set up your CloudQuery configuration file by following the AWS Source Plugin documentation. When writing your CloudQuery AWS configuration, make sure that you add
aws_ec2_security_groups to the list of tables being synced.You can start exploring the data from your accounts AWS EC2 security groups using these examples of views and checks created by the CloudQuery AWS Compliance Package. One of these is the security groups ingress rules:
select
account_id,
region,
group_name,
arn,
group_id AS id,
vpc_id,
(i->>'FromPort')::integer AS from_port,
(i->>'ToPort')::integer AS to_port,
i->>'IpProtocol' AS ip_protocol,
ip_ranges->>'CidrIp' AS ip,
ip6_ranges->>'CidrIpv6' AS ip6
from aws_ec2_security_groups, JSONB_ARRAY_ELEMENTS(aws_ec2_security_groups.ip_permissions) AS i
LEFT JOIN JSONB_ARRAY_ELEMENTS(i->'IpRanges') AS ip_ranges ON true
LEFT JOIN JSONB_ARRAY_ELEMENTS(i->'Ipv6Ranges') AS ip6_ranges ON trueThis SQL query retrieves detailed information about AWS EC2 security groups, including their account ID, region, group name, ARN (Amazon Resource Name), VPC ID, and various security rule details. It specifically extracts and converts JSON data within the security group’s
ip_permissions field to get the port ranges (FromPort and ToPort), the IP protocol (IpProtocol), and both IPv4 (CidrIp) and IPv6 (CidrIpv6) address ranges. The query uses JSON functions to handle the complex nested data structures and joins them to get a comprehensive view of each security group’s rules.Similarly, we can extract the data for outbound ports and IPs affected by the group:
SELECT
account_id,
region,
group_name,
arn,
group_id AS id,
vpc_id,
(i->>'FromPort')::integer AS from_port,
(i->>'ToPort')::integer AS to_port,
i->>'IpProtocol' AS ip_protocol,
ip_ranges->>'CidrIp' AS ip,
ip6_ranges->>'CidrIpv6' AS ip6
FROM aws_ec2_security_groups, JSONB_ARRAY_ELEMENTS(aws_ec2_security_groups.ip_permissions_egress) AS i
LEFT JOIN JSONB_ARRAY_ELEMENTS(i->'IpRanges') AS ip_ranges ON true
LEFT JOIN JSONB_ARRAY_ELEMENTS(i->'Ipv6Ranges') AS ip6_ranges ON trueThis SQL query retrieves specific details from AWS EC2 security groups, focusing on outbound (egress) permissions. It selects the account ID, region, group name, ARN (Amazon Resource Name), group ID (renamed to id), and VPC ID. Additionally, it extracts and converts JSON data within the
ip_permissions_egress field to retrieve and display the port ranges (FromPort and ToPort), IP protocol (IpProtocol), and both IPv4 (CidrIp) and IPv6 (CidrIpv6) address ranges. The query uses JSON functions to handle these nested data structures and join them to produce a comprehensive set of egress rules for each security group.The complex JSON columns being flattened are:
ip_permissions- a JSON column containing an array of IP Permissions objects for inbound rules.ip_permissions_egress- a JSON column containing an array of IP Permissions objects for outbound rules.
While these objects are complex, these queries highlight the important properties you need to look at to understand what the security group affects.
To further understand these columns, you need to look at the check VPC default security groups should not allow inbound or outbound traffic from AWS CIS 3.0.
SELECT
account_id,
arn,
CASE WHEN
group_name='default'
AND (jsonb_array_length(ip_permissions) > 0
OR jsonb_array_length(ip_permissions_egress) > 0)
THEN 'fail'
ELSE 'pass'
END AS status
FROM
aws_ec2_security_groupsThis SQL query checks AWS EC2 default security groups for any inbound or outbound rules. It selects the account ID and ARN, and marks the status as ‘fail’ if the default group has rules, otherwise ‘pass’. This helps ensure default groups comply with policy.
Another example is Security groups should not allow unrestricted access to ports with high risk.
-- uses view aws_compliance__security_group_ingress_rules
WITH IndividualRuleStatus AS (
SELECT
account_id,
id AS resource_id,
CASE WHEN
(ip = '0.0.0.0/0' OR ip = '::/0')
AND ((from_port IS NULL AND to_port IS NULL) -- all ports
OR 20 BETWEEN from_port AND to_port
OR 21 BETWEEN from_port AND to_port
OR 22 BETWEEN from_port AND to_port
OR 23 BETWEEN from_port AND to_port
OR 25 BETWEEN from_port AND to_port
OR 110 BETWEEN from_port AND to_port
OR 135 BETWEEN from_port AND to_port
OR 143 BETWEEN from_port AND to_port
OR 445 BETWEEN from_port AND to_port
OR 143 BETWEEN from_port AND to_port
OR 143 BETWEEN from_port AND to_port
OR 300 BETWEEN from_port AND to_port
OR 330 BETWEEN from_port AND to_port
OR 338 BETWEEN from_port AND to_port
OR 433 BETWEEN from_port AND to_port
OR 500 BETWEEN from_port AND to_port
OR 543 BETWEEN from_port AND to_port
OR 550 BETWEEN from_port AND to_port
OR 560 BETWEEN from_port AND to_port
OR 808 BETWEEN from_port AND to_port
OR 808 BETWEEN from_port AND to_port
OR 888 BETWEEN from_port AND to_port
OR 920 BETWEEN from_port AND to_port
OR 930 BETWEEN from_port AND to_port)
THEN 'fail'
ELSE 'pass'
END AS status
FROM aws_compliance__security_group_ingress_rules
)
SELECT
account_id,
resource_id,
CASE
WHEN SUM(CASE WHEN status = 'fail' THEN 1 ELSE 0 END) > 0 THEN 'fail'
ELSE 'pass'
END as status
FROM IndividualRuleStatus
GROUP BY account_id, resource_idThis SQL query assesses AWS EC2 security group ingress rules for potential vulnerabilities. It first creates a temporary view (IndividualRuleStatus) that flags rules as ‘fail’ if they allow traffic from any IP (0.0.0.0/0 or ::/0) on critical ports. The final query then aggregates these results by account ID and resource ID, marking the security group as ‘fail’ if any rule is flagged, otherwise as ‘pass’. This helps in identifying security groups with potentially risky open access.
With these examples, you are ready to explore the security group data available from our AWS Source Plugin. By modifying these queries, you can gain insights into various aspects of your AWS security groups.
Ready to dive deeper into your AWS security group data? Try CloudQuery today and gain comprehensive insights into your AWS security configurations. Contact us or join our Community to connect with other users and our engineering team, and explore the full potential of CloudQuery.
Written by Jonathan Sarig
Jonathan is a software engineer at CloudQuery with a particular interest in machine learning. He primarily works in golang but also has experience in (and a passion for) Rust.
