Automatic rebalance fails in RSA Authentication Manager

[#1]

Article Number

000039625

Applies To

RSA Product Set: SecurID Access
RSA Product/Service Type: Authentication Manager
RSA Version/Condition: 8.3
Platform: Linux
O/S Version: Suse Linux

Issue

The Replica Server has been successfully deployed and attached to the primary instance. However, when navigating to the contact list, the new Replica server does not have a checkmark next to it. 

Image description

When doing an automatic rebalance of the contact list, an error message is received. Therefore, the sdconf.rec file is not updated and fails to add the new replica entry 

Image description




In /opt/rsa/am/server/logs/imsTrace.log reports the following:

2021-03-29 10:26:55,541, [[ACTIVE] ExecuteThread: '1' for queue: 'weblogic.kernel.Default (self-tuning)'], (DataObjectAccessSql.java:212), trace.com.rsa.authmgr.internal.admin.common.dal.sql.DataObjectAccessSql, ERROR, parsa1.ascap.com,,,,failed to add domain object AM_AGENT_PROTOCOL_SERVER_LIST
org.springframework.dao.DataIntegrityViolationException: Could not execute JDBC batch update; SQL [insert into AM_AGENT_PROTOCOL_SERVER_LIST (LAST_UPDATED_BY, NOTES, LAST_UPDATED_ON, TIME_STAMP, INSTANCE_ID, ROW_VERSION, NAME, ID) values (?, ?, ?, ?, ?, ?, ?, ?)]; constraint [null]; nested exception is org.hibernate.exception.ConstraintViolationException: Could not execute JDBC batch update
    at org.springframework.orm.hibernate3.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:643)
    at org.springframework.orm.hibernate3.HibernateAccessor.convertHibernateAccessException(HibernateAccessor.java:412)
    at org.springframework.orm.hibernate3.HibernateTemplate.doExecute(HibernateTemplate.java:412)
    at org.springframework.orm.hibernate3.HibernateTemplate.executeWithNativeSession(HibernateTemplate.java:375)
    at org.springframework.orm.hibernate3.HibernateTemplate.flush(HibernateTemplate.java:882)
    at com.rsa.authmgr.internal.admin.common.dal.sql.DataObjectAccessSql.add(DataObjectAccessSql.java:209)
    at com.rsa.authmgr.internal.admin.servermgt.impl.ServerListAdministrationImpl.add(ServerListAdministrationImpl.java:24)
    at com.rsa.authmgr.internal.admin.servermgt.impl.ServerListSynchronizerImpl.a(ServerListSynchronizerImpl.java:243)
    at com.rsa.authmgr.internal.admin.servermgt.impl.ServerListSynchronizerImpl.a(ServerListSynchronizerImpl.java:198)
    at com.rsa.authmgr.internal.admin.servermgt.impl.ServerListSynchronizerImpl.synchronizeAutomaticLists(ServerListSynchronizerImpl.java:166)
    at com.rsa.authmgr.internal.admin.servermgt.impl.ServerListSynchronizerImpl.doSynch(ServerListSynchronizerImpl.java:274)
    at com.rsa.authmgr.internal.admin.servermgt.impl.ServerListSynchronizationRefresher.refresh(ServerListSynchronizationRefresher.java:10)
    at com.rsa.ims.common.AbstractPeriodicRefresher$1.run(AbstractPeriodicRefresher.java:98)
    at com.rsa.ims.security.spi.SimpleSecurityContextImpl.doAs(SimpleSecurityContextImpl.java:80)
    at com.rsa.security.SecurityContext.doAs(SecurityContext.java:412)
    at com.rsa.security.SecurityContext.doAsSystem(SecurityContext.java:454)
    at com.rsa.ims.common.AbstractPeriodicRefresher.onTimer(AbstractPeriodicRefresher.java:96)
    at com.rsa.ims.common.timer.CommonJTimerServiceImpl$CommonJTimerInfo.doInTransaction(CommonJTimerServiceImpl.java:342)
    at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:131)
    at com.rsa.ims.common.timer.CommonJTimerServiceImpl$CommonJTimerInfo.timerExpired(CommonJTimerServiceImpl.java:319)
    at weblogic.timers.internal.commonj.ListenerWrap.timerExpired(ListenerWrap.java:37)
    at weblogic.timers.internal.TimerImpl.run(TimerImpl.java:304)
    at weblogic.work.SelfTuningWorkManagerImpl$WorkAdapterImpl.run(SelfTuningWorkManagerImpl.java:553)
    at weblogic.work.ExecuteThread.execute(ExecuteThread.java:311)
    at weblogic.work.ExecuteThread.run(ExecuteThread.java:263)
Caused by: org.hibernate.exception.ConstraintViolationException: Could not execute JDBC batch update
    at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:71)
    at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
    at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:249)
    at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:235)
    at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:139)
    at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:298)
    at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:27)
    at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1000)
    at org.springframework.orm.hibernate3.HibernateTemplate$28.doInHibernate(HibernateTemplate.java:884)
    at org.springframework.orm.hibernate3.HibernateTemplate.doExecute(HibernateTemplate.java:407)
    ... 22 more
Caused by: java.sql.BatchUpdateException: Batch entry 0 insert into AM_AGENT_PROTOCOL_SERVER_LIST (LAST_UPDATED_BY, NOTES, LAST_UPDATED_ON, TIME_STAMP, INSTANCE_ID, ROW_VERSION, NAME, ID) values ('<system>', 'Automatic server list for instance: PAXRSA1.ascap.com', '2021-03-29 14:26:55.537000 -04:00:00', '2021-03-29 10:26:55.537000 -04:00:00', '06d1a1b76006a10a449ff1d31453a714', '0', 'PAXRSA1.ascap.com', 'cf5705346403050a7879167b950ad18d') was aborted.  Call getNextException to see the cause.
    at org.postgresql.jdbc2.AbstractJdbc2Statement$BatchResultHandler.handleError(AbstractJdbc2Statement.java:2598)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1836)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:407)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeBatch(AbstractJdbc2Statement.java:2737)
    at weblogic.jdbc.wrapper.PreparedStatement.executeBatch(PreparedStatement.java:216)
    at com.rsa.ims.instrumentation.monitor.InstrumentedStatementProxy.executeBatch(InstrumentedStatementProxy.java:141)
    at com.rsa.ims.common.database.SavePointStatementProxy.executeBatch(SavePointStatementProxy.java:158)
    at org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:48)
    at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:242)
    ... 29 more

In /opt/rsa/am/rsapgdata/pg_log/postgress_mm_dd.log

2021-03-29 10:26:55.531 GMT [unknown] rsa_user 60619b60.4a5a 6/13375696 32386429 DETAIL:  Key (name)=(PAXRSA1.ascap.com) already exists.
2021-03-29 10:26:55.531 GMT [unknown] rsa_user 60619b60.4a5a 6/13375696 32386429 STATEMENT:  insert into AM_AGENT_PROTOCOL_SERVER_LIST (LAST_UPDATED_BY, NOTES, LAST_UPDATED_ON, TIME_STAMP, INSTANCE_ID, ROW_VERSION, NAME, ID) values ($1, $2, $3, $4, $5, $6, $7, $8)
2021-03-29 10:29:55.532 GMT [unknown] rsa_user 6061a987.4dcb 2/13215320 32386487 ERROR:  duplicate key value violates unique constraint "uk_am_aps_list_name"
 

Cause

The automatic rebalance fails due to duplicate keys in the RSA Authentication Manager database.

Resolution

Before you begin:

1.  In the Operations Console of the primary instance, click Maintenance > Backup and Restore > Back Up Now.
2.  Store the backup file in a secure location  

Access the PostgreSQL database

1. Connect to the RSA Authentication Manager 8.x primary, either by local console or SSH session.
2. Login using the rsaadmin account (note that during Quick Setup another user name may have been selected. Use that user name to login).
3. Navigate to /opt/rsa/am/utils.
4. Run the command below to obtain the database password.  In the example below, the password returned is rSKD5bGguLGNL9uGvFWnJoxIcHJah2, but this value is different for every deployment of Authentication Manager.

   login as: rsaadmin
   Using keyboard-interactive authentication.
   Password: <enter operating system user password>
   Last login: Thu Nov 17 16:08:00 2016 from jumphost.vcloud.local
   RSA Authentication Manager Installation Directory: /opt/rsa/am
   rsaadmin@am81p:~> cd /opt/rsa/am/utils
   rsaadmin@am81p:/opt/rsa/am/utils> ./rsautil manage-secrets -a get com.rsa.db.dba.password
   Please enter OC Administrator username: <enter Operations Console administrator's user name>
   Please enter OC Administrator password: <enter Operations Console administrator's password>
   com.rsa.db.dba.password: rSKD5bGguLGNL9uGvFWnJoxIcHJah2
   rsaadmin@am81p:/opt/rsa/am/utils>
   

5. Copy this password for use in the commands below.
6. Navigate to ../pgsql/bin and connect to the database using the password collected above.

   rsaadmin@am81p:/opt/rsa/am/utils> cd ../pgsql/bin
   rsaadmin@am81p:/opt/rsa/am/pgsql/bin> ./psql -h localhost -p 7050 -d db -U rsa_dba
   Password for user rsa_dba:rSKD5bGguLGNL9uGvFWnJoxIcHJah2
   psql.bin (9.2.4)
   SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
   Type "help" for help.
   db=#

7. Run the following command to list am_agent_protocol_server_list and then remove the duplicate keys in this table. In this issue, the key is '79bcfe386403050a1c948ad4b4c9dd59'
   db=# SELECT * FROM am_agent_protocol_server_list;
   db=# DELETE FROM am_agent_protocol_server_list WHERE id='79bcfe386403050a1c948ad4b4c9dd59'; 
   ERROR: update or delete on table "am_agent_protocol_server_list" violates foreign key constraint "fk_am_apslist" on table "am_aps_linkwith_aps_list" 
   DETAIL: Key (id)=(79bcfe386403050a1c948ad4b4c9dd59) is still referenced from table "am_aps_linkwith_aps_list". 
8. Run the following command to list am_aps_linkwith_aps_list and remove the duplicate keys in this table that is db=# SELECT * FROM am_aps_linkwith_aps_list; agent_protocol_server_id | agent_protocol_server_list_id ----------------------------------+----------------------------------  dfb5cdd76403050a1b9709a3ca1743fb | e195f72e6403050a1b98b25b23976f48  e53ed8016403050a0e78699968d6cfdb | e195f72e6403050a1b98b25b23976f48  dfb5cdd76403050a1b9709a3ca1743fb | e565a2416403050a4abb05e01aac3115  e53ed8016403050a0e78699968d6cfdb | e565a2416403050a4abb05e01aac3115  dfb5cdd76403050a1b9709a3ca1743fb | 79bcfe386403050a1c948ad4b4c9dd59  e53ed8016403050a0e78699968d6cfdb | 79bcfe386403050a1c948ad4b4c9dd59 db=# DELETE FROM am_aps_linkwith_aps_list WHERE agent_protocol_server_list_id='79bcfe386403050a1c948ad4b4c9dd59';  DELETE 2
9. Then, you can delete the duplicate key in am_agent_protocol_server_list db=# DELETE FROM am_agent_protocol_server_list WHERE id='79bcfe386403050a1c948ad4b4c9dd59';  DELETE 1
10. When done, login to the Security Console > Access > Authentication Agents > Authentication Manager Contact List > manage existing and verify that the replica server in question is removed from the Authentication Manager contact list
11. Then navigate to Access > Authentication Agents >Authentication Manager Contact List > Automatic Rebalance and click on "Rebalance".  You should see the new Replica server is listed with a checkmark next to it. Also, the new sdconf.rec file will include the entry of the new replica server successfully.