turbot/aws_insights

Query: iam_all_policies_for_user

Usage

powerpipe query aws_insights.query.iam_all_policies_for_user

SQL

-- Policies (attached to groups)
select
p.title as "Policy",
p.arn as "ARN",
'Group: ' || g.title as "Via"
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 = $1
-- Policies (inline from groups)
union select
i ->> 'PolicyName' as "Policy",
'N/A' as "ARN",
'Group: ' || grp.title || ' (inline)' as "Via"
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 = $1
-- Policies (attached to user)
union select
p.title as "Policy",
p.arn as "ARN",
'Attached to User' as "Via"
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 = $1
-- Inline Policies (defined on user)
union select
i ->> 'PolicyName' as "Policy",
'N/A' as "ARN",
'Inline' as "Via"
from
aws_iam_user as u,
jsonb_array_elements(inline_policies_std) as i
where
u.arn = $1

Dashboards

The query is used in the dashboards: