Table: aws_sns_topic

select
  title,
  kms_master_key_id
from
  aws_sns_topic
where
  kms_master_key_id is null;

select
  title,
  kms_master_key_id
from
  aws_sns_topic
where
  kms_master_key_id = 'alias/aws/sns';

select
  title,
  tags
from
  aws_sns_topic
where
  not tags :: JSONB ? 'owner';

select
  title,
  p as principal,
  a as action,
  s ->> 'Effect' as effect,
  s -> 'Condition' as conditions
from
  aws_sns_topic,
  jsonb_array_elements(policy_std -> 'Statement') as s,
  jsonb_array_elements_text(s -> 'Principal' -> 'AWS') as p,
  jsonb_array_elements_text(s -> 'Action') as a
where
  p = '*'
  and s ->> 'Effect' = 'Allow';

select
  title,
  p as principal,
  a as action,
  s ->> 'Effect' as effect,
  s -> 'Condition' as conditions
from
  aws_sns_topic,
  jsonb_array_elements(policy_std -> 'Statement') as s,
  jsonb_array_elements_text(s -> 'Principal' -> 'AWS') as p,
  jsonb_array_elements_text(s -> 'Action') as a
where
  s ->> 'Effect' = 'Allow'
  and a in ('*', 'sns:*');

select
  title
from
  aws_sns_topic
where
  title not in (
    select
      title
    from
      aws_sns_topic,
      jsonb_array_elements(policy_std -> 'Statement') as s,
      jsonb_array_elements_text(s -> 'Principal' -> 'AWS') as p,
      jsonb_array_elements_text(s -> 'Action') as a,
      jsonb_array_elements_text(s -> 'Condition' -> 'Bool' -> 'aws:securetransport') as ssl
    where
      p = '*'
      and s ->> 'Effect' = 'Deny'
      and ssl :: bool = false
  );

select
  title,
  topic_arn,
  region
from
  aws_sns_topic
where
  application_failure_feedback_role_arn is null
  and firehose_failure_feedback_role_arn is null
  and http_failure_feedback_role_arn is null
  and lambda_failure_feedback_role_arn is null
  and sqs_failure_feedback_role_arn is null;