Re-creating OAuthClient fails with error 'oauth2.client.exists" in VIDM 3.3.7

Products

• VCF Automation

Issue/Introduction

• Re-creating an OAuth Client with a previously used name fails with error "oauth2.client.exists"
• /opt/vmware/horizon/workspace/logs/horizon.log shows the errors as below:

ERROR (tomcat-http--6) [######;#########;#.#.#.#;] org.hibernate.engine.jdbc.spi.SqlExceptionHelper - ERROR: duplicate key value violates unique constraint "Users_orgId_dom_name_uniq"
  Detail: Key ("idOrganization", domain, "strUsername", "strNotDeleted")=(#, System Domain, <OAuthClient Name>, 1) already exists.
INFO  (tomcat-http--6) [######;#########;#.#.#.#;] org.hibernate.engine.jdbc.batch.internal.AbstractBatchImpl - HHH000010: On release of batch it still contained JDBC statements
ERROR (tomcat-http--6) [######;#########;#.#.#.#;] org.hibernate.internal.SessionImpl - HHH000346: Error during managed flush [could not execute statement]
WARN  (tomcat-http--6) [######;#########;#.#.#.#;] com.vmware.horizon.components.identity.DirectoryAdminServiceImpl - User name already exists: <OAuthClient Name>
WARN  (tomcat-http--6) [######;#########;#.#.#.#;] com.vmware.horizon.components.authentication.OAuth2RemoteAccessServiceImpl - Failed to create API user for OAuth2 client: user.already.exists

Environment

VMware Identity Manager 3.3.7

Cause

This occurs because deleting an OAuth Client does not automatically remove its associated OAuth Client User. Attempting to recreate the OAuth Client with the same name fails due to a conflict with this stale user entry.

Resolution

1. Take a no memory snapshot of VIDM node(s)
2. Once done, login to SAAS DB and run the query: 
   • psql -U horizon saas
   • select "uuid", "strUsername" from "Users" where "Users"."strUsername"='<OAuthClient Name>';
3. Delete the user via the API below (Replace the fields highlighted):
   • curl -kX DELETE "https://<VIDM_FQDN>/SAAS/jersey/manager/api/scim/Users/<uuid obtained above> -H "Accept: application/json" -H "Authorization: HZN <Bearer>" 
4. Retry the OauthClient creation