Query: sqs_queue_public_access_status

with public_access as (
  select
  title
from
  aws_sqs_queue,
  jsonb_array_elements(policy_std -> 'Statement') as s,
  jsonb_array_elements_text(s -> 'Principal' -> 'AWS') as p,
  string_to_array(p, ':') as pa,
  jsonb_array_elements_text(s -> 'Action') as a
where
  s ->> 'Effect' = 'Allow'
  and (
    pa[5] != account_id
    or p = '*'
  )
),
public_access_status as (
  select
    case
      when a.title is null or policy_std is null then 'private'
    else
      'public'
    end public_access_status
  from
    aws_sqs_queue as q
    left join public_access as a on q.title = a.title
  )
  select
    public_access_status,
    count(*)
  from
    public_access_status
  group by
    public_access_status
  order by
    public_access_status desc;