Connect to SingleStore Helios using TLS/SSL

The VERIFY_CA option is not required to use TLS/SSL. However, it can be used to prevent sophisticated 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. If this is a concern, then use offline CA files in any SSL connection (not only Singlestore).

Refer to SingleStore Helios Endpoints and Server Configuration to Require Secure Client Connections for more information.

Refer to The SingleStore JDBC Driver for details on how to connect using JDBC.

Generating Client Certificates for SingleStore mTLS Connections

The following instructions can be used to create a client certificate/key pair signed by the CA. These files can then be used with SingleStore’s --ssl-cert and --ssl-key options for secure mutual TLS (mTLS) authentication.

If you already have a CA certificate from your organization or another trusted source, you can skip the "Create a Certificate Authority" section below and use your existing CA to sign the client certificate. The generated CA certificates can be used for local testing.

The CA certificate (ca-cert.pem) must also be trusted by the SingleStore cluster configuration for client authentication. In the portal, it should be uploaded in the Security tab of the deployment page.

Prerequisites

OpenSSL must be installed (for example for Ubuntu/Debian).

sudo apt-get install openssl -y

Create a Certificate Authority (CA)

Generate a CA private key as follows:
```
openssl genrsa -out ca-key.pem 4096
```

Generate a CA certificate as follows:

openssl req -x509 -new -nodes -key ca-key.pem -sha256 -days 3650 -out ca-cert.pem \ 
-subj
"/C=US/ST=CA/L=SanFrancisco/O=ExampleOrg/OU=IT/CN=Example-CA"

You now have:

ca-key.pem: The private key for your CA.
ca-cert.pem: The public CA certificate.

Keep the CA key (ca-key.pem) private and secure.

Create the Client Certificate and Key

Generate the client private key and certificate signing request (CSR) as follows:

openssl req -newkey rsa:2048 -nodes -keyout client-key.pem -out client-req.pem \ 
-subj
"/C=US/ST=CA/L=SanFrancisco/O=ExampleOrg/OU=Client/CN=client.example.com"

Create a client certificate signed by the CA. The following command uses the CA’s certificate and key to sign the client’s certificate.
```
openssl x509 -req -in client-req.pem -CA ca-cert.pem -CAkey ca-key.pem \  
-CAcreateserial -out client-cert.pem -days 365 -sha256
```

You now have:

client-key.pem: Client private key.
client-cert.pem: Client certificate signed by the CA.

Verify the Certificates

You can confirm that the client certificate is properly signed by the CA by using the below command:

openssl verify -CAfile ca-cert.pem client-cert.pem

The resulting output should be:

client-cert.pem: OK

Using the Certificates

When connecting to SingleStore with mTLS, specify the client certificate and key. Ensure the server is configured with server cert and key before using the following command.

mysql -u user \
--ssl-ca=/path/to/ca.pem \
--tls-version=TLSv1.2 \
--ssl-cert=/path/to/client-cert.pem \    	
--ssl-key=/path/to/client-key.pem

Configure the SingleStore Helios Connection

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

Download the singlestore_bundle.pem certificate file and save it to your MySQL client machine.
When connecting to SingleStore Helios, be sure to include:

a. The host shown under the Endpoint from your workspace in the Cloud Portal.

b. Port 3306.

c. The --default-auth=mysql_native_password option.

d. 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.

e. The --ssl-mode=VERIFY_CA option to verify the certificate.
Test the connection to SingleStore Helios. 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 Helios.
Shell
```
mysql -u admin -p -h <endpoint-host> -P 3306 \
--default-auth=mysql_native_password \
--ssl-ca=./singlestore_bundle.pem \
--ssl-mode=VERIFY_CA
```

Verify that a secure connection has been established to SingleStore Helios via the status command.

Shell

mysql -u admin -p -h <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