Query: sqs_queue_granted_public_access

with policy as (
  select
    *,
    unnest(
      from_json(
        (
          request_parameters -> 'attributes' ->> 'Policy' -> 'Statement'
        ),
        '["JSON"]'
      )
    ) as statement_item,
  from
    aws_cloudtrail_log
  where
    event_source = 'sqs.amazonaws.com'
    and event_name = 'SetQueueAttributes'
    and (request_parameters -> 'attributes' ->> 'Policy') != ''
)
select
  tp_timestamp as timestamp,
  string_split(event_source, '.') [ 1 ] || ':' || event_name as operation,
  coalesce(
    response_elements ->> 'queueUrl',
    request_parameters ->> 'queueUrl'
  ) as resource,
  user_identity.arn as actor,
  tp_source_ip as source_ip,
  tp_index as account_id,
  aws_region as region,
  tp_id as source_id,
  *
from
  policy
where
  (statement_item ->> 'Effect') = 'Allow'
  and (
    (
      json_contains((statement_item -> 'Principal'), '{"AWS":"*"}')
    )
    or ((statement_item ->> 'Principal') = '*')
  )
  and error_code is null
order by
  event_time desc;