Skip to content

Commit 1591f9f

Browse files
disqdisq
authored
fix: GCP policies (#1879)
Missing just a single policy, tracked by #1995
1 parent 062907a commit 1591f9f

89 files changed

Lines changed: 2172 additions & 0 deletions

File tree

Some content is hidden

Large Commits have some content hidden by default. Use the searchbox below for content that may be hidden.

‎plugins/source/gcp/policies/README.md‎

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1,3 +1,7 @@
1+
# Deprecation Notice
2+
3+
These are the policy files for CloudQuery **v0.x.x**. Please use the [policies_v1/](../policies_v1/) directory for CloudQuery v1.x.x policies.
4+
15
# CloudQuery Policies
26
CloudQuery SQL Policies for GCP
37

‎plugins/source/gcp/policies_v1/README.md‎

Lines changed: 42 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,42 @@
1+
# CloudQuery Policies
2+
CloudQuery SQL Policies for GCP
3+
4+
## Policies and Compliance Frameworks Available
5+
6+
- [GCP CIS v1.2.0](./cis_v1.2.0/policy.sql)
7+
8+
## Running
9+
10+
You can execute policies with `psql`. For example:
11+
12+
```bash
13+
# Execute CIS V1.2.0 Policy
14+
psql ${DSN} -f ./cis_v1.2.0/policy.sql
15+
```
16+
17+
This will create all the results in `gcp_policy_results` table which you can query directly, connect to any BI system (Grafana, Preset, AWS QuickSight, PowerBI, …).
18+
19+
You can also output it into CSV or HTML with the following built-in `psql` commands:
20+
21+
```bash
22+
# Set DSN to your PostgreSQL populated by CloudQuery
23+
export DSN=postgres://postgres:pass@localhost:5432/postgres
24+
25+
# default tabular output
26+
psql ${DSN} -c "select * from gcp_policy_results"
27+
# CSV output
28+
psql ${DSN} -c "select * from gcp_policy_results" --csv
29+
# HTML output
30+
psql ${DSN} -c "select * from gcp_policy_results" --html
31+
32+
# see only failed checks
33+
psql ${DSN} -c "select * from gcp_policy_results where status='fail'"
34+
```
35+
36+
## Dashboards
37+
38+
Currently we have pre-built dashboards on top of `gcp_policy_results` table which is available [here](../dashboards/grafana/compliance.json).
39+
40+
<img alt="Azure Asset Inventory Grafana Dashboard" src="../dashboards/grafana/compliance.png" width=50% height=50%>
41+
42+
See [installation instructions](../dashboards/README.md#gcp-compliance-and-cspm-cloud-security-posture-management-dashboard)

‎plugins/source/gcp/policies_v1/cis_v1.2.0/policy.sql‎

Lines changed: 18 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,18 @@
1+
\set ON_ERROR_STOP on
2+
SET TIME ZONE 'UTC';
3+
-- 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.2.0'
11+
\ir ../create_gcp_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
17+
\ir section_6.sql
18+
\ir section_7.sql

‎plugins/source/gcp/policies_v1/cis_v1.2.0/section_1.sql‎

Lines changed: 62 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,62 @@
1+
\set framework 'cis_v1.2.0'
2+
\echo "Creating CIS V1.2.0 Section 1 Views"
3+
\ir ../views/project_policy_members.sql
4+
\echo "Executing CIS V1.2.0 Section 1"
5+
\set check_id '1.1'
6+
\echo "Executing check 1.1"
7+
\echo "Ensure that corporate login credentials are used (Automated)"
8+
\echo "needs to list folders and organizations which is currently not supported" -- TODO
9+
-- MANUAL
10+
\set check_id '1.2'
11+
\echo "Executing check 1.2"
12+
\echo "Ensure that multi-factor authentication is enabled for all non-service accounts (Manual)"
13+
\ir ../queries/manual.sql
14+
-- MANUAL
15+
\set check_id '1.3'
16+
\echo "Executing check 1.3"
17+
\echo "Ensure that Security Key Enforcement is enabled for all admin accounts (Manual)"
18+
\ir ../queries/manual.sql
19+
\set check_id '1.4'
20+
\echo "Executing check 1.4"
21+
\ir ../queries/iam/managed_service_account_keys.sql
22+
\set check_id '1.5'
23+
\echo "Executing check 1.5"
24+
\ir ../queries/iam/service_account_admin_priv.sql
25+
\set check_id '1.6'
26+
\echo "Executing check 1.6"
27+
\ir ../queries/iam/users_with_service_account_token_creator_role.sql
28+
\set check_id '1.7'
29+
\echo "Executing check 1.7"
30+
\ir ../queries/iam/service_account_keys_not_rotated.sql
31+
\set check_id '1.8'
32+
\echo "Executing check 1.8"
33+
\ir ../queries/iam/separation_of_duties.sql
34+
\set check_id '1.9'
35+
\echo "Executing check 1.9"
36+
\ir ../queries/kms/publicly_accessible.sql
37+
\set check_id '1.10'
38+
\echo "Executing check 1.10"
39+
\ir ../queries/kms/keys_not_rotated_within_90_days.sql
40+
\set check_id '1.11'
41+
\echo "Executing check 1.11"
42+
\ir ../queries/iam/kms_separation_of_duties.sql
43+
-- MANUAL
44+
\set check_id '1.12'
45+
\echo "Executing check 1.12"
46+
\echo "Ensure API keys are not created for a project (Manual)"
47+
\ir ../queries/manual.sql
48+
-- MANUAL
49+
\set check_id '1.13'
50+
\echo "Executing check 1.13"
51+
\echo "Ensure API keys are restricted to use by only specified Hosts and Apps (Manual)"
52+
\ir ../queries/manual.sql
53+
-- MANUAL
54+
\set check_id '1.14'
55+
\echo "Executing check 1.14"
56+
\echo "Ensure API keys are restricted to only APIs that application needs access (Manual)"
57+
\ir ../queries/manual.sql
58+
-- MANUAL
59+
\set check_id '1.15'
60+
\echo "Executing check 1.15"
61+
\echo "Ensure API keys are restricted to only APIs that application needs access (Manual)"
62+
\ir ../queries/manual.sql

‎plugins/source/gcp/policies_v1/cis_v1.2.0/section_2.sql‎

Lines changed: 40 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,40 @@
1+
\set framework 'cis_v1.2.0'
2+
\echo "Creating CIS V1.2.0 Section 2 Views"
3+
\ir ../views/log_metric_filters.sql
4+
\echo "Executing CIS V1.2.0 Section 2"
5+
\set check_id '2.1'
6+
\echo "Executing check 2.1"
7+
\ir ../queries/logging/not_configured_across_services_and_users.sql
8+
\set check_id '2.2'
9+
\echo "Executing check 2.2"
10+
\ir ../queries/logging/sinks_not_configured_for_all_log_entries.sql
11+
\set check_id '2.3'
12+
\echo "Executing check 2.3"
13+
\ir ../queries/logging/log_buckets_retention_policy_disabled.sql
14+
\set check_id '2.4'
15+
\echo "Executing check 2.4"
16+
\ir ../queries/logging/project_ownership_changes_without_log_metric_filter_alerts.sql
17+
\set check_id '2.5'
18+
\echo "Executing check 2.5"
19+
\ir ../queries/logging/audit_config_changes_without_log_metric_filter_alerts.sql
20+
\set check_id '2.6'
21+
\echo "Executing check 2.6"
22+
\ir ../queries/logging/custom_role_changes_without_log_metric_filter_alerts.sql
23+
\set check_id '2.7'
24+
\echo "Executing check 2.7"
25+
\ir ../queries/logging/vpc_firewall_changes_without_log_metric_filter_alerts.sql
26+
\set check_id '2.8'
27+
\echo "Executing check 2.8"
28+
\ir ../queries/logging/vpc_route_changes_without_log_metric_filter_alerts.sql
29+
\set check_id '2.9'
30+
\echo "Executing check 2.9"
31+
\ir ../queries/logging/vpc_network_changes_without_log_metric_filter_alerts.sql
32+
\set check_id '2.10'
33+
\echo "Executing check 2.10"
34+
\ir ../queries/logging/storage_iam_changes_without_log_metric_filter_alerts.sql
35+
\set check_id '2.11'
36+
\echo "Executing check 2.11"
37+
\ir ../queries/logging/sql_instance_changes_without_log_metric_filter_alerts.sql
38+
\set check_id '2.12'
39+
\echo "Executing check 2.12"
40+
\ir ../queries/logging/dns_logging_disabled.sql

‎plugins/source/gcp/policies_v1/cis_v1.2.0/section_3.sql‎

Lines changed: 34 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,34 @@
1+
\set framework 'cis_v1.2.0'
2+
\echo "Creating CIS V1.2.0 Section 3 Views"
3+
\ir ../views/firewall_allowed_rules.sql
4+
\echo "Executing CIS V1.2.0 Section 3"
5+
\set check_id '3.1'
6+
\echo "Executing check 3.1"
7+
\ir ../queries/compute/default_network_exist.sql
8+
\set check_id '3.2'
9+
\echo "Executing check 3.2"
10+
\ir ../queries/compute/legacy_network_exist.sql
11+
\set check_id '3.3'
12+
\echo "Executing check 3.3"
13+
\ir ../queries/compute/dnssec_disabled.sql
14+
\set check_id '3.4'
15+
\echo "Executing check 3.4"
16+
\ir ../queries/dns/key_signing_with_rsasha1.sql
17+
\set check_id '3.5'
18+
\echo "Executing check 3.5"
19+
\ir ../queries/dns/zone_signing_with_rsasha1.sql
20+
\set check_id '3.6'
21+
\echo "Executing check 3.6"
22+
\ir ../queries/compute/ssh_access_permitted.sql
23+
\set check_id '3.7'
24+
\echo "Executing check 3.7"
25+
\ir ../queries/compute/rdp_access_permitted.sql
26+
\set check_id '3.8'
27+
\echo "Executing check 3.8"
28+
\ir ../queries/compute/flow_logs_disabled_in_vpc.sql
29+
\set check_id '3.9'
30+
\echo "Executing check 3.9"
31+
-- \ir ../queries/compute/ssl_proxy_with_weak_cipher.sql
32+
\set check_id '3.10'
33+
\echo "Executing check 3.10"
34+
\ir ../queries/compute/allow_traffic_behind_iap.sql

‎plugins/source/gcp/policies_v1/cis_v1.2.0/section_4.sql‎

Lines changed: 37 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,37 @@
1+
\set framework 'cis_v1.2.0'
2+
\echo "Executing CIS V1.2.0 Section 4"
3+
\set check_id '4.1'
4+
\echo "Executing check 4.1"
5+
\ir ../queries/compute/instances_with_default_service_account.sql
6+
\set check_id '4.2'
7+
\echo "Executing check 4.2"
8+
\ir ../queries/compute/instances_with_default_service_account_with_full_access.sql
9+
\set check_id '4.3'
10+
\echo "Executing check 4.3"
11+
\ir ../queries/compute/instances_without_block_project_wide_ssh_keys.sql
12+
\set check_id '4.4'
13+
\echo "Executing check 4.4"
14+
\ir ../queries/compute/oslogin_disabled.sql
15+
\set check_id '4.5'
16+
\echo "Executing check 4.5"
17+
\ir ../queries/compute/serial_port_connection_enabled.sql
18+
\set check_id '4.6'
19+
\echo "Executing check 4.6"
20+
\ir ../queries/compute/instance_ip_forwarding_enabled.sql
21+
\set check_id '4.7'
22+
\echo "Executing check 4.7"
23+
\ir ../queries/compute/disks_encrypted_with_csek.sql
24+
\set check_id '4.8'
25+
\echo "Executing check 4.8"
26+
\ir ../queries/compute/instances_with_shielded_vm_disabled.sql
27+
\set check_id '4.9'
28+
\echo "Executing check 4.9"
29+
\ir ../queries/compute/instances_with_public_ip.sql
30+
-- MANUAL
31+
\set check_id '4.10'
32+
\echo "Executing check 4.10"
33+
\echo "Ensure that App Engine applications enforce HTTPS connections (Manual)"
34+
\ir ../queries/manual.sql
35+
\set check_id '4.11'
36+
\echo "Executing check 4.11"
37+
\ir ../queries/compute/instances_without_confidential_computing.sql

‎plugins/source/gcp/policies_v1/cis_v1.2.0/section_5.sql‎

Lines changed: 10 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,10 @@
1+
\set framework 'cis_v1.2.0'
2+
\echo "Creating CIS V1.2.0 Section 5 Views"
3+
\ir ../views/buckets_permissions.sql
4+
\echo "Executing CIS V1.2.0 Section 5"
5+
\set check_id '5.1'
6+
\echo "Executing check 5.1"
7+
\ir ../queries/storage/buckets_publicly_accessible.sql
8+
\set check_id '5.2'
9+
\echo "Executing check 5.2"
10+
\ir ../queries/storage/buckets_without_uniform_bucket_level_access.sql

‎plugins/source/gcp/policies_v1/cis_v1.2.0/section_6.sql‎

Lines changed: 96 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,96 @@
1+
\set framework 'cis_v1.2.0'
2+
\echo "Executing CIS V1.2.0 Section 6"
3+
-- MANUAL
4+
\set check_id '6.1.1'
5+
\echo "Executing check 6.1.1"
6+
\echo "Ensure that a MySQL database instance does not allow anyone to connect with administrative privileges (Automated)"
7+
\ir ../queries/manual.sql
8+
\set check_id '6.1.2'
9+
\echo "Executing check 6.1.2"
10+
\ir ../queries/sql/mysql_skip_show_database_flag_off.sql
11+
\set check_id '6.1.3'
12+
\echo "Executing check 6.1.3"
13+
\ir ../queries/sql/mysql_local_inline_flag_on.sql
14+
\set check_id '6.2.1'
15+
\echo "Executing check 6.2.1"
16+
\ir ../queries/sql/postgresql_log_checkpoints_flag_off.sql
17+
\set check_id '6.2.2'
18+
\echo "Executing check 6.2.2"
19+
\ir ../queries/sql/postgresql_log_error_verbosity_flag_not_strict.sql
20+
\set check_id '6.2.3'
21+
\echo "Executing check 6.2.3"
22+
\ir ../queries/sql/postgresql_log_connections_flag_off.sql
23+
\set check_id '6.2.4'
24+
\echo "Executing check 6.2.4"
25+
\ir ../queries/sql/postgresql_log_disconnections_flag_off.sql
26+
\set check_id '6.2.5'
27+
\echo "Executing check 6.2.5"
28+
\ir ../queries/sql/postgresql_log_duration_flag_off.sql
29+
\set check_id '6.2.6'
30+
\echo "Executing check 6.2.6"
31+
\ir ../queries/sql/postgresql_log_lock_waits_flag_off.sql
32+
\set check_id '6.2.7'
33+
\echo "Executing check 6.2.7"
34+
\echo "Ensure 'log_statement' database flag for Cloud SQL PostgreSQL instance is set appropriately (Manual)"
35+
\ir ../queries/manual.sql
36+
\set check_id '6.2.8'
37+
\echo "Executing check 6.2.8"
38+
\ir ../queries/sql/postgresql_log_hostname_flag_off.sql
39+
\set check_id '6.2.9'
40+
\echo "Executing check 6.2.9"
41+
\ir ../queries/sql/postgresql_log_parser_stats_flag_on.sql
42+
\set check_id '6.2.10'
43+
\echo "Executing check 6.2.10"
44+
\ir ../queries/sql/postgresql_log_planner_stats_flag_on.sql
45+
\set check_id '6.2.11'
46+
\echo "Executing check 6.2.11"
47+
\ir ../queries/sql/postgresql_log_executor_stats_flag_on.sql
48+
\set check_id '6.2.12'
49+
\echo "Executing check 6.2.12"
50+
\ir ../queries/sql/postgresql_log_statement_stats_flag_on.sql
51+
\set check_id '6.2.13'
52+
\echo "Executing check 6.2.13"
53+
\echo "Ensure that the 'log_min_messages' database flag for Cloud SQL PostgreSQL instance is set appropriately (Manual)"
54+
\ir ../queries/manual.sql
55+
\set check_id '6.2.14'
56+
\echo "Executing check 6.2.14"
57+
\ir ../queries/sql/postgresql_log_min_error_statement_flag_less_error.sql
58+
\set check_id '6.2.15'
59+
\echo "Executing check 6.2.15"
60+
\ir ../queries/sql/postgresql_log_temp_files_flag_off.sql
61+
\set check_id '6.2.16'
62+
\echo "Executing check 6.2.16"
63+
\ir ../queries/sql/postgresql_log_min_duration_statement_flag_on.sql
64+
\set check_id '6.3.1'
65+
\echo "Executing check 6.3.1"
66+
\ir ../queries/sql/sqlserver_external_scripts_enabled_flag_on.sql
67+
\set check_id '6.3.2'
68+
\echo "Executing check 6.3.2"
69+
\ir ../queries/sql/sqlserver_cross_db_ownership_chaining_flag_on.sql
70+
\set check_id '6.3.3'
71+
\echo "Executing check 6.3.3"
72+
\ir ../queries/sql/sqlserver_user_connections_flag_not_set.sql
73+
\set check_id '6.3.4'
74+
\echo "Executing check 6.3.4"
75+
\ir ../queries/sql/sqlserver_user_options_flag_set.sql
76+
\set check_id '6.3.5'
77+
\echo "Executing check 6.3.5"
78+
\ir ../queries/sql/sqlserver_remote_access_flag_on.sql
79+
\set check_id '6.3.6'
80+
\echo "Executing check 6.3.6"
81+
\ir ../queries/sql/sqlserver_trace_flag_on.sql
82+
\set check_id '6.3.7'
83+
\echo "Executing check 6.3.7"
84+
\ir ../queries/sql/sqlserver_contained_database_authentication_flag_on.sql
85+
\set check_id '6.4'
86+
\echo "Executing check 6.4"
87+
\ir ../queries/sql/db_instance_without_ssl.sql
88+
\set check_id '6.5'
89+
\echo "Executing check 6.5"
90+
\ir ../queries/sql/db_instance_publicly_accessible.sql
91+
\set check_id '6.6'
92+
\echo "Executing check 6.6"
93+
\ir ../queries/sql/db_instance_with_public_ip.sql
94+
\set check_id '6.7'
95+
\echo "Executing check 6.7"
96+
\ir ../queries/sql/db_instances_without_backups.sql

‎plugins/source/gcp/policies_v1/cis_v1.2.0/section_7.sql‎

Lines changed: 11 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,11 @@
1+
\set framework 'cis_v1.2.0'
2+
\echo "Executing CIS V1.2.0 Section 7"
3+
\set check_id '7.1'
4+
\echo "Executing check 7.1"
5+
\ir ../queries/bigquery/datasets_publicly_accessible.sql
6+
\set check_id '7.2'
7+
\echo "Executing check 7.2"
8+
\ir ../queries/bigquery/tables_not_encrypted_with_cmek.sql
9+
\set check_id '7.3'
10+
\echo "Executing check 7.3"
11+
\ir ../queries/bigquery/datasets_without_default_cmek.sql

0 commit comments

Comments
 (0)