Query: iam_role_cross_account_write_access_policy

with cross_account_write_policies as (
  select
    r.arn as role_arn,
    r.account_id,
    r.region,
    p.arn as policy_arn,
    p.policy_std
  from
    aws_iam_role as r
    cross join jsonb_array_elements_text(r.attached_policy_arns) as policy_arn
    join aws_iam_policy as p on p.arn = policy_arn
  where
    p.policy_std -> 'Statement' @> '[{"Effect": "Allow", "Principal": {"AWS": ["*"]}}]'
    or p.policy_std -> 'Statement' @> '[{"Effect": "Allow", "Principal": {"Service": ["*"]}}]'
    and (
      p.policy_std -> 'Statement' @> '[{"Action": ["*"]}]'
      or p.policy_std -> 'Statement' @> '[{"Action": ["*:*"]}]'
      or p.policy_std -> 'Statement' @> '[{"Action": ["*:Put*"]}]'
      or p.policy_std -> 'Statement' @> '[{"Action": ["*:Delete*"]}]'
      or p.policy_std -> 'Statement' @> '[{"Action": ["*:Create*"]}]'
      or p.policy_std -> 'Statement' @> '[{"Action": ["*:Update*"]}]'
      or p.policy_std -> 'Statement' @> '[{"Action": ["*:Modify*"]}]'
      or p.policy_std -> 'Statement' @> '[{"Action": ["*:Attach*"]}]'
      or p.policy_std -> 'Statement' @> '[{"Action": ["*:Detach*"]}]'
      or p.policy_std -> 'Statement' @> '[{"Action": ["*:Replace*"]}]'
      or p.policy_std -> 'Statement' @> '[{"Action": ["*:Tag*"]}]'
      or p.policy_std -> 'Statement' @> '[{"Action": ["*:Untag*"]}]'
    )
)
select
  r.arn as resource,
  case
    when c.role_arn is null then 'ok'
    else 'alarm'
  end as status,
  case
    when c.role_arn is null then 'Role does not have cross-account write access'
    else 'Role has cross-account write access'
  end as reason
  , r.account_id
from
  aws_iam_role as r
  left join cross_account_write_policies as c on r.arn = c.role_arn;