Dashboard: AWS IAM User Detail

-- 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