Query: iam_user_manage_policies_sankey

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)