SSL is an Enterprise Only feature.
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.
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.
cd /var/lib/memsql
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 -days 3600 -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
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.
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.
Place server-cert.pem
and server-key.pem
files on each aggregator in the cluster. You can copy the files from the Generating SSL Certificates section to all aggregators.
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
like:
memsql-ops memsql-list -q -r aggregator master | xargs memsql-ops memsql-update-config --key ssl_cert --value ./certs/server-cert.pem
memsql-ops memsql-list -q -r aggregator master | xargs memsql-ops memsql-update-config --key ssl_key --value ./certs/server-key.pem
or by editing the memsql.cnf
file on all aggregators to add the certificate paths in the [server]
section, like:
ssl_cert = ./certs/server-cert.pem
ssl_key = ./certs/server-key.pem
Then, restart all MemSQL 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.
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.
Place server-cert.pem
, server-key.pem
, and ca-cert.pem
files on each MemSQL node in the cluster. You can copy the files from the Generating SSL Certificates section to all nodes.
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
like:
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
or by editing the memsql.cnf
file on all aggregators to add the certificate paths in the [server]
section, like:
ssl_cert = ./certs/server-cert.pem
ssl_key = ./certs/server-key.pem
ssl_ca = ./certs/ca-cert.pem
Then, restart all MemSQL nodes, e.g. by running memsql-ops memsql-restart --all
.
You should also add REQUIRE SSL
to the GRANT
statement of the MemSQL account used for intracluster work (by default, this account is root
) and all MemSQL accounts used for connecting to leaves (the accounts specified in ADD LEAF
).
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.
Server Configuration to Require Secure Client Connections
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. To restrict access to clients over SSL only, you must add the REQUIRE SSL
clause to the user’s GRANT
statement, for example:
memsql> grant all on *.* to 'user'@'%' REQUIRE SSL;
Adding REQUIRE SSL
helps protect against misconfigured clients by preventing them from connecting over an insecure plaintext connection.
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
Client Configuration for Secure Client Connections
This section describes 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 - 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 must configure the client to require a secure connection and verify the appropriate server certificate to protect against active network attacks (man in the middle attacks).
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 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.
- Make sure to have your private key
key.pem
and certificatecert.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, andOPS_IP_ADDRESS
with the primary agent IP address or hostname, e.g. if you connect to Ops viahttp://192.168.0.1:9000
, then replaceOPS_IP_ADDRESS
with192.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"
- Install the key and certificate in MemSQL Ops
sudo memsql-ops ssl-set-cert -k key.pem -c cert.pem
- 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.
- 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
.
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).