SingleStore DB

Configuring SingleStore DB for Kerberos Authentication

Once you have generated a SPN and a keytab on the Kerberos KDC server, you can configure SingleStore DB to allow access for Kerberos-authenticated users.

Depending on your SingleStore DB cluster configuration, the SPN and keytab file may need to be configured on one or many aggregator nodes. For example, if your cluster contains one master aggregator and one child aggregator, you must configure both nodes to allow client connections for Kerberos-authenticated users.

The following steps describe how to use the SPN and keytab file you created in earlier sections.

Copying the Keytab File to SingleStore DB

A keytab file is a credential that can be used to access network resources, so it is important to consider the security implications of copying it from one location to another. Additionally, since a keytab is a binary file, you must use a transfer method that will not corrupt the file. For example, if FTP is configured to transfer as an ASCII file type instead of a binary file type, the keytab will be corrupted.

Use a secure file transfer method, such as scp, to copy the keytab file from the KDC to each SingleStore DB aggregator node. After the keytab file has been copied, its filepath on the aggregator node will be used to configure the memsql.cnf file in the next step.

Configuring memsql.cnf

Before Kerberos-authenticated users can connect to SingleStore DB, you must configure each SingleStore DB aggregator node’s memsql.cnf file with two variables:

  • gssapi-keytab-path: The local path to the keytab file that was created on the KDC and copied to the node. For example: /path/to/memsql.keytab

  • gssapi-principal-name: The SPN for SingleStore DB that was created on the KDC. For example: memsql/host.domain.com@DOMAIN.COM

Notice

You cannot use the SET GLOBAL VARIABLE command when setting these variables. They must be configured in the memsql.cnf file on the applicable node using the steps described below.

To set these variables in the memsql.cnf file:

  1. Ensure that the cluster is in a stopped state before continuing.

  2. Open the node’s memsql.cnf file in a text editor.

  3. Add the following line to the file anywhere below the [server] declaration:

    [server]
    ...
    gssapi-keytab-path = /path/to/memsql.keytab
    gssapi-principal-name = memsql/host.domain.com@DOMAIN.COM
    ...
  4. Save the file.

  5. Repeat this process for each aggregator node’s memsql.cnf file.

  6. After the memsql.cnf file has been updated for each aggregator node in the cluster, start the cluster.

  7. Verify that the change was successful by executing the following command on one of the affected nodes:

    SHOW GLOBAL VARIABLES LIKE 'gssapi%';
    --------------
    SHOW GLOBAL VARIABLES LIKE 'gssapi%'
    --------------
    
    +-----------------------+-------------------------------------+
    | Variable_name         | Value                               |
    +-----------------------+-------------------------------------+
    | gssapi_keytab_path    | /path/to/memsql.keytab              |
    | gssapi_principal_name | memsql/host.domain.com@DOMAIN.COM   |
    +-----------------------+-------------------------------------+
    2 rows in set (0.00 sec)
Granting a User Kerberos Authentication Permissions

Now that the SingleStore DB cluster has been configured, you can grant permissions for a Kerberos-authenticated user to connect to SingleStore DB. To grant permissions, you simply map a SingleStore DB user to their Kerberos SPN. You can map Kerberos SPNs in two ways: by providing the name of a single Kerberos SPN, or by using regular expressions to match with or more Kerberos SPNs.

Mapping a Single Kerberos Identity

The following example grants an existing SingleStore DB user ('user1'@'%') permissions to authenticate via Kerberos on all databases, where their Kerberos SPN is user1@EXAMPLE.COM:

GRANT ALL ON *.* TO 'user1'@'%' IDENTIFIED WITH 'authentication_gss' AS 'user1@EXAMPLE.COM';

When this command is executed, the 'user1'@'%' SingleStore DB user is mapped to the user1@EXAMPLE.COM Kerberos SPN. After the command is executed, all subsequent SingleStore DB connection attempts for 'user1'@'%' will require a ticket-granting-ticket (TGT) from the KDC for the user1@EXAMPLE.COM SPN. If a user attempts to connect with a TGT for a different SPN, the connection will fail.

Mapping with Regular Expressions

You can optionally use regular expressions in the AS clause of the GRANT statement. This functionality is useful if you want to map a single SingleStore DB user to one or more Kerberos SPNs across multiple domains, or if you have other unique realm requirements.

Caution

By using regular expressions in your GRANT statement, ensure that you have validated that the expression evaluates in an expected manner. Without thorough validation of your regular expression, unintended results may occur.

Regular expressions can only be used in the AS clause of the GRANT statement. By default, regular expressions are disabled in the AS clause.

To enable regular expressions support, the first character in your AS clause must start with a forward slash: /. We recommend that the string should use standard ^ (start of line) and $ (end of line) operators to indicate when the regular expression should start and end.

Consider the following example:

GRANT ALL ON *.* to 'user1'@'%' IDENTIFIED WITH 'authentication_gss' AS '/^user1@EXAMPLE\.COM$';

This example will use regular expressions to match the user1@EXAMPLE.COM string in any Kerberos SPN that attempts to authenticate with SingleStore DB. It uses proper start of line and end of line operators to indicate that only the full user1@EXAMPLE.COM SPN should be matched. It’s possible to introduce unexpected behavior without providing these operators. Consider the following example:

GRANT ALL ON *.* to 'user1'@'%' IDENTIFIED WITH 'authentication_gss' AS '/user1@EXAMPLE\.COM$';

The example above differs from the previous example by omitting the ^ start of line operator. This simple omission introduces significant security implications, because another Kerberos SPN might contain the substring of user1@EXAMPLE.COM, such as other_user1@EXAMPLE.COM. Always ensure that your regular expression produces the intended result.

Example: Optional Subdomain

The following example maps the SingleStore DB user 'user1'@'%' with a Kerberos SPN that contains an optional subdomain:

GRANT ALL ON *.* to 'user1'@'%' IDENTIFIED WITH 'authentication_gss' AS '/^user1@(SUBDOMAIN\.)?EXAMPLE\.COM$';
Example: Optional Domain Suffix

The following example maps the SingleStore DB user 'user1'@'%' with a Kerberos SPN across domains with different suffixes, namely .COM, .NET, or .ORG:

GRANT ALL ON *.* to 'user1'@'%' IDENTIFIED WITH 'authentication_gss' AS '/^user1@EXAMPLE\.(COM|NET|ORG)$';
Connecting to SingleStore DB as a Kerberos-authenticated User

After a SingleStore DB user has been associated with a Kerberos SPN, you can connect to SingleStore DB as that user. Before authenticating however, you must have a valid TGT in your credential cache. Depending on your domain and system configuration, there are many different ways to acquire a valid TGT. For example, the most common method on Linux/Unix is to execute kinit from the terminal. On Windows, TGT acquisition and renewal is typically done automatically, as per the Active Directory group policy configuration.

Using the MariaDB command-line interface for example, you can execute the following command to connect to SingleStore DB as user1:

mysql --plugin-dir=/path/to/plugin-dir -u user1 --prompt="singlestore> "

Note that this authentication method requires you to specify the plugin directory that contains the auth_gssapi plugin for MariaDB, as described in the Prerequisites section above. Depending on the host operating system, you must provide the appropriate GSSAPI authentication plugin version. MariaDB provides authentication plugins for 32-bit and 64-bit versions of each supported operating system.

If you want to connect using ODBC, consider the following example below:

Driver=<path-to-mariadb-odbc-connector>;Database=information_schema;ServerName=10.0.0.1;Port=3306;UID=myusername;Plugin_Dir=<path-to-gssapi-plugin-directory>;