Query: ram_user_manage_policies_sankey

with args as (
  select
    $1 as ram_user_arn
) -- User
select
  null as from_id,
  name as id,
  title,
  0 as depth,
  'alicloud_ram_user' as category
from
  alicloud_ram_user
where
  arn in (
    select
      ram_user_arn
    from
      args
  ) -- Groups
union
select
  u.name as from_id,
  g.name as id,
  user_groups ->> 'GroupName' as title,
  1 as depth,
  'alicloud_ram_group' as category
from
  alicloud_ram_user as u,
  jsonb_array_elements(u.groups) as user_groups
  inner join alicloud_ram_group g on g.name = user_groups ->> 'GroupName'
where
  u.arn in (
    select
      ram_user_arn
    from
      args
  ) -- Policies (attached to groups)
union
select
  g.name as from_id,
  p.title as id,
  p.title as title,
  2 as depth,
  'alicloud_ram_policy' as category
from
  alicloud_ram_user as u,
  alicloud_ram_policy as p,
  jsonb_array_elements(u.groups) as user_groups
  inner join alicloud_ram_group g on g.name = user_groups ->> 'GroupName',
  jsonb_array_elements(g.attached_policy) as user_policy
where
  user_policy ->> 'PolicyName' = p.title
  and u.arn in (
    select
      ram_user_arn
    from
      args
  ) -- Policies (attached to user)
union
select
  u.name as from_id,
  p.title as id,
  p.title as title,
  2 as depth,
  'alicloud_ram_policy' as category
from
  alicloud_ram_user as u,
  jsonb_array_elements(u.attached_policy) as pol_arn,
  alicloud_ram_policy as p
where
  pol_arn ->> 'PolicyName' = p.title
  and u.arn in (
    select
      ram_user_arn
    from
      args
  );