Fully-Encrypted Database

Encryption Model

A fully-encrypted database uses a multi-level encryption model. The encryption model involves three objects: data, column key, and master key, which are described as follows:

• Data:
  1. Data contained in the SQL syntax, for example, 'data' contained in the INSERT... VALUES ('data') syntax.
  2. Query result returned from the database server, for example, the query result returned after the SELECT syntax is executed.
  

  An encrypted database driver encrypts data in encrypted columns within the SQL syntax and decrypts the corresponding query results returned by the database server.

• Column key: Data is encrypted using column keys. The column keys are generated by the database driver or manually imported by users. The ciphertext of the column keys is stored on the database server.
• Master key: Column keys are encrypted using the master key. The master key is generated and stored by an external key manager. The database driver automatically accesses the external key manager to encrypt and decrypt column keys. Currently, Huawei KMS and user_token can be used for key management.
  Figure 1 Fully-encrypted database encryption model
  

Key Management

Currently, Huawei KMS and user_token can be used for key management in fully-encrypted databases.

• Huawei KMS: an online key management service provided by Huawei Cloud. It provides functions such as creating, deleting, querying, and backing up keys, and supports online data encryption and decryption using keys.
• user_token: The key is derived from the password provided by users on the client.

Database Driver

Currently, fully-encrypted databases support the gsql, JDBC, and Go drivers.

Memory Encryption/Decryption Mode

Memory encryption/decryption mode is based on encrypted equality query. The key is passed to the database memory to decrypt data, implementing the special calculation or query function (including range query and sorting) on the ciphertext columns. Automatic encryption and decryption are performed when other ciphertext operations and type conversion (implicit or explicit) are involved.

Example: Using gsql for an Encrypted Database

Before running SQL statements, ensure that the master key has been generated and the parameters for accessing the master key are clear.

This example uses a complete execution process as an example to describe how to use the encrypted database syntax, covering three phases: DDL statement execution, DML statement execution, and cleanup.

1. Connect to a database and use the -C parameter to enable full encryption.

   gsql -p PORT -d DATABASE -h HOST -U USER -W PASSWORD -r -C

2. Use meta-commands to set parameters for accessing the master key.

   Note: There must be no line feed or space in the string starting from keyType. Otherwise, the gsql tool cannot identify the entire parameter.

   Huawei Cloud supports two authentication modes. The number of parameters and parameter types required by the two authentication modes are different. You can select either of them.

   • Authentication mode 1: AK/SK authentication

     gaussdb=# \key_info keyType=huawei_kms,kmsProjectId={Project ID},ak={AK},sk={SK}

     Parameters: For details about how to obtain parameters, including the project ID, AK, and SK, see the master key generation phase.

     Example: \key_info keyType=huawei_kms,kmsProjectId=0b59929e8100268a2f22c01429802728,ak=XMAUMJY******DFWLQW,sk=ga6rO8lx1Q4uB*********2gf80muIzUX

   • Authentication mode 2: Account and password authentication

     gaussdb=# \key_info keyType=huawei_kms,iamUrl={IAM server address},iamUser={IAM username},iamPassword={IAM user password},iamDomain={Account name},kmsProject={Project}

     Parameters: For details about how to obtain related parameters, including the IAM server address, IAM username, IAM user password, account name, and project, see the master key generation phase.

     Example: \key_info keyType=huawei_kms,iamUrl=https://iam.example.com/v3/auth/tokens,iamUser=test,iamPassword=*********,iamDomain=test_account,kmsProject=xxx

3. Define a master key.
   In the master key generation phase, the KMS has generated and stored the master key. Running this syntax only stores the master key information in the database for future access. For details about the syntax format, see CREATE CLIENT MASTER KEY.

   gaussdb=# CREATE CLIENT MASTER KEY cmk1 WITH (KEY_STORE = huawei_kms, KEY_PATH = '{KMS server address}/{Key ID}', ALGORITHM = AES_256);
   CREATE CLIENT MASTER KEY

   • Parameters: For details about how to obtain related parameters, including KMS server address and key ID, see the master key generation phase.

     Example of KEY_PATH: https://kms.cn-north-4.myhuaweicloud.com/v1.0/0b59929e8100268a2f22c01429802728/kms/9a262917-8b31-41af-a1e0-a53235f32de9

4. Define a column key.
   The column key is encrypted by the master key defined in the previous step. For details about the syntax, see CREATE COLUMN ENCRYPTION KEY.

   gaussdb=# CREATE COLUMN ENCRYPTION KEY cek1 WITH VALUES (CLIENT_MASTER_KEY = cmk1, ALGORITHM = AES_256_GCM);

5. Define an encrypted table.
   In this example, the name and credit_card columns in the table are specified as encrypted columns by using syntax.

   gaussdb=# CREATE TABLE creditcard_info (
     id_number int,
     name text encrypted with (column_encryption_key = cek1, encryption_type = DETERMINISTIC),
     credit_card varchar(19) encrypted with (column_encryption_key = cek1, encryption_type = DETERMINISTIC));
   CREATE TABLE

6. Perform other operations on the encrypted table.

   -- Write data to the encrypted table.
   gaussdb=# INSERT INTO creditcard_info VALUES (1,'joe','6217986500001288393');
   INSERT 0 1
   gaussdb=# INSERT INTO creditcard_info VALUES (2, 'joy','6219985678349800033');
   INSERT 0 1
   
   -- Query data from the encrypted table.
   gaussdb=# select * from creditcard_info where name = 'joe';
    id_number | name |     credit_card
   -----------+------+---------------------
            1 | joe  | 6217986500001288393
   
   -- Update data in the encrypted table.
   gaussdb=# update creditcard_info set credit_card = '80000000011111111' where name = 'joy';
   UPDATE 1
   
   -- Add an encrypted column to the table.
   gaussdb=# ALTER TABLE creditcard_info ADD COLUMN age int ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = cek1, ENCRYPTION_TYPE = DETERMINISTIC);
   ALTER TABLE
   
   -- Drop an encrypted column from the table.
   gaussdb=# ALTER TABLE creditcard_info DROP COLUMN age;
   ALTER TABLE
   
   -- Query master key information from the system catalog.
   gaussdb=# SELECT * FROM gs_client_global_keys;
    global_key_name | key_namespace | key_owner | key_acl |        create_date
   -----------------+---------------+-----------+---------+----------------------------
    cmk1            |          2200 |        10 |         | 2021-04-21 11:04:00.656617
   (1 rows)
   
   -- Query column key information from the system catalog.
   gaussdb=# SELECT column_key_name,column_key_distributed_id ,global_key_id,key_owner FROM gs_column_keys;
    column_key_name | column_key_distributed_id | global_key_id | key_owner
   -----------------+---------------------------+---------------+-----------
    cek1            |                 760411027 |         16392 |        10
   (1 rows)
   
   -- View meta information of a column in the table.
   gaussdb=# \d creditcard_info
           Table "public.creditcard_info"
      Column    |       Type        | Modifiers
   -------------+-------------------+------------
    id_number   | integer           |
    name        | text              |  encrypted
    credit_card | character varying |  encrypted

7. Perform cleanup.

   -- Drop the encrypted table.
   gaussdb=# DROP TABLE creditcard_info;
   DROP TABLE
   
   -- Drop the column key.
   gaussdb=# DROP COLUMN ENCRYPTION KEY cek1;
   DROP COLUMN ENCRYPTION KEY
   
   -- Drop the master key.
   gaussdb=# DROP CLIENT MASTER KEY cmk1;
   DROP CLIENT MASTER KEY