User password truncated error

Problem

There's a know bug (internal reference number JS-57775) that if users used incompatible keystores in their server farm deployment sharing the same repository database, and used SSO integration with JRS, they can expose a defect that the external user password got incorrectly encrypted and stored in jiuser.password column multiple times causing the password length go beyond the column length.

One of our users had previously increased the password column's length to 4000 (max for their SQL Server DB), but now it turned out that the length of one of the hashed passwords was more than 10 thousand characters triggering the truncation error.

Analysis

The incompatible keystore files trigger the 7.5+ legacy decryption logic to cross wire with the SSO logic. When the user is granted the access by SSO, the 7.5+ logic believes it is time to encrypt the password to make it "compatible" with the server node keystore.

As a result, the external user's empty password becomes encrypted on the initial login then gets encrypted again on the subsequent login, and again.. again... again... until the DB length limit is reached. That's the reason that by extending the password length in the jiuser table, it will only delay the error to occur but will not fix the problem until the keystore is fixed and/or 7.5+ password encryption/update logic is changed to stop the cross wire situation.

Solution

Users need to make sure all their server nodes should use the identicle keystore files. In addition, to clean up the DB mess, users should not rely on increasing the password column length in jiuser table for its just delaying the problem (refer to the aforementioned reason). Instead, users should run the following SQL query through a scheduled task to clean up the external user password.

update jiuser set password ='' where id in (select id from jiuser where externallydefined is true and length(password) > 0)

The syntax for the query is for PostgreSQL database. Other DBs might need to change externallydefined column condition clauses to meet their DB standard. For instance, in SQL DB, the condition would be externallydefined = 1

=================================

TTC-20220219