Table: alicloud_ram_user

select
  user_id,
  name,
  display_name
from
  alicloud_ram_user;

select
  name,
  last_login_date
from
  alicloud_ram_user
where
  last_login_date < current_date - interval '30 days';

select
  name,
  last_login_date
from
  alicloud_ram_user
where
  last_login_date is null;

select
  name as user_name,
  iam_group ->> 'GroupName' as group_name,
  iam_group ->> 'JoinDate' as join_date
from
  alicloud_ram_user,
  jsonb_array_elements(groups) as iam_group;

select
  name as user_name,
  policies ->> 'PolicyName' as policy_name,
  policies ->> 'PolicyType' as policy_type,
  policies ->> 'DefaultVersion' as policy_default_version,
  policies ->> 'AttachDate' as policy_attachment_date
from
  alicloud_ram_user,
  jsonb_array_elements(attached_policy) as policies
where
  policies ->> 'PolicyName' = 'AdministratorAccess';

select
  name as user_name,
  user_id as user_id,
  mfa_enabled
from
  alicloud_ram_user
where
  not mfa_enabled;

select
  name as user_name,
  user_id as user_id
from
  alicloud_ram_user
where
  cs_user_permission <> '[]';