turbot/gcp_compliance

Query: compute_instance_no_write_permission_on_deny_policy

Usage

powerpipe query gcp_compliance.query.compute_instance_no_write_permission_on_deny_policy

SQL

with role_with_write_permission_on_deny_policy as (
select
distinct name,
project
from
gcp_iam_role,
jsonb_array_elements_text(included_permissions) as p
where
not is_gcp_managed
and p in ( 'iam.denypolicies.delete', 'iam.denypolicies.update')
), policy_with_write_permission_on_deny_policy as (
select
distinct entity,
project
from
gcp_iam_policy,
jsonb_array_elements(bindings) as p,
jsonb_array_elements_text(p -> 'members') as entity
where
p ->> 'role' in (select name from role_with_write_permission_on_deny_policy)
), compute_instance_with_write_permission_on_deny_policy as (
select
distinct self_link
from
gcp_compute_instance as i,
jsonb_array_elements(service_accounts) as e
left join policy_with_write_permission_on_deny_policy as b on b.entity = concat('serviceAccount:' || (e ->> 'email'))
where
b.entity is not null
)
select
i.self_link as resource,
case
when p.self_link is not null then 'alarm'
else 'ok'
end as status,
case
when p.self_link is not null then i.title || ' allow write permission on_deny policies.'
else i.title || ' restrict swrite permission on_deny policies.'
end as reason
, location as location, project as project
from
gcp_compute_instance as i
left join compute_instance_with_write_permission_on_deny_policy as p on p.self_link = i.self_link;

Controls

The query is being used by the following controls: