AWS

Tutorials

Exploring AWS EC2 Security Groups - Best Practices and Query Examples

AWS Compliance Policy and Dashboards have been deprecated.

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 Integration to any destination available in our destination integrations. In the query examples below, we’ll use the PostgreSQL Destination Integration.

To get started, download the CloudQuery CLI, and set up your CloudQuery configuration file by following the AWS Source Integration 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 true

This 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 true

This 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:

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_groups

This 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_id

This 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 Integration. By modifying these queries, you can gain insights into various aspects of your AWS security groups.

If you're auditing egress rules specifically for vulnerability exposure - for instance, finding EC2 instances and Lambda functions that have unrestricted outbound access and could be exploited via Log4Shell - see the companion post Finding Unrestricted AWS Outbound Access in 2026. It extends the egress view from this post into a combined query that cross-references Internet Gateways and NAT Gateways to identify instances that likely have a path to the internet based on VPC-level gateway presence.

Ready to explore 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.

Try CloudQuery yourself

Self-hosted, open source, and free to start. Or schedule a demo to see the managed version.

Related posts

AWS

Security

Tutorials

Finding AWS Unrestricted Outbound Access in 2026: EC2, Lambda, and Beyond

Find EC2, Lambda, and ECS Fargate resources with unrestricted outbound access in AWS using SQL. Why egress controls still break attack chains in 2026.

Joe Karlsson

Joe Karlsson

May 5, 2026

AWS

Security

Tutorials

AWS IAM Identity Center with Google Workspace as an IdP: 2026 Step-by-Step Guide

Set up AWS IAM Identity Center with Google Workspace: SAML authentication, SCIM user provisioning, and group sync with CLI or ssosync. Updated for 2026.

Joe Karlsson

Joe Karlsson

May 5, 2026

AWS

Cloud Infrastructure

Security

Tutorials

Integrate AbuseIPDB Threat Intelligence with CloudQuery

Enrich your cloud asset inventory with AbuseIPDB threat intelligence to identify public cloud IPs with known abuse reports.

Joe Karlsson

Joe Karlsson

April 20, 2026

AWS

Cloud Infrastructure

Tutorials

How to Query ipinfo.io Data with CloudQuery

Learn how to enrich your cloud asset inventory with ipinfo.io IP geolocation and threat intelligence data using CloudQuery.

Joe Karlsson

Joe Karlsson

April 20, 2026

AWS

Security

Tutorials

AWS IAM Identity Center (Formerly AWS SSO): The Complete 2026 Guide

AWS IAM Identity Center replaced AWS SSO in 2022. Learn how to set it up, configure permission sets, use the AWS CLI, and audit access with CloudQuery.

Joe Karlsson

Joe Karlsson

April 6, 2026

AWS

Tutorials

AWS Pinpoint Is Being Deprecated: How to Assess Your Migration Scope

AWS Pinpoint reaches end of support October 2026. Query your Pinpoint usage across all accounts to find what needs migrating before the deadline.

Joe Karlsson

Joe Karlsson

March 24, 2026

AWS

Best Practices

Tutorials

CloudQuery Sync Performance: A Practical Troubleshooting Guide

Diagnose why CloudQuery syncs slow down after plugin upgrades, identify expensive tables, and use the CLI command that replaces the tables wildcard.

Joe Karlsson

Joe Karlsson

March 6, 2026

AWS

Security

Tutorials

Set Up AWS CLI with SSO (IAM Identity Center) — Step by Step

Set up AWS CLI v2 with IAM Identity Center in 5 minutes. Covers SSO registration scopes, sso-session config, token refresh, and multi-account profiles.

Yevgeny Pats

Yevgeny Pats

February 25, 2026

AWS

Cloud Infrastructure

Tutorials

AWS Lambda Python 3.9 End-of-Support: Find and Upgrade Every Affected Function

AWS Lambda Python 3.9 security patches stopped Dec 15, 2025. Find every affected function across all accounts with SQL queries and upgrade to 3.12 or 3.13.

Joe Karlsson

Joe Karlsson

February 10, 2026

Own your pipeline.
Own your data.