pg_tde

Introduction

pg_tde is a Transparent Data Encryption (TDE) extension for PostgreSQL. It automatically encrypts data when it is written to disk and decrypts data when it is read. The entire process is completely transparent to upper-layer applications and SQL queries, so you do not need to modify any application code. Unlike disk-level or file-system-level encryption, pg_tde provides database-level encryption control. Even if storage media or backups are leaked, data content cannot be decrypted without the key.

To use pg_tde, submit a service ticket.

Supported Versions

This extension is only supported in the latest minor versions of PostgreSQL 16.14, 17.10, and later. To check whether your instance supports this extension, run the following SQL statement:

SELECT * FROM pg_available_extension_versions WHERE name = 'pg_tde'; 

Notes and Constraints

• Encrypted tables do not support logical decoding or logical replication. You must migrate them manually.
• The current version does not support index encryption, system catalog encryption, or write-ahead log (WAL) encryption.
• Due to the special nature of key files, if your database contains encrypted tables, major and minor version upgrades are not supported for the instance.
• If you use pg_tde for instances in a disaster recovery (DR) relationship, you must ensure that pg_tde is preloaded on all instances (DR primary and standby) before creating any key files. Otherwise, the DR standby instance may become abnormal.

Installing and Uninstalling the Extension

1. In the instance list, click the target instance name to go to the Summary page.
2. Choose Plugins, search for pg_tde, and click Load.
   Figure 1 Plugins page
   
3. Reboot the instance to apply the shared_preload_libraries parameter.
4. Install the pg_tde extension.
   • Installing pg_tde through the console

     On the Plugins page, select your target database, search for pg_tde, and click Install.

     Figure 2 Installing pg_tde
     
   • Installing pg_tde using SQL statements

     Log in to the target database and run the following SQL statement to create the extension:

     CREATE EXTENSION IF NOT EXISTS pg_tde;

5. Uninstall the pg_tde extension.
   • Uninstalling pg_tde through the console

     On the Plugins page, select your target database, search for pg_tde, and click Uninstall.

     Figure 3 Uninstalling pg_tde
     
   • Uninstalling pg_tde using SQL statements

     Log in to the target database and run the following SQL statement to delete the extension:

     DROP EXTENSION IF EXISTS pg_tde;

   

   If there are encrypted tables in your database, uninstalling the extension will fail. To fully remove it, first back up the data in your encrypted tables and run DROP EXTENSION IF EXISTS pg_tde CASCADE. Alternatively, delete all encrypted tables and then uninstall the extension through the console.

How to Use

• Setting the master key

  -- Ensure the Ruby user has read/write permissions on the key file directory. You are advised to store key files under /var/lib/pgsql/data/pg_tde.
  SELECT pg_tde_add_key_provider_file('my_file_keyring1','/var/lib/pgsql/data/pg_tde/my_keyring1'); 
  SELECT pg_tde_set_principal_key('my_principal_key_v1','my_file_keyring1');

• Creating an encrypted table

  -- Use the tde_heap_basic access method.
  CREATE TABLE test_encrypted_users (id INT PRIMARY KEY,   name TEXT,   age INT,   created_at TIMESTAMP ) USING tde_heap_basic; 
  -- Verify that the table is an encrypted table.
  SELECT pg_tde_set_principal_key('my_principal_key_v1','my_file_keyring1');

• Rotating the key

  SELECT pg_tde_add_key_provider_file('my_file_keyring2','/var/lib/pgsql/data/pg_tde/my_keyring2'); 
  SELECT pg_tde_rotate_principal_key('my_principal_key_v2','my_file_keyring2');

Except for table creation, SQL operations on encrypted tables are the same as those on regular tables.