Authenticate via JWT

A JSON Web Token (JWT) is an open, industry standard (RFC 7519) that defines a compact and self-contained method for securely transmitting information between parties as a JSON object, which can be verified and trusted as it is digitally signed.

JWTs are useful for both authorization (the most common scenario for using a JWTs) and information exchange (where information can be securely transmitted between parties). SingleStoreDB Cloud uses JWTs for authentication. Refer to JWT.io for more information.

Use JWTs with the SingleStore and MySQL Clients

To use a JWT with the SingleStore client, replace the password with the JWT:

singlestore -u $EMAIL_ADDRESS -h $CLUSTER_HOSTNAME -P $CLUSTER_PORT --password=$JWT --ssl=TRUE --enable-cleartext-plugin

Similarly, to use a JWT with the MySQL client, replace the password with the JWT.

mysql -u $EMAIL_ADDRESS -h $CLUSTER_HOSTNAME -P $CLUSTER_PORT --password=$JWT --ssl=TRUE

About JWT Users

For the purposes of this document, a “JWT user” is a user that can authenticate with a JWT when connecting to the SingleStoreDB Cloud.

JWT users are created by the SingleStore portal. See Create a JWT User for more information.

For authenticating to SingleStoreDB Cloud with a JWT:

  • When using browser-based SSO in conjunction with the singlestore-auth-helper or SingleStore driver, the JWT user is identified by the user’s portal email address, where the sub field of the JWT will contain the user’s email address as the username. Any user configured with a corresponding JWT user in the database can use this method to authenticate. The default expiration time for these tokens is sixteen (16) hours. Clusters accept these JWTs that are signed by the SingleStore portal.

  • When using the portal SQL Editor against a running cluster, a JWT user is identified by a UUID. Only portal users that are members of the organization which owns the cluster may access the database using this method. Clusters accept these JWTs that are signed by the SingleStore portal.

  • Customers can create and sign JWTs. For a customer-created JWT to be accepted by SingleStoreDB Cloud, the cluster must be configured to accept the public key corresponding to the private key used to sign the JWT.

  • Clusters are configured to accept JWTs using JSON Web Key Sets (JWKS). JWTs are matched to JSON Web Keys (JWKs) to validate the JWT. Together, the JWK and JWT specify the database username to use.

Validate JWTs with JWKS

Customer-signed JWTs are validated using JSON Web Key Sets (JWKS). JWKS are a set of keys which contain public keys that can be used to authenticate any JWT. JWKS is a standard for downloading a batch of JWKs from a URL.

JWTs are matched with JSON Web Keys (JWKs) for validation as follows:

  • If the JWT has a kid (Key ID) field, the JWKs with matching kid fields are validated.

  • If the JWT has a kid field that doesn’t match any JWK, the authentication request is rejected.

  • If the JWT has an iss (Issuer) field (instead of a kid field) that matches the kid in one or more JWKs, the JWKs with matching kid fields are validated.

  • If the JWT does not have a kid field and the iss field does not match the kid field in any JWK, then validation is attempted with all the JWKs with a matching alg (Algorithm) field. If the alg field is not specified, the kty (Key Type) field is used instead.

The JWTs signature is validated as follows:

  • The JWT’s signature is validated against matching JWKs. If the JWT’s signature matches more than one JWK, validation is attempted against all matching JWKs. If the signature cannot be validated, the authentication request is rejected.

  • If the matching JWK includes an aud (Audience) field which does not match the aud field in the JWT, then the authentication request is rejected. The aud field can be a string or an array of strings. If any aud string of the JWT matches any aud string of the JWK, it is considered a match.

  • If the matching JWK does not define an audience (aud), audience checking is skipped. Note that aud is not a standard field in JWK.

The database username is identified as follows:

  • If the matching JWK has a usernameFrom field, the value of this JWK field is used to identify the name of the field in the JWT that determines the database username.

  • If the JWK does not have a usernameFrom field, but the JWT has a username field, then the username field determines the database username.

  • If the JWK does not have a usernameFrom field and the JWT does not have a username field, then the sub field in the JWT determines the database username.

Note

The database username in the JWT, once determined, must exactly match the database username used in the connection attempt. Otherwise, the authentication is rejected.

Configure JWKS

To authenticate using JWKS, you may build a JWKS configuration file that contains your public keys and serve it from your Web server via static URL.

SingleStoreDB Cloud supports the following public key signature algorithms: RS256, RS384, RS512, ES256, ES384, and ES512.

Perform the following tasks to configure JWKS.

  1. Configure the certificate authority used to validate the TLS transfer of the JWKS using SSL.

    1. Upload a public key certificate corresponding to your TLS encryption certificate authority that secures the HTTP SSL transfers.

      Assuming that your JWKS endpoint is http://some-domain.com/some/path, you can obtain the proper certificate using the following Linux command.

      openssl s_client --connect some-domain.com:443 --servername some-domain.com --showcerts < /dev/null | sed -ne '/-BEGIN CERTIFICATE-/,/-END CERTIFICATE-/p' > certificate-filename.pem
      1. On the SingleStore portal, select the cluster on which to enable a TLS/SSL connection.

      2. Under Security, select the Upload Certificate button to upload your security certificate. Once the certificate is uploaded, it is available to all nodes in the cluster. This allows you to secure outbound connections via TLS/SSL.

    2. Specify the certificate file using the following command:

      SET GLOBAL jwks_ssl_ca_certificate='certificate-filename.pem'
  2. Configure the JWKS endpoint using the following command:

    SET GLOBAL jwks_endpoint='https://example.com/.well-known/jwks.json'

    Updating the endpoint configuration triggers a JWKS update and validation.

    The SET GLOBAL command will return an error if:

    • The GET request fails.

    • There are no valid keys in the updated JWKS.

    A warning is generated if any JWKS is rejected, even if the JWKS has valid keys. Information on rejected keys is available in the system log.

To disable JWKS updates, set the endpoint to an empty string.

SET GLOBAL jwks_endpoint=''

Optionally, you can set a refresh interval to update the JWKS configuration automatically (specify the value in seconds).

SET GLOBAL jwks_update_interval = 3600

If this value is not set, or set to 0, the JWKS configuration updates only when the SET GLOBAL jwks_endpoint command is run. To manually refresh the JWKS configuration, run the SET GLOBAL jwks_endpoint command with its existing value.

JWKS Update Status

To check the status of the most recent JWKS update, use the SHOW STATUS EXTENDED command. It provides the following information:

  • JWKS_update_status: Specifies the status of the update. The status can be one of the following: SUCCESS, FAILED or DISABLED. The FAILED status is followed by the reason behind the failure, in parenthesis.

  • JWKS_update_time: Specifies the timestamp of JWKS update.

    singlestore> SHOW STATUS EXTENDED LIKE 'JWKS_update%';
    +--------------------+-----------------------------------------------------------------------+
    | Variable_name      | Value                                                                 |
    +--------------------+-----------------------------------------------------------------------+
    | JWKS_update_status | FAILED (Failed to connect to localhost port 8082: Connection refused) |
    | JWKS_update_time   | Mon May  2 14:03:08 2022 EST                                         |
    +--------------------+-----------------------------------------------------------------------+

Create a JWT User

Notice

Creating JWT users is only supported in SingleStoreDB Cloud clusters running SingleStoreDB v7.8.5 and later.

By default, each member of a SingleStoreDB Cloud organization that belongs to a cluster has a corresponding JWT user created for that cluster. This JWT user is based on the user’s portal email address as the username.

As a consequence, do not manually create a JWT user with a username that resembles an email address unless email syncing is disabled.

To create a JWT user, run the following SQL command.

CREATE USER 'email@example.com'@'%' IDENTIFIED WITH authentication_jwt REQUIRE SSL;

where:

  • email@example.com is the JWT user’s email address which serves as the username; anything but a UUID may be used

  • IDENTIFIED WITH authentication_jwt sets the user’s authentication method to use a JWT

  • REQUIRE SSL (mandatory) enforces the use of SSL for the JWT connection

Notes on JWT Authentication

For SingleStoreDB Cloud, the portal ultimately controls the set of JWT users within a cluster that matches the username patterns that the portal is managing, and will create and delete users so that the set of JWT users within a cluster is equal to the set of users within an organization.

SingleStoreDB Cloud creates two JWT users for each user in the organization:

  1. One user based on a UUID, which is used for the SQL Editor and other portal functions.

  2. One user based on the user’s email address, which is used with browser-based SSO to SingleStoreDB Cloud clusters.

Syncing users based on email addresses is optional. It is enabled by default so that the singlestore-auth-helper and associated drivers function correctly. This behavior can be disabled on a per-organization basis.

Notice

To stop email address syncing, please open a support ticket. All SingleStoreDB Cloud clusters owned by the organization must be running SingleStoreDB v7.3.12 or later.

SingleStore recommends that syncing user emails is disabled when assigning JWT users in SingleStoreDB Cloud. This will allow a customer-created JWT user to use an email address as the username, and the singlestore-auth-helper and associated drivers to access it.