SingleStore DB

SingleStore DB Studio Security

Important

SingleStore DB Studio is designed to work with MemSQL 6.5 or later and is only supported on Chrome and Firefox browsers at this time.

Authentication

SingleStore DB Studio (or simply "Studio") is a visual SQL client that connects to your SingleStore DB clusters. As such, it defers to your clusters for authentication.

Logging In

To login to a cluster from Studio, you must use the same username and password that you would use when connecting through an application such as the MySQL client, and the host value must either be a wildcard, or the name of the computer that's running Studio. This ensures that only users with proper permissions on known hosts can access clusters using Studio. See Securing SingleStore DB for more details on configuring user accounts.

Logging in Using Kerberos

As of Studio 1.9.8, SingleStore DB users that have been configured with Kerberos/GSSAPI may now use Kerberos authentication to log into Studio.

Once configured, Studio will work as any other application that uses Kerberos authentication inside a browser.

Kerberos Authentication Flow

The Kerberos authentication flow is depicted in the following diagram.

Figure 11. Kerberos authentication flow
Kerberos authentication flow


Configuring Kerberos Authentication
Prerequisites
  • As Studio authentication relies on SingleStore DB engine authentication, complete the steps in Kerberos Authentication before configuring Kerberos in Studio.

  • Confirm that your browser has Kerberos configured.

  • (Optional) To allow Studio to automatically log in with the current Kerberos user, set the kerberosAutologin property to true in the Studio state file. You must restart Studio after changing this configuration.

Configuration Example

This example uses the configuration detailed in the Kerberos SPN generation section of The Chromium Projects’ HTTP authentication. The example uses the following domains and realms, which may differ from your setup.

  • Studio is served under the CNAME auth-server.example.com.

  • The Kerberos Realm is example.com.

  • Studio is accessed from auth-server.example.com.

Use the following steps to configure Studio to allow Kerberos authentication.

  1. Register SingleStore DB Studio as a trusted client in Kerberos

    Create a Kerberos Service Principal Name (SPN) of HTTP/auth-server.example.com@EXAMPLE.COM for authentication. This is the default SPN generated by the browser. This SPN must be added to your KDC as a trusted new client. Your SPN must be created in the domain from which SingleStore DB Studio is run.

  2. Generate the keytab

    Using ktutil, generate a new keytab that includes the SPN.

  3. Update the SingleStore DB engine variable

    The gssapi_principal_name engine variable is defined with the desired SPN (in this example, HTTP/auth-server.example.com@EXAMPLE.COM). To update this variable, run the following command.

    SET GLOBAL gssapi_principal_name = <your_SPN>;

    The SPN is also added to the Kerberos keytab file and defined in gssapi_keytab_path. To list the SPNs associated with a keytab, run the following on the command line:

    klist -ket /path/to/name.keytab
Running Queries

Once logged in, all queries from Studio against a cluster are run with the user’s cluster credentials. This ensures that all user permissions on the cluster are respected. As Studio does not have any additional access to the database beyond the user’s connection, the data returned will be the same as if the user was running the query from the command line.

Serving the UI with HTTPS

To serve the Studio user interface with HTTPS, edit the Studio configuration file to include the following two options. (See Configuration File for a more comprehensive example.)

HTTPSCertificateFile = "./server.crt"
HTTPSCertificateKeyFile = "./server.key"

Where:

  • The HTTPSCertificateFile key must point to the path of the public certificate that you’d like to use for HTTPS.

  • The HTTPSCertificateKeyFile must point to the path of the key file.

These paths can be absolute or relative to the Studio binary, although the absolute path is recommended. To learn how to create SSL certificates, see Generating SSL Certificates.

Test the Connection

To confirm that the connection to Studio is secure and the certificate is valid, launch a Chrome or Firefox browser and attempt to connect to Studio using HTTPS. If there are no obvious security-related connection issues, view the certificate to confirm that it is both valid and is in use. For more information, visit Google Chrome Help or Firefox Help.

Configuring TLS Versions and Cipher Suites

As of version 3.2.3, the SingleStore DB Studio (or simply “Studio”) server can be configured to encrypt browser connections via a subset of the available TLS protocols and cipher suites. Encrypted connections using TLSv1, TLSv1.1, TLSv1.2, and TLSv1.3 are supported along with the cipher suites permitted for each TLS version. During a TLS handshake, the most secure protocol with a compatible cipher suite that is supported by both the Studio server and the browser will be negotiated. A connection between the Studio server and the browser will not be established if a common protocol and cipher suites cannot be negotiated.

To enforce the use of specific TLS versions and cipher suites by the Studio server, modify the Studio configuration file (singlestoredb-studio.hcl) by including TLSVersions and TLSCipherSuites properties in the following format. (See Configuration File for a more comprehensive example.)

TLSVersions = ["TLSv1.1", "TLSv1.2"]
TLSCipherSuites = ["ECDHE-ECDSA-RC4-SHA", "ECDHE-ECDSA-AES128-SHA", "ECDHE-ECDSA-AES256-SHA", "ECDHE-RSA-RC4-SHA", "ECDHE-RSA-DES-CBC3-SHA", "ECDHE-RSA-AES128-SHA", "ECDHE-RSA-AES256-SHA"]

The TLSCipherSuites property takes OpenSSL cipher suite names as values. The order in which the protocols and cipher suites are specified does not affect the protocol negotiation during a TLS handshake.

Be sure to specify consecutive protocol versions when configuring multiple protocols. If the versions are not consecutive, only the latest versions in a consecutive range will be allowed for encryption. For example, in a setting such as TLSVersions = ["TLSv1.1", "TLSv1.3"], TLSv1.3 will be the only allowed protocol. To allow both TLSv1.1 and TLSv1.3, TLSv1.2 must be specified as well. Therefore, the recommended setting would be TLSVersions = ["TLSv1.1", "TLSv1.2", "TLSv1.3"].

Note

  • TLS versions and cipher suites may only be used with HTTPS.

  • If TLSVersions and TLSCipherSuites properties are not included or are left empty in the Studio configuration file, all of the supported TLS versions and their cipher suites will be permitted.

  • TLS version and cipher suite configurations apply to the Studio server (the "server side").

Supported TLS Cipher Suites

Cipher Suite (OpenSSL)

Cipher Suite (IANA)

TLS Versions Supported

RC4-SHA

TLS_RSA_WITH_RC4_128_SHA

Up to TLSv1.2

DES-CBC3-SHA

TLS_RSA_WITH_3DES_EDE_CBC_SHA

Up to TLSv1.2

AES128-SHA

TLS_RSA_WITH_AES_128_CBC_SHA

Up to TLSv1.2

AES256-SHA

TLS_RSA_WITH_AES_256_CBC_SHA

Up to TLSv1.2

AES128-SHA256

TLS_RSA_WITH_AES_128_CBC_SHA256

TLSv1.2

AES128-GCM-SHA256

TLS_RSA_WITH_AES_128_GCM_SHA256

TLSv1.2

AES256-GCM-SHA384

TLS_RSA_WITH_AES_256_GCM_SHA384

TLSv1.2

ECDHE-ECDSA-RC4-SHA

TLS_ECDHE_ECDSA_WITH_RC4_128_SHA

Up to TLSv1.2

ECDHE-ECDSA-AES128-SHA

TLS_ECDHE_ECDSA_WITH_AES_128_CBC_SHA

Up to TLSv1.2

ECDHE-ECDSA-AES256-SHA

TLS_ECDHE_ECDSA_WITH_AES_256_CBC_SHA

Up to TLSv1.2

ECDHE-RSA-RC4-SHA

TLS_ECDHE_RSA_WITH_RC4_128_SHA

Up to TLSv1.2

ECDHE-RSA-DES-CBC3-SHA

TLS_ECDHE_RSA_WITH_3DES_EDE_CBC_SHA

Up to TLSv1.2

ECDHE-RSA-AES128-SHA

TLS_ECDHE_RSA_WITH_AES_128_CBC_SHA

Up to TLSv1.2

ECDHE-RSA-AES256-SHA

TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA

Up to TLSv1.2

ECDHE-ECDSA-AES128-SHA256

TLS_ECDHE_ECDSA_WITH_AES_128_CBC_SHA256

TLSv1.2

ECDHE-RSA-AES128-SHA256

TLS_ECDHE_RSA_WITH_AES_128_CBC_SHA256

TLSv1.2

ECDHE-RSA-AES128-GCM-SHA256

TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256

TLSv1.2

ECDHE-ECDSA-AES128-GCM-SHA256

TLS_ECDHE_ECDSA_WITH_AES_128_GCM_SHA256

TLSv1.2

ECDHE-RSA-AES256-GCM-SHA384

TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384

TLSv1.2

ECDHE-ECDSA-AES256-GCM-SHA384

TLS_ECDHE_ECDSA_WITH_AES_256_GCM_SHA384

TLSv1.2

ECDHE-RSA-CHACHA20-POLY1305-OLD

TLS_ECDHE_RSA_WITH_CHACHA20_POLY1305

TLSv1.2

ECDHE-ECDSA-CHACHA20-POLY1305-OLD

TLS_ECDHE_ECDSA_WITH_CHACHA20_POLY1305

TLSv1.2

TLS_AES_128_GCM_SHA256

TLS_AES_128_GCM_SHA256

TLSv1.3 (see note)

TLS_AES_256_GCM_SHA384

TLS_AES_256_GCM_SHA384

TLSv1.3 (see note)

TLS_CHACHA20_POLY1305_SHA256

TLS_CHACHA20_POLY1305_SHA256

TLSv1.3 (see note)

Note

The TLSCipherSuites property cannot be used when TLSv1.3 is selected for a TLS handshake. TLSv1.3 uses three predefined cipher suites that cannot be modified using the TLSCipherSuites property.

Enabling HTTP/2

As of version 3.2.3, Studio also supports encrypted connections via HTTP/2 over TLS. To enable HTTP/2, set the EnableHTTP2 property to true in the Studio configuration file. (See Configuration File for a more comprehensive example.)

EnableHTTP2 = true

Note

HTTP/2 may only be used with HTTPS.

Encrypted connections via HTTP/2 over TLS 1.2 introduces additional restrictions on the supported cipher suites:

  • HTTP/2 deployments over TLS 1.2 should not use any cipher suites listed in the TLS 1.2 Cipher Suite Black List as it may result in a connection error.

  • This black list includes the mandatory cipher suite for TLS 1.2, which means that TLS 1.2 deployments could have non-intersecting sets of permitted cipher suites. To avoid potential TLS handshake failures, deployments of HTTP/2 that use TLS 1.2 must support TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 [TLS-ECDHE] with the P-256 elliptic curve [FIPS186].

For more information, see related IETF documentation.

Configuration File

The following is an example configuration file with HTTPS, TLS, and cipher suite settings.

# This is the IP address that SingleStore DB Studio will bind to.
host = "0.0.0.0"

# This is the port that SingleStore DB Studio will bind to.
port = 8080

# This is the absolute path to the state file of SingleStore DB Studio.
statePath = "/PATH/TO/studio.hcl"

# This is the absolute path to the log file of SingleStore DB Studio.
logPath = "/PATH/TO/studio.log"

# This is the absolute path to the SSL certificate used to serve SingleStore DB Studio UI with HTTPS.
HTTPSCertificateFile = "/PATH/TO/server.crt"

# This is the absolute path to the key file of the SSL certificate used to serve SingleStore DB Studio UI with HTTPS. 
HTTPSCertificateKeyFile = "/PATH/TO/server.key"

# This property specifies the TLS protocol versions that can be used for Studio’s connections with the browser 
TLSVersions = ["TLSv1.1", "TLSv1.2"]

#This property specifies the TLS cipher suites that can be used for Studio’s connections with the browser.
TLSCipherSuites = ["ECDHE-ECDSA-RC4-SHA", "ECDHE-ECDSA-AES128-SHA", "ECDHE-ECDSA-AES256-SHA", "ECDHE-RSA-RC4-SHA", "ECDHE-RSA-DES-CBC3-SHA", "ECDHE-RSA-AES128-SHA", "ECDHE-RSA-AES256-SHA", "ECDHE-RSA-AES128-GCM-SHA256"]

#This enables encrypted connections via HTTPS/2 over TLS
EnableHTTP2 = true