# Protect Directories in SingleStore Using CTE Now that you have the environment configured, you can start protecting your data. ## Get the List of Directories to Guard and Add them to CTE The directories (or folders) identified by the following SingleStore directories need to be secured on every node in the cluster: * data * tracelogs * plancache * auditlogs The nodes metadata file (usually called nodes.hcl) has to be secured on each host in the cluster. If you are using the default SingleStore installation paths, use the following default directories as Guard points to simplify the process: * `/var/lib/memsql` for RPM/Debian based `singlestoredb-server`/`memsql-server` installations * `$tar_install_dir/nodes` for tar based `singlestoredb-server`/`memsql-server` installations Otherwise, to find these directories, you can run `sdb-admin describe-node --all`. Here is part of the sample output of this command corresponding to the master node in the cluster example: ``` +--------------------+---------------------------------------------------------------------------------+ | memsqlId | D04E4F761C5651BAF9CE1922B3FC7C05E30CE037 | host | ec2-18-192-24-241.eu-central-1.compute.amazonaws.com | role | Master | port | 3306 | bindAddress | 0.0.0.0 | processState | Running | version | 7.1.11 | memsqlConfig | /home/memsql/memsql/nodes/d8d2b5f4-d046-4a6f-a0c1-9f6e0525fd16/memsql.cnf | datadir | /home/memsql/memsql/nodes/d8d2b5f4-d046-4a6f-a0c1-9f6e0525fd16/data | plancachedir | /home/memsql/memsql/nodes/d8d2b5f4-d046-4a6f-a0c1-9f6e0525fd16/plancache | tracelogsdir | /home/memsql/memsql/nodes/d8d2b5f4-d046-4a6f-a0c1-9f6e0525fd16/tracelogs | auditlogsdir | /home/memsql/memsql/nodes/d8d2b5f4-d046-4a6f-a0c1-9f6e0525fd16/auditlogs | memsqld | /home/memsql/memsql/memsql-server-7.1.11-6c108deb15/memsqld | nodeID | 1 | pid | 22072 | dpid | 22079 | autoRestartEnabled | true | isConnectable | true | recoveryState | Online | availabilityGroup | -- | config | Omitted from table view. Use 'sdb-admin describe-node ... --property config' | variables | Omitted from table view. Use 'sdb-admin describe-node ... --property variables' +--------------------+--------------------------------------------------------------------------------- ``` To encrypt data and enable access control using CTE, you will need to set up Guard points at the directories shown in the output above as the values of: * datadir * plancachedir * tracelogsdir * auditlogsdir You **also** need to guard the directory where `nodesMetadataFile` is located. You will need the path to memsqld (`/home/memsql/memsql/memsql-server-7.1.11-6c108deb15` in our case) in order to find the location of the nodes metadata file. To find this, run `/memsqlctl env`. Here is the sample output: ``` +-------------------+----------------------------------------+ | memsqlctlConfig | /home/memsql/memsql/memsqlctl.hcl | | defaultInstallDir | /home/memsql/memsql/nodes | | nodeMetadataFile | /home/memsql/memsql/nodes/nodes.hcl | | userInfo | uid=1000(memsql) gid=1000(memsql) | | user | memsql | +-------------------+----------------------------------------+ ``` You should see the directory where `nodesMetadataFile` is located. Add it to the list of the directories to protect. In default SingleStore installations, it is `/var/lib/memsql`. In our example, it is `$tar_install_dir/nodes`. The example cluster used `memsql-server-7.1.11.tar.gz` for cluster setup, with `tar_install_dir=/home/memsql/memsql`, so it would require a guard point for the directory `/home/memsql/memsql/nodes`. The same procedure would apply for any other directories that you have configured in a non-default way. You will have to identify the specific path for each of those and configure CTE to protect them individually. In addition to protecting the data, plancache, tracelogs, auditlogs, and nodes metadata, you may wish to secure the destination directory or directories for backups and `SELECT INTO OUTFILE` results. You can use the same set of policies to protect those directories. The location of those directories depends on your specific configuration. Now that you have the locations of the directories, you can proceed to configuring DSM to guard them. ## Configure Vormetric Data Security Manager (DSM) Perform the following steps on the DSM using the web GUI: 1. Generate the SingleStore Key for Encryption 2. Create a SingleStore User Set 3. Create Encryption and Decryption Data Transformation Policies 4. Create a SingleStore Process Set 5. Configure Host File Settings 6. Create a SingleStore Security Policy 7. Create Encryption Guard Points and Transform Data 8. Enable the SingleStore Security Policy Once you have completed these steps you can [verify the installation and configuration](https://docs.singlestore.com/db/v9.1/security/encryption/securing-data-at-rest-with-ciphertrust-transparent-encryption/protecting-folders-in-singlestore-db-using-cte.md). ## 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. ## Encryption Policy 1. Select **Policies > Manage Policies > Manage Policies**. 2. Click **Add**. 3. Choose the **Standard** policy type. 4. Enter the name: `SingleStore_encryption`. 5. Under **Security rules**, click **Add** to add a security rule. 6. Click **Select** next to **Action**, choose `key_op`. 7. Click **Select** next to **Effect**, choose **Permit, Apply key, Audit**. Click **Ok**. 8. Under **Key Selection Rules** section, click **Add**, then **Select**. 9. Choose `clear_key`, click **Select Key**. Click **Ok**. 10. Under **Data Transformation Rules**, click **Add**, then **Select**. 11. Choose `singlestore_key`, click **Select Key**. Click **Ok**. ## Decryption Policy 1. Select **Policies > Manage Policies > Manage Policies**. 2. Click **Add**. 3. Choose the **Standard** policy type. 4. Enter the name `SingleStore_decryption`. 5. Under **Security rules**, click **Add** to add a security rule. 6. Click **Select** next to **Action**, choose `key_op`. 7. Click **Select** next to **Effect**, choose **Permit, Apply key, Audit**. Click **Ok**. 8. Under **Key Selection Rules** section, click **Add**, then **Select**. 9. Choose `singlestore_key`, click **Select Key**. Click **Ok**. 10. Under **Data Transformation Rules**, click **Add**, then **Select**. 11. Choose `clear_key`, click **Select Key**. Click **Ok**. ## Create a SingleStore Process Set > **📝 Note**: This section specifies which processes access the data directories used by SingleStore nodes. If the operational model of the database changes, this list will need to be updated. 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](https://docs.singlestore.com/#UUID-52fd3c53-2ded-5707-5a49-21a95b62d5ed.md) 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: | Directory | Base Name | | ------------------------- | -------------- | | \ | `memsqlctl` | | \ | `memsqld` | | \ | `memsqld_safe` | | /bin/ | bash | | /bin/ | sh | | /bin/ | cat | | /bin/ | touch | For Debian/RPM based installations `singlestore-process-set` should be similar to the following configuration: | Directory | Base Name | | --------------------------- | ------------- | | /bin/ | bash | | /bin/ | sh | | /bin/ | touch | | /bin/ | chown | | /bin/ | chmod | | /bin/ | cat | | /bin/ | memsqlctl | | /usr/bin/ | bash | | /usr/bin/ | sudo | | /usr/bin/ | memsqlctl | | /opt/memsql-server-\* | memsqld | | /opt/memsql-server-\* | memsqld\_safe | | /opt/singlestoredb-server\* | memsqld | | /opt/singlestoredb-server\* | memsqld\_safe | 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: ``` |authenticator_euid|/memsqld |authenticator_euid|/memsqld_safe |authenticator_euid|/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 > **⚠️ Warning**: Before encrypting the data, all nodes must be stopped. To do this, run `sdb-admin stop-node --all --yes` on the Toolbox host (the local machine, in the example configuration). 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](https://docs.singlestore.com/db/v9.1/security/encryption/securing-data-at-rest-with-ciphertrust-transparent-encryption/protecting-folders-in-singlestore-db-using-cte.md). 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 ` (replacing `GUARD_DIR` with 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 ` * 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`. ## Verify the Installation In order to check that the process has been performed successfully: 1. On a SingleStore host, open one of the nodes’ config files: ```shell cat /home/memsql/memsql/nodes/d8d2b5f4-d046-4a6f-a0c1-9f6e0525fd16/memsql.cnf ``` You should see that the data is encrypted. 2. Run `sdb-admin describe-node --all`. This should show the same result as before the encryption. 3. Connect to the database and run some queries: create a database or table, insert some data, select some data. ## Configure Guard Points after SingleStore Upgrade When upgrading SingleStore (say, from version 7.11 to 7.12), you need to update your configuration. For SingleStore, if the tar installation is used, the `memsqlctl` path changes on upgrade, and therefore you need to update the `MEMSQL_BINARIES_PATH` directory to add the new binaries to the `singlestore-process-set`. > **📝 Note**: If you are using CTE 7.1+, you can use wildcards to specify the location of binaries. This feature allows you to specify `/opt/singlestoredb-server*` and `/opt/memsql-server-*` as location of binaries for Debian/RPM clusters and upgrade SingleStore without additional configuration. 1. Run `sdb-deploy install `. 2. Go to your `tar_install_dir` and find the newly installed `memsql-server` directory, e.g. `memsql-server-7.1.12-2sdt55frq5`. 3. In DSM, add three entries to `singlestore-process-set` with `MEMSQL_BINARIES_PATH` replaced by `/memsql-server-7.1.12-2sdt55frq5`. 4. Run `sdb-deploy upgrade `. 5. Delete entries corresponding to the old SingleStore version. ## Appendix A The following example explains how to prepare an AWS instance for use with CTE and Ubuntu. For other supported Linux distributions in AWS (including RHEL, CentOS, SLES, Amazon Linux) changing the kernel is not required before CTE installation. ## 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 sudo apt install linux-generic ``` 3. Find the `$menuentry_id_option` for the submenu: ```shell grep submenu /boot/grub/grub.cfg ``` SAMPLE OUTPUT: ``` 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 grep gnulinux /boot/grub/grub.cfg ``` SAMPLE OUTPUT: ``` 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 sudo update-grub ``` 7. Reboot the server; after reboot you should be running the generic kernel that you selected. Confirm by running: ```shell sudo uname -a ``` *** Modified at: August 4, 2025 Source: [/db/v9.1/security/encryption/securing-data-at-rest-with-ciphertrust-transparent-encryption/protecting-folders-in-singlestore-db-using-cte/](https://docs.singlestore.com/db/v9.1/security/encryption/securing-data-at-rest-with-ciphertrust-transparent-encryption/protecting-folders-in-singlestore-db-using-cte/) (An index of the documentation is available at /llms.txt)