sync-ldap

Description

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

The --password flag is optional and specifies the SingleStore root password. You can use this flag in conjunction with the --user flag to specify a SingleStore 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:

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

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

YAML

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:

YAML

ldap_client:
  search:
    filter: (|(objectClass=posixAccount)(objectClass=posixGroup))
    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: ""

For better readability, the filter criteria can be written over multiple lines, where a new line starts with a left parenthesis and the line break comes after a right parenthesis. For example:

ldap_client:
  search:
    filter:
           (|(objectClass=posixAccount)
           (objectClass=posixGroup))

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

YAML

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.

Groups

As of Toolbox 1.17.17, SQL user parameters can be specified for a group.

Parameter	Type / Values	Description
`resource_pool`	String	Resource pool name for new SingleStore users
`failed_login_attempts_limit`	Integer	Maximum failed login attempts for new SingleStore users
`password_lock_time`	Integer	Failed login lockout time, in seconds, for new SingleStore users
`auth_method`	`kerberos`\|`pam`\|`saml`\|`jwt`	Authentication method for new SingleStore users
`pam_auth_service`	String	Service name for PAM
`kerberos_realm`	String	Service principal name domain for Kerberos
`require_ssl`	`true`\|`false`	Enable the `REQUIRE SSL` for the synced users

If a group has at least one parameter specified, and if an LDAP user belongs to this group, the corresponding SQL user is created with the parameters assigned to the group.

If a parameter's value is not explicitly specified, its default value is used. Otherwise, the parameters specified on the command line or in the sql_user section of the YAML file are used. For example:

Command Line

Shell

sdb-admin sync-ldap \
  --uris ldap://52.59.219.12 \
  --groups Medical,Engineering[auth-method=jwt,resource_pool=engineering] \
  --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

YAML File

YAML

drop_unmanaged_memsql_users: false
groups:
- Medical
- Enineering:
     resource_pool: "engineering"
     auth_method: jwt
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

Behavior when Deleting Database Users

The sdb-admin sync-ldap command (or, more simply, sync-ldap) creates a SingleStore 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.

SQL

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]

  For flags that can accept multiple values (indicated by VALUES after the name of the flag),
  separate each value with a comma.

Flags:
      --auth-method {kerberos, pam, saml, jwt}              Authentication method for new SingleStore users.
                                                            This value will be used if a user's group does not have any additional configs specified explicitly for the group with the
                                                            --groups option (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 SingleStore 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 SingleStore users.
                                                            This value will be used if a user's group does not have any additional configs specified explicitly for the group with the --groups option
      --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)
      --groups GROUPS                                       User group name(s) to sync, separated by commas. Additional config options (e.g., resource-pool, auth-method) can be provided in square brackets.
                                                            (e.g. Medical[resource-pool=high-performance,auth-method=PAM],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. This value will be used if a user's group does not have additional configs specified explicitly for
                                                            the group with the --groups option
      --password string                                     SQL password for connecting to SingleStore
      --password-lock-time POSITIVE_INTEGER                 Failed login lockout time in seconds for new SingleStore users.
                                                            This value will be used if a user's group does not have any additional configs specified explicitly for the group with the --groups option
      --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.
                                                            This value will be used if a user's group does not have any additional configs specified explicitly for the group with the --groups option
      --resource-pool string                                Resource pool name for new SingleStore users.
                                                            This value will be used if a user's group does not have any additional configs specified explicitly for the group with the --groups option
      --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 SingleStore via SSL
      --ssl-ca FILE_PATH                                    The path to the CA file to use when the SQL user connects to SingleStore 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 SingleStore (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

--config-file option

The --config-file option is an alternative to the command line arguments approach providing configuration for the sdb-admin sync-ldap command.

If you run the sdb-admin sync-ldap --config-file path/to/config command, you should provide a valid configuration file in your YAML. The structure of the configuration file should be the following (some fields may not be required in a particular configuration)::

drop_unmanaged_memsql_users: true|false // Drop users created in SingleStore but no longer found in LDAP, by default is false
groups: <list of strings> // User group name(s) to sync. Only the groups listed may be granted or revoked
exclude_users: <list of strings> // DAP user name(s) to exclude from syncing
schema: unspecified|active-directory|open-ldap // LDAP schema which defines the structure of user and group entries
show_detail: true|false // Show syncing details on users and groups
ldap_users_to_lower: true|false // Sync LDAP usernames to lowercase SQL usernames
exclude_ldap_users_to_lower: <list of strings> // LDAP user name(s) to exclude from syncing to lowercase
ldap_client: // one or several configurations for LDAP client
    uris: <list of strings> // URI(s) of the LDAP server(s)
    start_tls: true|false // Issue StartTLS (Transport Layer Security) extended operation
    ca_paths: <list of pathes> // The path(s) to the TLS root CA file. The default root certificate(s) will be used if no value(s) are provided
    dial_with_tls_config: true|false // Dial with TLS config
    insecure_skip_verify: true|false // Disable TLS certificate verification (may be used with self-signed certificates for testing purposes)
    user: <string> // The user name DN to log into LDAP
    credentials: <string> // Credentials of the LDAP user
    base: <string> // Search base path for the LDAP search object. For example: dc=example,dc=org
    filter: <string> // LDAP filter to narrow search results
    detail: // defines LDAP search details which may be defaulted if an LDAP schema is used
        user_object_class: <string> // The objectClass name to match against an LDAP user
        group_object_class: <string> // The objectClass name to match against an LDAP group
        user_attribute: <string> // The field name for an LDAP username
        group_attribute: <string> // The field name for an LDAP group name
        user_member_of_attribute: <string> // The field name for an LDAP user's groups
        group_members_attribute: <string> // The field name for an LDAP group's users
        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
        saml_user_domain_attribute: <string> // The field name for an LDAP user's SAML domain suffix. Only applies to the SAML authentication method
sql_user: // New SQL users configuration
  resource_pool: <string> // Resource pool name for new SingleStore users
  failed_login_attempts_limit: <integer> // Maximum failed login attempts for new SingleStore users
  password_lock_time: <integer> // Failed login lockout time in seconds for new SingleStore users
  auth_method: kerberos|pam|saml|jwt // Authentication method for new SingleStore users
  pam_auth_service: <string> // Service name for PAM
  kerberos_realm: <string> // Service principal name domain for Kerberos
  require_ssl: true|false // Enable the `REQUIRE SSL` for the synced users
sql_client: // Configuration for the connection to SingleStore
  user: <string> // SQL user for connecting to SingleStore
  password: <string> // SQL password for connecting to SingleStore
  host: <string> // The cluster-addressable hostname for the node
  port: <integer> // The cluster-addressable port for the node
  ssl: true|false // Allow the SQL user to connect to SingleStore via SSL
  ssl_ca_file: <path to CA file> // The path to the CA file to use when the SQL user connects to SingleStore via SSL. If this option is not specified, the default CA file will be used
  insecure_ssl: true|false // Disable SSL certificate verification. May be used with self-signed certificates for testing purposes

Remarks

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

sync-ldap

On this page

Description

Groups

Behavior when Deleting Database Users

Usage

--config-file option

Remarks

Was this article helpful?

On this page

Was this article helpful?