Vertica and VorMetric Data Security Manager: A Technical Exploration

About this Document

This document presents the results of our investigation in response to a request from a mutual customer to verify that VorMetric Data Security Manager 6.0.x can do transparent (at rest) encryption of the base directory containing the data and catalog of Vertica 9.x. An exerpt from the customer's email request states that they are: "... using Vormetric for encryption at rest. DB is Vertica 9.0.1. The data filesystem /vertica/data is encrypted and the vormetric version is 6.0.2."

We have verified that VorMetric Data Security Manager 6.0.x can do transparent (at rest) encryption of the base directory containing the data and catalog in Vertica 9.x. This verification, however, does not indicate official support or certification of Vertica with VorMetric. It simply presents the results of a set of high-level tests that found no major issues with security or performance when VorMetric applies a guard to the base directory containing Vertica's data and catalog. Much more comprehensive testing would be needed before official certification or support could be established. Currently neither Micro Focus Vertica nor VorMetric have plans to pursue that type of testing.

Test Environment

• VorMetric Data Security Manager (hereafter referred to as DSM).
• VM image provided by VorMetric and running under vSphere Client 5.5.0. The Image is CentOS 6 running DSM 6.0.3.8098.
• DSM installed and configured per the VDS_6.0.3_DSM_InstallConfig_Guide_v1.pdf, section DSM Virtual Appliance Setup.
• Vertica server and VorMetric VTE Agent.
• VM running CentOS 7.3.
• Vertica Server 9.0.0-0, single node cluster, VMart example database.
• Vormetric VTE (download vee-fs-6.0.3-68-rh7-x86_64.bin).
• VTE installed and configured per the Guide_Install_&_Configuration_VTE_Agent_v6.0.3_Doc_v1.pdf, sections Installing VTE for Linux and VTE for Linux Utilities.
• VTE was registered with DSM using the Shared Secret method.
• The host was registered by the VTE install (it was not predefined in DSM).

DSM Guard FS Configuration

In the DSM web client:

• Created a "Vertica" administrator of type All.
• Created a "Vertica" domain and made the Vertica admin the domain administrator.
• Logged in as Vertica admin and switched domains to the Vertica domain.
• Created data transformation key "Test".

1. Test

General tab:

• Algorithm: AES256 (default)
• Key Type: Cached on Host

Attributes tab:

• x-key-state / ACTIVE
• x-key-actions / PROTECT_AND_PROCESS
• UUID / value filled in by tool

Created Initial and Operational policies:

1. Initial_Policy:

   • Policy Type "Standard"
   • Security Rules: Action "key_op" (which enables data transformation), Effect "Permit, Apply Key"
   • Key Selection Rules: Key "Clear_Key"
   • Data Transformation Rules: Key "Test"

2. vmartlockdown

   • Policy Type "Standard"

   • Security Rules:

     • Order 1: Action "f_rd_att, f_rd_sec, d_rd, d_rd_sec, d_rd_att", Effect "Permit"
     • Order 2: User "dbadmins" (created user set with dbamin and uidbadmin users), Action "all_ops", Effect "Permit, Apply Key"
     • Order 3: Action "all_ops", Effect "Audit, Deny" (safety net for anyhting not meeting 1 or 2)

   • Key Selection Rules:

     • Order 1: Resource "verticadatamart" (added with path /vertica/data/VMart/ sub folders), Key "clear_key"
     • Order 2: Key "Test"

Start Guard FS/Encryption

For our tests we used Option 1, Encrypt in place. This used the least amount of space and was less likely to break the database, since Vertica does not have separation of compute and storage (except in EON mode in the cloud).

1. Enable the Initial policy in DSM web client, refresh until status shows green.

2. Run the dataxform utility:

   - /opt/vormetric/DataSecurityExpert/agent/vmd/bin/dataxform --rekey --gp "/vertica/data/VMart/"

3. Disable the Initial policy, refresh until status shows red.
4. Enable the Operational policy, refresh until status shows green.

Tests Run

Access

• Verified that no users other than dbadmin and uiadmin had OS level access to the file content under the guarded directory.
• Verified that new ROS files are guarded.
• Verified that su to dbadmin from root did not have OS level access.
• Verified that Management Console, which runs under uidbadmin, was able to access the database and show normal monitoring results.
• Verified that third-party tools (SQuirreL, Metabase), connecting via JDBC using a Vertica-defined non-admin user, can access and see system and user tables.
• Verified that files in full backup by dbadmin vbr.py Objects directory tree are not guarded or encrypted.
• Verified that copy by dbadmin of a file from a guarded directory to /tmp results in the /tmp copy being not guarded or encrypted.
• Verified that a file under the VMart directory tree was encrypted (turned off guard and did head comparison of the database file to the file copied to /tmp).

Timing

• Loads: No significant adverse impact on doing the load of the standard VMart example. The 5m row tables took a few seconds longer, but all other tables were same amount of time. This was in line with the results from the Vertica version 8.0.0 tests run a couple of years ago.
• Queries: No adverse impact when running vmart_queries1.sql (a batch of 8 individual queries) or vmart_queries2.sql (a single join query).
• Backups: No adverse impact on doing a vbr.py full backup of the database to an unguarded location.

Upgrade

1. Shut down Vertica 9.0.0-1 database/Management console/MC agents, left DMS encryption and guard fs in place.
2. Ran the standard Vertica upgrade process with root user to upgrade to Vertica 9.1.0.
3. Restarted the Vertica database and Management console deamon.
4. Verified using vsql that database was in tact and in fact upgraded.
5. Verified that Management Console could still access the database and monitor properly.

Ran the access and timing tests outlined above with no significant differences noted.

Additional

Verified that in 9.1 I could drop and import the database using Management Console.