Query: sns_topic_policy_prohibit_cross_account_access

with cross_account_policies as (
  select
    topic_arn,
    count(*) as statements_num
  from
    aws_sns_topic,
    jsonb_array_elements(policy_std -> 'Statement') as s,
    jsonb_array_elements_text(s -> 'Principal' -> 'AWS') as p
  where
    s ->> 'Effect' = 'Allow'
    and (
      ( s -> 'Principal' -> 'AWS') = '["*"]'
      or s ->> 'Principal' = '*'
      or split_part(p, ':', 5) <> account_id
    )
  group by
    topic_arn
)
select
  t.topic_arn as resource,
  case
    when p.topic_arn is null then 'ok'
    else 'alarm'
  end as status,
  case
    when p.topic_arn is null then title || ' does not allow cross account access.'
    else title || ' contains ' || coalesce(p.statements_num,0) || ' statements that allows cross account access.'
  end as reason
  
  , t.region, t.account_id
from
  aws_sns_topic as t
  left join cross_account_policies as p on p.topic_arn = t.topic_arn;