Back
Outdated Version

You are viewing an older version of this section. View current production version.

/Security/

SSL Secure Connections min read

MemSQL supports secure connections over SSL. The configuration closely follows and is compatible with MySQL’s SSL features, including the standard MySQL client and drivers. The SSL library used is OpenSSL.

Connect to MemSQL Helios using TLS/SSL

Applies to: MemSQL Helios / SingleStore Managed Service

To ensure a secure connection to MemSQL, SQL clients must be properly configured to both require a secure connection and to verify the supplied server certificate. Otherwise, the SQL client will not use TLS/SSL connections to MemSQL, even if TLS/SSL is enabled on the MemSQL cluster. This can compromise security and lead to man-in-the-middle attacks, where a would-be attacker can impersonate a server when SSL is disabled, or create a secure connection by impersonating a server using an illegitimate server certificate.

Configure the SingleStore Managed Service Connection

These instructions describe how to configure the MySQL command-line client to connect to SingleStore Managed Service with a secure connection. SQL clients other than MySQL’s will likely require a different configuration.

  1. Download the singlestore_bundle.pem certificate file and save it to your MySQL client machine.

  2. When connecting to SingleStore Managed Service, be sure to include:

    • The host shown under Admin Endpoint from your cluster in the Customer Portal.

    • Port 3306.

    • The --default-auth=mysql_native_password option.

    • The --ssl-ca option, including the path to the singlestore_bundle.pem file. This can be done via command-line option, as in --ssl-ca=/path/singlestore_bundle.pem, or by setting the appropriate option in the configuration files for the MySQL command-line client. Include the --ssl-mode=REQUIRED when using older versions of the MySQL client, even when the --ssl-ca option is specified.

    • The --ssl-mode=VERIFY_CA option to verify the certificate.

  3. Test the connection to SingleStore Managed Service. The MySQL client will abort with an error if a secure connection cannot be established. While this is most likely due to a misconfiguration, it can also be due to a would-be attacker manipulating the secure connection to SingleStore DB.

    mysql -u admin -p -h <admin-endpoint-host> -P 3306 \
--default-auth=mysql_native_password \
--ssl-ca=./singlestore_bundle.pem \
--ssl-mode=VERIFY_CA

  4. Verify that a secure connection has been established to SingleStore Managed Service via the status command.

    mysql -u admin -p -h <admin-endpoint-host> -P 3306 \
--default-auth=mysql_native_password \
--ssl-ca=./singlestore_bundle.pem -e 'status' \
--ssl-mode=VERIFY_CA
****
mysql  Ver 14.14 Distrib 5.6.19, for osx10.9 (x86_64) using  EditLine wrapper

Connection id:        13
Current database:
Current user:	      root@yyy.yyy.yyy.yyy
SSL:                  Cipher in use is AES256-SHA
Current pager:        stdout
Using outfile:        ''
Using delimiter:	  ;
Server version:       5.5.8 MemSQL source distribution (compatible; MySQL Enterprise & MySQL Commercial)
Protocol version:     10
Connection:           xxx.xxx.xxx.xxx via TCP/IP
Server characterset:  utf8
Db     characterset:  utf8
Client characterset:  utf8
Conn.  characterset:  utf8
TCP port:             3306

Configure the MemSQL Helios Connection with Node.js

To enable SSL connections between MemSQL Helios and Node.js, download the singlestore_bundle.pem certificate file and use it to create the connection.

The following is an example of using the singlestore_bundle.pem certificate file with node-mysql2.

var mysql = require("mysql2");
var fs = require("fs");

var connection = mysql.createConnection({
    host: HELIOS_CLUSTER_HOSTNAME,
    user: 'admin',
    password: HELIOS_ADMIN_PASSWORD,
    ssl: {
        ca: fs.readFileSync(__dirname + '/singlestore_bundle.pem')
    }
});

connection.connect();
connection.end();

End: Connect to MemSQL Helios using TLS/SSL

Generating SSL Certificates

To enable SSL, you must generate certificates and keys (or use existing ones, but sharing keys across different services is not recommended in general).

Each MemSQL node which will be receiving SSL connections needs a server certificate and key - these can be the same or different for all servers. The server certificate(s) should be signed by a CA certificate.

Here are example steps for generating a set of self-signed certificates and keys to use with MemSQL. You can also use certificates with more sophisticated X509 certificate chains, but the process to create these certificates is beyond the scope of this document.

mkdir certs
cd certs

## The subject string for certificate signing requests.
## Edit the details to match your organization.
SUBJ="/C=US/ST=CA/L=San Francisco/O=MemSQL/CN="
CA_SUBJ="${SUBJ}memsql.ssl.test.ca"
SERV_SUBJ="${SUBJ}memsql.ssl.test.server"

## create the CA cert and key
openssl genrsa 2048 > ca-key.pem
openssl req -new -x509 -nodes -days 3600 -key ca-key.pem -out ca-cert.pem -subj "$CA_SUBJ"

## create the server cert, key, and sign with CA
openssl req -newkey rsa:2048 -nodes -keyout server-key.pem -out server-req.pem -subj "$SERV_SUBJ"
openssl rsa -in server-key.pem -out server-key.pem
openssl x509 -req -in server-req.pem -days 3600 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem

## verify the certificate chain
openssl verify -CAfile ca-cert.pem server-cert.pem

Note that the ​certs​ directory and its contents must be owned by the ​memsql​ user and group (e.g., ​chown -R memsql:memsql <directory>​ after copying the certificates to ​directory​​).

Configuring MemSQL for Secure Connections

It’s important to note that enabling secure connections between the client and the MemSQL cluster is separate from enabling secure connections between nodes inside the MemSQL cluster itself. To configure the MemSQL cluster to use secure connections, see either of:

  • Server Configuration for Secure Client Connections, or
  • Server Configuration for Secure Client and Intra-Cluster Connections

To configure your client to use secure connections, see Client Configuration for Secure Client Connections.

Specifying the TLS Version

Use the tls_version global variable to specify the TLS versions allowed by the server. By default, TLS versions TLSv1, TLSv1.1, and TLSv1.2 are supported by SingleStore DB and can be set only at startup.

SELECT @@tls_version;
****
+-----------------------+
| @@tls_version         |
+-----------------------+
| TLSv1,TLSv1.1,TLSv1.2 |
+-----------------------+

The following examples demonstrate how you can update the tls_version variable on your cluster nodes by running SingleStore Tools or MemSQL Ops commands at the Linux command line.

SingleStore Tools

The following command updates the TLS version to TLSv1.2 on all nodes in the cluster.

sdb-admin update-config --key tls_version --value TLSv1.2 --all
****
Toolbox is about to run 'memsqlctl update-config --key tls_version --value TLSv1.2' on the following nodes:
    - On host 127.0.0.1:
      + 27235D3E385B1056478CE11258959592CE49EE82
      + F9F6A7E64946D5D3D1E6F00C175EF00FC240AB97

Would you like to continue? [y/N]: y
✓ Updated configuration on 127.0.0.1
Operation completed successfully

The following commands restart all nodes in the cluster for the new tls_version value to take effect.

sdb-admin stop-node --all
****
✓ Successfully connected to host 127.0.0.1
Toolbox is about to perform the following actions:
  · Stop all nodes in the cluster

Would you like to continue? [y/N]: y
✓ Stopped Master node on 127.0.0.1 (1/1)
✓ Successfully stopped Master node on 1 host
✓ Stopped Master node
✓ Stopped Leaf nodes on 127.0.0.1 (1/1)
✓ Successfully stopped Leaf nodes on 1 host
✓ Stopped Leaf node
Operation completed successfully

sdb-admin start-node --all
****
Toolbox is about to perform the following actions:
  · Start all nodes in the cluster

Would you like to continue? [y/N]: y
✓ Successfully connected to host 127.0.0.1
✓ Started Leaf nodes on 127.0.0.1 (1/1)
✓ Successfully started Leaf nodes on 1 host
✓ Successfully connected to Leaf node
✓ Started Master node on 127.0.0.1 (1/1)
✓ Successfully started Master node on 1 host
✓ Successfully connected to Master node
Operation completed successfully

singlestore -p
****
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.5.58 MemSQL source distribution (compatible; MySQL Enterprise & MySQL Commercial)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

No entry for terminal type "xterm-256color";
using dumb terminal settings.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

SELECT @@tls_version;
****
+---------------+
| @@tls_version |
+---------------+
| TLSv1.2       |
+---------------+
1 row in set (0.01 sec)

MemSQL Ops

The following command updates the TLS version to TLSv1.2 on all nodes in the cluster.

memsql-ops memsql-update-config --key tls_version --value TLSv1.2 --all
****
Updating MemSQL configs
2020-08-13 00:17:17: J8cda22 [INFO] Changing config for MemSQL node 4FEF6F6AC8A971149F5E13587DA9EBD4B43F20F3 on Agent A3069bb123cd34e0994b183328f4f0c10 with values {"tls_version":"TLSv1.2"}
2020-08-13 00:17:18: J87d96d [INFO] Changing config for MemSQL node F2D99919B50D01E288B140E43A012C3E8B8D3BFF on Agent A3069bb123cd34e0994b183328f4f0c10 with values {"tls_version":"TLSv1.2"}
2020-08-13 00:17:18: J8cda22 [INFO] Successfully updated config for MemSQL node 4FEF6F6AC8A971149F5E13587DA9EBD4B43F20F3
2020-08-13 00:17:18: J87d96d [INFO] Successfully updated config for MemSQL node F2D99919B50D01E288B140E43A012C3E8B8D3BFF

The following command restarts all nodes in the cluster for the new tls_version value to take effect.

memsql-ops memsql-restart --all
****
Stopping cluster
Successfully stopped cluster
Starting cluster
Successfully started cluster

memsql
****
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.5.58 MemSQL source distribution (compatible; MySQL Enterprise & MySQL Commercial)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

SELECT @@tls_version;
****
+---------------+
| @@tls_version |
+---------------+
| TLSv1.2       |
+---------------+
1 row in set (0.01 sec)

Server Configuration for Secure Client Connections

This section describes how to enable secure connections between clients and the MemSQL cluster, but not between nodes within the MemSQL cluster. This requires configuring the ssl_cert and ssl_key settings on all aggregators.

Note that, depending on the client configuration, a client connecting to MemSQL may or may not use a secure connection even when SSL is enabled on the server. See the Server Configuration to Require Secure Client-Cluster Connections section.

SingleStore Tools

  1. Place server-cert.pem and server-key.pem files in the certs directory on each aggregator in the cluster. You can copy the files from the Generating SSL Certificates section to all aggregators.

    Note that the ​certs​ directory and its contents must be owned by the ​memsql​ user and group (e.g., ​chown -R memsql:memsql <directory>​ after copying the certificates to ​directory​​).

  2. Update the MemSQL configuration for all aggregators (it is also fine to configure all nodes) to set the ssl_cert and ssl_key settings to the paths to the server-cert.pem and server-key.pem files, respectively. These can be absolute paths, or relative to the MemSQL installation directory. You can do this by using sdb-admin update-config. For example:

    sdb-admin list-nodes -q -r aggregator | xargs bash -c '</dev/tty sdb-admin update-config --key ssl_cert --value ./certs/server-cert.pem --memsql-id "$@"' memsql

    sdb-admin list-nodes -q -r master | xargs bash -c '</dev/tty sdb-admin update-config --key ssl_cert --value ./certs/server-cert.pem --memsql-id "$@"' memsql

    sdb-admin list-nodes -q -r aggregator | xargs bash -c '</dev/tty sdb-admin update-config --key ssl_key --value ./certs/server-key.pem --memsql-id "$@"' memsql

    sdb-admin list-nodes -q -r master | xargs bash -c '</dev/tty sdb-admin update-config --key ssl_key --value ./certs/server-key.pem --memsql-id "$@"' memsql

  3. Alternatively, edit the memsql.cnf file on all aggregators to add the certificate paths in the [server] section. For example:

    ssl_cert = ./certs/server-cert.pem
ssl_key = ./certs/server-key.pem

  4. Restart all MemSQL aggregators.

    sdb-admin restart-node --all

MemSQL Ops

  1. Place server-cert.pem and server-key.pem files in the certs directory on each aggregator in the cluster. You can copy the files from the Generating SSL Certificates section to all aggregators.

    Note that the ​certs​ directory and its contents must be owned by the ​memsql​ user and group (e.g., ​chown -R memsql:memsql <directory>​ after copying the certificates to ​directory​​).

  2. Update the MemSQL configuration for all aggregators (it is also fine to configure all nodes) to set the ssl_cert and ssl_key settings to the paths to the server-cert.pem and server-key.pem files, respectively. These can be absolute paths, or relative to the MemSQL installation directory. You can do this by using memsql-ops memsql-update-config. For example:

    memsql-ops memsql-list -q -r aggregator | xargs memsql-ops memsql-update-config --key ssl_cert --value ./certs/server-cert.pem

    memsql-ops memsql-list -q -r master | xargs memsql-ops memsql-update-config --key ssl_cert --value ./certs/server-cert.pem

    memsql-ops memsql-list -q -r aggregator | xargs memsql-ops memsql-update-config --key ssl_key --value ./certs/server-key.pem

    memsql-ops memsql-list -q -r master | xargs memsql-ops memsql-update-config --key ssl_key --value ./certs/server-key.pem

  3. Alternatively, edit the memsql.cnf file on all aggregators to add the certificate paths in the [server] section. For example:

    ssl_cert = ./certs/server-cert.pem
ssl_key = ./certs/server-key.pem

  4. Restart all MemSQL aggregators.

    memsql-ops memsql-restart --all

Server Configuration for Secure Client and Intra-Cluster Connections

This section describes how to enable secure connections between clients and the MemSQL cluster, as well as between nodes within the MemSQL cluster. This requires configuring the ssl_cert, ssl_key, and ssl_ca settings on all MemSQL nodes.

This configuration secures intra-cluster communication by making each MemSQL node connect to other MemSQL nodes only over secure connections authenticated by a valid server certificate signed by the CA cert specified by the ssl_ca setting.

Note that, depending on the client configuration, a client connecting to MemSQL may or may not use a secure connection even when SSL is enabled on the server. See the Server Configuration to Require Secure Client-Cluster Connections section.

Info

This secures communication between all nodes in the cluster and also secures communication between that cluster and a secondary cluster that is replicating databases using MemSQL replication. If the performance cost of securing intra-cluster communication is too high but you still want to secure the communication to the secondary cluster, then set node_replication_ssl_only = true in memsql.cnf on every node in your primary cluster. This will disable SSL within the cluster but secure the communication to the secondary replicated cluster. See examples below on how to persist this behavior across your cluster.

SingleStore Tools

  1. Place server-cert.pem, server-key.pem, and ca-cert.pem files in the certs directory on each MemSQL node in the cluster. You can copy the files from the Generating SSL Certificates section to all nodes.

    Note that the ​certs​ directory and its contents must be owned by the ​memsql​ user and group (e.g., ​chown -R memsql:memsql <directory>​ after copying the certificates to ​directory​​).

  2. Update the MemSQL configuration for all nodes to set the ssl_cert, ssl_key, and ssl_ca settings to the paths to the server-cert.pem, server-key.pem, and ca-cert.pem files, respectively. These can be absolute paths, or relative to the MemSQL installation directory. You can do this by using sdb-admin update-config. For example:

    sdb-admin update-config --all --key ssl_cert --value ./certs/server-cert.pem

    sdb-admin update-config --all --key ssl_key --value ./certs/server-key.pem

    sdb-admin update-config --all --key ssl_ca --value ./certs/ca-cert.pem

  3. Alternatively, edit the memsql.cnf file on all aggregators to add the certificate paths in the [server] section. For example:

    ssl_cert = ./certs/server-cert.pem
ssl_key = ./certs/server-key.pem
ssl_ca = ./certs/ca-cert.pem

  4. Restart all nodes.

    sdb-admin restart-node --all

It is also recommended to add REQUIRE SSL, as described in the next section, to the GRANT statement of all MemSQL accounts used to connect to aggregator and leaf nodes in ADD AGGREGATOR and ADD LEAF statements (by default, root).

Disable SSL

  1. To disable SSL, run the following commands.

    sdb-admin update-config --all --key ssl_cert --reset

    sdb-admin update-config --all --key ssl_key --reset

    sdb-admin update-config --all --key ssl_ca --reset

  2. Restart all nodes.

    sdb-admin restart-node --all

MemSQL Ops

  1. Place server-cert.pem, server-key.pem, and ca-cert.pem files in the certs directory on each MemSQL node in the cluster. You can copy the files from the Generating SSL Certificates section to all nodes.

    Note that the ​certs​ directory and its contents must be owned by the ​memsql​ user and group (e.g., ​chown -R memsql:memsql <directory>​ after copying the certificates to ​directory​​).

  2. Update the MemSQL configuration for all nodes to set the ssl_cert, ssl_key, and ssl_ca settings to the paths to the server-cert.pem, server-key.pem, and ca-cert.pem files, respectively. These can be absolute paths, or relative to the MemSQL installation directory. You can do this by using memsql-ops memsql-update-config. For example:

    memsql-ops memsql-update-config --all --key ssl_cert --value ./certs/server-cert.pem

    memsql-ops memsql-update-config --all --key ssl_key --value ./certs/server-key.pem

    memsql-ops memsql-update-config --all --key ssl_ca --value ./certs/ca-cert.pem

  3. Alternatively, edit the memsql.cnf file on all aggregators to add the certificate paths in the [server] section. For example:

    ssl_cert = ./certs/server-cert.pem
ssl_key = ./certs/server-key.pem
ssl_ca = ./certs/ca-cert.pem

  4. Restart all MemSQL nodes.

    memsql-ops memsql-restart --all

It is also recommended to add REQUIRE SSL, as described in the next section, to the GRANT statement of all MemSQL accounts used to connect to aggregator and leaf nodes in ADD AGGREGATOR and ADD LEAF statements (by default, root).

Server Configuration to Require Secure Client Connections

To make the server restrict access to clients over SSL only, add the REQUIRE SSL clause to the user’s GRANT statement, for example:

SingleStore DB

GRANT all ON *.* TO 'user'@'%' REQUIRE SSL;

SingleStore Managed Service

CREATE USER 'user'@'%' IDENTIFIED BY 'password';
ALTER USER 'user'@'%' REQUIRE SSL;

For example, if REQUIRE SSL is specified for the user user:

## this connection attempt is rejected with an "Access denied" error:
$ mysql -u user -h 1.2.3.4

## this works:
$ mysql -u user -h 1.2.3.4 --ssl-ca=ca-cert.pem

Unless the client is configured properly, the client may or may not use SSL to connect to MemSQL even if SSL is enabled on the MemSQL cluster. Adding REQUIRE SSL helps protect against misconfigured clients by preventing them from connecting over an insecure plaintext connection. However, proper client configuration is still necessary for security against active network attacks, regardless of server configuration. See the Client Configuration for Secure Client Connections section.

Client Configuration for Secure Client Connections

To ensure secure connections, clients must be properly configured to require a secure connection and verify the appropriate server certificate. Otherwise, the client may or may not use SSL to connect to MemSQL even if SSL is enabled on the MemSQL cluster, and man in the middle attacks can compromise security, e.g. an attacker may impersonate a server with SSL disabled or impersonate a server while presenting a different server certificate.

The instructions below describe how to configure the MySQL command-line client to connect to MemSQL with a secure connection. Other clients may need to be configured differently.

Copy ca-cert.pem to your client machine. Specify the path to ca-cert.pem with the --ssl-ca option. This can be given as a command line option, as in --ssl-ca=path/ca-cert.pem, or by setting the appropriate option in the configuration files for the MySQL command-line client. Add the --ssl-mode=REQUIRED option to require a secure connection (this is necessary in older versions of the MySQL client, even when --ssl-ca is specified). The client will abort with an error if a secure connection cannot be established, e.g. if the server is misconfigured or an attacker is modifying the connection.

You can use the status command to print connection details. For example:

$ mysql -uroot -h1.2.3.4 --ssl-ca=ca-cert.pem -e 'status'
--------------
mysql  Ver 14.14 Distrib 5.6.19, for osx10.9 (x86_64) using  EditLine wrapper

Connection id:		13
Current database:
Current user:		root@4.5.6.7
SSL:			Cipher in use is AES256-SHA
Current pager:		stdout
Using outfile:		''
Using delimiter:	;
Server version:		5.5.8 MemSQL source distribution (compatible; MySQL Enterprise & MySQL Commercial)
Protocol version:	10
Connection:		1.2.3.4 via TCP/IP
Server characterset:	utf8
Db     characterset:	utf8
Client characterset:	utf8
Conn.  characterset:	utf8
TCP port:		3306
--------------

Configuring SingleStore Tools for Secure Connections

SingleStore Tools support SSL secure connections to encrypt communications with the SingleStore DB server.

To configure SingleStore Tools to connect with the server via SSL, you’ll need a certificate for SingleStore Tools issued by a trusted certificate authority (CA). Next, you’ll need to perform either of the following to enable SSL.

  • Edit the memsqlctl.hcl configuration file on each host to add the CA certificate file. The path can be absolute or relative to the location of the node’s base directory. For example:

    sslCaFile = /ca-file/including/path

  • Set the ssl_ca engine variable to specify the CA certificate file including the file path on each node. The file path can be absolute or relative to the node’s base directory. This setting is only applicable to SingleStore DB versions 7.1.17+ and 7.3.4+. In these versions, memsqlctl and SingleStore Tools establish SSL connections by default unless the connection fails due to a misconfiguration or invalid credentials.

In the absence of a CA certificate file in the memsqlctl.hcl configuration file and the ssl_ca engine variable, system certificates available on the host will be used.

Note that the CA certificate file is essential for establishing SSL connections with nodes from SingleStore DB user accounts that contain the REQUIRE SSL clause in the user’s GRANT statement.

Configuring MemSQL Ops for Secure Connections

As of version 4.0.31, MemSQL Ops supports SSL secure connections to protect communications between the browser and Ops primary agent. By default, Ops will be reachable via HTTPS on port 9001.

To enable SSL you need a private key and a certificate for MemSQL Ops, issued by a trusted Certificate Authority. In this guide we assume the private key is stored in key.pem and the certificate in cert.pem.

Connect to MemSQL Ops primary agent, then follow the steps below.

  1. Make sure to have your private key key.pem and certificate cert.pem. If you don’t have a key or certificate, you can generate a fresh RSA key and a self-signed certificate (replace location, organization name, and OPS_IP_ADDRESS with the primary agent IP address or hostname, e.g. if you connect to Ops via http://192.168.0.1:9000, then replace OPS_IP_ADDRESS with 192.168.0.1):

    openssl req -x509 -newkey rsa:2048 -sha256 -keyout key.pem -out cert.pem \
  -nodes -subj "/C=US/ST=CA/L=San Francisco/O=My Org/CN=OPS_IP_ADDRESS"

  2. Install the key and certificate in MemSQL Ops

    sudo memsql-ops ssl-set-cert -k key.pem -c cert.pem

  3. Restart MemSQL Ops

    sudo memsql-ops restart [--ssl-port 9001]

    Make sure the SSL port (default 9001) is open and reachable on your primary agent host.

  4. Reload the web page in the browser.

    Note that, if you have generated a self-signed certificate as above, your browser won’t trust this certificate and will refuse to connect to MemSQL Ops. You may want to ignore the error message or add the self signed certificate to the browser’s trusted sources. Depending on browser and operating system the procedure is slightly different, if not familiar we recommend to search the web with keywords add self signed certificate.

    Info

    This product includes software developed by the OpenSSL Project for use in the OpenSSL Toolkit. (http://www.openssl.org/). This product includes cryptographic software written by Eric Young (eay@cryptsoft.com).

Need help or have suggestions? Go to SingleStore Forums