SSL configurations for Singlestore Cluster and Studio tend to raise some questions. This guide provides answers to many and tries to clarify the setup of SSL.
Depending on which configuration you're setting up, there are multiple certificates used. These can have any name but, for simplicity, we will use some pre-defined name in this article. Feel free to replace with whatever names you prefer.
ca-key.pem - This is the Certification Authority private key file. You will only have this file if you're generating the certificates with your own Certification Authority as we exemplify in Generating SSL Certificates. If your organization has its own Certification Authority managed by a different team or if you're using a public Certification Authority you will not have this file and you will not need it anyway.
ca-cert.pem - This is the public key of the Certification Authority. This key it's used to validate that the server certificate you have. You need this file to setup SSL for Intra-Cluster communication or to deploy Studio with SSL.
server-key.pem - This is the private key for the public certificate you will install in the Singlestore cluster. When clients connect to an SSL endpoint, they will receive the public certificate to encrypt the connection. The private key it's what allows decryption to happen.
server-cert.pem - This is the public key that clients will use to encrypt the connection when communicating with the Singlestore cluster.
server.key - This is the private key for Studio SSL connection. It can be the same as server-key.pem if Studio is running in the same host as Singlestore cluster. However, you should use a different one for Studio as typically this is deployed in a separate host with a "friendly" name, such as the https://studio.myorg.com.
server.crt - This is the public key for the Studio SSL connection. Same remarks as above.
The Certification Authority, sometimes abbreviated as CA, is responsible for issuing the Certificates. In most situations, the client will need to trust the CA in order to validate that the certificate presented by server is a valid one. By default, the exception to this is when connecting to the Singlestore cluster with SSL from the command line with singlestore or mysql or using another client, such as DBeaver or HeidiSQL. These clients, by default, don't check the certificate and only use it to encrypt the communications. If the client is a web browser, the certificate needs to be trusted by the browser; otherwise, it will display a security warning about being unable to validate the certificate. This warning also happens when the URL name doesn't match the hostname in the certificate and/or if the certificate it's expired or revoked.
In addition to this, Intra-Cluster communications also require to trust the public server certificate.
Intermediate Certification Authority
It is common that certificates issued by Public CA's or in a Enterprise CA deploying are issued by an Intermediate Certification Authority. In this scenario, make sure that CA public certificate (ca-cert.pem) includes the full chain. This is a public certificate file that includes all the public key certificates for all CA's involved (usually the Root CA and the Intermediate CA).
Basically the client needs to trust the entity that issued the certificate (the Intermediate CA) and also the entity that created the Intermediate CA (the Root CA).
How to Check if the Certificate is OK?
Once you have the certificates, you can use the openssl tool to verify it. This tool's usually bundled in most Linux distributions. If it's not installed in your's, check you distribution documentation on how to do it, but this usually works:
For Debian based distributions (Ubuntu, Debian):
sudo apt install openssl
For Redhat based distributions (CentOS, RedHat):
sudo yum install openssl
Once you confirm that openssl is installed, you can use it to test the Public Certificate, the Private Key, and the CA Key. This tool is also handy to convert between different formats.
Check the certificate
openssl x509 -in /etc/memsql/server-cert.pem -text -noout
This displays the information present in the certificate, including the name to which the certificate was issued and the expiration date.
Check the private key
openssl rsa -in /etc/memsql/server-cert.pem -check -noout
Verify that the certificate and private key match by comparing checksums
openssl x509 -noout -modulus -in /etc/memsql/server-cert.pem | openssl md5
openssl rsa -noout -modulus -in /etc/memsql/server-cert.pem | openssl md5
Configuring SSL in the Singlestore Cluster for Client encryption
Most clients will not validate the SSL certificate. Therefore, as long as the cluster has a usable certificate to encrypt the connection, the clients will use it and the communication will be protected from eavesdropping. This is a basic SSL configuration for the cluster, where only the server-key.pem and server-cert.pem are needed. Place both files in /etc/memsql of both the Master Aggregator and
Child Aggregators and in your host with configured with Toolbox execute these steps:
Add the Private Key in the MA:
sdb-admin list-nodes -q -r aggregator | xargs bash -c '</dev/tty sdb-admin update-config --key ssl_key --value /etc/memsql/server-key.pem --memsql-id "$@"' memsql
Add the Public Key in the MA:
sdb-admin list-nodes -q -r master | xargs bash -c '</dev/tty sdb-admin update-config --key ssl_cert --value /etc/memsql/server-cert.pem --memsql-id "$@"' memsql
If you also have Child Aggregators, do the same.
Add the Private Key in the MA:
sdb-admin list-nodes -q -r aggregator | xargs bash -c '</dev/tty sdb-admin update-config --key ssl_cert --value /etc/memsql/server-cert.pem --memsql-id "$@"' memsql
Add the Public Key in the MA:
sdb-admin list-nodes -q -r master | xargs bash -c '</dev/tty sdb-admin update-config --key ssl_key --value /etc/memsql/server-key.pem --memsql-id "$@"' memsql
Make sure to restart the MA and CA for the configuration to be enabled.
Note that the Singlestore documentation uses a different location for the certificate files. To exemplify that the location can be anywhere and for simplicity, the location it's changed here to /etc/memsql.
For more information about generating your own certificates, see:
Configuring SSL in the Singlestore Cluster for Intra-Cluster encryption
To further enhance security, you can enable SSL in the communications between the cluster nodes. This mode is enabled just by adding to CA public key to the configuration by setting the variable ssl_ca in addition to the Public and Private keys. Note that while setting up SSL for client connections, you only need to configure the MA and CA; for Intra-cluster SSL communications, each node needs to have the ssl_ca, ssl_key, and ssl_cert variables set. To set it, do:
On each Singlestore host:
Copy ca-cert.pem, server-key.pem and server-cert.pem to /etc/memsq.
From the toolbox host:
sdb-admin update-config --all --key ssl_cert --value /etc/memsql/server-cert.pem
sdb-admin update-config --all --key ssl_key --value /etc/memsql/server-key.pem
sdb-admin update-config --all --key ssl_ca --value /etc/memsql/ca-cert.pem
Make sure to restart the cluster for the configuration to be enabled.
Important note regarding Canonical Names (CN) in certificates and Intra-cluster SSL communications
Note that if the certificate was generated with a CN, this means it will be only possible to use it in the host which name matches that CN. While this is possible, it's not recommended as it means an SSL certificate for each node of the cluster, making it prone to errors resulting from expired certificates, replaced, or added nodes. You should opt instead not to add a CN or to use a wildcard certificate. With the wildcard certificate, because the full hostname is validated, make sure that the domain is part of the cluster name. If you have your cluster setup with hosts that only use the IP address or a simple hostname, it will not be possible to use a wildcard certificate.
If certificate validation is needed and it fails, there will be the following error in memsql.log:
41958505 2021-06-17 16:25:34.171 WARN: SSLConnect() on socket failed with ssl3_get_server_certificate:certificate verify failed (1, 0)
SSL in Studio
The Studio can connect to the cluster in 2 different modes:
Client mode only requires configurations in Studio. The user connects to Studio, and Studio connects to the Cluster as a regular client.
This mode has the advantage of being easy to configure and can work with any SingleStore cluster to which the user has a valid login.
However, this setup will not work for scenarios with Integrated authentication, such as Kerberos, Active Directory, or SAML. Also, the communication between Studio and the Cluster needs to be in cleartext.
To configure Studio in Client mode, place both server.key and server.crt in /etc/memsql and add the location to Studio's configuration file (usually /etc/singlestore/singlestoredb-studio.hcl):
HTTPSCertificateFile = "/etc/memsql/server.crt"
HTTPSCertificateKeyFile = "/etc/memsql/server.key"
IMPORTANT: note that this configuration will be needed to secure the communication between the clients and Studio, even when using the WebSocket mode!
In websocket configuration, instead of querying the cluster using the usual 3306 port, Studio will "proxy" the requests to the cluster using a specific port.
Note that websocket is the mode for Studio to communicate with the Cluster. To setup SSL in Studio, you need the previous step to install the SSL certificate.
This allows Studio to use the Cluster authentication mechanisms and to have the communication between Studio and the cluster encrypted using SSL.
This mode is configured in studio's state file for a specific cluster. The studio state file (studio.hcl) contains all the configurations for the various Singlestore clusters. Some may be configured with webproxy, with or without SSL between Studio and the Cluster, or in Client mode (with the websocket and websocketSSL variables both set to false).
Note that only either websocket or websocketSSL are mutually exclusive - i.e., if one is true, the other needs to be set to false).
Setting websocketSSL to true means that Studio will connect to the cluster using SSL as a client. This means that the Singlestore cluster needs to be configure to support SSL.
NOTE: As of Singlestore Studio 3.2.9, password-protected private keys are not supported. We have it in the roadmap but for now, to enable SSL in Studio you can't have the private key protected with a password. If you do:
How to remove the passphrase from a Private Key
openssl rsa -in server-key.pem -out server-key.pem
Verify if the SSL Certificate is valid
The verify option allows checking if a certificate is valid given a specific CA Public certificate or certificate chain.
openssl verify -CAfile ca-cert.pem server-cert.pem
The output will be a simple OK if everything is fine. Otherwise, you may have a wrong certificate or certificate chain.
Verify the SSL connection
Check that you can connect with SSL to your Studio host using a designated name
openssl s_client -verify_hostname studio.sdbcluster.org -connect studio.sdbcluster.org:8080
Check that you can connect with SSL to the Singlestore cluster (note: older versions of openssl may not support this)
openssl s_client -connect YOUR_AGGREGATOR:3306 -starttls mysql
For an Intra-Cluster communication check, you should add the CA file to the test:
openssl s_client -connect YOUR_AGGREGATOR:3306 -CAfile /etc/memsql/ca-cert.pem -starttls mysql
Assorted links about SSL Certificates