ApsaraDB RDS:Sensitive field encryption (rds_encdb)

Prerequisites

• The RDS instance runs PostgreSQL 16.

• The instance minor engine version is 20250228 or later.

  To upgrade the minor engine version, see Upgrade the minor engine version.

Scenarios

The rds_encdb extension is suitable for scenarios that require dynamic data masking of query results, especially for industries with highly sensitive data such as finance, healthcare, and e-commerce. For example:

• Dynamic data encryption scenarios

• Compliance audit scenarios

• Third-party data sharing scenarios

By using this extension, you can implement result set-level encryption without modifying your business code, thereby maintaining data privacy while ensuring query efficiency.

Limits

The following SQL features are not currently supported:

• Query result sets returned by functions.

• Non-SELECT type queries, such as cursor operations and prepare/execute statements.

• CTE and UNION clauses.

Install the extension

1. Configure instance parameters, and change the Running Parameter Value of rds_encdb.enable_encryption to on.

2. Use a privileged account to connect to the destination database and execute the following SQL statement to install the rds_encdb extension. To create a privileged account, see Create an account.

   CREATE EXTENSION rds_encdb;

   Note

   Execute SELECT * FROM pg_extension; to view installed extensions.

Configure column encryption policies

The rds_encdb extension uses the metadata table rds_encdb.encryption_rule to record columns that need to be encrypted. To enable encryption for a specific column, you only need to insert a corresponding record into this table. After that, queries targeting this column will return results in ciphertext format. This setting is effective for existing sessions as well.

rds_encdb.encryption_rule table structure

Encryption algorithm

The default encryption algorithm is AES_256_GCM.

Configuration example

1. Use the test account test_user to connect to the RDS PostgreSQL instance, create a test table, and insert test data.

   CREATE TABLE test(a text,b text,c text);
   
   INSERT INTO test VALUES ('foo','bar','hello world');

2. Query the data in the test table. At this point, the results are returned in plaintext.

   SELECT * FROM test;

   Result:

     a  |  b  |      c
   -----+-----+-------------
    foo | bar | hello world
   (1 row)

3. Use the account that installed the rds_encdb extension (privileged account) to connect to RDS PostgreSQL, and insert a record into the metadata table rds_encdb.encryption_rule to encrypt the first and second columns of the test table.

   INSERT INTO rds_encdb.encryption_rule 
   VALUES 
       (9, 'rule1', 'test', '1'), 
       (10, 'rule1', 'test', '2');

4. Use the test account test_user to connect to the RDS PostgreSQL instance and query the data in the test table. At this point, the results are returned in ciphertext.

   SELECT * FROM test;

   Result:

                                   a                                 |                                b                                 |      c
   ------------------------------------------------------------------+------------------------------------------------------------------+-------------
    1yAZAAAACVyTxvBACK5JFw0w/ZU62Yt9btkv9bSN8TcJWOfXCiWVnCqnakSZCwI= | DSAZAAAACaSrnhi0usv3MiJsgRQKXA5xEArdALSdnFVjqD0nrd1s6ilShhw00EM= | hello world
   (1 row)

5. View the configured encryption rules in the metadata table rds_encdb.encryption_rule.

   SELECT * FROM rds_encdb.encryption_rule;

   Result:

    id | rule_name | attrelid | attnum
   ----+-----------+----------+--------
     9 | rule1     | test     |      1
    10 | rule1     | test     |      2
   (2 rows)

The rds_encdb extension provides a view. By executing the following SQL statement, you can aggregate the configured encryption rule records by (rule_name, table_name).

SELECT * FROM rds_encdb.rules;

Example:

 rule_name | attrelid | attname_list
-----------+----------+--------------
 rule1     | test     | b,a
(1 row)

Configure account encryption column permissions

Use the privileged account that installed the rds_encdb extension to set the following encryption column permissions for the target account. If not configured, the default permission is RESTRICTED ACCESS.

Configure account encryption column permissions

SELECT rds_encdb.setup_encryption_role('account', 'encryption_column_permission', 'expiration_time');

Remove account encryption column permissions

SELECT rds_encdb.remove_encryption_role('account');

Accounts with configured encryption column permissions are recorded in the rds_encdb extension's metadata table rds_encdb.encryption_role_auth. The structure of this table is as follows:

Configuration example

Using the test table from the previous example with encryption policies already configured, the account test_user that created the table has the default RESTRICTED ACCESS permission.

1. Use the test account test_user to connect to the RDS PostgreSQL instance and query the data in the test table. At this point, the results are returned in ciphertext.

   SELECT * FROM test;

   Result:

                                   a                                 |                                b                                 |      c
   ------------------------------------------------------------------+------------------------------------------------------------------+-------------
    1yAZAAAACVyTxvBACK5JFw0w/ZU62Yt9btkv9bSN8TcJWOfXCiWVnCqnakSZCwI= | DSAZAAAACaSrnhi0usv3MiJsgRQKXA5xEArdALSdnFVjqD0nrd1s6ilShhw00EM= | hello world
   (1 row)

2. Use the account that installed the rds_encdb extension (privileged account) to connect to RDS PostgreSQL and set encryption column permissions for the target account.

   SELECT rds_encdb.setup_encryption_role('test_user','FULL ACCESS','2025-04-17 16:01:02.509447+00');

3. View the accounts with configured encryption column permissions.

   SELECT * FROM rds_encdb.encryption_role_auth;

   Result:

      role    | role_type | salt |          expire_time
   -----------+-----------+------+-------------------------------
    test_user | f         |      | 2025-04-18 00:01:02.509447+08
   (1 row)

4. Use the test account test_user to connect to the RDS PostgreSQL instance and query the data in the test table. At this point, the results are returned in plaintext.

   SELECT * FROM test;

   Result:

     a  |  b  |      c
   -----+-----+-------------
    foo | bar | hello world
   (1 row)