Watch the 7.3 Webinar On-Demand
This new release brings updates to Universal Storage, query optimization, and usability that you won’t want to miss.

SingleStore DB Studio Security

Info

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 is a visual SQL client that connects to your clusters. As such, it defers to your clusters for authentication.

Logging in

To login to a cluster from SingleStore DB 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 host of the machine running SingleStore DB Studio. This ensures that only users with proper permissions on the proper hosts can access clusters using SingleStore DB Studio. See Securing SingleStore DB for more details on configuring SingleStore DB user accounts.

Logging in using Kerberos

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

Once configured, SingleStore DB Studio will work as any other application using Kerberos authentication inside a browser.

Prerequisites

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

  • (Optional) To allow SingleStore DB Studio to automatically log in with the current Kerberos user, set the kerberosAutologin property to true in the SingleStore DB Studio state file. You must restart SingleStore DB 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.

  • SingleStore DB Studio is served under the CNAME auth-server.example.com and the Kerberos Realm is example.com.

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

  • The browser has Kerberos configured.

  • A Kerberos Service Principal Name (SPN) of HTTP/auth-server.example.com@EXAMPLE.COM is used for authentication. This is the default SPN generated by the browser. This SPN must be added to your KDC as a trusted new client.

  • The variable gssapi_principal_name SingleStore DB engine variable is defined with the desired SPN (in this example, HTTP/auth-server.example.com@EXAMPLE.COM).

    • This 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
    

Kerberos authentication flow

The Kerberos authentication flow is depicted in the following diagram.

Kerberos authentication flow

Running queries

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

Serving the UI with HTTPS

To serve the SingleStore DB Studio user interface with HTTPS, edit the SingleStore DB Studio configuration file to include the following two options:

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 SingleStore Studio binary, although the absolute path is recommended. To learn how to create SSL certificates, see Generating SSL Certificates.

An example configuration file:

# 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 path to the state file of SingleStore DB Studio.
statePath = "/var/lib/singlestoredb-studio/studio.hcl"

# This is the path to the log file of SingleStore DB Studio.
logPath = "/var/lib/singlestoredb-studio/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"