Use of fully encrypted database
Tags: opengauss
Published: 2023-03-29 04:02:47

The fully encrypted database is intended to solve the problem of privacy protection throughout the data life cycle, so that no matter what business scenarios and environments the system is in, the data is always in the state of ciphertext in all aspects of transmission, calculation and storage. After the data owner completes the data encryption on the client and sends it to the server, the attacker still cannot obtain effective value information when the attacker steals user data through system vulnerabilities, thereby protecting data privacy.

Since the entire business data flow exists in the form of ciphertext during data processing, through the fully encrypted database, it can be realized:

Protect the privacy and security of data throughout the life cycle on the cloud. No matter what state the data is in, the attacker cannot obtain valid information from the database server.
Help cloud service providers gain third-party trust. Whether it is a business administrator, operation and maintenance administrator in the enterprise service scenario, or an application developer in the consumer cloud business, users hold the key in their own hands, so that high-privileged users cannot obtain valid data information.
Allow cloud database services to better comply with laws and regulations on personal privacy protection with the help of full confidentiality capabilities.
The fully encrypted database currently supports two connection methods: gsql connection and jdbc connection. The process of using the database under the two connection modes will be introduced in detail below.

Connect to the fully confidential database
GSQL connects to the database and executes the following command to enable the secret state switch:

gsql -p PORT -d postgres -r -C
Parameter Description:

-p: port number
-d: database name
-C: It is to open the secret state switch.
JDBC supports operations related to confidential databases, and you need to set enable_ce=1

Create user key
The fully encrypted database has two kinds of keys, namely the client master key CMK and the data encryption key CEK. CMK is used to encrypt CEK, and CEK is used to encrypt user data.

The order and dependencies of key creation are: Create CMK > Create CEK.

Create CMK and CEK in GSQL environment:

【Create CMK】

CREATE CLIENT MASTER KEY client_master_key_name WITH （KEY_STORE =key_store_name, KEY_PATH = "key_path_value", ALGORITHM = algorithm_type）;
Parameter Description:

client_master_key_name

This parameter is used as the key object name, and must satisfy the naming uniqueness constraint under the same namespace.

Value range: string, which must conform to the naming convention of identifiers.

KEY_STORE

Specifies the key tool or component that manages the CMK; value: currently only localkms is supported.

KEY_PATH

KEY_STORE is responsible for managing multiple CMK keys, and the KEY_PATH option is used to uniquely identify the CMK in KEY_STORE. The value is similar: "key_path_value".

ALGORITHM

The COLUMN ENCRYPTION KEY created by this syntax is used to specify the type of encryption algorithm. Value range: RSA_2048, RSA3072 and SM2.

Note:  Key storage path: By default, localkms will generate/read/delete key files under the $LOCALKMS_FILE_PATH path, and users can manually configure this environment variable. However, users do not need to configure this environment variable separately. When trying to obtain $LOCALKMS_FILE_PATH fails, localkms will try to obtain the $GAUSSHOME/etc/localkms/ path. If the path exists, it will be used as the key storage path. Key-related file names: When using the CREATE CMK syntax, localkms will create four files related to storing keys. Example: When KEY_PATH = “key_path_value”, the names of the four files are key_path_value.pub, key_path_value.pub.rand, key_path_value.priv, key_path_value.priv.rand respectively. Therefore, in order to successfully create key-related files, it should be ensured that there is no existing file with the same name as the key-related file under the key storage path.

【Create CEK】

CREATE COLUMN ENCRYPTION KEY column_encryption_key_name WITH(CLIENT_MASTER_KEY = client_master_key_name, ALGORITHM = algorithm_type, ENCRYPTED_VALUE = encrypted_value);
Parameter Description:

column_encryption_key_name

This parameter is used as the key object name, and must satisfy the naming uniqueness constraint under the same namespace.

Value range: string, which must conform to the naming convention of identifiers.

CLIENT_MASTER_KEY

Specifies the CMK used to encrypt this CEK.

The value is: CMK object name, the CMK object is created by CREATE CLIENT MASTER KEY syntax.

ALGORITHM

Specifies which encryption algorithm the CEK will be used for.

The value range is: AEAD_AES_256_CBC_HMAC_SHA256, AEAD_AES_128_CBC_HMAC_SHA256 and SM4_SM3;

ENCRYPTED_VALUE (optional)

This value is the key password specified by the user, and the length of the key password ranges from 28 to 256 characters. The key security strength derived from 28 characters meets AES128. If the user needs to use AES256, the length of the key password needs to be 39 characters. If not specified, a 256-bit key will be automatically generated.

Note:  Since algorithms such as SM2, SM3, and SM4 belong to the Chinese National Cryptography Standard Algorithm, they need to be used together to avoid legal risks. If you specify the SM4 algorithm to encrypt the CEK when creating the CMK, you must specify the SM4_SM3 algorithm to encrypt data when creating the CEK.

【Example: In the GSQL environment:】

-- 创建$GAUSSHOME/etc/localkms/路径
mkdir -p $GAUSSHOME/etc/localkms/
-- 使用特权账户，创建一个普通用户alice。
 openGauss=# CREATE USER alice PASSWORD '********'; 
-- 使用普通用户alice的账户，连接密态数据库，并执行本语法。
 gsql -p 57101 postgres -U alice -r -C 
-- 创建客户端加密主密钥（CMK）对象。
 openGauss=> CREATE CLIENT MASTER KEY alice_cmk WITH (KEY_STORE = localkms , KEY_PATH = "key_path_value”, ALGORITHM = RSA_2048); 
-- 创建客户端列加密密钥（CEK）对象。
 openGauss=> CREATE COLUMN ENCRYPTION KEY ImgCEK WITH VALUES (CLIENT_MASTER_KEY = alice_cmk, ALGORITHM  = AEAD_AES_256_CBC_HMAC_SHA256); 
Create CMK and CEK in JDBC environment:

// 创建客户端主密钥
 Connection conn = DriverManager.getConnection("url","user","password");Statement stmt = conn.createStatement();int rc = stmt.executeUpdate("CREATE CLIENT MASTER KEY ImgCMK1 WITH ( KEY_STORE = localkms , KEY_PATH = "key_path_value" , ALGORITHM = AES_256_CBC);"); 

 // 创建列加密密钥
 int rc2 = stmt.executeUpdate("CREATE COLUMN ENCRYPTION KEY ImgCEK1 WITH VALUES (CLIENT_MASTER_KEY = ImgCMK1, ALGORITHM  = AEAD_AES_256_CBC_HMAC_SHA256);");
Create encrypted table
After the client master key CMK and data encryption key CEK are created, the CEK can be used to create an encryption table. The creation of encrypted tables supports random encryption and deterministic encryption of encrypted columns.

Create an encrypted table under the GSQL connection environment:

【Example】

openGauss=# CREATE TABLE creditcard_info (id_number int, name text encrypted with (column_encryption_key = ImgCEK, encryption_type = DETERMINISTIC),credit_card  varchar(19) encrypted with (column_encryption_key = ImgCEK, encryption_type = DETERMINISTIC));
Parameter Description

ENCRYPTION_TYPE is the encryption type in the ENCRYPTED WITH constraint, and the value of encryption_type_value is [ DETERMINISTIC | RANDOMIZED ].

Create an encrypted table in the JDBC environment:

int rc3 = stmt.executeUpdate("CREATE TABLE creditcard_info (id_number    int, name  varchar(50) encrypted with (column_encryption_key = ImgCEK, encryption_type = DETERMINISTIC),credit_card  varchar(19) encrypted with (column_encryption_key = ImgCEK1, encryption_type = DETERMINISTIC));");
Insert data into the encrypted table and query
After the encrypted table is created, you can insert data into the encrypted table and view the data in the encrypted database mode (connection parameter -C). When using the normal environment (turn off the connection parameter -C), the encrypted table cannot be operated, and only the ciphertext data can be seen when viewing the encrypted table.

Insert data into the encrypted table in the GSQL environment and view:

openGauss=# INSERT INTO creditcard_info VALUES (1,'joe','6217986500001288393'); 
 INSERT 0 1 
 openGauss=# INSERT INTO creditcard_info VALUES (2, 'joy','6219985678349800033'); 
 INSERT 0 1 
 openGauss=# select * from creditcard_info where name = 'joe'; 
  id_number | name |     credit_card 
 -----------+------+--------------------- 
          1 | joe  | 6217986500001288393 
 (1 row)
Explanation:  When using a non-confidential client to view the encrypted table data, it is ciphertext.

openGauss=# select id_number,name from creditcard_info; 
  id_number |                name 
 -----------+------------------------------------------- 
          1 | \x011aefabd754ded0a536a96664790622487c4d36 
          2 | \x011aefabd76853108eb406c0f90e7c773b71648f 
 (2 rows)
Insert data into the encrypted table in the JDBC environment and view:

// 插入数据
 int rc4 = stmt.executeUpdate("INSERT INTO creditcard_info VALUES (1,'joe','6217986500001288393');"); 
 // 查询加密表
 ResultSet rs = null; 
 rs = stmt.executeQuery("select * from creditcard_info where name = 'joe';"); 
 // 关闭语句对象
 stmt.close();