Securing MemSQL (MemSQL Ops)
Please follow this guide to learn how to migrate to SingleStore tools.
This topic does not apply to SingleStore Managed Service.
SingleStore DB supports features for user authentication, password policies, fine-grained access controls, and certificate-based network encryption between clients and the database cluster, as well as between individual nodes in the cluster.
A newly installed SingleStore DB instance is open to connections by default so you can immediately access SingleStore DB/MemSQL: by default, you can log into SingleStore DB with the root
user and an empty password over an unsecured channel. This page describes configuration changes and best practices required to secure SingleStore DB.
Configuring SingleStore DB user accounts
By default, user accounts are configured independently on each aggregator node. Whenever you add a new aggregator node, you will also need to configure user accounts on it. Users do not normally connect to leaves, so it is generally not necessary to configure user accounts on leaf nodes. Only the root
user is required on leaf nodes.
Also, you can automatically synchronize non-root user accounts across your cluster to avoid manually managing these accounts on each aggregator node. See Synchronize Permissions for more details on how to enable this functionality.
Securing the initial SingleStore DB user accounts
Configuring the root
password
When SingleStore DB is installed, the root
user is created on each SingleStore node with a blank password by default. You should set a password for the root
user.
If you manage your cluster with MemSQL Ops, you can set or change the root
password with the MEMSQL-UPDATE-ROOT-PASSWORD command. This command configures the root
password for a single SingleStore node. To configure the password on all nodes in a cluster, run:
memsql-ops memsql-list -q | xargs -n 1 memsql-ops memsql-update-root-password --no-confirmation -p <password>
When using MemSQL 6.5 or later and MemSQL Ops 6.0.11 or later, changing the root
password is an online operation for both aggregators and leaves and you do not have to perform any additional operations.
If you are running MemSQL 6.0 or older and/or MemSQL Ops 6.0.10 or older, memsql-update-root-password
is considered an offline operation. In this scenario, once you have configured the root
password on all nodes, run REBALANCE PARTITIONS on each database in your cluster.
On the master aggregator, for each database, run:
REBALANCE PARTITIONS ON database_name;
Whenever you add a new SingleStore node, you must also configure the root password on it.
Deleting unnecessary default users
In MemSQL 6.0 and later, the only default user created on each SingleStore 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 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 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 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 node, you should also delete any unnecessary default users on it.
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.
Adding a User
To add a user, use the GRANT command. If you have sync_permissions
enabled on your cluster, log into the master aggregator as the root
user or another user with sufficient permissions, and run:
memsql> GRANT <grant_options> TO '<user>'@'<host>' IDENTIFIED BY '<password>'
For example:
memsql> GRANT SELECT, INSERT ON db.* TO 'username'@'%' IDENTIFIED BY 'password'
See the GRANT documentation for more details.
If you do not have sync_permissions
enabled, then you must perform the above operations on each aggregator in your cluster.
You can also use Kerberos, SAML, or PAM instead of password-based authentication.
Changing a Password for a User
To change a password for a user, you have the following options:
-
Use
GRANT
as shown in the previous section. You must haveSUPER
privileges to change another user’s password. -
Use
SET PASSWORD
to create a password hash and assign it to the user. Users can also change their own password with this command.SET PASSWORD FOR 'username'@'host' = PASSWORD('password');
Removing a User
To remove a user, use the DROP USER command. Similar to other user management operations, if you have sync_permissions
enabled, log into the master aggregator as the root
user or another user with sufficient permissions, and run:
memsql> DROP USER '<user>'@'<host>'
If sync_permissions
is not enabled, then you must perform this operation on each aggregator in the cluster.
Inspecting Permissions
You can view grants and permissions by querying information_schema.user_privileges
.
You can also view grants for a user by running SHOW GRANTS:
memsql> SHOW GRANTS FOR user@domain;
Configuring Host-Based Security
You can use a firewall to restrict which hosts can access SingleStore DB. For example, if you’re running a cluster on Amazon EC2, you can configure security groups to restrict network access by specifying allowed IP addresses or security groups.
You can also set the bind-address
variable to restrict the range of IP addresses which are allowed to connect to SingleStore DB. For example, if you set it to 127.0.0.1
, you will only be able to connect to SingleStore DB locally. See bind_address
in the list of engine variables.
Configuring SSL
Setting SECURE_FILE_PRIV
The secure_file_priv
global variable controls where users with the FILE READ and FILE WRITE privilege can read or save files. It should be set on all nodes to a directory that is not used by SingleStore DB or other software. If not set, a user with the FILE READ or FILE WRITE privilege can tamper with the system by reading or creating files in sensitive locations. For information on how to set engine variables, see the engine variables overview.
Encryption at Rest
SingleStore DB is compatible with at-rest disk-based encryption via LUKS (Linux Unified Key Setup). While LUKS is the recommended encryption technology, SingleStore DB may be compatible with other solutions. Please contact us if you have questions about using SingleStore DB with other encryption technologies.
To use SingleStore DB with LUKS, configure your block device to be encrypted with LUKS, and then simply install SingleStore DB on the encrypted volume.
For more information about how to implement LUKS with different versions of Linux, see the links in the section below. Note that ‘ecryptfs’ should never be used - only volume or block level encryption.
Example Setup Process
- Prepare block device
- Encrypt block device with LUKS
- Create filesystem (i.e.,
mkfs.ext4 /dev/mapper/myencryptedvolume
) - Mount filesystem (i.e.,
mount /dev/mapper/myencryptedvolume /data
) - Install SingleStore DB normally to encrypted location using MemSQL Ops
How to Use LUKS With Different Versions of Linux
-
Red Hat Enterprise Linux: For an overview on using LUKS with Red Hat, go here.
- Red Hat Enterprise Linux version 5 (Linux kernel 2.6.18-8)
- Red Hat Enterprise Linux version 6 (Linux kernel 2.6.32-71)
- Red Hat Enterprise Linux version 7 (Linux kernel 3.10.0-123)
-
SUSE Linux Enterprise Server:
- SUSE version 10 (Linux kernel 2.6.16.21-0.8)
- SUSE version 11 (Linux kernel 2.6.27.19-5.1)
- SUSE version 12 (Linux kernel 3.12.28-4.6)
-
Ubuntu: For information about implementing LUKS on Ubuntu, go here.
-
openSUSE: For information about implementing LUKS on openSUSE, go here.