Table: aws_sns_topics
This table shows data for Sns Topics.
https://docs.aws.amazon.com/sns/latest/api/API_GetTopicAttributes.html (opens in a new tab)
The primary key for this table is arn.
Columns
| Name | Type |
|---|---|
| _cq_id | uuid |
| _cq_parent_id | uuid |
| account_id | utf8 |
| region | utf8 |
| arn (PK) | utf8 |
| tags | json |
| delivery_policy | json |
| policy | json |
| effective_delivery_policy | json |
| display_name | utf8 |
| owner | utf8 |
| subscriptions_confirmed | int64 |
| subscriptions_deleted | int64 |
| subscriptions_pending | int64 |
| kms_master_key_id | utf8 |
| fifo_topic | bool |
| content_based_deduplication | bool |
| unknown_fields | json |
Example Queries
These SQL queries are sampled from CloudQuery policies and are compatible with PostgreSQL.
SNS topics should be encrypted at rest using AWS KMS
SELECT
'SNS topics should be encrypted at rest using AWS KMS' AS title,
account_id,
arn AS resource_id,
CASE
WHEN kms_master_key_id IS NULL OR kms_master_key_id = '' THEN 'fail'
ELSE 'pass'
END
AS status
FROM
aws_sns_topics;Logging of delivery status should be enabled for notification messages sent to a topic
SELECT
'Logging of delivery status should be enabled for notification messages sent to a topic'
AS title,
account_id,
arn AS resource_id,
CASE
WHEN (unknown_fields->'HTTPSuccessFeedbackRoleArn') IS NULL
AND (unknown_fields->'FirehoseSuccessFeedbackRoleArn') IS NULL
AND (unknown_fields->'LambdaSuccessFeedbackRoleArn') IS NULL
AND (unknown_fields->'ApplicationSuccessFeedbackRoleArn') IS NULL
AND (unknown_fields->'SQSSuccessFeedbackRoleArn') IS NULL
THEN 'fail'
ELSE 'pass'
END
AS status
FROM
aws_sns_topics;Unused SNS topic
WITH
subscription AS (SELECT DISTINCT topic_arn FROM aws_sns_subscriptions)
SELECT
'Unused SNS topic' AS title,
topic.account_id,
topic.arn AS resource_id,
'fail' AS status
FROM
aws_sns_topics AS topic
LEFT JOIN subscription ON subscription.topic_arn = topic.arn
WHERE
subscription.topic_arn IS NULL;