000035523 - UIServer error message and Admin UI will not display the existing users in RSA Web Threat Detection 6.0

Document created by RSA Customer Support Employee on Mar 30, 2018
Version 1Show Document
  • View in full screen mode

Article Content

Article Number000035523
Applies ToRSA Product Set: Web Threat Detection
RSA Product/Service Type: Mitigator
RSA Version/Condition: 6.0
 
IssueThe user database fails with the login request. Error message from:  
/var/log/silvertail.log Aug 14 14:03:58  - all for [uiserver] --  
[info] 0 DB problems: Database query failed: no connection to the server.
[info] 0 /login?user=user&passwd=*&app=ui
[info] 0 DB problems: Database query failed: no connection to the server.
[info] 0 /login?user=user&passwd=*&app=ui
[info] 0 Exception No user roles found.
[info] 0 DB problems: GetUserRoles failed


In the log --  /var/log/postgresql-Fri.log there can be seen error message
WARNING: there is already a transaction in progress
WARNING: there is already a transaction in progress
WARNING: there is no transaction in progress
WARNING: there is no transaction in progress
ERROR: duplicate key value violates unique constraint "user_role_pkey" STATEMENT: INSERT INTO user_role(tenantid, username, role_id) VALUES ($1, $2, (SELECT role_id FROM roles WHERE role_name = $3)); ERROR: duplicate key value violates unique constraint "user_role_pkey" STATEMENT: INSERT INTO user_role(tenantid, username, role_id) VALUES ($1, $2, (SELECT role_id FROM roles WHERE role_name = $3)); ERROR: duplicate key value violates unique constraint "user_role_pkey" STATEMENT: INSERT INTO user_role(tenantid, username, role_id) VALUES ($1, $2, (SELECT role_id FROM roles WHERE role_name = $3)); WARNING: there is no transaction in progress LOG: unexpected EOF on client connection
CauseThere is a corruption or missing data in the "user_role"  table, there are one or more  "role_name" entries missing.
This stops the UI from displaying the user list. 
Resolution
  1. Run the UIServer at the DEBUG level (-see KB article  000035269  "How to Enable debug logs in RSA Web Threat detection" for how to do this), then restart the UIServer service.
  2. Do the tasks that would generate an error (In this example it was to go from Rules to Users repeatedly, so debug information would show.) 
  3. Interpret from the log which user(s) are missing data in the table listed (it might be another table beside user_role)
  4. UIServer debug will show the database starting and where the user_role retrieval is stopped by an error
  5. Log into the postgress database and insert a single role value for the user, and restart the UIServer then go to the Admn UI to see if the Users will populate. 

Example:  silvertail=> INSERT INTO user_role (tenantid, username, role_id} VALUES {*, *, '<username>', 1};


This will insert  role '1' into the table for the <username>

        

6. Next review the changes in the user_role table   
     silvertail => SELECT * from user_role;
7.  If the change was made to the user, then login to the Admin UI and look if Users are populated. 
8.  Complete the correct access permissions (roles) on the <username> that just had the role 1 added.

Attachments

    Outcomes