Protect Directories in SingleStore Using CTE

Generate the SingleStore Key for Encryption

1. Select Keys > Agent Keys > Keys from the main menu.

2. Click the Add button to add the key. Enter the name singlestore_key. Choose the default template Default_SQL_Symmetric_KeyTemplate. Click Ok.

Create a SingleStore User Set

1. Select Policies > Manage Policies > User Sets from the main menu.

2. Enter the name for the User Set: SingleStoreUser.

3. Click Add.

4. Click Browse Users.

5. Select the host on which you want to add the user. Click Ok.

6. The list of users should appear in the bottom of the screen. Choose the user that is running SingleStore nodes on the host (memsql in most cases) and the user that is running Toolbox commands.

Create Encryption and Decryption Data Transformation Policies

Encryption Policy will be used for initial data encryption. Decryption Policy may be used in case you need to decrypt the data protected by CTE.

Create a SingleStore Process Set

1. Select Policies > Manage Policies > Process Sets.

2. Click Add.

3. Enter the name: singlestore-process-set.

4. Add entries via the Add button on the left.

5. Select the SingleStore host.

6. Add the following entries:

   • memsqld (main database daemon process)

   • memsqld_safe (daemon that restarts memsqld if it crashes or is killed)

   • memsqlctl (the tool that controls local DB nodes)

   • bash, sh (to perform certain file operations)

Find the locations of the memsql* commands/binaries by running: sdb-admin describe-node --all on the Toolbox host. In the example above memsqld is located at /home/memsql/memsql/memsql-server-7.1.11-6c108deb15/memsqld. The same directory contains memsqld_safe and memsqlctl. That path (/home/memsql/memsql/memsql-server-7.1.11-6c108deb15/ in our example) is the MEMSQL_BINARIES_PATH mentioned below. Wherever you see MEMSQL_BINARIES_PATH below, replace it with your path.

When you know the location, you can enter it in the Directory field, then add the process name in File and click Ok. You should end up with a configuration similar to this:

For Debian/RPM based installations singlestore-process-set should be similar to the following configuration:

Note that you may need to replace /bin/ with /usr/bin/ above. Although, you may need to include both the entries, /bin and /usr/bin.To check the bash location, run which bash.

Configure Host File Settings

1. On the DSM Management Console, navigate to the Hosts > Hosts page.

2. For each SingleStore host, click on the host’s IP Address or Hostname.

3. Select the Host Settings tab, and add the following host settings entries as new lines:

   Copy

   |authenticator_euid|<MEMSQL_BINARIES_PATH>/memsqld
   |authenticator_euid|<MEMSQL_BINARIES_PATH>/memsqld_safe
   |authenticator_euid|<MEMSQL_BINARIES_PATH>/memsqlctl

4. Click the Re-Sign Host Settings checkbox.

5. Click Ok.

Create a SingleStore Security Policy

You need to create a specific SingleStore security policy that will include the user and process sets defined above. This SingleStore security policy will be used by the guard points to enforce read, write, and execution of files in the protected paths (/home/memsql/memsql/nodes in this example case). This is the main point where access control is defined, so it should be modified according to your needs.

1. Select Policies > Manage Policies > Manage Policies.

2. Click Add.

3. Choose the Standard policy type. Enter the name: SingleStore_security.

4. Under Security Rules, click Add. Add the following security rules in this order:

   • This rule will be applied for any user to allow browsing the directories on host.

     • Action: f_rd_att, f_rd_sec, d_rd, d_rd_att, d_rd_sec

     • Effect: Permit

   • This rule will be applied for the user(s) running SingleStore and the corresponding processes. It is the only policy with Apply Key enabled to decrypt data.

     • User: SingleStoreUser

     • Process: singlestore-process-set

     • Action: all_ops

     • Effect: Permit, Apply Key

   • This rule allows everyone to read encrypted data.

     • Action: read

     • Effect: Permit

   • This rule will be applied for all other users/processes attempting to access SingleStore data.

     • Action: all_ops

     • Effect: Audit, Deny

5. Under Key Selection Rules, click Add.

6. Select singlestore_key.

7. Click Ok.

Create Encryption Guard Points and Transform Data

1. Select Hosts > Hosts in the main menu.

2. Click on the SingleStore Host name.

3. Go to the Guard Points tab.

4. Click Guard.

5. Choose the SingleStore_encryption Policy.

6. Enter the list of full paths to the directories you want to guard.

7. Click Ok.

8. The log file will show a message that the target directory paths are successfully guarded. You will see a green circle next to the guard point that you created.

9. On the host where CTE is installed, switch to a root user. For each of the guarded directories run dataxform --rekey --preserve_modified_time --gp <GUARD_DIR> (replacing GUARD_DIR with the the name of each directory).

Again, verify that you have created a guard point for all data, plan cache, audit log, trace log, nodes metadata, backup, and outfile directories (if any) that you wish to protect for your SingleStore environment.

Troubleshooting Tips

• If you need to re-run dataxform for the same directory more than once, you may get the Automatic data transform status for /home/memsql/memsql/nodes: previous attempt completed error message. In this case you need to run dataxform --cleanup --gp <GUARD_DIR>

• If you do not see a green circle next to your guard point, the directory may be busy, i.e. there is a shell connected to it. You have to exit the directory for the guard point to be active.

• To decrypt the data, disable all the guard points, then create guard points for the same directories as you used for SingleStore_encryption but with SingleStore_decryption policy, and run dataxform --rekey --preserve_modified_time --gp GUARD_DIR

Enable the SingleStore Security Policy

1. Select Hosts > Hosts, click on your CTE host.

2. Disable all entries with Policy SingleStore_enryption.

3. Select Guard points tab, click Guard.

4. Select the SingleStore_security policy, Type: Directory (Auto Guard).

5. Enter the list of full paths to the directories you want to guard (/home/memsql/memsql/nodes in this example).

6. Click Ok.

7. The log file will show a message that the target directory paths are successfully guarded. You will see a green circle next to the guard point that you created.

8. Click Ok.

9. After a short delay you will see the green circle next to the newly created SingleStore_security entries. This means that the policy is active and SingleStore processes and data are protected.

10. Now you can start the SingleStore cluster by running sdb-admin start-node --all --yes.

Prepare an AWS Instance (Ubuntu Only)

1. Launch an AWS instance with at least 4 CPU cores and 16 GB of RAM. You will then need to modify it to install the CTE agent software.

2. Install the generic Ubuntu kernels:

   Shell

   Copy

   sudo apt install linux-generic

3. Find the $menuentry_id_option for the submenu:

   Shell

   Copy

   grep submenu /boot/grub/grub.cfg

   SAMPLE OUTPUT:

   Copy

   submenu 'Advanced options for Debian GNU/Linux' $menuentry_id_option 'gnulinux-advanced-38ea4a12-6cfe-4ed9-a8b5-036295e62ffc'

4. Find the $menuentry_id_option for the menu entry for the generic kernel version you want to use. You can choose any kernel version up to 4.4.0-189-generic:

   Shell

   Copy

   grep gnulinux /boot/grub/grub.cfg

   SAMPLE OUTPUT:

   Copy

    menuentry 'Debian GNU/Linux' --class debian --class gnu-linux --class gnu --class os $menuentry_id_option 'gnulinux-simple-38ea4a12-6cfe-4ed9-a8b5-036295e62ffc' { submenu 'Advanced options for Debian GNU/Linux' $menuentry_id_option 'gnulinux-advanced-38ea4a12-6cfe-4ed9-a8b5-036295e62ffc' { menuentry 'Debian GNU/Linux, with Linux 4.18.0-0.bpo.1-rt-amd64' --class debian --class gnu-linux --class gnu --class os $menuentry_id_option 'gnulinux-4.18.0-0.bpo.1-rt-amd64-advanced-38ea4a12-6cfe-4ed9-a8b5-036295e62ffc' { menuentry 'Debian GNU/Linux, with Linux 4.18.0-0.bpo.1-rt-amd64 (recovery mode)' --class debian --class gnu-linux --class gnu --class os $menuentry_id_option 'gnulinux-4.18.0-0.bpo.1-rt-amd64-recovery-38ea4a12-6cfe-4ed9-a8b5-036295e62ffc' { menuentry 'Debian GNU/Linux, with Linux 4.18.0-0.bpo.1-amd64' --class debian --class gnu-linux --class gnu --class os $menuentry_id_option 'gnulinux-4.18.0-0.bpo.1-amd64-advanced-38ea4a12-6cfe-4ed9-a8b5-036295e62ffc' {menuentry 'Debian GNU/Linux' --class debian --class gnu-linux --class gnu --class os $menuentry_id_option 'gnulinux-simple-38ea4a12-6cfe-4ed9-a8b5-036295e62ffc' { submenu 'Advanced options for Debian GNU/Linux' $menuentry_id_option 'gnulinux-advanced-38ea4a12-6cfe-4ed9-a8b5-036295e62ffc' { menuentry 'Debian GNU/Linux, with Linux 4.18.0-0.bpo.1-rt-amd64' --class debian --class gnu-linux --class gnu --class os $menuentry_id_option 'gnulinux-4.18.0-0.bpo.1-rt-amd64-advanced-38ea4a12-6cfe-4ed9-a8b5-036295e62ffc' { menuentry 'Debian GNU/Linux, with Linux 4.18.0-0.bpo.1-rt-amd64 (recovery mode)' --class debian --class gnu-linux --class gnu --class os $menuentry_id_option 'gnulinux-4.18.0-0.bpo.1-rt-amd64-recovery-38ea4a12-6cfe-4ed9-a8b5-036295e62ffc' { menuentry 'Debian GNU/Linux, with Linux 4.18.0-0.bpo.1-amd64' --class debian --class gnu-linux --class gnu --class os $menuentry_id_option 'gnulinux-4.18.0-0.bpo.1-amd64-advanced-38ea4a12-6cfe-4ed9-a8b5-036295e62ffc' {

5. Comment out your current GRUB_DEFAULT definition in /etc/default/grub and replace it with the submenu $menuentry_id_option from step 3, and the selected kernel’s $menuentry_id_option from step 4 separated by >.

   Example modified GRUB_DEFAULT: #GRUB_DEFAULT=0 GRUB_DEFAULT="gnulinux-advanced-38ea4a12-6cfe-4ed9-a8b5-036295e62ffc>gnulinux-4.18.0-0.bpo.1-amd64-advanced-38ea4a12-6cfe-4ed9-a8b5-036295e62ffc"

6. Update grub to save the changes:

   Shell

   Copy

   sudo update-grub

7. Reboot the server; after reboot you should be running the generic kernel that you selected. Confirm by running:

   Shell

   Copy

   sudo uname -a