SingleStoreDB v7.3

SingleStoreDB Studio WebSocket Proxy

SingleStoreDB v7.1 introduced a WebSocket Proxy which provides a new method for communicating with a SingleStoreDB cluster. The WebSocket Proxy (or, simply, “the proxy”) has a small footprint, and typically a low overhead when running.

Each proxy instance opens: - A new Unix socket for communicating with the SingleStoreDB engine - An HTTPS port for communicating with the outside world

Start the Proxy

There are three options available for starting the proxy. Before starting the proxy, keep the following points in mind:

A free, open port must be used for the WebSocket proxy; the port can be specified using the https_proxy_port or the http_proxy_port variable.
As the https_proxy_port and http_proxy_port WebSocket variables are mutually exclusive, the proxy will fail to start if both are set, and you will be prompted to update the memsql.cnf file.
The https_proxy_port variable requires SSL to be enabled. For more information on enabling SSL, refer to SSL Secure Connections guide.
If multiple nodes have a WebSocket variable set, only the Master Aggregator’s proxy will be used, with the non-aggregator proxies remaining dormant. If a new aggregator is attached or promoted, it must have a WebSocket variable set in order to serve WebSocket traffic.

Option 1: Modify the `memsql.cnf` file

On the Master Aggregator host of a SingleStoreDB cluster, add one of the following lines to the memsql.cnf file.
- For HTTPS: https_proxy_port = <port>
- For HTTP: http_proxy_port = <port>
Start the cluster.
```
sdb-admin start-node --all
```

Option 2: SingleStoreDB Toolbox

On the Master Aggregator host of a running cluster, use sdb-admin update config to set either the https_proxy_port or http_proxy_port variable.
For HTTPS:
```
sdb-admin update-config --key https_proxy_port --value <port> 
```
For HTTP:
```
sdb-admin update-config --key http_proxy_port --value <port> 
```

Refer to the update-config command reference for more information on how to update the configuration of the Master Aggregator.

Option 3: SQL

Start the cluster and run the following in a database connection where you have superuser SUPER privileges.
For HTTPS:
```
SET GLOBAL https_proxy_port = <port>;
```
For HTTP:
```
SET GLOBAL http_proxy_port = <port>;
```
Restart the proxy.
```
RESTART PROXY;
```

This will allow the proxy to start even if no proxy port was specified in the memsql.cnf file. As setting both https_proxy_port and http_proxy_port is not supported, select the protocol that best fits your requirements.

Caution

In the case of conflicting variables, such as when both the https_proxy_port and http_proxy_port variables are set, the proxy will throw an error but not abort. This means that it is possible for both variables to be set at runtime, but RESTART PROXY will be disabled until this discrepancy is resolved. As these variables can be set coincidentally, there should be no expectation that the state of the global variable reflects the state of the proxy.

Change the Proxy Port

To change a proxy’s port when it’s running, specify a new port and restart the proxy.
For HTTPS:
```
SET GLOBAL https_proxy_port = <port>;
```
For HTTP:
```
SET GLOBAL http_proxy_port = <port>;
```
Restart the proxy. Note that RESTART PROXY still requires SUPER.
```
RESTART PROXY;
```

By design, RESTART PROXY is designed to either fail, where nothing undesirable happens and an error is returned, or to work absolutely, where a proxy is running on the port specified by the global variable.

About the Proxy State

Regardless of how it was started, the proxy will not restart itself on a failure. This is intentional, because if the proxy terminates, it will require manual intervention to restart it. In this case, the user and/or the host’s administrator must verify that the host itself is stable and problem-free before issuing a RESTART PROXY.

If RESTART PROXY does not throw an error, the state of the proxy is reflected in the global variable at the time of the restart. Once started, it is the user’s responsibility to ensure that the proxy does not enter an unknown state.

The proxy log, websocket_proxy.log, can be found in the same directory as the other SingleStoreDB logs. Refer to Trace Log for more information.

Use SingleStoreDB Studio with the Proxy

Prior to Studio v1.9.7, Studio was required to be co-located on the cluster’s Master Aggregator host if secure connections to SingleStoreDB were desired.

Figure 8. Master Aggregator Host Architecture

With the introduction of this WebSocket Proxy (or, simply, “the proxy”), Studio can now connect to SingleStoreDB via HTTPS on hosts other than the Master Aggregator.

This solution has a number of advantages:

Unlike the previous solution, Studio can now accept users that are configured as REQUIRE SSL. Previously, the communication between Studio and SingleStoreDB was not over HTTPS, which necessitated the co-location requirement. As a consequence, Studio no longer needs to be co-located with a SingleStoreDB cluster in order to provide secure communications.
Traffic no longer needs to be proxied through Studio and can now go directly to the SingleStoreDB cluster.

As shown in the diagram below, Studio can now connect directly to a SingleStoreDB cluster through the wss:// protocol.

Figure 9. Separate Host Architecture

Architectural Differences

There are some differences with this new SingleStoreDB Studio architecture compared to the one it replaces:

The machine running the browser needs to have direct access to the Master Aggregator host. Previously, only the singlestoredb-studio server required this access.
For situations where REQUIRE SSL is not mandatory, and if the additional configuration required to use a direct WebSocket connection becomes a bottleneck, it may be simpler to use the existing Studio architecture, where Studio is served over HTTPS and the singlestoredb-studio server is co-located with the Master Aggregator.

Prerequisites

The following requirements must be met in order to use a secure proxy connection from your browser.

Studio must be served via HTTPS to establish and use a proxy connection via the wss:// protocol.
The SingleStoreDB endpoint added to Studio must use the same domain that’s configured in the SingleStoreDB SSL certificate as expressed in the memsql.cnf file.
The port (the https_proxy_port or http_proxy_port value) must be exposed so that a browser can communicate over it.
The browser must be configured to trust the certificate. This can either be achieved by using a certificate created by a known CA, such as letsencrypt.org, or by an internal enterprise CA that may be pre-installed on a company computer. Refer to How to add a trusted CA certificate to Chrome and Firefox for more information.

Configure SingleStoreDB Studio

Use the following steps to configure Studio to use the WebSocket proxy to connect to your cluster.

Start the Proxy.
Edit the Studio state file and add the following lines to the cluster definition:
- websocket = true
- websocketSSL = true or false
  - If using https_proxy_port: true
  - If using http_proxy_port: false
- port = <port>
  - If websocket is true, the WebSocket port specified using the https_proxy_port or the http_proxy_port variable in this step

Restart Studio.

Using systemd with package-based installations of Studio:

sudo systemctl restart singlestoredb-studio

Using sudo with package-based installations of Studio:

sudo singlestoredb-studio &

For non-sudo/tarball-based installations of Studio:

cd singlestoredb-studio-<version>

nohup ./singlestoredb-studio > studio.stdout 2> studio.stderr < /dev/null &

Log into Studio and connect to the cluster that you configured to use WebSockets.

In this section: