feat: Updated query of security_groups_with_open_critical_ports (cloudquery#13854)

ronsh12 · web-flow · commit 6834ee920039 · 2023-10-11T12:04:45.000Z
A change was made because duplicate rows were inserted into the aws_policy_results table.
diff --git a/plugins/source/aws/policies/queries/ec2/security_groups_with_open_critical_ports.sql b/plugins/source/aws/policies/queries/ec2/security_groups_with_open_critical_ports.sql
@@ -1,13 +1,10 @@
 -- uses view which uses aws_security_group_ingress_rules.sql query
 insert into aws_policy_results
-select
-    :'execution_time' as execution_time,
-    :'framework' as framework,
-    :'check_id' as check_id,
-    'Security groups should not allow unrestricted access to ports with high risk' as title,
-    account_id,
+WITH IndividualRuleStatus AS (
+  SELECT
+      account_id,
     id as resource_id,
-    case when
+     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
@@ -36,5 +33,20 @@ select
         or 9300 between from_port and to_port)
         then 'fail'
         else 'pass'
-    end
-from view_aws_security_group_ingress_rules
+    end as status
+  FROM view_aws_security_group_ingress_rules
+)
+
+SELECT
+	:'execution_time' as execution_time,
+	:'framework' as framework,
+	:'check_id' as check_id,
+	'Aggregates rules of security groups with ports and IPs including ipv6' as title,	
+    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
diff --git a/website/pages/docs/plugins/sources/aws/policies.md b/website/pages/docs/plugins/sources/aws/policies.md
@@ -320,7 +320,7 @@ AWS Foundational Security Best Practices performs the following checks:
   - Unused network access control lists should be removed
   - EC2 instances should not use multiple ENIs
   - Aggregates rules of security groups with ports and IPs including ipv6
-  - Security groups should not allow unrestricted access to ports with high risk
+  - Aggregates rules of security groups with ports and IPs including ipv6
   - Amazon ECS task definitions should have secure networking modes and user definitions
   - Amazon ECS services should not have public IP addresses assigned to them automatically
   - Amazon EFS should be configured to encrypt file data at rest using AWS KMS
