sync-ldap

Description

Sync LDAP users and groups. LDAP is not modified in any way.

The --password flag is optional and specifies the SingleStoreDB root password. You can use this flag in conjunction with the --user flag to specify a SingleStoreDB user that is different from the root user and the user’s password. Note that the MEMSQL_PASSWORD environment variable is a safer alternative option for setting the password.

Wrap the password string in single quotes (') to avoid having the shell try to interpret any special characters included in the string.

This command may be run by either:

  • Specifying the required flags on the command line (--groups, --uris, --schema, --auth-method, --search-base...)

  • Providing a configuration file with the --config-file flag

Below is example of syncing users and groups from Active Directory (AD) using flags on the command line:

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
--exclude-ldap-users jsmith

The following YAML-based configuration file is equivalent to the example above.

drop_unmanaged_memsql_users: false
groups:
- Medical
exclude_users:
- jsmith
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 that there are a number of additional fields that may be specified in the config 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 flags on the command line. The detail struct describes the structure of an LDAP entry. Either the schema field or the --schema flag can be used to define the required details. Note that these details can also be overridden.

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: ""

Should you need to sync with several LDAP servers, you can specify all of them in the configuration file. For example:

ldap_client:
- uris:
- ldap://52.59.219.12
start_tls: false
search:
base: dc=memsql,dc=ldap,dc=testing
detail:
user_member_of_attribute: memberOf
- uris:
- ldap://50.124.12.7
start_tls: false
search:
base: dc=memsql,dc=ldap,dc=testing
detail:
user_member_of_attribute: groupmembership

Currently supported schemas include unspecified, active-directory, open-ldap. You should only override the detail struct if the required schema is unsupported.

All users created with this command are members of the group ldap_users_internal_group. Note that this group must not be modified.

LDAP bind credentials may be indicated using the LDAP_BIND_CREDENTIALS environment variable.

Behavior when Deleting Database Users

The sdb-admin sync-ldap command (or, more simply, sync-ldap) creates a SingleStoreDB database user for every LDAP user added to Active Directory (AD), and assigns the database user to an internal database group to distinguish it from “local” database users (those database users that were not created via sync-ldap).

The default behavior of sync-ldap is to delete the matching database users when LDAP users are deleted from AD, but only if the database users were created via sync-ldap. "Local" database users will not be deleted. You may run the following SQL command to see which database users have been created via sync-ldap.

SHOW USERS FOR GROUP 'your_ldap_database_users_internal_group';

However, if the --drop-unmanaged-memsql-users flag is included, for those database users that are not present in AD, both the matching database users that were created via sync-ldapand the matching "local" database users will be deleted.

Usage

Usage:
sdb-admin sync-ldap [flags]
Flags:
--auth-method {kerberos, pam, saml, jwt} Authentication method for new SingleStoreDB users (default unspecified)
--bind-credentials string Credentials of the LDAP user
--bind-user string The user name DN to log into LDAP (e.g. cn=admin,dc=example,dc=org)
--ca-paths FILE_PATHS The path(s) to the TLS root CA file. The default root certificate(s) will be used if no value(s) are provided
--config-file FILE_PATH The path to the configuration file (e.g. ../ldap-sync-config.yaml)
--dial-with-tls-config Dial with TLS config
--drop-unmanaged-memsql-users Drop users created in SingleStoreDB but no longer found in LDAP
--exclude-ldap-users strings LDAP user name(s) to exclude from syncing, separated by commas (e.g. jdoe,jsmith)
--exclude-ldap-users-file FILE_PATH The filename, including path, that contains the LDAP usernames to exclude from syncing, one per line (e.g. ../exclude-ldap-users.txt)
--exclude-ldap-users-to-lower strings LDAP username(s) to exclude from syncing to lowercase, separated by commas (e.g. JDoe,JSmith)
--failed-login-attempts-limit POSITIVE_INTEGER Maximum failed login attempts for new SingleStoreDB users
--group-attribute string The field name for an LDAP group name (ADVANCED)
--group-members-attribute string The field name for an LDAP group's users (ADVANCED)
--group-object-class string The objectClass name to match against an LDAP group (ADVANCED)
--groups strings User group name(s) to sync, separated by commas. Only the groups listed may be granted or revoked (e.g. Medical,Engineering)
-h, --help Help for sync-ldap
--host string The cluster-addressable hostname for the node
--insecure-skip-verify Disable TLS certificate verification (may be used with self-signed certificates for testing purposes)
--kerberos-realm string Service principal name domain for Kerberos (ADVANCED)
--ldap-users-to-lower Sync LDAP usernames to lowercase SQL usernames
--pam-auth-service string Service name for PAM
--password string SQL password for connecting to SingleStoreDB
--password-lock-time POSITIVE_INTEGER Failed login lockout time in seconds for new SingleStoreDB users
--port PORT The cluster-addressable port for the node
--query-filter string LDAP filter to narrow search results
--require-ssl REQUIRE SSL Enable the REQUIRE SSL for the synced users
--resource-pool string Resource pool name for new SingleStoreDB users
--saml-user-domain-attribute string The field name for an LDAP user's SAML domain suffix. Only applies to the SAML authentication method (ADVANCED)
--schema {unspecified, active-directory, open-ldap} LDAP schema which defines the structure of user and group entries (default unspecified)
--search-base string Search base path for the LDAP search object (e.g. dc=example,dc=org)
--show-detail Show syncing details on users and groups
--ssl Allow the SQL user to connect to SingleStoreDB via SSL
--ssl-ca FILE_PATH The path to the CA file to use when the SQL user connects to SingleStoreDB via SSL. If this option is not specified, the default CA file will be used
--start-tls Issue StartTLS (Transport Layer Security) extended operation
--uris strings URI(s) of the LDAP server(s), separated by commas (e.g. ldap://172.17.0.2)
--user string SQL user for connecting to SingleStoreDB (default "root")
--user-attribute string The field name for an LDAP username (ADVANCED)
--user-member-of-attribute string The field name for an LDAP user's groups (ADVANCED)
--user-object-class string The objectClass name to match against an LDAP user (ADVANCED)
--user-principal-name-attribute string The field name for an LDAP user's Kerberos/SAML principal name. Only applies to Kerberos and SAML authentication methods (ADVANCED)
Global Flags:
--backup-cache FILE_PATH File path for the backup cache
--cache-file FILE_PATH File path for the Toolbox node cache
-c, --config FILE_PATH File path for the Toolbox configuration
--disable-colors Disable color output in console, which some terminal sessions/environments may have difficulty with
--disable-spinner Disable the progress spinner, which some terminal sessions/environments may have issues with
-j, --json Enable JSON output
--parallelism POSITIVE_INTEGER Maximum number of operations to run in parallel
--runtime-dir DIRECTORY_PATH Where to store Toolbox runtime data
--ssh-control-persist SECONDS Enable SSH ControlPersist and set it to the specified duration in seconds
--ssh-max-sessions POSITIVE_INTEGER Maximum number of SSH sessions to open per host, must be at least 3
--ssh-strict-host-key-checking Enable strict host key checking for SSH connections
--ssh-user-known-hosts-file FILE_PATH Path to the user known_hosts file for SSH connections. If not set, /dev/null will be used
--state-file FILE_PATH Toolbox state file path
-v, --verbosity count Increase logging verbosity: valid values are 1, 2, 3. Usage -v=count or --verbosity=count
-y, --yes Enable non-interactive mode and assume the user would like to move forward with the proposed actions by default

Remarks

This command is interactive unless you use either --yes or --json flag to override interactive behavior.

Last modified: September 8, 2023

Was this article helpful?