Fine-grained TDE

Precautions

When using fine-grained TDE, comply with the following constraints to ensure that services are running properly:

• Fine-grained TDE is supported only by clusters of version 9.1.1.200 or later. You need to contact technical support to set the GUC parameter tde_version to 2.
• It is supported only by row-store tables and column-store 2.0 tables.
• Row-store tables do not support column-level TDE. Only column-store tables support column-level TDE.
• Cluster-level TDE and fine-grained TDE are mutually exclusive and cannot be both enabled. If cluster-level TDE is enabled, fine-grained TDE cannot be enabled.
• During the upgrade observation period of a cluster, TDE tables cannot be created.
• TDE settings of indexes cannot be modified.
• Materialized views cannot be created based on encrypted tables, and materialized views cannot be encrypted.
• Table-level encryption does not work for certain types of tables like common column-store with delta tables, non-HStore Opt tables, hybrid row-column tables, or LSM tables.
• Temporary tables cannot be encrypted.
• To use fine-grained TDE, you must obtain keys from Key Management Service (KMS). If an error is displayed for a DWS cluster, contact technical support to rectify the KMS fault. Do not restart the DWS cluster or instance before the fault is rectified. Otherwise, the cluster will be unavailable.

Example

The employees table is used as an example to describe how to use fine-grained TDE.

1. Create the employees table as an initial unencrypted table.

   CREATE TABLE employees (
        emp_id int PRIMARY KEY,
        emp_name text,
        emp_email text,
        emp_phone text,
        salary decimal(10,2),
        hire_date date );

2. Create the employees_encrypted table (using the AES-CTR-128 algorithm) and migrate the data in the employees table to the employees_encrypted table.

   CREATE TABLE employees_encrypted (
        emp_id int PRIMARY KEY,
        emp_name text,
        emp_email text,
        emp_phone text,
        salary decimal(10,2),
        hire_date date ) 
   WITH (encrypt_option = table,encrypt_algo = AES_CTR_128);  
   INSERT INTO employees_encrypted SELECT * FROM employees; 

3. Create the salaries table that uses SM4 (an SM series cryptographic algorithm).

   CREATE TABLE salaries (
        emp_id int,
        salary decimal(10,2),
        bonus decimal(10,2),
        effective_date date ) 
   WITH (encrypt_option = table, encrypt_algo = SM4_CTR_128);

4. Create the column-level encryption table hr_analysis to encrypt only the column salary.

   CREATE TABLE hr_analysis (
        emp_id int,
        emp_name text,
        emp_department text,
        salary decimal(10,2) ENCRYPT, --Encrypt only the salary column.
        performance_score int ) 
   WITH (orientation = column, encrypt_option = column);

5. Change the column-level encryption of the hr_analysis table from specified columns to full columns.

   ALTER TABLE hr_analysis MODIFY (emp_id to encrypt);
   ALTER TABLE hr_analysis MODIFY (emp_name to encrypt);
   ALTER TABLE hr_analysis MODIFY (emp_department to encrypt);
   ALTER TABLE hr_analysis MODIFY (performance_scoreto encrypt);
   VACUUM FULL hr_analysis;

6. Decrypt the encrypted table.

   ALTER TABLE employees_encrypted SET (encrypt_option = off); 
   VACUUM FULL employees_encrypted; 

   Enable encryption for the initially unencrypted table.

   ALTER TABLE employees SET (encrypt_option = table); 
   VACUUM FULL employees;

7. Change the cryptographic algorithm of the table to SM4.

   ALTER TABLE employees SET (encrypt_algo = SM4_CTR_128); 
   VACUUM FULL employees; 
   ALTER TABLE hr_analysis SET (encrypt_algo = SM4_CTR_128); 
   VACUUM FULL hr_analysis;

8. Perform a key rotation every half a year according to the security policy.

   ALTER CLUSTER ENCRYPTION KEY ROTATION;