Using the LDAP Tool
To periodically sync LDAP directory users and groups with SingleStoreDB, 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 SingleStoreDB 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
andpassword
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 SingleStoreDB 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 SingleStoreDB 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
).
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.
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.
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 SingleStoreDB every 5 minutes. The command in the cron
job would resemble the following.
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 SingleStoreDB 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 SingleStoreDB user.
CREATE USER user@% IDENTIFIED WITH authentication_pam AS 'pam_service' WITH FAILED_LOGIN_ATTEMPTS = 3 PASSWORD_LOCK_TIME = 300;