ERROR: duplicate key value violates unique constraint "uq_cwd_user_dir_ext_id"

Synchronising with an a directory server where a "User Unique ID Attribute" is not available will result in users unable to log in, and the following error in the Stash logs:

ERROR: duplicate key value violates unique constraint "uq_cwd_user_dir_ext_id"

This new constraint was added in STASH-5244, however relies on the external_id column in the cwd_user table being populated with a NULL in the case no "User Unique ID Attribute" (typically entryUUID) is provided by the directory server.

This problem is occurring because, when the required attribute is missing from the directory server response, an empty string is inserted into the external_id column rather than NULL.

Work Around
Ideally an external id (such as a UUID) should be provided to Stash by the directory server. This permits renaming of users. LDAP servers should provide an attribute 'entryUUID' according to RFC 4530. In some cases this is provided via a different attribute, and Stash should be configured to use this attribute. Some details of this can be found here: https://confluence.atlassian.com/display/STASH/Connecting+Stash+to+an+existing+LDAP+directory

The setting can be found as follows for LDAP servers:

1. Go to Administration >> User Directories
2. Click Edit on the LDAP server
3. Go to User Schema Settings >> User Unique ID Attribute
4. Update this to the correct attribute then click "Save and Test"

Reproducing the problem
Configure an LDAP Directory server as per normal, but instead change the "User Unique ID Attribute" (see above section for how to locate that setting) to some random string that will not be a valid entry attribute. Once synchronisation is complete, inspect the cwd_user table and note a single user for that directory has been inserted and has an empty string in the external_id column.

The fix
Ensure a null gets inserted into the external_id column, rather than an empty string. Then the constraint will be happy.