SingleStore DB

Securing the initial SingleStore DB user accounts
Configuring the root password

When SingleStore DB is installed, the root user is created on each SingleStore DB node. You are required to set a password when running sdb-admin create-node, but this can be set to a blank password for testing purposes. As a best practice, you should set a secure password for the root user.

Once your cluster is deployed, you can also change the password for the root user with the sdb-admin change-root-password command. This command configures the root password for a single SingleStore DB node. To configure the password on all nodes in a SingleStore DB cluster, run:

sdb-admin change-root-password --all --yes --password <secure_password>

Changing the root password is an online operation for both aggregators and leaves and you do not have to perform any additional operations; however, you must ensure your nodes are running before calling the change-root-password command.

Warning

Warning

Setting the root password via a command-line argument is often not secure and SingleStore recommends safer, alternative methods to configure passwords.

While it is convenient to set the root password using a command-line argument, it is often recommended against this practice for security reasons. The command-line argument accepts passwords entered as plain text, which makes them vulnerable to being discovered in the list of processes running on the system during application runtime. The plain text passwords could also be saved and accessed in the history file if your command-line interpreter maintains a history. For example, in Bash, the command history including password inputs is logged at ~/.bash_history. In addition, the root password supplied as a command-line argument is displayed on-screen and is visible to anyone who is reading the user’s screen.

Some of the more secure, alternative methods to set SingleStore DB root password are as follows.

  • Set the root password using the MEMSQL_PASSWORD environment variable. This option is best suited for automated applications.

  • As of SingleStore DB Toolbox 1.6.4, Toolbox commands can solicit the root password interactively from users. The passwords entered in an interactive prompt are not displayed on-screen and are effectively secured from anyone reading the user’s screen. The interactive prompt is invoked if neither the --password flag nor the MEMSQL_PASSWORD environment variable has been used to set the root password.

Deleting unnecessary default users

In MemSQL 6.0 and later, the only default user created on each SingleStore DB node during installation is the 'root'@'%' user, which should be configured as described in the previous section.

In MemSQL 5.8 and earlier, several default users are created on each SingleStore DB node during installation. We recommend deleting all of these default users except for the 'root'@'%' user.

To delete these users, use the DROP USER command. On each SingleStore DB node (including both aggregators and leaves), log in as the root user or another user with sufficient permissions, and run:

DROP USER ''@'localhost';
DROP USER ''@'127.0.0.1';
DROP USER 'root'@'localhost';
DROP USER 'dashboard'@'%';
DROP USER 'dashboard'@'localhost';

These additional default users are created in MemSQL installations of 5.8 and earlier, but not 6.0 and later. They are not changed during upgrade, so a SingleStore DB cluster installed on 5.8 or earlier and upgraded to 6.0 or later will still have these user accounts, unless you have deleted them.

Note that since user accounts are configured independently on each node, whenever you add a new SingleStore DB node, you should also delete any unnecessary default users on it.

Caution

The users ''@'localhost' and ''@'127.0.0.1' are “anonymous” user accounts, which allow any user to log in from the localhost (with a limited set of permissions). The blank user string matches any username - for example, attempting to log in as user alice from localhost will match the ''@'localhost' user, unless a user account 'alice'@'localhost' exists, in which case that takes precedence. Note that even if a user account 'alice'@'%' exists, the anonymous user account takes precedence over that. More specific hostnames take precedence first, and a specific username takes precedence over a blank “anonymous” username for the same hostname specificity. For example, a login as user ‘alice’ from localhost matches 'alice'@'localhost', ''@'localhost', and 'alice'@'%' in that order of precedence. This can cause unexpected behavior: if you have the anonymous users and an 'alice'@'%' user, but not an 'alice'@'localhost' user, when you attempt to log in as the user ‘alice’ from localhost, you will be logged in as ''@'localhost', whereas when you attempt to log in as the user ‘alice’ from any other host, you will be logged in as the 'alice'@'%' user. We recommend deleting the anonymous user accounts.

For this reason, the 'root'@'localhost' account is necessary only when the anonymous user accounts for localhost are present. Logging in as the root user from localhost matches 'root'@'localhost', ''@'localhost', and 'root'@'%' in that order of precedence. Therefore, when the anonymous user accounts are not present, the 'root'@'localhost' account can be removed, leaving only the 'root'@'%' account. We recommend deleting the 'root'@'localhost' account in addition to the anonymous user accounts to avoid the possibility of misconfiguring the 'root'@'localhost' and 'root'@'%' accounts differently (for example, misconfiguring them with different passwords).

The 'dashboard'@'%' and 'dashboard'@'localhost' accounts were used by MemSQL Ops versions prior to 4.0. They are no longer used, so we recommend deleting them.