Summary

Few users are not able to login into Confluence despite being successfully synchronised from your external User Directory

Environment

Confluence 7.19.X
Confluence 8.X

Diagnosis

When any of the affected user tries to login into Confluence, the following message appears in atlassian-confluence.log: 

2023-11-27 02:58:41,327 ERROR [http-nio-8090-exec-9] [impl.web.filter.ErrorHandlingFilter] logException [UUID: 0cc458c3-f96a-48f5-aadd-0106a1fa79d2] Received SSO request for user TestUSER1, but the user does not exist
 -- url: /plugins/servlet/samlconsumer | userName: anonymous | referer: https://login.microsoftonline.com/ | traceId: ae9af3ec4db8cfbb
com.atlassian.plugins.authentication.impl.web.usercontext.AuthenticationFailedException: Received SSO request for user conuallain, but the user does not exist
	at com.atlassian.plugins.authentication.impl.web.saml.SamlConsumerServlet.lambda$doPost$2(SamlConsumerServlet.java:110)
	at java.base/java.util.Optional.orElseThrow(Unknown Source)
...
...

In the message above, the user is trying to login via SAML/SSO, but you could have a similar error when using username/password to authenticate. 

When checking in the database side, you can confirm that the user cannot be found in the USER_MAPPING table when searching by its lower_username:

confluence=# SELECT * FROM user_mapping WHERE lower_username = 'testuser1';
user_key | username | lower_username
---------+----------+---------------
(0 rows)

However, the same user can be found when searching by username column or using a LIKE clause for the lower_username: 

confluence=# SELECT * FROM user_mapping WHERE lower_username like 'testuser1%';
user_key                         | username  | lower_username
---------------------------------+-----------+----------------
40288733782c550001783ab63a5c0001 | TestUSER1 | testuser1

confluence=# SELECT * FROM user_mapping WHERE username = 'testuser1';
user_key                         | username  | lower_username
---------------------------------+-----------+----------------
40288733782c550001783ab63a5c0001 | TestUSER1 | testuser1

Cause

At a first glance, it might seem that there is a hidden character/symbol after the lower_username attribute. However, that is not happening here. 
For some unknown reason, the index used by the table USER_MAPPING in PostgreSQL database got corrupted or invalid. 

Solution

If you are running into this issue in PostgreSQL database, you just need to reindex the USER_MAPPING table as follows: 

REINDEX TABLE USER_MAPPING;   

Depending on the situation, it might be the case that the table that needs to be reindexed is different. It might also be that you are using a different database vendor but running into a similar issue, in that case, we recommend you to find the specific commands for reindexing the tables or contact the vendor's team support.