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
groups:
- Medical # --groups Medical
schema: active-directory # --schema active-directory
show_detail: true # --show-detail
ldap_client: # ldap_client is the config of the LDAP client
  uris:  
  - ldap://52.59.219.12 # --uris ldap://52.59.219.12  
  start_tls: true # --start-tls
  ca_paths: ["../ldap-sync-config.yaml"] # --ca-paths "../ldap-sync-config.yaml"
  bind: # bind specifies how to bind to the LDAP server  
    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"  
    credentials: password # --bind-credentials password
  search: # search specifies the ldapsearch details
    base: dc=memsql,dc=ldap,dc=testing # --search-base "dc=memsql,dc=ldap,dc=testing"
    filter: (&(objectClass=*)) # --query-filter "(&(objectClass=*))"   
    detail: # detail specifies the structure of the user and group ldap entries
      user_object_class: user # --user-object-class user    
      group_object_class: group # --group-object-class group
      user_attribute: sAMAccountName # --user-attribute sAMAccountName
      group_attribute: sAMAccountName # --group-attribute sAMAccountName  
      user_member_of_attribute: memberOf # --user-member-of-attribute memberOf
      group_members_attribute: member # --group-members-attribute member   
      user_principal_name_attribute: userPrincipalName # --user-principal-name-attribute userPrincipalName   
sql_user: # sql_user specifies how new SQL users are created   
  resource_pool: "pool" # --resource-pool pool  
  failed_login_attempts_limit: 1 # --failed-login-attempts-limit 1     
  password_lock_time: 10 # --password-lock-time 10
  auth_method: kerberos # --auth-method kerberos  
  pam_auth_service: "" # --pam-auth-service ""     
  kerberos_realm: "example.org" # --kerberos-realm "example.org"
sql_client: # sql_client specifies user and password to the SQL user which runs SQL commands    
  user: root                                                                                           
  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

The following listings show samples with details on 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                                                                                           
  user_object_class: user # --user-object-class user                                                                          
  group_object_class: group # --group-object-class group                                                                         
  user_attribute: sAMAccountName # --user-attribute sAMAccountName                                                                    
  group_attribute: sAMAccountName # --group-attribute sAMAccountName                                                                   
  user_member_of_attribute: memberOf # --user-member-of-attribute memberOf                                                                
  group_members_attribute: member # --group-members-attribute member                                                                 
  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                                                            
dn: CN=peter pan,CN=Users,DC=memsql,DC=ldap,DC=testing                                                                                                                                  
objectClass: user # --user-object-class user is used to distinguish user entries from all the other entries                                                                                                             
memberOf: CN=employees,DC=memsql,DC=ldap,DC=testing # --user-member-of-attribute memberOf is used to map the user to their groups                                                                                                   
sAMAccountName: pan # --user-attribute sAMAccountName is used as a name for the SQL user                                                                                 
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';

Where: - -

'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';

Where:

'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.

# employees, memsql.ldap.testing                                                                      
dn: CN=employees,DC=memsql,DC=ldap,DC=testing                                                          
objectClass: group # --group-object-class group is used to distinguish group entries from all the other entries                                                                                                                                                                         
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
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 creates 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.