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;