How to use the rds_tde_utils extension for bulk encryption and decryption - ApsaraDB RDS

Encrypting or decrypting tables with ALTER statements requires a separate statement per table, which becomes impractical for large databases. The rds_tde_utils extension lets you encrypt or decrypt multiple tables and indexes at a time — either a single table or all tables in a database.

Prerequisites

Before you begin, make sure that:

Your RDS instance runs PostgreSQL 10 or later
Your RDS instance uses minor engine version 20221030 or later — see Update the minor engine version of an ApsaraDB RDS for MySQL instance
Transparent Data Encryption (TDE) is enabled — see Enable TDE for an ApsaraDB RDS for PostgreSQL instance and use TDE

Usage notes

Batch encryption and decryption operations run synchronously — the call returns only after all tables and indexes are processed. If a large number of tables exist in a database, these operations can take a long time. Proceed with caution.

Install and remove the extension

Run the following statements using a privileged account.

Install the extension:

CREATE EXTENSION rds_tde_utils;

Remove the extension:

DROP EXTENSION rds_tde_utils;

Function reference

The extension provides eight functions. Each function operates on either a single table or the entire database, using one of two rewrite methods.

Choose a rewrite method

Method	Behavior
Lazy	Rewrite logic is similar to LAZY VACUUM.
Full	Rewrite logic is similar to VACUUM FULL. Do not run during peak hours.

Encryption functions

Function	Scope	Method
`rds_tde_lazy_encrypt_table('table_name'::regclass)`	Single table and its indexes	Lazy
`rds_tde_encrypt_table('table_name'::regclass)`	Single table and its indexes	Full
`rds_tde_lazy_encrypt_database()`	All tables and indexes in the database	Lazy
`rds_tde_encrypt_database()`	All tables and indexes in the database	Full

Decryption functions

Function	Scope	Method
`rds_tde_lazy_decrypt_table('table_name'::regclass)`	Single table and its indexes	Lazy
`rds_tde_decrypt_table('table_name'::regclass)`	Single table and its indexes	Full
`rds_tde_lazy_decrypt_database()`	All tables and indexes in the database	Lazy
`rds_tde_decrypt_database()`	All tables and indexes in the database	Full

Examples

Set up test tables

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 a single table and its indexes

Use the lazy method to reduce impact on running workloads:

SELECT rds_tde_lazy_encrypt_table('table_a'::regclass);

Use the full method during off-peak hours for a complete table rewrite:

SELECT rds_tde_encrypt_table('table_a'::regclass);

Encrypt all tables and indexes in the database

-- Lazy method (lower impact)
SELECT rds_tde_lazy_encrypt_database();

-- Full method (complete rewrite, run during off-peak hours)
SELECT rds_tde_encrypt_database();

Decrypt a single table and its indexes

-- Lazy method
SELECT rds_tde_lazy_decrypt_table('table_a'::regclass);

-- Full method (run during off-peak hours)
SELECT rds_tde_decrypt_table('table_a'::regclass);

Decrypt all tables and indexes in the database

-- Lazy method (lower impact)
SELECT rds_tde_lazy_decrypt_database();

-- Full method (complete rewrite, run during off-peak hours)
SELECT rds_tde_decrypt_database();