PostgreSQL (GCP) Adding pre-existing Access Right throws an error

Products

• Clarity PPM SaaS

Issue/Introduction

Product: Clarity PPM
Keywords: GCP; Postgres; Instance Access Rights

Steps to Reproduce:
1. Create a new resource from Administration -> Resources
2. Navigate to Resource's Access Rights -> Instance
3. Click on Add -> Object = "Process Definition" -> "Process -AutoStart" -> {Choose any record} -> Add
4. Click on Add -> Object = "Process Definition" -> "Process -AutoStart" and "Process - Cancel" -> {Choose same record as in Step 3} -> Add

Expected Result: "Process - Cancel" right is added along with "Process - AutoStart"
Actual Result: "Process - Cancel" right is not added

Cause

This is caused by DE57142

Environment

Clarity PPM15.8.1

Resolution

This is fixed in 15.9.0

The following workarounds can be easily used in 15.8.1.

1. While adding and picking Access Rights, do not pick those that are already present
2. Delete any specific Access Rights that are already present, and then re-add them along with the new Access Rights to be granted
3. Use Groups to manage Access Rights so that these changes can happen at one place, and be percolated to all relevant accounts

Additional Information

The following error message will be observable in the app-ca.log.

ERROR 2020-08-06 12:01:08,737 [http-nio-80-exec-636] niku.xql2 (clarity:admin:8377508__744E14B6-41EE-A098-A4E87D6C828B:nmc.userInstanceRightsAssociationsAdd) Database Error thrown 
com.niku.union.persistence.PersistenceException: 
SQL error code: 0
Error message: ERROR: current transaction is aborted, commands ignored until end of transaction block
Executed:
call CMN_SEC_ASND_INST_RIGHT_IDS_SP(?,?,?,?,?)
...
...
Caused by: org.postgresql.util.PSQLException: ERROR: RIGHT ASSIGNMENT EXISTS
  Where: PL/pgSQL function cmn_sec_asnd_inst_right_ids_sp(text,bigint,bigint,bigint,bigint) line 18 at RAISE
 ... 110 more