Skip to content

Commit 0f64196

Browse files
amanenkamanenk
authored
feat(aws): Update cis 1.5.0 policy (#1615)
 #### Summary closes #1559 some cloudwatch alerts checks are refined but they are still not perfect. Formatting of rules can these affect checks result. <!-- Explain what problem this PR addresses --> ---
1 parent 51f18ea commit 0f64196

31 files changed

+493
-87
lines changed

‎plugins/source/aws/policies_v1/cis_v1.5.0/policy.sql‎

Lines changed: 16 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,16 @@
1+
\set ON_ERROR_STOP on
2+
SET TIME ZONE 'UTC';
3+
-- neat trick to set execution_time if not already set
4+
-- https://stackoverflow.com/questions/32582600/only-set-variable-in-psql-script-if-not-specified-on-the-command-line
5+
\set execution_time :execution_time
6+
SELECT CASE
7+
WHEN :'execution_time' = ':execution_time' THEN to_char(now(), 'YYYY-MM-dd HH24:MI:SS.US')
8+
ELSE :'execution_time'
9+
END AS "execution_time" \gset
10+
\set framework 'cis_v1.5.0'
11+
\ir ../create_aws_policy_results.sql
12+
\ir ./section_1.sql
13+
\ir ./section_2.sql
14+
\ir ./section_3.sql
15+
\ir ./section_4.sql
16+
\ir ./section_5.sql

‎plugins/source/aws/policies_v1/cis_v1.5.0/section_1.sql‎

Lines changed: 55 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,55 @@
1+
\echo "Executing CIS V1.5.0 Section 1"
2+
\set check_id '1.1'
3+
-- manual
4+
\set check_id '1.2'
5+
-- manual
6+
\set check_id '1.3'
7+
-- manual
8+
\set check_id '1.4'
9+
\echo "Executing check 1.4"
10+
\ir ../queries/iam/root_user_no_access_keys.sql
11+
\set check_id '1.5'
12+
\echo "Executing check 1.5"
13+
\ir ../queries/iam/mfa_enabled_for_root.sql
14+
\set check_id '1.6'
15+
\echo "Executing check 1.6"
16+
\ir ../queries/iam/hardware_mfa_enabled_for_root.sql
17+
\set check_id '1.7'
18+
-- todo credential report add password_last_used, access_key_1_last_used_date, access_key_2_last_used_date
19+
\set check_id '1.8'
20+
\echo "Executing check 1.8"
21+
\ir ../queries/iam/password_policy_min_length.sql
22+
\set check_id '1.9'
23+
\echo "Executing check 1.9"
24+
\ir ../queries/iam/password_policy_prevent_reuse.sql
25+
\set check_id '1.10'
26+
\echo "Executing check 1.10"
27+
\ir ../queries/iam/mfa_enabled_for_console_access.sql
28+
\set check_id '1.11'
29+
-- todo credential report add access_key_1_last_used_date,access_key_2_last_used_date
30+
\set check_id '1.12'
31+
\echo "Executing check 1.12"
32+
\ir ../queries/iam/unused_creds_disabled_45_days.sql
33+
\set check_id '1.13'
34+
\echo "Executing check 1.13"
35+
\ir ../queries/iam/users_with_two_active_access_keys.sql
36+
\set check_id '1.14'
37+
\echo "Executing check 1.14"
38+
\ir ../queries/iam/old_access_keys.sql
39+
\set check_id '1.15'
40+
\echo "Executing check 1.15"
41+
\ir ../queries/iam/policies_attached_to_groups_roles.sql
42+
\set check_id '1.16'
43+
-- todo svc.ListPolicies is not used (implement it and then do a check)
44+
\set check_id '1.17'
45+
-- todo svc.ListPolicies is not used (implement it and then do a check)
46+
\set check_id '1.18'
47+
-- manual
48+
\set check_id '1.19'
49+
\echo "Executing check 1.19"
50+
\ir ../queries/iam/server_certificates_expired.sql
51+
\set check_id '1.20'
52+
\echo "Executing check 1.20"
53+
-- \ir ../queries/accessanalyzer/regions_with_no_accessanalyzers.sql
54+
\set check_id '1.21'
55+
-- manual

‎plugins/source/aws/policies_v1/cis_v1.5.0/section_2.sql‎

Lines changed: 29 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,29 @@
1+
\echo "Executing CIS V1.5.0 Section 2"
2+
\set check_id '2.1.1'
3+
\echo "Executing check 2.1.1"
4+
\ir ../queries/s3/s3_server_side_encryption_enabled.sql
5+
\set check_id '2.1.2'
6+
\echo "Executing check 2.1.2"
7+
\ir ../queries/s3/deny_http_requests.sql
8+
\set check_id '2.1.3'
9+
\echo "Executing check 2.1.3"
10+
\ir ../queries/s3/mfa_delete.sql
11+
\set check_id '2.1.4'
12+
-- manual
13+
\set check_id '2.1.5'
14+
\echo "Executing check 2.1.5"
15+
\ir ../queries/s3/bucket_level_public_access_blocks.sql
16+
\set check_id '2.2.1'
17+
\echo "Executing check 2.2.1"
18+
\ir ../queries/ec2/ebs_encryption_by_default_disabled.sql
19+
\set check_id '2.3.1'
20+
\echo "Executing check 2.3.1"
21+
\ir ../queries/rds/rds_db_instances_should_have_encryption_at_rest_enabled.sql
22+
\set check_id '2.3.2'
23+
\echo "Executing check 2.3.2"
24+
\ir ../queries/rds/rds_automatic_minor_version_upgrades_should_be_enabled.sql
25+
\set check_id '2.3.3'
26+
\echo "Executing check 2.3.3"
27+
\ir ../queries/rds/rds_db_instances_should_prohibit_public_access.sql
28+
\set check_id '2.4.1'
29+
-- manual

‎plugins/source/aws/policies_v1/cis_v1.5.0/section_3.sql‎

Lines changed: 28 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,28 @@
1+
\echo "Executing CIS V1.5.0 Section 3"
2+
\set check_id '3.1'
3+
\echo "Executing check 3.1"
4+
\ir ../queries/cloudtrail/enabled_in_all_regions.sql
5+
\set check_id '3.2'
6+
\echo "Executing check 3.2"
7+
\ir ../queries/cloudtrail/log_file_validation_enabled.sql
8+
\set check_id '3.3'
9+
-- todo
10+
\set check_id '3.4'
11+
\echo "Executing check 3.4"
12+
\ir ../queries/cloudtrail/integrated_with_cloudwatch_logs.sql
13+
\set check_id '3.6'
14+
\echo "Executing check 3.6"
15+
\ir ../queries/cloudtrail/bucket_access_logging.sql
16+
\set check_id '3.7'
17+
\echo "Executing check 3.7"
18+
\ir ../queries/cloudtrail/logs_encrypted.sql
19+
\set check_id '3.8'
20+
\echo "Executing check 3.8"
21+
\ir ../queries/kms/rotation_enabled_for_customer_key.sql
22+
\set check_id '3.9'
23+
\echo "Executing check 3.9"
24+
\ir ../queries/ec2/flow_logs_enabled_in_all_vpcs.sql
25+
\set check_id '3.10'
26+
-- todo cloud_trail_event_selectors data_resources field is wrong
27+
\set check_id '3.11'
28+
-- todo cloud_trail_event_selectors data_resources field is wrong

‎plugins/source/aws/policies_v1/cis_v1.5.0/section_4.sql‎

Lines changed: 49 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,49 @@
1+
\echo "Executing CIS V1.5.0 Section 4"
2+
\echo "Creating view_aws_log_metric_filter_and_alarm"
3+
\ir ../views/log_metric_filter_and_alarm.sql
4+
\set check_id '4.1'
5+
\echo "Executing check 4.1"
6+
\ir ../queries/cloudwatch/alarm_unauthorized_api.sql
7+
\set check_id '4.3'
8+
\echo "Executing check 4.3"
9+
\ir ../queries/cloudwatch/alarm_root_account.sql
10+
\set check_id '4.4'
11+
\echo "Executing check 4.4"
12+
\ir ../queries/cloudwatch/alarm_iam_policy_change.sql
13+
\set check_id '4.5'
14+
\echo "Executing check 4.5"
15+
\ir ../queries/cloudwatch/alarm_cloudtrail_config_changes.sql
16+
\set check_id '4.6'
17+
\echo "Executing check 4.6"
18+
\ir ../queries/cloudwatch/alarm_console_auth_failure.sql
19+
\set check_id '4.7'
20+
\echo "Executing check 4.7"
21+
\ir ../queries/cloudwatch/alarm_delete_customer_cmk.sql
22+
\set check_id '4.8'
23+
\echo "Executing check 4.8"
24+
\ir ../queries/cloudwatch/alarm_s3_bucket_policy_change.sql
25+
\set check_id '4.9'
26+
\echo "Executing check 4.9"
27+
\ir ../queries/cloudwatch/alarm_aws_config_changes.sql
28+
\set check_id '4.10'
29+
\echo "Executing check 4.10"
30+
\ir ../queries/cloudwatch/alarm_security_group_changes.sql
31+
\set check_id '4.11'
32+
\echo "Executing check 4.11"
33+
\ir ../queries/cloudwatch/alarm_nacl_changes.sql
34+
\set check_id '4.12'
35+
\echo "Executing check 4.12"
36+
\ir ../queries/cloudwatch/alarm_network_gateways.sql
37+
\set check_id '4.13'
38+
\echo "Executing check 4.13"
39+
\ir ../queries/cloudwatch/alarm_route_table_changes.sql
40+
\set check_id '4.14'
41+
\echo "Executing check 4.14"
42+
\ir ../queries/cloudwatch/alarm_vpc_changes.sql
43+
\set check_id '4.15'
44+
\echo "Executing check 4.15"
45+
\ir ../queries/cloudwatch/alarm_organization_changes.sql
46+
\set check_id '4.16'
47+
\echo "Executing check 4.16"
48+
-- todo add security hub resources to aws provider
49+

‎plugins/source/aws/policies_v1/cis_v1.5.0/section_5.sql‎

Lines changed: 18 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,18 @@
1+
\echo "Executing CIS V1.5.0 Section 5"
2+
\echo "Creating view_aws_security_group_ingress_rules"
3+
\ir ../views/security_group_ingress_rules.sql
4+
\echo "Creating view_aws_nacl_allow_ingress_rules"
5+
\ir ../views/networks_acls_ingress_rules.sql
6+
\set check_id '5.1'
7+
\echo "Executing check 5.1"
8+
\ir ../queries/ec2/no_broad_public_ingress_acl_on_port_22_3389.sql
9+
\set check_id '5.2'
10+
\echo "Executing check 5.2"
11+
\ir ../queries/ec2/no_broad_public_ipv4_ingress_on_port_22_3389.sql
12+
\set check_id '5.3'
13+
\ir ../queries/ec2/no_broad_public_ipv6_ingress_on_port_22_3389.sql
14+
\set check_id '5.4'
15+
\echo "Executing check 5.4"
16+
\ir ../queries/ec2/default_sg_no_access.sql
17+
\set check_id '5.5'
18+
-- manual

‎plugins/source/aws/policies_v1/queries/accessanalyzer/regions_with_no_accessanalyzers.sql‎

Lines changed: 23 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,23 @@
1+
insert into aws_policy_results
2+
WITH regions_with_enabled_accessanalyzer
3+
AS (SELECT ar.region AS analyzed_region
4+
FROM aws_regions ar
5+
LEFT JOIN aws_accessanalyzer_analyzers aaaa ON
6+
ar.region = aaaa.region
7+
WHERE aaaa.status = 'ACTIVE')
8+
SELECT :'execution_time' AS execution_time,
9+
:'framework' AS framework,
10+
:'check_id' AS check_id,
11+
'Ensure that IAM Access analyzer is enabled for all regions (Automated)' AS title,
12+
account_id,
13+
region AS resource_id,
14+
CASE
15+
WHEN
16+
aregion.analyzed_region IS NULL
17+
AND ar.enabled = TRUE
18+
THEN 'fail'
19+
ELSE 'pass'
20+
END AS status
21+
FROM aws_regions ar
22+
LEFT JOIN regions_with_enabled_accessanalyzer aregion ON
23+
ar.region = aregion.analyzed_region;

‎plugins/source/aws/policies_v1/queries/cloudwatch/alarm_actions_disabled.sql‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -8,4 +8,4 @@ select :'execution_time' as execution_time,
88
'fail' as status
99
from aws_cloudwatch_alarms
1010
where actions_enabled = false
11-
or array_length(alarm_actions, 1) = 0;
11+
or array_length(alarm_actions, 1) = 0

‎plugins/source/aws/policies_v1/queries/cloudwatch/alarm_aws_config_changes.sql‎

Lines changed: 4 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -7,8 +7,10 @@ select
77
account_id,
88
cloud_watch_logs_log_group_arn as resource_id,
99
case
10-
when pattern = '{($.eventSource = kms.amazonaws.com) '
11-
|| '&& (($.eventName=DisableKey)||($.eventName=ScheduleKeyDeletion)) }"' then 'pass'
10+
when pattern NOT LIKE '%NOT%'
11+
AND pattern LIKE '%($.eventSource = kms.amazonaws.com)%'
12+
AND pattern LIKE '%($.eventName = DisableKey)%'
13+
AND pattern LIKE '%($.eventName = ScheduleKeyDeletion)%' then 'pass'
1214
else 'fail'
1315
end as status
1416
from view_aws_log_metric_filter_and_alarm

‎plugins/source/aws/policies_v1/queries/cloudwatch/alarm_cloudtrail_config_changes.sql‎

Lines changed: 6 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -7,11 +7,12 @@ select
77
account_id,
88
cloud_watch_logs_log_group_arn as resource_id,
99
case
10-
when pattern = '{ ($.eventName = CreateTrail) '
11-
|| '|| ($.eventName = UpdateTrail) '
12-
|| '|| ($.eventName = DeleteTrail) '
13-
|| '|| ($.eventName = StartLogging) '
14-
|| '|| ($.eventName = StopLogging) }' then 'pass'
10+
when pattern NOT LIKE '%NOT%'
11+
AND pattern LIKE '%($.eventName = CreateTrail)%'
12+
AND pattern LIKE '%($.eventName = UpdateTrail)%'
13+
AND pattern LIKE '%($.eventName = DeleteTrail)%'
14+
AND pattern LIKE '%($.eventName = StartLogging)%'
15+
AND pattern LIKE '%($.eventName = StopLogging)%' then 'pass'
1516
else 'fail'
1617
end as status
1718
from view_aws_log_metric_filter_and_alarm

0 commit comments

Comments
 (0)