Using gsql to Operate an Encrypted Database
Executing SQL Statements
Before running the SQL statements in this section, ensure that the master key has been generated and the parameters for accessing the master key are clear.
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.
- 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
- Set parameters for accessing the master key using a meta-command.
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
- Authentication mode 1: AK/SK authentication
- 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
- 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);
- 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 column by default. HINT: Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column. CREATE TABLE
- 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 -- Delete 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 row) -- 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 row) -- 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
- Enter the cleanup phase.
-- Delete the encrypted table. gaussdb=# DROP TABLE creditcard_info; DROP TABLE -- Delete the column key. gaussdb=# DROP COLUMN ENCRYPTION KEY cek1; DROP COLUMN ENCRYPTION KEY -- Delete the master key. gaussdb=# DROP CLIENT MASTER KEY cmk1; DROP CLIENT MASTER KEY
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot