Loading

×Sorry to interrupt
This page has an error. You might just need to refresh it.
[NoErrorObjectAvailable] Uncaught ReferenceError: addCTracking is not defined
Failing descriptor: {markup://c:KM_ArticleThemeLayout}

Refresh

Cookie Preference Center

General Information on cookies

When you visit our website, we use cookies to ensure that we give you the best experience. This information does not usually directly identify you, but it can give you a more personalized web experience. Because we respect your right to privacy, you can choose not to allow some types of cookies by clicking on the different category headings to find out more and change your settings. However, blocking some types of cookies may impact your experience on the site and the services we are able to offer. Further information can be found in our

Cookie Policy.

Strictly Necessary

Strictly Necessary

Always Active

Strictly Necessary

Strictly necessary cookies are always enabled since they are essential for our website to function. They enable core functionality such as security, network management, and website accessibility. You can set your browser to block or alert you about these cookies, but this may affect how the website functions. For more information please visit www.aboutcookies.org or www.allaboutcookies.org.

Cookie Details‎

Performance

Performance

Performance

Performance cookies are used to analyze the user experience to improve our website by collecting and reporting information on how you use it. They allow us to know which pages are the most and least popular, see how visitors move around the site, optimize our website and make it easier to navigate.

Cookie Details‎

Please confirm your settings by refreshing the page.

Functional

Functional

Functional

Functional cookies help us keep track of your past browsing choices so we can improve usability and customize your experience. These cookies enable the website to remember your preferred settings, language preferences, location and other customizable elements such as font or text size. If you do not allow these cookies, then some or all of these services may not function properly.

Cookie Details‎

Advertising

Advertising

Advertising

Advertising cookies are used to send you relevant advertising and promotional information. They may be set through our site by third parties to build a profile of your interests and show you relevant advertisements on other sites. These cookies do not directly store personal information, but their function is based on uniquely identifying your browser and internet device.

Cookie Details‎

Advertising Cookies

Consent Leg.Interest

Select All Vendors

Select All Vendors

Select All Hosts

Select All

Clear Filters

Information storage and access

Apply

Confirm My Choices Reject All Allow All

My Subscriptions

Loading

Knowledge Base

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

Last Updated: 4/26/2018Categories: TroubleshootingTotal Views: 5736Language:
Chinese (Simplified)English

Subscribe to get an email
newsletter with new updates

Please provide article feedback:

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.

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:

Open the vcac database with psql:

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

Set the schema to saas by running the command

set schema 'saas';

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

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

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;

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:

Before the cleanup operation starts run the command:  vcac-vami horizon ui enable

Copy the attached 51501_removeOAuth2Client.zip file to vRA terminal.

Extract the zip file to get removeOAuth2Client.sh script.

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

Repeat these steps for each tenant:

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  )

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 ~/

Set the vRA hostname in variable VRA. Use the vRA node hostname, not the load balancer hostname.

export VRA=vra-fqdn

Set the TenantId in variable TenantId :
export TenantId=<tenant name for this tenant>

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"

Set the sessionToken from step “e" in variable AUTH:
export AUTH=<sessionToken>

Run the below script, it is expected that, script will be running for a very long time:

./removeOAuth2Client.sh

Once the cleanup operation is done run the command:

vcac-vami horizon ui disable

Related Information

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

Detectable by VMware SkylineTM

Actions

Copy

link to clipboard
copied!

Print

Language:
Chinese (Simplified)English

Attachments

51501_removeOAuth2Client.zip

Additional Resources

KB
•
End Of General Availability of the Free vSphere Hypervisor (ESXi 7.x and 8.x) (2107518)

Result 1 of 1

Ask The Community

Get answers quickly from VMware experts in the community

Post Subject

CONTINUE IN COMMUNITIES

Related Products:

VMware Aria Automation

Related Versions:

VMware vRealize Automation 7.4.x

VMware vRealize Automation 7.3.x

Take Our Survey

Support Assistant