Accessing Custom Groups cause System exception in vRA UI
Updated: Sep 18, 2019
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
Login into vRA's Database either ssh or using PgAdmin tool. PgAdmin is the recommended as it's easy to modify and update
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';
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>);
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';
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.