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;