Using the LDAP Tool

To periodically sync LDAP directory users and groups with SingleStore, run the LDAP tool using common Linux scheduling tools, such as by creating a cron job. You can also manually run sdb-admin sync-ldap on the Linux command line by specifying the required options or using a YAML configuration file. SingleStore recommends using a configuration file as it provides an easier way to input the necessary parameters.

Prerequisites

The SingleStore user who runs this tool must be authorized to obtain user and group information from the LDAP directory server.

A user can connect to the cluster using the user and password parameters.
If a user or password is not specified, Toolbox assumes the database "root" user and uses a secure, hashed password. If a non-root user is specified and a password is not set, Toolbox prompts for a password; an error is thrown if using the LDAP tool in non-interactive mode.
To keep your SingleStore user password secure, set the MEMSQL_PASSWORD environment variable in the Linux shell.

Required Configuration Parameters

If you choose to run the sdb-admin sync-ldap command manually without a configuration file, you must specify the following command-line options.

--auth-method - Authentication method to authenticate new SingleStore users that are created from the LDAP sync. Supported authentication methods include Kerberos, PAM, SAML, and JWT. Only the authentication methods that are preconfigured in your cluster can be specified for this option. Depending on the authentication method used, you may need to include additional options and/or values. For example, --kerberos-realm must only be used with the Kerberos authentication method, whereas --saml-user-domain-attribute only applies to SAML authentication.

--bind-credentials string - Credentials of the LDAP bind user to authenticate. To keep your bind credentials secure, use the LDAP_BIND_CREDENTIALS environment variable in the Linux shell. Alternatively, a configuration file may be used.

--bind-user string - DN/username of the LDAP bind user to authenticate. For example, cn=admin,dc=example,dc=org.

--groups string - User group(s) names to sync. Group names must be separated by commas. For example, Medical,Engineering. Only the groups listed may be granted or revoked. Note that this option must contain non-empty value(s) for the command to successfully run.

--schema - LDAP Schema that specifies the structure of user and group entries. Currently, supported schemas include active-directory, open-ldap, and unspecified.

--search-base string - LDAP Base DN that specifies the base of the subtree in which the search is to be constrained for a search object. For example, dc=example,dc=org.

--uris string - URI(s) of the LDAP directory server(s). Multiple values are separated by commas. For example, ldap://172.17.0.2.

In addition to the options listed above, the sdb-admin sync-ldap command supports several optional flags. For more information, refer to the sdb-admin sync-ldap reference.

Using YAML Configuration File

As an alternative to specifying options on the command-line, you can use a YAML configuration file and pass it to the sdb-admin sync-ldap command using the --config-file option. Below is a mapping of the configuration file fields with the appropriate command-line options, along with information about each field. Note that the configuration file is structured by logical entities (e.g., ldap_client, bind).

YAML

 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

Example 1

The following example covers all of the required options for the sdb-admin sync-ldap command.

Shell

sdb-admin sync-ldap
  --uris ldap://52.59.219.12
  --groups Medical
  --search-base dc=memsql,dc=ldap,dc=testing
  --auth-method kerberos
  --schema active-directory
  --bind-user "CN=Some User,CN=Users,DC=memsql,DC=ldap,DC=testing"
  --bind-credentials password

The following YAML-based configuration file is equivalent to the example with mandatory command-line options.

YAML

drop_unmanaged_memsql_users: false
groups:
- Medical
schema: active-directory
show_detail: false
ldap_client:
  uris:
  - ldap://52.59.219.12
  start_tls: false
  ca_paths: []
  bind:
    user: CN=Some User,CN=Users,DC=memsql,DC=ldap,DC=testing
    credentials: password
  search:
    base: dc=memsql,dc=ldap,dc=testing
    filter: (&(objectClass=*))
    detail:
      user_object_class: user
      group_object_class: group
      user_attribute: sAMAccountName
      group_attribute: sAMAccountName
      user_member_of_attribute: memberOf
      group_members_attribute: member
      user_principal_name_attribute: userPrincipalName
sql_user:
  resource_pool: ""
  failed_login_attempts_limit: 0
  password_lock_time: 0
  auth_method: kerberos
  pam_auth_service: ""
  kerberos_realm: ""
sql_client:
  user: root
  password: null

Note the use of the additional fields that may also be specified in the configuration file, such as ca_paths and the detail struct.

For custom LDAP implementations, use either the detail struct in the config file or the appropriate options on the command line. The detail struct describes the structure of an LDAP entry. Either the schema field or the --schema option can be used to define the required details. Note that these details can also be overridden. However, you should only override the detail struct if the required schema is unsupported.

For example, given an LDAP user entry:

# adam, example.org
dn: uid=adam,dc=example,dc=org
objectClass: posixAccount
uid: adam

...and an LDAP group entry:

# dbagrp, example.org
dn: cn=dbagrp,dc=example,dc=org
objectClass: posixGroup
cn: dbagrp
memberUid: user

...a detail configuration will resemble:

ldap_client:
  search:
    detail:
      user_object_class: posixAccount
      group_object_class: posixGroup
      user_attribute: uid
      group_attribute: cn
      user_member_of_attribute: ""
      group_members_attribute: memberUid
      user_principal_name_attribute: ""

Example 2

You can configure a cron job to sync Active Directory (AD) users and groups with SingleStore every 5 minutes. The command in the cron job would resemble the following.

Shell

sdb-admin sync-users 
--ldap-uri "ldap://www.Company1.com:389"
--ldap-search-base "ou=Engineers,dc=Company1,dc=com"
--ldap-bind-method "Simple"
--ldap-bind-user john.smith
--ldap-bind-credentials "qkwhe123jk23jhe"
--ldap-result-attribute "samAccountName"
--ldap-user-groups "DentalCSR,MedicalCSR"
--ldap-start-tls
--ldap-version 3
--failed-login-attempts 3
--password-lock-time 300
--authentication-method "PAM"
--authentication-service "pam_serice"
--user "root"
--password <password>

In the above example, users and groups in the DentalCSR and MedicalCSR AD user groups are synced from the LDAP server, which is identified by the LDAP server connection string (specified by --ldap-uri). The LDAP tool connects to the SingleStore cluster with the "root" user and the password specified. The user names synced will be returned in the samAccountName field (For more information, refer to Processing LDAP Search Results). The following command is used to create each new SingleStore user.

SQL

CREATE USER user@% 
IDENTIFIED WITH authentication_pam AS 'pam_service'
WITH FAILED_LOGIN_ATTEMPTS = 3 PASSWORD_LOCK_TIME = 300;

Using the LDAP Tool

On this page

Prerequisites

Required Configuration Parameters

Using YAML Configuration File

Example 1

Example 2

Was this article helpful?

On this page

Was this article helpful?