Convert data between plaintext and ciphertext - ApsaraDB RDS

If the fully encrypted database feature is enabled for your ApsaraDB RDS for PostgreSQL instance, you can convert data in a column between plaintext and ciphertext.

Prerequisites

The fully encrypted database feature is enabled and configured for the RDS instance. For more information, see Enable the fully encrypted database feature, Sensitive data, and Client usage.
You are authorized by using a behavior control list (BCL). For more information, see Grant access permissions on multi-user data.
The minor engine version of the RDS instance is 20230830 or later. For more information about how to update the minor engine version, see Update the minor engine version of an ApsaraDB RDS for PostgreSQL instance.

Syntax

ALTER TABLE <table_name> 
    ALTER COLUMN <column_name> [SET DATA] TYPE <Data type after conversion> 
    USING <Data conversion function>(<column_name>[, <keyname>]);

Note

If you convert data from ciphertext to plaintext, you need to only configure the column_name parameter.
If you convert data from plaintext to ciphertext, you can configure only the column_name parameter. You can also configure the column_name and keyname parameters at the same time.

Table 1 Data conversion functions
Plaintext data	Ciphertext data	Plaintext-to-ciphertext conversion function	Ciphertext-to-plaintext conversion function
int4	enc_int4	encdb.enc_int4_encrypt	encdb.decrypt
int8	enc_int8	encdb.enc_int8_encrypt	encdb.decrypt
float4	enc_float4	encdb.enc_float4_encrypt	encdb.decrypt
float8	enc_float8	encdb.enc_float8_encrypt	encdb.decrypt
numeric	enc_decimal	encdb.enc_decimal_encrypt	encdb.decrypt
text	enc_text	encdb.enc_text_encrypt	encdb.decrypt
timestamp	enc_timestamp	encdb.enc_timestamp_encrypt	encdb.decrypt

Examples

Use the key of the constructed keyname to convert data in a column from plaintext to ciphertext.

Construct keyname.

SELECT encdb.keyname_generate(<user_name>, <database_name>, <schema_name>, <table_name>, <column_name>);

Generate a key for keyname.

SELECT encdb.dek_generate(<keyname>[, <json_params>]);

The json_params parameter in encdb.dek_generate is optional. You can specify a JSON-formatted value for this parameter. The following table describes the supported key-value pairs.

Key	Value	Example
algorithm	AES_128_GCM AES_128_ECB AES_128_CTR AES_128_CBC SM4_128_CBC SM4_128_ECB SM4_128_CTR CLWW_ORE Note Order-revealing encryption (ORE) is implemented and optimized. For more information, see Practical Order-Revealing Encryption with Limited Leakage.	The encryption algorithm. Default value: AES_128_GCM.
policy	DEFAULT	The encryption policy. This parameter is reserved. The value is fixed as DEFAULT.
flags	RND DET	The encryption method. Valid values: RND: random encryption DET: deterministic encryption Default value: RND.
mekid	Master encryption key (MEK) ID of the user	The ID of the MEK that is used to generate data encryption keys (DEKs). The default value is the MEK ID of the current user.

Use the key of the keyname to convert data in the column from plaintext to ciphertext. For example, you can convert data from the int4 type to the enc_int4 type.
```
ALTER TABLE <table_name> ALTER COLUMN <column_name> SET DATA TYPE enc_int4 USING encdb.enc_int4_encrypt(<column_name>, <keyname>);
```

Convert data in the column from plaintext to ciphertext by using the default key of the current database.
Note
The keyname of the default key that is used for the current database is |<User>|<Database>|.
```
ALTER TABLE <table_name> ALTER COLUMN <column_name> SET DATA TYPE enc_int4 USING encdb.enc_int4_encrypt(<column_name>);
```

Convert data in the column from plaintext to ciphertext.

ALTER TABLE <table_name> ALTER COLUMN <column_name> SET DATA TYPE int4 USING encdb.decrypt(<column_name>);