Configuring SingleStore for Kerberos Authentication

Copying the Keytab File to SingleStore

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 file is a binary file, it must be transferred using a 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 file will be corrupted.

Use a secure file transfer method, such as scp, to copy the keytab file from the KDC server to each SingleStore aggregator node. After the keytab file has been copied, its file path 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, you must configure each SingleStore 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 server and copied to the node. For example: /path/to/memsql.keytab

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

To set these variables in the memsql.cnf file:

1. Ensure that the cluster is stopped before continuing.

2. Open the aggregator node’s memsql.cnf file in a text editor (typical location for this file).

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

   Copy

   [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:

   SQL

   Copy

   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 cluster has been configured, you can use the GRANT command to create a Kerberos-authenticated user and grant permissions. To grant permissions, you simply map a SingleStore 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.

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

GRANT DELETE ON db.* TO steve;

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

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

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

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

Connecting to SingleStore as a Kerberos-authenticated User

After a SingleStore user has been associated with a Kerberos SPN, you can connect to SingleStore 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 a variety of 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.

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