turbot/aws_insights

Query: iam_user_manage_policies_sankey

Usage

powerpipe query aws_insights.query.iam_user_manage_policies_sankey

SQL

with args as (
select $1 as iam_user_arn
)
-- User
select
null as from_id,
arn as id,
title,
0 as depth,
'aws_iam_user' as category
from
aws_iam_user
where
arn in (select iam_user_arn from args)
-- Groups
union select
u.arn as from_id,
g ->> 'Arn' as id,
g ->> 'GroupName' as title,
1 as depth,
'aws_iam_group' as category
from
aws_iam_user as u,
jsonb_array_elements(groups) as g
where
u.arn in (select iam_user_arn from args)
-- Policies (attached to groups)
union select
g.arn as from_id,
p.arn as id,
p.title as title,
2 as depth,
'aws_iam_policy' as category
from
aws_iam_user as u,
aws_iam_policy as p,
jsonb_array_elements(u.groups) as user_groups
inner join aws_iam_group g on g.arn = user_groups ->> 'Arn'
where
g.attached_policy_arns :: jsonb ? p.arn
and u.arn in (select iam_user_arn from args)
-- Policies (inline from groups)
union select
grp.arn as from_id,
concat(grp.group_id, '_' , i ->> 'PolicyName') as id,
concat(i ->> 'PolicyName', ' (inline)') as title,
2 as depth,
'inline_policy' as category
from
aws_iam_user as u,
jsonb_array_elements(u.groups) as g,
aws_iam_group as grp,
jsonb_array_elements(grp.inline_policies_std) as i
where
grp.arn = g ->> 'Arn'
and u.arn in (select iam_user_arn from args)
-- Policies (attached to user)
union select
u.arn as from_id,
p.arn as id,
p.title as title,
2 as depth,
'aws_iam_policy' as category
from
aws_iam_user as u,
jsonb_array_elements_text(u.attached_policy_arns) as pol_arn,
aws_iam_policy as p
where
u.attached_policy_arns :: jsonb ? p.arn
and pol_arn = p.arn
and u.arn in (select iam_user_arn from args)
-- Inline Policies (defined on user)
union select
u.arn as from_id,
concat('inline_', i ->> 'PolicyName') as id,
concat(i ->> 'PolicyName', ' (inline)') as title,
2 as depth,
'inline_policy' as category
from
aws_iam_user as u,
jsonb_array_elements(inline_policies_std) as i
where
u.arn in (select iam_user_arn from args)

Dashboards

The query is used in the dashboards: