LDAP slow initial Sync

Technical assistance
4

I think i solved it.

The “mail” attribute of LDAP users was not being mapped to the “email” field of zammad users because the “mail” attribute was already in use for the “login” field.
Without an email address associated to a zammad user the mysql statement “INSERT INTO authorizations (provider, uid, username, user_id, created_at, updated_at) select distinc […]” probably can not find a match and produces the aforementioned duplicate entries.
The larger that table gets the longer it takes to cycle through it, resulting in the amounting slow queries.

It did take me quite a while to realize that, because in fact i did map the LDAP “mail” attribute to the zammad “email” field.
My LDAP mapping looked like that:

mail            => login
givenname       => firstname
sn              => lastname
mail            => email
telephoneNumber => phone

And i never noticed that the mapping “mail => email” gets silently dropped when pressing the save button for the configuration on the LDAP integrations page in favour of the “mail => login” mapping.

irb(main):001:0> Setting.get('ldap_config') 
=> {
     "host_url"=>"ldap://192.168.122.224",
     "options"=>{
       "dc=organization,dc=com"=>"dc=organization,dc=com"
     },
     "option"=>"dc=organization,dc=com",
     "base_dn"=>"dc=organization,dc=com",
     "bind_user"=>"cn=Manager,dc=organization,dc=com",
     "bind_pw"=>"supersecretpassword",
     "wizardData"=>{
       "backend_user_attributes"=>{
         "dn"=>"dn (e.g., uid=1,dc=organization,dc=com)",
         "cn"=>"cn (e.g., Admin )",
         "sn"=>"sn (e.g., Mustermann)",
         "uid"=>"uid (e.g., 1)",
         "mail"=>"mail (e.g., admin)",
         "givenname"=>"givenname (e.g., Admin)",
         "userpassword"=>"userpassword (e.g., {PBKDF2-SHA512}30000$salt$hash)"
       },
       "backend_groups"=>{
         "dc=organization,dc=com"=>"dc=organization,dc=com"
       },
       "user_attributes"=>{
         "login"=>"Login",
         "firstname"=>"First name",
         "lastname"=>"Last name",
         "email"=>"Email",
         "web"=>"Web",
         "phone"=>"Phone",
         "mobile"=>"Mobile",
         "fax"=>"Fax",
         "department"=>"Department",
         "address"=>"Address",
         "note"=>"Note"
       },
       "roles"=>{
         "1"=>"Admin",
         "2"=>"Agent",
         "3"=>"Customer"
       }
     },
     "user_uid"=>"uid",
     "user_filter"=>"(objectClass=person)",
     "group_uid"=>"dn",
     "group_filter"=>"(objectClass=organization)",
     "user_attributes"=>{
       "givenname"=>"firstname",
       "sn"=>"lastname",
       "mail"=>"login",
       "telephoneNumber"=>"phone"
     },
     "group_role_map"=>{},
     "unassigned_users"=>"sigup_roles"
   }

Apparently it is not possible to map one LDAP attribute to multiple zammad fields as already observed in this thread:

Solution:

I introduced a new attribute in the LDAP server “displayName” (from InetOrgPerson Schema) to hold the same value as the “mail” attribute and mapped that attribute to zammads “login” field.
This way i can map the email to both “login” & “email” and everything works as expected.

irb(main):001:0> Setting.get('ldap_config') 
=> {
     "host_url"=>"ldap://192.168.122.224",
     "options"=>{
       "dc=organization,dc=com"=>"dc=organization,dc=com"
     },
     "option"=>"dc=organization,dc=com",
     "base_dn"=>"dc=organization,dc=com",
     "bind_user"=>"cn=Manager,dc=organization,dc=com",
     "bind_pw"=>"supersecretpassword",
     "wizardData"=>{
       "backend_user_attributes"=>{
         "dn"=>"dn (e.g., uid=1,dc=organization,dc=com)",
         "cn"=>"cn (e.g., Admin )",
         "sn"=>"sn (e.g., Mustermann)",
         "uid"=>"uid (e.g., 1)",
         "mail"=>"mail (e.g., admin)",
         "givenname"=>"givenname (e.g., Admin)",
         "displayname"=>"displayname (e.g., admin)",
         "userpassword"=>"userpassword (e.g., {PBKDF2-SHA512}30000$salt$hash)"
       },
       "backend_groups"=>{
         "dc=organization,dc=com"=>"dc=organization,dc=com"
       },
       "user_attributes"=>{
         "login"=>"Login",
         "firstname"=>"First name",
         "lastname"=>"Last name",
         "email"=>"Email",
         "web"=>"Web",
         "phone"=>"Phone",
         "mobile"=>"Mobile",
         "fax"=>"Fax",
         "department"=>"Department",
         "address"=>"Address",
         "note"=>"Note"
       },
       "roles"=>{
         "1"=>"Admin",
         "2"=>"Agent",
         "3"=>"Customer"
       }
     },
     "user_uid"=>"uid",
     "user_filter"=>"(objectClass=person)",
     "group_uid"=>"dn",
     "group_filter"=>"(objectClass=organization)",
     "user_attributes"=>{
       "displayname"=>"login",
       "givenname"=>"firstname",
       "sn"=>"lastname",
       "mail"=>"email",
       "telephoneNumber"=>"phone"
     },
     "group_role_map"=>{},
     "unassigned_users"=>"sigup_roles"
   }

The resulting number of entries in the authorizations table is by a factor ~10 smaller and there are almost no duplicate entries anymore.

MariaDB [zammad]> select COUNT(*) from authorizations;
+----------+
| COUNT(*) |
+----------+
|    59221 |
+----------+

The initial Sync after enabling the LDAP integration completed in ~10min. Every subsequent Sync takes less than a few seconds.

Feature Request:

A warning message in the LDAP integration configuration dialog would be nice to notify the admin that mapping the same attribute to multiple zammad user fields is not possible.
Or preferably make it possible so that one can map LDAP attributes to multiple fields :slight_smile:

I hope this helps.

Best regards,
Julian

1 Like