Query: iam_role_cross_account_read_only_access_policy

with read_only_access_roles as (
  select
    *
  from
    aws_iam_role,
    jsonb_array_elements_text(attached_policy_arns) as a
  where
    a = 'arn:aws:iam::aws:policy/ReadOnlyAccess'
),
read_only_access_roles_with_cross_account_access as (
  select
    arn
  from
    read_only_access_roles,
    jsonb_array_elements(assume_role_policy_std -> 'Statement') as stmt,
    jsonb_array_elements_text(stmt -> 'Principal' -> 'AWS') as p
  where
    stmt ->> 'Effect' = 'Allow'
    and (
      p = '*'
      or not (p like '%' || account_id || '%')
    )
)
select
  r.arn as resource,
  case
    when ar.arn is null then 'skip'
    when c.arn is not null then 'alarm'
    else 'ok'
  end as status,
  case
    when ar.arn is null then r.title || ' not associated with ReadOnlyAccess policy.'
    when c.arn is not null then r.title || ' associated with ReadOnlyAccess cross account access.'
    else r.title || ' associated ReadOnlyAccess without cross account access.'
  end as reason,
  r.account_id
from
  aws_iam_role as r
  left join read_only_access_roles as ar on r.arn = ar.arn
  left join read_only_access_roles_with_cross_account_access as c on c.arn = r.arn;