Hologres:Encrypt data in Hologres

Limits

• Only Hologres V1.1 and later support data encryption for storage. If the version of your Hologres instance is earlier than V1.1, manually upgrade your Hologres instance in the Hologres console or join a Hologres DingTalk group to apply for an instance upgrade. For more information about how to manually upgrade a Hologres instance, see Instance upgrades. For more information about how to join a Hologres DingTalk group, see Obtain online support for Hologres.

• If the version of your Hologres instance is earlier than V1.3.31, you must add backend configurations before you can use data encryption for storage. If the version of your Hologres instance is V1.3.31 or later, you do not need to add backend configurations. If you want to use data encryption for storage, we recommend that you upgrade your Hologres instance to V1.3.31 or later. For more information, see Instance upgrades.

• Your operations such as disabling and deleting your customer master keys (CMKs) in KMS may affect data encryption or decryption in Hologres. Hologres caches historical configurations. Your operations in KMS take effect in a delayed manner within 24 hours.

• You can encrypt data for storage only if data encryption for storage is enabled. Data in the tables that are created before this feature is enabled cannot be encrypted for storage.

• After the data encryption feature is enabled, you can still create a Hologres foreign table in MaxCompute for data reads and writes.

• After the data encryption feature is enabled, you can use the following SQL statement to disable data encryption for a database. The data of the tables that are created before this feature is disabled is still encrypted. Only data in the tables that are created after this feature is disabled cannot be encrypted for storage.

  ALTER DATABASE <database_name> set hg_experimental_encryption_options='';

• If the KMS keys of a table are disabled or invalid after the data encryption feature is enabled, data cannot be read from or written into the table.

• In Hologres V2.0 and later, you can configure a data encryption rule for each table. You can configure different KMS keys for different tables.

Data encryption mechanism

Procedure

1. Create a custom policy.

   1. Log on to the Resource Access Management (RAM) console. In the left-side navigation pane, choose Permissions > Policies. On the Policies page, click Create Policy.

      

   2. On the Create Policy page, click the JSON tab. On the JSON tab, enter the following script in the code editor and set Name to AliyunHologresEncryptionDefaultRolePolicy.

      

      {
          "Version": "1",
          "Statement": [
              {
                  "Action": [
                      "kms:Encrypt",
                      "kms:Decrypt",
                      "kms:GenerateDataKey",
                      "kms:DescribeKey"
                  ],
                  "Resource": "acs:kms:*:*:*/*",
                  "Effect": "Allow"
              }
          ]
      }

   3. Click OK. The custom policy is created.

2. Create a RAM role and grant permissions to the RAM role.

   1. Log on to the RAM console. In the left-side navigation pane, choose Identities > Roles.

   2. On the Roles page, click Create Role. In the Select Role Type step of the Create Role panel, select Alibaba Cloud Service for Select Trusted Entity.

   3. Click Next. In the Configure Role step, set Role Type to Normal Service Role, enter AliyunHologresEncryptionDefaultRole in the RAM Role Name field, select Hologres from the Select Trusted Service drop-down list, and then click OK.

   4. In the Finish step, click Add Permissions to RAM Role.

   5. In the Add Permissions panel, set Authorized Scope to Alibaba Cloud Account. In the Select Policy section, click the Custom Policy tab and enter AliyunHologresEncryptionDefaultRolePolicy that you created in Step 1.

   6. Click OK. The RAM role is created and permissions are granted to the RAM role.

      After the RAM role is created, click the name of the role. On the Trust Policy Management tab, check the trust policy settings.

3. Create a CMK.

   For more information, see Getting started with keys.

   Important

   Hologres supports only symmetric keys of the Aliyun_AES_256 type.

4. Enable data encryption for a database.

   1. Log on to the database for which you want to enable data encryption. For more information about how to log on to a database, see Log on to a database.

   2. On the Ad-hoc Query tab, select the instance to which the database belongs from the Instance drop-down list and select the database from the Database drop-down list. Enter the following statement in the SQL editor and click Run.

      This SQL statement is used to enable data encryption for the database.

      ALTER DATABASE <db_name> SET hg_experimental_encryption_options='<encryption_type>,<cmk_id>,<ram_role>,<uid>';

      The following table describes the parameters in the preceding syntax.

      Parameter	Description
      db_name	The name of the database for which you want to enable data encryption.
      encryption_type	The encryption algorithm that you want to use to encrypt data. Valid values: AES-256, AES-CTR, and RC4.
      cmk_id	The ID of the CMK. You can obtain the ID of the CMK on the CMK details page in the KMS console.
      ram_role	The RAM role that is assigned to Hologres.
      uid	The ID of the Alibaba Cloud account. For more information, see Account IDs.

   The following sample statements show you how to enable data encryption for a database, create a table in the database and write data to the table, and then query the data of the table in Hologres.

   1. Enable data encryption for a database.

      ALTER DATABASE hoxxxx set hg_experimental_encryption_options=
      'AES256,623c26ee-xxxx-xxxx-xxxx-91d323cc4855,AliyunHologresEncryptionDefaultRole,187xxxxxxxxxxxxx';

   2. Create a table in the database and write data to the table.

      DROP TABLE IF EXISTS a;
      
      CREATE TABLE a(id int);
      
      INSERT INTO a values(1);
      
      SELECT hg_admin_command('flush'); -- This statement is used for testing only. This statement allows query results to be immediately displayed.

   3. Query data.

      SELECT * FROM a;

      The following figure shows the query result. If you disable KMS and restart the Hologres instance, an error is reported when you query the data in Table a. If you do not restart the Hologres instance after KMS is disabled, you can query the data in Table a within 24 hours. After 24 hours, an error is reported when you query the data in Table a.

Configure data encryption for a table

In Hologres V2.0 and later, you can configure data encryption rules for a table after you enable data encryption for the database where the table resides. This way, you can use different KMS keys to encrypt data in different tables.

Configure data encryption rules for a table

• Usage notes

  • If no data encryption rule is configured for a table, the system uses the data encryption rule specified by hg_experimental_encryption_options to encrypt data in the table.

  • You can configure data encryption rules for a table only when you create the table.

• Syntax

  CALL SET_TABLE_PROPERTY('<table_name>', 'encryption_options', '<encryption_type>,<cmk_id>,<ram_role>,<uid>');

  The following table describes the parameters in the preceding syntax.

  Parameter	Description
  table_name	The name of the table for which you want to configure data encryption rules.
  encryption_type	The encryption algorithm that you want to use to encrypt data. Valid values: AES-256, AES-CTR, and RC4.
  cmk_id	The ID of the CMK. You can obtain the ID of the CMK on the CMK details page in the KMS console.
  ram_role	The RAM role that is assigned to Hologres.
  uid	The ID of the Alibaba Cloud account. For more information, see Account IDs.

• Example

  Create a table named lineitem and configure the AES-256 encryption algorithm and the CMK 623c26ee-xxxx-xxxx-xxxx-91d323cc4855 for the table.

  BEGIN;
  CREATE TABLE LINEITEM
  (
      L_ORDERKEY      BIGINT      NOT NULL,
      L_PARTKEY       INT         NOT NULL,
      L_SUPPKEY       INT         NOT NULL,
      L_LINENUMBER    INT         NOT NULL,
      L_QUANTITY      DECIMAL(15,2) NOT NULL,
      L_EXTENDEDPRICE DECIMAL(15,2) NOT NULL,
      L_DISCOUNT      DECIMAL(15,2) NOT NULL,
      L_TAX           DECIMAL(15,2) NOT NULL,
      L_RETURNFLAG    TEXT        NOT NULL,
      L_LINESTATUS    TEXT        NOT NULL,
      L_SHIPDATE      TIMESTAMPTZ NOT NULL,
      L_COMMITDATE    TIMESTAMPTZ NOT NULL,
      L_RECEIPTDATE   TIMESTAMPTZ NOT NULL,
      L_SHIPINSTRUCT  TEXT        NOT NULL,
      L_SHIPMODE      TEXT        NOT NULL,
      L_COMMENT       TEXT        NOT NULL,
      PRIMARY KEY (L_ORDERKEY,L_LINENUMBER)
  );
  CALL SET_TABLE_PROPERTY('LINEITEM', 'encryption_options', 'AES256,623c26ee-xxxx-xxxx-xxxx-91d323cc4855,AliyunHologresEncryptionDefaultRole,153xxxxxxxxxxxxx');
  COMMIT;

Query data encryption rules

SELECT
    *
FROM
    hologres.hg_table_properties
WHERE
    property_key = 'encryption_options';