Using gsql to Operate an Encrypted Database

Executing SQL Statements

Before running the SQL statements in this section, ensure that the master key creation and configuration phases are complete.

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

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

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

2. 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 "SQL Reference > SQL Syntax > CREATE CLIENT MASTER KEY" in Developer Guide.

   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

3. Define a column key.
   The column key is encrypted by the master key defined in the previous step. For details about the syntax, see "SQL Reference > SQL Syntax > CREATE COLUMN ENCRYPTION KEY" in Developer Guide.

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

4. 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));
   NOTICE: The 'DISTRIBUTE BY' clause is not specified. Using 'id_number' as the distribution key by default.
   HINT: Please use 'DISTRIBUTE BY' clause to specify suitable data distribution key.
   CREATE TABLE

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

6. Enter the cleanup phase.

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