Query: kms_key_decryption_restricted_in_iam_customer_managed_policy

with policy_with_decrypt_grant as (
  select
    distinct arn
  from
    aws_iam_policy,
    jsonb_array_elements(policy_std -> 'Statement') as statement
  where
    not is_aws_managed
    and statement ->> 'Effect' = 'Allow'
    and statement -> 'Resource' ?| array [ '*',
    'arn:aws:kms:*:' || account_id || ':key/*',
    'arn:aws:kms:*:' || account_id || ':alias/*' ]
    and statement -> 'Action' ?| array [ '*',
    'kms:*',
    'kms:decrypt',
    'kms:reencryptfrom',
    'kms:reencrypt*' ]
)
select
  i.arn as resource,
  case
    when d.arn is null then 'ok'
    else 'alarm'
  end as status,
  case
    when d.arn is null then i.title || ' doesn''t allow decryption actions on all keys.'
    else i.title || ' allows decryption actions on all keys.'
  end as reason,
  i.account_id
from
  aws_iam_policy i
  left join policy_with_decrypt_grant d on i.arn = d.arn
where
  not is_aws_managed;