Use the rds_tde_utils extension to encrypt and decrypt multiple data records at a time - ApsaraDB RDS

Transparent Data Encryption (TDE) uses the ALTER statement to encrypt and decrypt tables and indexes. To simplify encryption and decryption, ApsaraDB RDS for PostgreSQL provides the rds_tde_utils extension to encrypt and decrypt multiple data records at a time.

Prerequisites

Your ApsaraDB RDS for PostgreSQL instance meets the following requirements:

The major version of the RDS instance is PostgreSQL 10 or later.
The minor engine version of the RDS instance is 20221030 or later.
Note For more information about how to update the minor engine version of an RDS instance, see Update the minor engine version of an ApsaraDB RDS for MySQL instance.
TDE is enabled. For more information, see Enable TDE for an ApsaraDB RDS for PostgreSQL instance and use TDE.

Usage notes

If you execute statements to encrypt or decrypt multiple data records at a time, the result is returned only after all data records are encrypted or decrypted. If a large number of tables exist in a database, a long period of time is required to complete the encryption or decryption. Proceed with caution.

Create or delete the extension

Note We recommend that you use a privileged account to execute the statements that are provided in this section.

Create the extension.
```
CREATE EXTENSION rds_tde_utils;
```
Delete the extension.
```
DROP EXTENSION rds_tde_utils;
```

Examples

Create test data.

CREATE TABLE table_a(id int);
CREATE INDEX index_a1 ON table_a(id);
CREATE INDEX index_a2 ON table_a USING hash(id);
CREATE TABLE table_b(id int);
CREATE INDEX index_b1 ON table_b(id);
CREATE INDEX index_b2 ON table_b USING hash(id);

Encrypt multiple data records at a time.
Note
The encryption process rewrites the table. You can call the rds_tde_lazy_encrypt_table or rds_tde_encrypt_table function to encrypt the table. The following list describes the rewrite logic of the functions:
The rewrite logic of the rds_tde_lazy_encrypt_table function is similar to that of LAZY VACUUM.
The rewrite logic of the rds_tde_encrypt_table function is similar to that of VACUUM FULL. We recommend that you do not encrypt data during peak hours.
- Encrypt the table named table_a and its indexes (index_a1 and index_a2) at a time.
  - ```
  SELECT rds_tde_lazy_encrypt_table('table_a'::regclass);
```
- ```
SELECT rds_tde_encrypt_table('table_a'::regclass);
```
- Encrypt all tables (table_a and table_b) in the database and the indexes of the tables at a time.
  - ```
  SELECT rds_tde_lazy_encrypt_database();
```
- ```
SELECT rds_tde_encrypt_database();
```
Decrypt multiple data records at a time.
Note
The decryption process rewrites the table. You can call the rds_tde_lazy_decrypt_table or rds_tde_decrypt_table function to decrypt the table. The following list describes the rewrite logic of the functions:
The rewrite logic of the rds_tde_lazy_decrypt_table function is similar to that of LAZY VACUUM.
The rewrite logic of the rds_tde_decrypt_table function is similar to that of VACUUM FULL. We recommend that you do not decrypt data during peak hours.
- Decrypt the table named table_a and its indexes (index_a1 and index_a2) at a time.
  - ```
  SELECT rds_tde_lazy_decrypt_table('table_a'::regclass);
```
- ```
SELECT rds_tde_decrypt_table('table_a'::regclass);
```
- Decrypt all tables (table_a and table_b) in the database and the indexes of the tables at a time.
  - ```
  SELECT rds_tde_lazy_decrypt_database();
```
- ```
SELECT rds_tde_decrypt_database();
```