Renaming an external group breaks Active Directory incremental syncing

Issue Summary

Embedded Crowd in Jira provides Active Directory connectors with a read only, with local groups option. When an AD user is added to a group in a different directory (or vice-versa), Crowd stores a local copy in the database. If a user in a separate directory is added to an AD group and that group is subsequently renamed, the AD connector will always revert to a full sync.

This bug may be related to the work done to fix CWD-5256. This may behave differently in other Server/DC apps that implement Hibernate as the ORM (for example, Confluence) instead of OfBiz.

This is reproducible on Data Center: yes

Steps to Reproduce

1. Set up Jira DC and Active Directory.
2. Add AD as a connector. Set LDAP permissions to read only, with local groups. Enable incremental synchronization. Leave all other settings as default.
3. Perform an initial, full directory sync.
4. Create a new group group-a in AD.
5. Perform an incremental sync.
6. Add local user Bob to the external (AD) group you just created.
7. Rename group group-a to group-b in AD (update the cn attribute).
8. Perform two syncs. The first will be incremental, and the second will be full. Any subsequent syncs are incremental.
9. Add an external (AD) user Alice to the (local) group-a.
10. Attempt to perform an incremental sync.

Technical explanation

To facilitate user/group renames, Crowd maintains an external_id attribute in the cwd_group table. By default, this is the objectGUID LDAP attribute. Admins may customize this ldap.external.id parameter via the user unique ID attribute setting in the directory configuration. This value applies to both users and groups.

Step 6: When local user Bob is added to external group group-a, a copy of the group is added to the internal directory (ID: 1). This copy still includes the external_id attribute needed initially for the external group.

Steps 7-8: After renaming group-a to group-b in AD, the cwd_group record for the external group is updated as expected. However, the internal directory copy isn't altered, so it's still group-a.

Step 8: Since group A doesn't exist in AD, and Crowd doesn't have permission to add it, it creates a local copy. This cwd_group record is stored in the AD directory cache, with the local flag as 1. Since it copied this group from the internal record (which itself was a copy of the original AD group), it still carries the original external_id attribute.

Step 9: During an incremental sync, Crowd compares the number of external_id values with the number of groups in its AD directory cache. Since this number is mismatched, it reverts to a full sync. A full sync does not correct this problem.

Expected Results

Jira successfully performs an incremental sync.

Actual Results

Jira continuously reverts to full synchronizations. The following error is printed to the atlassian-jira.log file:

2023-08-07 18:21:19,411+0000 Caesium-1-1 INFO ServiceRunner     [c.a.crowd.directory.DbCachingRemoteDirectory] INCREMENTAL synchronisation for directory [ 10000 ] starting
2023-08-07 18:21:19,426+0000 Caesium-1-1 INFO ServiceRunner     [c.a.crowd.directory.DbCachingRemoteDirectory] Attempting INCREMENTAL synchronisation for directory [ 10000 ]
[...]
2023-08-07 18:21:19,494+0000 Caesium-1-1 ERROR ServiceRunner     [c.a.crowd.directory.DbCachingRemoteDirectory] Incremental synchronisation for directory [ 10000 ] was unexpectedly interrupted, falling back to a full synchronisation
com.atlassian.crowd.directory.ldap.cache.UsnChangedCacheRefresherIncSyncException: Cache returned different number of guids and non-local groups (possible reason is overlapping guids in cache, most likely null/empty values).
	at com.atlassian.crowd.directory.synchronisation.cache.UsnChangedCacheRefresher.getAndValidateGroupGuidsFromCache(UsnChangedCacheRefresher.java:440)
	at com.atlassian.crowd.directory.synchronisation.cache.UsnChangedCacheRefresher.synchroniseGroupChanges(UsnChangedCacheRefresher.java:390)
	at com.atlassian.crowd.directory.synchronisation.cache.UsnChangedCacheRefresher.synchroniseChanges(UsnChangedCacheRefresher.java:127)
	at com.atlassian.crowd.directory.DbCachingRemoteDirectory.synchroniseCache(DbCachingRemoteDirectory.java:1077)
	at com.atlassian.crowd.manager.directory.DirectorySynchroniserImpl.lambda$synchronise$0(DirectorySynchroniserImpl.java:82)
	at com.atlassian.crowd.audit.NoOpAuditLogContext.withAuditLogSource(NoOpAuditLogContext.java:17)
	at com.atlassian.crowd.manager.directory.DirectorySynchroniserImpl.synchronise(DirectorySynchroniserImpl.java:80)
	at com.atlassian.crowd.directory.DbCachingDirectoryPoller.pollChanges(DbCachingDirectoryPoller.java:48)
	at com.atlassian.crowd.manager.directory.monitor.poller.DirectoryPollerJobRunner.runJob(DirectoryPollerJobRunner.java:92)
	[...]
2023-08-07 18:21:19,498+0000 Caesium-1-1 INFO ServiceRunner     [c.a.crowd.directory.DbCachingRemoteDirectory] FULL synchronisation for directory [ 10000 ] starting
[...]
2023-08-07 18:21:19,991+0000 Caesium-1-1 INFO ServiceRunner     [c.a.crowd.directory.DbCachingRemoteDirectory] FULL synchronisation complete for directory [ 10000 ] in [ 580ms ]

This is not to be confused with Incremental synchronisation failed due to cache returned different number of guids and users.

Workaround

Check if you're affected

This only applies if:

• Jira is connected to an Active Directory server (not internal with LDAP authentication).
• Enable incremental synchronisation is checked in the directory advanced settings.
• LDAP permissions are set to read only, with local groups.
• You've renamed one or more groups in AD.

Run the following SELECT query:

SELECT * FROM cwd_group
WHERE external_id IN (SELECT external_id
    FROM cwd_group
    WHERE directory_id = 10000
    GROUP BY external_id
    HAVING COUNT(external_id) > 1);

Replace 10000 with the ID of the AD connector. You can find this under ⚙️ (gear icon) > User management > User directories > Directory configuration summary > Active Directory > Directory ID.

Apply patch

Clear the external_id column of affected entries:

UPDATE cwd_group
SET external_id = NULL
WHERE external_id IN (SELECT external_id
    FROM cwd_group
    WHERE directory_id = 10000
    GROUP BY external_id
    HAVING COUNT(external_id) > 1);

Replace 10000 with the ID of the AD connector.

Always back up your data before performing any modifications to the database. If possible, test any alter, insert, update, or delete SQL commands on a staging server first.

After applying the patch, manually initiate a synchronization from the directories page. This should complete as incremental. If not, please open an Atlassian Support case and attach a support zip.