Back
Outdated Version

You are viewing an older version of this section. View current production version.

Securing Data at Rest with Vormetric Transparent Encryption

This topic describes how to secure data on persistent storage (data at rest) in SingleStore DB with Vormetric Transparent Encryption (VTE), from Thales. This configuration allows you to protect all SingleStore DB information, including data files, backups, and logs from unauthorized access, including by unauthorized administrative users. The process is also known as Transparent Database Encryption or TDE.

VTE encrypts all protected SingleStore DB data with strong encryption. If the encrypted data is obtained in any way by someone without keys to access it, it will be useless. Even the root user on the Linux system running SingleStore DB can be prevented from accessing the information directly via the files where it is stored, even if they impersonate a user with access by using sudo. Keys can also be revoked to render data inaccessible.

Getting Started

The certification matrix below shows the latest versions tested for SingleStore DB and VTE:

Certification Matrix Versions
VTE 6.3.1
SingleStore DB 7.1.x, 7.3.x or newer
Certified OS RHEL/CentOS 6 or 7 (version 7 is preferred), Debian 8 or 9 (version 9 is preferred)

Note: This document assumes you already have a working SingleStore DB cluster.

Before you begin configuring SingleStore DB to work with VTE, ensure you have the Vormetric Data Security Manager (DSM) installed and configured. Refer to the DSM Installation and Configuration Guide available on the Thales Support Portal.

When using VTE, you must use a kernel supported by Thales. Contact your Thales representative for a list of supported kernel versions. The VTE/OS compatibility matrix is available to Thales customers via their web portal.

Warning

The default AWS Linux kernel for Ubuntu is not supported with VTE. See Appendix A for instructions on how to prepare an Ubuntu AWS instance.

Notes on Product Naming

From release 7.0.0 and onward, the VTE (Vormetric Transparent Encryption) Agent is rebranded to CTE (CipherTrust Transparent Encryption). For the purposes of this document, the terms are interchangeable.

The Thales DSM’s CTE counterpart is the CipherTrust Manager (CTM). The documentation for CTM deployment is publicly available: https://www.thalesdocs.com/ctp/cm/2.0/deployment/index.html

CTE and VTE agents can be registered to either DSM or CTM; however, the steps outlined below are specific to the DSM.

Process Overview

The process of protecting your data with VTE has two main components: installing and configuring VTE and your SingleStore DB cluster, and configuring which files are protected. Accordingly, this document is split into the following major sections:

Setting up VTE Components and a SingleStore DB Cluster

This document describes how to configure VTE components on a SingleStore DB cluster, via an example. You will need to substitute versions, file path names, IP address, domain names, and so on, as appropriate for your installation. Ubuntu 16.04 and SingleStore DB (formerly MemSQL) 7.1.11 are used in this example, but similar configurations using other supported Linux distributions and SingleStore DB versions work. The example here uses AWS; if you’re hosting the software in your own data center or on another cloud platform, you’ll have to adjust the steps appropriately for your environment.

Example SingleStore DB Cluster Configuration

Here’s an example SingleStore DB cluster configuration file (cluster_single_host.yaml). This file defines the configuration used in later examples in this document. The values enclosed in angle brackets (<>) and tar_install_dir and memsql_server_file_path may be different in your configuration:

```
license: <INSERT_LICENSE_HERE>
hosts:
- hostname: <YOUR_HOST_NAME>
  localhost: false
  ssh:
    host: <YOUR_HOST_NAME>
    user: memsql
    private_key: <PATH_TO_SSH_PRIVATE_KEY>
  tar_install_dir: /home/memsql/memsql
  nodes:
  - role: Master
    config:
      port: 3306
      bind_address: 0.0.0.0
  - role: Aggregator
    config:
      port: 3307
      bind_address: 0.0.0.0
  - role: Leaf
    config:
      port: 3308
      bind_address: 0.0.0.0
  - role: Leaf
    config:
      port: 3309
      bind_address: 0.0.0.0
memsql_server_file_path: /shared/builds/memsql-server-7.1.11.tar.gz
root_password: <INSERT_PASSWORD_HERE>
```

Run sdb-deploy setup-cluster --cluster-file cluster_single_host.yaml

Preparing DSM

Before installing the VTE agent, configure the host in the DSM Admin as follows. This is necessary so you can register it during the VTE installation.

  1. Log in to the DSM Web GUI.

  2. Create a domain called SingleStoreDomain.

  3. Log in to the DSM Web GUI with the user assigned to the SingleStoreDomain.

  4. Once logged in, go to Domains -> Switch Domains.

  5. Select SingleStoreDomain and click Switch to domain. Once you switch domains, you will see the new menu options: Hosts, Keys.

  6. Select the Hosts menu option and click Add to add a new host.

  7. If you are using cloud based hosts, use the local IP address when adding the host since that remains static on reboot. This way you will not lose your VTE host configured guard points (they will be added after the agent installation). Select FS for fingerprint key exchange. When you click Ok, the host should appear in the list of hosts.

You should now be able to install the VTE 6.3.1.74 agent.

Installing VTE Agent Software

  1. Copy the binary provided by Vormetric to the instance configured at the previous step. It is named something like: vee-fs-6.3.1-74-ubuntu18-x86_64.bin

  2. SSH to the instance and go to the folder where the vee-fs installation binary is saved.

  3. Run the following commands

    sudo su
chmod 775 ./vee-fs-6.3.1-74-ubuntu18-x86_64.bin
./vee-fs-6.3.1-74-ubuntu18-x86_64.bin

  4. You will be presented with several prompts. For the following prompts, use these answers (answer other prompts as you want):

    • Please enter the primary Security Server host name: DSM public DNS, e.g. ec2-3-123-39-192.eu-central-1.compute.amazonaws.com. It must be the same as the “Server name” shown on the DSM dashboard.
    • Please enter the host name of this machine, or select from the following list: Select 2, i.e. local IP
    • Would you like to register to the Security Server using a registration shared secret (S) or using fingerprints (F)? (S/F) [S]: Answer F
    • It is possible to associate this installation with the hardware of this machine: Answer N
    • Do you want this host to have LDT support enabled on the server? Answer N
    • Do the fingerprints match? (You should check the fingerprint at the Dashboard of the primary DSM and then answer Y)

Now you should have your agent installed and ready to use.

Some troubleshooting tips:

If you were not able to successfully register the host, you can run /opt/vormetric/DataSecurityExpert/agent/vmd/bin/register_host after you fix the issues, e.g. add the host to the DSM.

If you need to reinstall the VTE agent, you can run dpkg -r vee-fs, and then repeat the procedure from the beginning.

If DSM cannot connect to the host after the registration, you can go the Hosts -> Hosts page, select the current host, disable the Registration Allowed and Communication Enabled checkboxes, and then click Apply. Then re-check these checkboxes and click Apply again.

Protecting Folders in SingleStore DB Using VTE

Now that you have the environment configured, you can start protecting your data.

Getting the List of Folders to Guard

The folders identified by the following SingleStore DB folders 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.

To find these folders, 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 VTE, you will need to set up Guard points at the folders 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 <PATH TO memsqld>/memsqlctl env. Here is the sample output:

+-------------------+----------------------------------------+
| memsqlctlConfig   | /home/memsql/pm_memsql/memsqlctl.hcl   |
| defaultInstallDir | /home/memsql/pm_memsql/nodes           |
| nodeMetadataFile  | /home/memsql/pm_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 folders to protect.

In default SingleStore DB installations, it is /var/lib/memsql. In our example, it is $tar_install_dir/nodes.

If you are using the default SingleStore DB installation paths, use the following default folders as Guard points to simplify the process:

  • /var/lib/memsql for RPM/DEB based memsql-server installations
  • $tar_install_dir/nodes for tar based memsql-server installations

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 folder /home/memsql/memsql/nodes. The same procedure would apply for any other folders that you have configured in a non-default way. You will have to identify the specific path for each of those and configure VTE to protect them individually.

In addition to protecting the data, plancache, tracelogs, auditlogs, and nodes metadata, you may wish to secure the destination folder or folders for backups and SELECT INTO OUTFILE results. You can use the same set of policies to protect those folders. The location of those folders depends on your specific configuration.

Now that you have the locations of the folders, you can proceed to configuring DSM to guard them.

Configuring 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.

Generating 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.

Creating 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 DB nodes on the host (memsql in most cases).

Creating 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 VTE.

Encryption Policy

  1. Select Policies -> Manage Policies -> Manage Policies.

  2. Click Add.

  3. Choose policy type: “Standard”.

  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 policy type: “Standard”.

  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.

Creating a SingleStore Process Set

Note: This section specifies which processes access the data folders used by SingleStore DB 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 above memsqld is located at /home/memsql/memsql/memsql-server-7.1.11-6c108deb15/memsqld. The same folder 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
<MEMSQL_BINARIES_PATH> memsqlctl
<MEMSQL_BINARIES_PATH> memsqld
<MEMSQL_BINARIES_PATH> memsqld_safe
/bin/ bash
/bin/ sh

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

Important Note About Upgrading SingleStore DB: When upgrading SingleStore DB (say, from version 7.11 to 7.12, for example), please note that you will need to upgrade your configuration. In the table above, the folder memsql-server-7.1.11-6c108deb15 will need to be updated whenever SingleStore DB is updated. For SingleStore DB, if the tar installation is used, the memsqlctl path changes on upgrade, and therefore new binaries need to be added to the singlestore-process-set.

Upgrading SingleStore DB

  1. Run sdb-deploy install .

  2. Go to your tar_install_dir and find the newly installed memsql-server folder, e.g. memsql-server-7.1.12-2sdt55frq5

  3. In DSM, add three entries to singlestore-process-set with MEMSQL_BINARIES_PATH replaced by <tar_install_dir>/memsql-server-7.1.12-2sdt55frq5

  4. Run sdb-deploy upgrade <desired version>.

  5. Delete entries corresponding to the old SingleStore DB version.

Configuring 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|<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.

Creating 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 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 DB 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.

Creating 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.

  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 VTE 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” folders (if any) that you wish to protect for your SingleStore DB environment.

Troubleshooting Tips

  • If you need to re-run dataxform for the same directory more than once, you may get the error message “Automatic data transform status for /home/memsql/memsql/nodes: previous attempt completed”. 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

Enabling the SingleStore Security Policy

  1. Select Hosts -> Hosts, click on your VTE 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 DB processes and data are protected.

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

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

    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.

Appendix A

Even though Ubuntu is not officially supported, we have included an example of how to prepare an AWS instance for use with VTE and Ubuntu. For other supported Linux distributions in AWS (including RHEL, CentOS, SLES, Amazon Linux) changing the kernel is not required before VTE installation.

Preparing 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 VTE agent software.

  2. Install the generic Ubuntu kernels:

    $ sudo apt install linux-generic

  3. Find the $menuentry_id_option for the submenu:

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

    $ 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' {

  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:

    $ sudo update-grub

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

    $ sudo uname -a

Questions?

Ask the Community, find solutions in the Troubleshooting Overview, get Self-Paced Training or learn more about our Support Plans.