Accessing Custom Groups cause System exception in vRA UI

I was involved in a problem recently where a tenant admin tries accessing custom groups and he encounters a system exception

Cause of this problem is unknown but due to changes performed for specific users from Active Directory perspective , vRA marks them as soft-deleted inside postgres database instead of completely removing them.

Note : To resolve this problem we have to modify vRA's Postgres database so ensure a proper backup is taken. I would also advice to take snapshots on vRA appliances

If in doubt or not comfortable in executing these changes contact VMware Support through Support Request

Step 0

Login into vRA's Database either ssh or using PgAdmin tool. PgAdmin is the recommended as it's easy to modify and update

Step 1

Extracting the User ids from the Custom Groups("groupType"='DYNAMIC') from Group table.

select array_agg(e::text::int) from saas."Group" ,json_array_elements("compositionRules"::json -> 'addedUserIds') as e where "groupType"='DYNAMIC';

Step 2

Take the UserIds from Step1 and Query for Soft deleted Users

select "idUser" from saas."Users" where "idUserStatus"=3 and "idUser" IN(<all ids from step 1>);

Step 3

Having all problematic User IDs, query back the Group table to get the groups in which they are members of

select "id" from saas."Group" where "compositionRules" ~* 'iduser1|idUser2';

As shown in the above screenshot id : 1959 is the one which was soft deleted from our environment

so the query would be

select "id" from saas."Group" where "compositionRules" ~* '1959';

Step 4

Now browse the group as we identified id of that group where this user is part of

Double click on compositionRules

You can see 1959 user part of this group

Edit this section to remove this id from this column and save it

As you can see 1959 does not exist anymore

Use PGAdmin to edit the groups from the list from Step 3 and remove the problematic User IDs. If PGAdmin is not an option, one needs to load everything for a given group, then remove the problematic User IDs from "compositionRules" column and update back the group to the DB

Perform a complete sync post this and this exception should not be seen anymore.