Convert column data between plaintext and ciphertext on Always confidential database - ApsaraDB RDS

When the Always confidential database feature is enabled on an ApsaraDB RDS for PostgreSQL instance, you can convert an existing column between plaintext and ciphertext in place using ALTER TABLE ... ALTER COLUMN.

Prerequisites

Before you begin, verify that the following conditions are met.

For all conversions:

The Always confidential database feature is enabled on the instance. For more information, see Enable the Always confidential database feature.
The minor engine version of the instance is 20230830 or later. To update the minor engine version, see Update the minor engine version of an ApsaraDB RDS for PostgreSQL instance.
Your client has connected to the database using EncDB SDK at least once. For more information, see Use the Always confidential database feature from a client.
Your account is authorized using a behavior control list (BCL). For more information, see Manage authorization.

For plaintext-to-ciphertext conversions only:

A keyname is available — either the database default keyname (|<User>|<Database>|) or a custom keyname constructed using encdb.keyname_generate and encdb.dek_generate (see the examples below).

Syntax

ALTER TABLE <table_name>
    ALTER COLUMN <column_name> [SET DATA] TYPE <target_data_type>
    USING <conversion_function>(<column_name>[, '<keyname>']);

The behavior depends on the conversion direction:

If the column is currently...	And you want to...	Then...
Plaintext	Encrypt it	Specify the target ciphertext type and the encrypt function. Omit `keyname` to use the database default key, or include `keyname` to use a custom key.
Ciphertext	Decrypt it	Specify the target plaintext type and `encdb.decrypt`. Omit `keyname` from the `USING` clause.

Supported data types

The following table lists the plaintext and ciphertext type pairs and their corresponding conversion functions.

Plaintext type	Ciphertext type	Encrypt function	Decrypt 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

Encrypt a column using a custom key

Use this approach when you need to control the encryption algorithm, method, or key binding.

Construct a keyname for the target column:

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

Generate a data encryption key (DEK) for the keyname:

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

The json_params argument is optional. When provided, it must be a valid JSON object. The following key-value pairs are supported:

Key	Accepted values	Default	Notes
algorithm	`AES_128_GCM`, `AES_128_ECB`, `AES_128_CTR`, `AES_128_CBC`, `SM4_128_CBC`, `SM4_128_ECB`, `SM4_128_CTR`, `CLWW_ORE`	`AES_128_GCM`	`CLWW_ORE` implements Order-Revealing Encryption (ORE). For more information, see Practical Order-Revealing Encryption with Limited Leakage.
policy	`DEFAULT`	`DEFAULT`	Reserved. The value is fixed.
flags	`RND`, `DET`	`RND`	`RND`: random encryption. `DET`: deterministic encryption.
mekid	Master encryption key (MEK) ID	Current user's MEK ID	Specifies which MEK to use when generating the DEK.

Convert the column from plaintext to ciphertext. The following example converts an int4 column to enc_int4:

ALTER TABLE <table_name>
    ALTER COLUMN <column_name> SET DATA TYPE enc_int4
    USING encdb.enc_int4_encrypt(<column_name>, '<keyname>');

Encrypt a column using the database default key

Use this approach for a simpler setup when you do not need a custom keyname. The default keyname 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>);

Decrypt a column

Use this approach to convert a ciphertext column back to plaintext. The encdb.decrypt function is used for all supported ciphertext types.

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

Troubleshooting

`ERROR: xxxx generate dek for keyname errno:f70a0000`

Cause: Your client has not connected to the database using EncDB SDK.

Fix: Connect using EncDB SDK at least once, then retry. For connection instructions, see Use the Always confidential database feature from a client.

`ERROR: xxxx errno:fa030000` or `ERROR: xxxx error:fa020000`

Cause: No authorization record exists for your account, or your account is not authorized to perform this operation.

Fix: Authorize your account using a BCL, then retry. For authorization instructions, see Manage authorization.