Encrypt sensitive columns using the rds_encdb extension

Prerequisites

Before you begin, ensure that you have:

• An RDS instance running PostgreSQL 16

• Instance minor engine version 20250228 or later

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

Use cases

Typical use cases include:

• Dynamic data encryption: Encrypt specific columns in query results so that restricted accounts always receive ciphertext.

• Compliance audit: Enforce data access policies at the database layer to support audit requirements.

• Third-party data sharing: Share data with external parties while keeping sensitive columns encrypted.

How it works

rds_encdb uses a metadata table (rds_encdb.encryption_rule) to track which columns to encrypt. When a database account queries a table with configured encryption rules, the extension intercepts the result set and returns the target columns as ciphertext—unless the account has FULL ACCESS permission.

Account permissions work as follows:

The encryption algorithm is AES_256_GCM. Encryption rules take effect immediately, including for existing sessions.

Install the extension

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

2. Connect to the target database using a privileged account and run the following statement. To create a privileged account, see Create an account.

   Run SELECT * FROM pg_extension; to verify that the extension is installed.

   CREATE EXTENSION rds_encdb;

Configure encryption and account permissions

The following walkthrough covers the full workflow: creating a table, configuring column encryption rules, granting an account full access, and verifying query results at each stage.

Step 1: Create a test table and insert data

Connect as test_user and run:

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

Query the table. Results are plaintext at this point:

SELECT * FROM test;

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

Step 2: Configure column encryption rules

Connect as the privileged account (the account that installed rds_encdb) and insert records into the rds_encdb.encryption_rule metadata table to encrypt columns a and b of the test table:

-- Connect as privileged account
INSERT INTO rds_encdb.encryption_rule
VALUES
    (9, 'rule1', 'test', '1'),
    (10, 'rule1', 'test', '2');

The rds_encdb.encryption_rule table has the following structure:

To view all configured rules aggregated by (rule_name, table_name), query the rds_encdb.rules view:

SELECT * FROM rds_encdb.rules;

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

Step 3: Verify that restricted accounts see ciphertext

Connect as test_user and query the table again. Because test_user has the default RESTRICTED ACCESS permission, columns a and b are now returned as ciphertext:

-- Connect as test_user
SELECT * FROM test;

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

Step 4: Grant full access to an account

Connect as the privileged account and grant test_user FULL ACCESS with an expiration time:

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

To remove account encryption column permissions:

SELECT rds_encdb.remove_encryption_role('account');

Accounts with configured permissions are recorded in the rds_encdb.encryption_role_auth metadata table:

To view accounts with configured permissions:

SELECT * FROM rds_encdb.encryption_role_auth;

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

Step 5: Verify that the account now sees plaintext

Connect as test_user and query the table again:

-- Connect as test_user
SELECT * FROM test;

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

test_user now sees the encrypted columns as plaintext.

Limitations

The following SQL features are not currently supported:

• Query result sets returned by functions

• Non-SELECT queries, such as cursor operations and PREPARE/EXECUTE statements

• Common Table Expressions (CTEs) and UNION clauses

What's next

• JDBC-based driver for column encryption (encjdbc)