sync-ldap Details

Structure

The following listing shows mapping from the config file (--config-file) fields to the appropriate flags and provides an explanation on all the sections. The config file is structured by logical entities (for example, ldap_client, bind).

drop_unmanaged_memsql_users: true # --drop-unmanaged-memsql-users  
2 groups:     
3 - Medical # --groups Medical  
4 schema: active-directory # --schema active-directory 
5 show_detail: true # --show-detail 
6 ldap_client: # ldap_client is the config of the LDAP client    
7   uris:   
8   - ldap://52.59.219.12 # --uris ldap://52.59.219.12  
9   start_tls: true # --start-tls  
10  ca_paths: ["../ldap-sync-config.yaml"] # --ca-paths "../ldap-sync-config.yaml"       
11  bind: # bind specifies how to bind to the LDAP server   
12  user: CN=Some User,CN=Users,DC=memsql,DC=ldap,DC=testing # --bind-user "CN=Some User,CN=Users,DC=memsql,DC=ldap,DC=testing"  
13  credentials: password # --bind-credentials password 
14  search: # search specifies the ldapsearch details        
15  base: dc=memsql,dc=ldap,dc=testing # --search-base "dc=memsql,dc=ldap,dc=testing"        
16  filter: (&(objectClass=*)) # --query-filter "(&(objectClass=*))"               
17  detail: # detail specifies the structure of the user and group ldap entries  
18  user_object_class: user # --user-object-class user                     
19  group_object_class: group # --group-object-class group     
20  user_attribute: sAMAccountName # --user-attribute sAMAccountName          
21  group_attribute: sAMAccountName # --group-attribute sAMAccountName        
22  user_member_of_attribute: memberOf # --user-member-of-attribute memberOf           
23  group_members_attribute: member # --group-members-attribute member             
24  user_principal_name_attribute: userPrincipalName # --user-principal-name-attribute userPrincipalName  
25   sql_user: # sql_user specifies how new SQL users are created  
26   resource_pool: "pool" # --resource-pool pool             
27   failed_login_attempts_limit: 1 # --failed-login-attempts-limit 1             
28   password_lock_time: 10 # --password-lock-time 10                 
29   auth_method: kerberos # --auth-method kerberos        
30   pam_auth_service: "" # --pam-auth-service ""  
31   kerberos_realm: "example.org" # --kerberos-realm "example.org"           
32   sql_client: # sql_client specifies user and password to the SQL user which runs SQL commands                   
33   user: root                                                                     
34   password: null

Communication with LDAP

Communication with LDAP is equivalent to a single ldapsearch command call.

ldapsearch -x -H ldap://35.158.138.5 -b "dc=memsql,dc=ldap,dc=testing" -D "CN=Peter Pan,CN=Users,DC=memsql,DC=ldap,DC=testing" -w MemSQL-active-directory-2020 (&(objectClass=*))

where
-H maps to --uris
-b maps to --search-base
-D maps to --bind-user
-w maps to --bind-credentials
-(&(objectClass=*)) maps to --query-filter

Processing LDAP Search Results

General

Processing LDAP search results is configured by a number of flags to get the relevant data for mapping to SQL users and groups. The section titled "detail" is responsible for that.

detail: # detail specifies the structure of the user and group ldap entries                                                                                           
18  user_object_class: user # --user-object-class user                                                                          
19  group_object_class: group # --group-object-class group                                                                         
20  user_attribute: sAMAccountName # --user-attribute sAMAccountName                                                                    
21  group_attribute: sAMAccountName # --group-attribute sAMAccountName                                                                   
22  user_member_of_attribute: memberOf # --user-member-of-attribute memberOf                                                                
23  group_members_attribute: member # --group-members-attribute member                                                                 
24  user_principal_name_attribute: userPrincipalName # --user-principal-name-attribute userPrincipalName

Only users and groups are taken into account.

Users

The following listing is a part of a sample LDAP user entry with details on processing the user entry.

# peter pan, Users, memsql.ldap.testing                                                            
2 dn: CN=peter pan,CN=Users,DC=memsql,DC=ldap,DC=testing                                                                                                                                  
3 objectClass: user # --user-object-class user is used to distinguish user entries from all the other entries                                                                                                             
4 memberOf: CN=employees,DC=memsql,DC=ldap,DC=testing # --user-member-of-attribute memberOf is used to map the user to their groups                                                                                                   
5 sAMAccountName: pan # --user-attribute sAMAccountName is used as a name for the SQL user                                                                                 
6 userPrincipalName: pan@memsql.ldap.testing # --user-principal-name-attribute userPrincipalName is used for as SPN for the Kerberos authentication

All the attribute keys can be overridden with the other values (which will result in using the other attributes, for example., cn instead of the sAMAccountName for the SQL user name).

Here are some examples of the CREATE USER queries which depend on the described flags.

CREATE USER 'Peter' IDENTIFIED WITH 'authentication_pam' AS 'testing.com';

-Peter is the value of the --user-attribute
-Authentication is selected by --auth-method pam
-testing.com is the value of --pam-auth-service

**

CREATE USER u IDENTIFIED WITH 'authentication_gss' AS 'u@testing.com';

-u is the value of the --user-attribute
-Authentication is selected by --auth-method kerberos
-u@testing.com is
    - either the value of the attribute which is selected by --user-principal-name-attribute
    - or u is the --user-attribute value and testing.com is the value of the --kerberos-realm which overrides the first option

Groups

The following listing is a part of a sample LDAP group entry with details on processing the group entry.

1 # employees, memsql.ldap.testing                                                                      
2 dn: CN=employees,DC=memsql,DC=ldap,DC=testing                                                          
3 objectClass: group # --group-object-class group is used to distinguish group entries from all the other entries                                                                                                                                                                         
4 member: CN=peter pan,CN=Users,DC=memsql,DC=ldap,DC=testing # --group-members-attribute member is used to map the group to its users                                             5 sAMAccountName: employees # --group-attribute sAMAccountName is used as a name for the SQL group

In CREATE GROUP 'employees' the group name is taken from the --group-attribute value.

How the Algorithm Syncs Users and Groups.

The design of the algorithm is focused on aggressively dropping not recognized users so that security breaches are prevented.

The two relevant flags are --groups which limits the set of groups to the listed groups and --drop-unmanaged-users.

A managed user is a user who is a member of the ldap_users_internal_group. Every user created with the tool is assigned the ldap_users_internal_group. Do not modify the group or users who are members of the internal group.

The tool executes the following queries to sync users and groups:

CREATE/DROP USER/GROUP
GRANT/REVOKE

It creates a user provided the user is a member of one of the listed LDAP groups

If the flag --drop-unmanaged-users is set then all the SQL users that are not on LDAP will be deleted.

If the flag --drop-unmanaged-users is not set (the default behaviour) then only SQL users that were previously created by the tool and are no longer on LDAP will be deleted.

It create a group provided it is a listed LDAP group.

It drops a group if there is no existing LDAP group to sync.

Recommendations

Select a proper limiting --query-filter if the directory is huge.

For example:

- "(|(objectClass=user)(&(objectClass=group)(sAMAccountName=employees)))"

where employees is the value of --group (this should be enough and leads to no changes in the behavior of the tool).

- "(|(&(objectClass=user)(memberOf=CN=employees,DC=memsql,DC=ldap,DC=testing))
(&(objectClass=group)(sAMAccountName=employees)))"

where CN=employees, DC=memsql, DC=ldap, DC=testing is the DN of the --group to sync (this should improve the performance, yet may lead to dropping users who are not members of the group to sync, this must be used with caution).

Sync all the groups with a single call by listing --groups a,b,c.
Schedule syncing.
Run the command with --json --show-detail --yes to see the detailed JSON with all the information on what is done in an easily parsable form. This enables automating, for example, granting privileges, or scripting checks, or dumping sync details for troubleshooting.