turbot/steampipe-mod-aws-compliance

Query: sns_topic_policy_prohibit_cross_account_access

Usage

powerpipe query aws_compliance.query.sns_topic_policy_prohibit_cross_account_access

Steampipe Tables

SQL

with cross_account_policies as (
select
topic_arn,
count(*) as statements_num
from
aws_sns_topic,
jsonb_array_elements(policy_std -> 'Statement') as s,
jsonb_array_elements_text(s -> 'Principal' -> 'AWS') as p
where
s ->> 'Effect' = 'Allow'
and (
( s -> 'Principal' -> 'AWS') = '["*"]'
or s ->> 'Principal' = '*'
or split_part(p, ':', 5) <> account_id
)
and s -> 'Condition' -> 'StringEquals' -> 'aws:sourceowner' is null
and s -> 'Condition' -> 'StringEqualsIgnoreCase' -> 'aws:sourceowner' is null
and (
s -> 'Condition' -> 'StringLike' -> 'aws:sourceowner' is null
or s -> 'Condition' -> 'StringLike' -> 'aws:sourceowner' ? '*'
)
-- aws:SourceAccount
and s -> 'Condition' -> 'StringEquals' -> 'aws:sourceaccount' is null
and s -> 'Condition' -> 'StringEqualsIgnoreCase' -> 'aws:sourceaccount' is null
and (
s -> 'Condition' -> 'StringLike' -> 'aws:sourceaccount' is null
or s -> 'Condition' -> 'StringLike' -> 'aws:sourceaccount' ? '*'
)
-- aws:PrincipalOrgID
and s -> 'Condition' -> 'StringEquals' -> 'aws:principalorgid' is null
and s -> 'Condition' -> 'StringEqualsIgnoreCase' -> 'aws:principalorgid' is null
and (
s -> 'Condition' -> 'StringLike' -> 'aws:principalorgid' is null
or s -> 'Condition' -> 'StringLike' -> 'aws:principalorgid' ? '*'
)
-- aws:PrincipalAccount
and s -> 'Condition' -> 'StringEquals' -> 'aws:principalaccount' is null
and s -> 'Condition' -> 'StringEqualsIgnoreCase' -> 'aws:principalaccount' is null
and (
s -> 'Condition' -> 'StringLike' -> 'aws:principalaccount' is null
or s -> 'Condition' -> 'StringLike' -> 'aws:principalaccount' ? '*'
)
-- aws:PrincipalArn
and s -> 'Condition' -> 'StringEquals' -> 'aws:principalarn' is null
and s -> 'Condition' -> 'StringEqualsIgnoreCase' -> 'aws:principalarn' is null
and (
s -> 'Condition' -> 'StringLike' -> 'aws:principalarn' is null
or s -> 'Condition' -> 'StringLike' -> 'aws:principalarn' ? '*'
)
and (
s -> 'Condition' -> 'ArnEquals' -> 'aws:principalarn' is null
or s -> 'Condition' -> 'ArnEquals' -> 'aws:principalarn' ? '*'
)
and (
s -> 'Condition' -> 'ArnLike' -> 'aws:principalarn' is null
or s -> 'Condition' -> 'ArnLike' -> 'aws:principalarn' ? '*'
)
-- aws:SourceArn
and s -> 'Condition' -> 'StringEquals' -> 'aws:sourcearn' is null
and s -> 'Condition' -> 'StringEqualsIgnoreCase' -> 'aws:sourcearn' is null
and (
s -> 'Condition' -> 'StringLike' -> 'aws:sourcearn' is null
or s -> 'Condition' -> 'StringLike' -> 'aws:sourcearn' ? '*'
)
and (
s -> 'Condition' -> 'ArnEquals' -> 'aws:sourcearn' is null
or s -> 'Condition' -> 'ArnEquals' -> 'aws:sourcearn' ? '*'
)
and (
s -> 'Condition' -> 'ArnLike' -> 'aws:sourcearn' is null
or s -> 'Condition' -> 'ArnLike' -> 'aws:sourcearn' ? '*'
)
group by
topic_arn
)
select
t.topic_arn as resource,
case
when p.topic_arn is null then 'ok'
else 'alarm'
end as status,
case
when p.topic_arn is null then title || ' does not allow cross account access.'
else title || ' contains ' || coalesce(p.statements_num,0) || ' statements that allow cross account access.'
end as reason
, t.region, t.account_id
from
aws_sns_topic as t
left join cross_account_policies as p on p.topic_arn = t.topic_arn;

Controls

The query is being used by the following controls: