Cleaning the OAuth2Client table for the excess of entries created by an operation in vRA
search cancel

Cleaning the OAuth2Client table for the excess of entries created by an operation in vRA

book

Article ID: 301274

calendar_today

Updated On:

Products

VMware Aria Suite

Issue/Introduction

Symptoms:
  • Connection timeout error is shown when creating a new tenant (or) editing (adding or removing) a tenant administrator of an existing tenant.
  • The OAuth2Client table contains hundreds or thousands of Test-Service-* records.


Environment

VMware vRealize Automation 7.4.x
VMware vRealize Automation 7.3.x

Cause

The OAuth2Client table is populated by the Health Service (Test-Service) making a lot of request for new OAuth2 clients. These clients create a problem when creating a new tenant because every new tenant copies all the existing OAuth2 clients for all the services from the default tenant. When the number of OAuth2 clients increases above few hundreds, this will result in a timeout for the tenant creation.

Resolution

This issue is resolved in VMware vRealize Automation 7.3.1 and VMware vRealize Automation 7.4, available at VMware Downloads

To resolve this issue in vRealize Automation 7.3.0, verify the state of the OAuth2Client table, apply the security fix mentioned in KB Security fix for deserialization vulnerability via Xenon(CVE-2017-4947) in vRealize Automation 7.3 (52326) and perform the cleanup operation: 

Note: Ensure to apply the above mentioned Security fix before applying the cleanup procedure.

To verify the state of the OAuth2Client table:

  1. Open the vcac database with psql:

    su - postgres /opt/vmware/vpostgres/9.5/bin/psql vcac


     
  2. Set the schema to saas by running the command

    set schema 'saas';


     
  3. Count the number of entries in the table by running the command:

    SELECT count(*) FROM saas."OAuth2Client";


     
  4. Count the number of entries with Test-Service in the name by running the command:

    SELECT count(*) FROM saas."OAuth2Client" WHERE "clientId" Like 'Test-Service-%' LIMIT 10;

     
  5. To exit the psql tool by running the command: \q

If the number of Test-Service-* records is more than a hundred, the cleanup procedure described in this KB article should be performed.

IMPORTANT: The procedure below SHOULD NOT be executed for records not following the pattern "Test-Service-GUID" in such cases VMware support should be contacted.



To perform the cleanup operation:
  1. Before the cleanup operation starts run the command:  vcac-vami horizon ui enable
  2. Copy the attached 51501_removeOAuth2Client.zip file to vRA terminal.
  3. Extract the zip file to get removeOAuth2Client.sh script.
  4. The script should do cleanup operation for all the tenant which are present in vRA. Get the tenant information from DB by running the command:

    select "strOrganization",id from "Organizations";
     
    -----------------sample output------------------------

    -[ RECORD 1 ]---+--------------
    strOrganization | VSPHERE.LOCAL
    id | 2
    -[ RECORD 2 ]---+--------------
    strOrganization | TENANT2
    id | 106
  5. Repeat these steps for each tenant:
    1. select "clientId" from "OAuth2Client" where "clientId" Like 'Test-Service-%' AND “idOrganization”=<id mapping for this tenant from step 1> \g client_list.txt

      Note: Ensure that not to use \x cmd (in other words Expanded display should be off) in the psql command line, when you are running the above query as it changes the format of client_list.txt file. In case the entries are very large (let’s say more than 40k) you can do the operation in chunks as well. Repeat the Step 5 again and again until the client_list becomes zero. For creating the chunk, run the query as:
       
      select "clientId" from "OAuth2Client" where "clientId" Like 'Test-Service-%' AND “idOrganization”=<id mapping for this tenant from step 1> Limit 40000 \g client_list.txt  )
    2. Move the file to the same location where you have placed the removeOAuth2Client.sh script, for example: location is /root.

      mv /var/vmware/vpostgres/9.4/client_list.txt ~/
       
    3. Set the vRA hostname in variable VRA. Use the vRA node hostname, not the load balancer hostname.

      export VRA=vra-fqdn
    4. Set the TenantId in variable TenantId :
      export TenantId=<tenant name for this tenant>
    5. Get sessionToken. Replace the username and password with the local admin username and password for this tenant:

      curl --insecure https://$VRA/SAAS/t/$TenantId/API/1.0/REST/auth/system/login/ -X POST -d '{"username":"<username>", "password":"<password>", "issueToken":"true"}' -H "Accept: application/json" -H "Content-Type: application/json"
    6. Set the sessionToken from step “e" in variable AUTH:
      export AUTH=<sessionToken>
    7. Run the below script, it is expected that, script will be running for a very long time:

      ./removeOAuth2Client.sh
  6. Once the cleanup operation is done run the command:

    vcac-vami horizon ui disable



Additional Information

简体中文:清理 OAuth2Client 表中因 vRA 操作而产生的多余条目

Attachments

51501_removeOAuth2Client.zip get_app