Percona Transparent Data Encryption

Documentation

Release Candidate (2025-03-27)

pg_tde Documentation

pg_tde is the open source, community driven and futureproof PostgreSQL extension that provides Transparent Data Encryption (TDE) to protect data at rest. pg_tde ensures that the data stored on disk is encrypted, and that no one can read it without the proper encryption keys, even if they gain access to the physical storage media.

Important

This is the RC2 version of the extension and it is not meant for production use yet. We encourage you to use it in testing environments and provide your feedback.

Overview Get Started What’s new in pg_tde RC2

Get Help from Percona

Our documentation guides are packed with information, but they can’t cover everything you need to know about pg_tde. They also won’t cover every scenario you might come across. Don’t be afraid to try things out and ask questions when you get stuck.

Percona’s Community Forum

Be a part of a space where you can tap into a wealth of knowledge from other database enthusiasts and experts who work with Percona’s software every day. While our service is entirely free, keep in mind that response times can vary depending on the complexity of the question. You are engaging with people who genuinely love solving database challenges.

We recommend visiting our Community Forum. It’s an excellent place for discussions, technical insights, and support around Percona database software. If you’re new and feeling a bit unsure, our FAQ and Guide for New Users ease you in.

If you have thoughts, feedback, or ideas, the community team would like to hear from you at Any ideas on how to make the forum better?. We’re always excited to connect and improve everyone’s experience.

Percona experts

Percona experts bring years of experience in tackling tough database performance issues and design challenges.

We understand your challenges when managing complex database environments. That’s why we offer various services to help you simplify your operations and achieve your goals.

Service	Description
24/7 Expert Support	Our dedicated team of database experts is available 24/7 to assist you with any database issues. We provide flexible support plans tailored to your specific needs.
Hands-On Database Management	Our managed services team can take over the day-to-day management of your database infrastructure, freeing up your time to focus on other priorities.
Expert Consulting	Our experienced consultants provide guidance on database topics like architecture design, migration planning, performance optimization, and security best practices.
Comprehensive Training	Our training programs help your team develop skills to manage databases effectively, offering virtual and in-person courses.

We’re here to help you every step of the way. Whether you need a quick fix or a long-term partnership, we’re ready to provide your expertise and support.

Features

pg_tde is available for Percona Server for PostgreSQL The Percona Server for PostgreSQL provides an extended Storage Manager API that allows integration with custom storage managers.

The following features are available for the extension:

• Table encryption, including:
  • Data tables
  • Index data for encrypted tables
  • TOAST tables
  • Temporary tables created during database operations

Note

Metadata of those tables is not encrypted.

• Global Write-Ahead Log (WAL) encryption for data in both encrypted and non-encrypted tables
• Single-tenancy support via a global keyring provider
• Multi-tenancy support
• Table-level granularity for encryption and access control
• Multiple Key management options
• Logical replication of encrypted tables

Overview Get Started

Overview

What is Transparent Data Encryption (TDE)?

What is Transparent Data Encryption (TDE)?

Transparent Data Encryption (TDE) protects your data at rest by ensuring that even if the underlying storage is compromised, the data remains unreadable without the correct encryption keys. This is especially critical for environments handling sensitive, regulated, or high-value information.

Encryption happens transparently in the background, with minimal impact on database operations.

TDE Benefits Check the full feature list Get Started

TDE Benefits

Benefits for organizations

• Data safety: Prevents unauthorized access to stored data, even if backup files or storage devices are stolen or leaked.
• Enterprise-ready Architecture: Supports both single and multi-tenancy, giving flexibility for SaaS providers or internal multi-user systems.

Benefits for DBAs and engineers

• Granular control: Encrypt specific tables or databases instead of the entire system, reducing performance overhead.
• Operational simplicity: Works transparently without requiring major application changes.
• Defense in depth: Adds another layer of protection to existing controls like TLS (encryption in transit), access control, and role-based permissions.

When combined with the external Key Management Systems (KMS), TDE enables centralized control, auditing, and rotation of encryption keys—critical for secure production environments.

See also

Percona Blog: Transparent Data Encryption (TDE)

How TDE works

How TDE Works

To encrypt the data, two types of keys are used:

• Internal encryption keys to encrypt user data. They are stored internally, near the data that they encrypt.
• The principal key to encrypt database keys. It is kept separately from the database keys and is managed externally in the key management store.

Note

For more information on managing and storing principal keys externally, see Configure Global Key Provider.

You have the following options to store and manage principal keys externally:

• Use the HashiCorp Vault server. Only the back end KV Secrets Engine - Version 2 (API) is supported.
• Use the KMIP-compatible server. pg_tde has been tested with the PyKMIP server and the HashiCorp Vault Enterprise KMIP Secrets Engine.

The encryption process is the following:

When a user creates an encrypted table using pg_tde, a new random key is generated internally for that table and is encrypted using the AES-CBC cipher algorithm. This key is used to encrypt all data the user inserts in that table. Eventually the encrypted data gets stored in the underlying storage.

The internal key itself is encrypted using the principal key. The principal key is stored externally in the key management store.

Similarly when the user queries the encrypted table, the principal key is retrieved from the key store to decrypt the internal key. Then the same unique internal key for that table is used to decrypt the data, and unencrypted data gets returned to the user. So, effectively, every TDE table has a unique key, and each table key is encrypted using the principal key.

Encrypted Data Scope

Encrypted Data Scope

pg_tde encrypts the following components:

• User data in tables using the extension, including associated TOAST data. The table metadata (column names, data types, etc.) is not encrypted.
• Temporary tables created during the query execution, for data tables created using the extension.
• Write-Ahead Log (WAL) data for the entire database cluster. This includes WAL data in encrypted and non-encrypted tables.
• Indexes on encrypted tables.
• Logical replication data for encrypted tables (ensures encrypted content is preserved across replicas).

Table Access Methods and TDE

Table Access Methods and TDE

A table access method is the way how PostgreSQL stores the data in a table. The default table access method is heap. PostgreSQL organizes data in a heap structure, meaning there is no particular order to the rows in the table. Each row is stored independently and identified by its unique row identifier (TID).

How the heap access method works

Insertion: When a new row is inserted, PostgreSQL finds a free space in the tablespace and stores the row there.

Deletion: When a row is deleted, PostgreSQL marks the space occupied by the row as free, but the data remains until it is overwritten by a new insertion.

Updates: PostgreSQL handles updates by deleting the old row and inserting a new row with the updated values.

Custom access method

Custom access methods allow you to implement and define your own way of organizing data in PostgreSQL. This is useful if the default table access method doesn’t meet your needs.

Custom access methods are typically available with PostgreSQL extensions. When you install an extension and enable it in PostgreSQL, a custom access method is created.

An example of such an approach is the tde_heap access method. It is automatically created only for the databases where you enabled the pg_tde extension and configured the key provider, enabling you to encrypt the data.

To use a custom access method, specify the USING clause for the CREATE TABLE command:

CREATE TABLE table_name (
    column1 data_type,
    column2 data_type,
    ...
) USING tde_heap;

How tde_heap works

The tde_heap access method works on top of the default heap access method and is a marker to point which tables require encryption. It uses the custom storage manager TDE SMGR, which becomes active only after you installed the pg_tde extension.

Every data modification operation is first sent to the Buffer Manager, which updates the buffer cache. Then, it is passed to the storage manager, which then writes it to disk. When a table requires encryption, the data is sent to the TDE storage manager, where it is encrypted before written to disk.

Similarly, when a client queries the database, the PostgreSQL core sends the request to the Buffer Manager which checks if the requested data is already in the buffer cache. If it’s not there, the Buffer Manager requests the data from the storage manager. The TDE storage manager reads the encrypted data from disk, decrypts it and loads it to the buffer cache. The Buffer Manager sends the requested data to the PostgreSQL core and then to the client.

Thus, the encryption is done at the storage manager level.

Changing the default table access method

You can change the default table access method so that every table in the entire database cluster is created using the custom access method. For example, you can enable data encryption by default by defining the tde_heap as the default table access method.

However, consider the following before making this change:

• This is a global setting and applies across the entire database cluster and not just a single database. We recommend setting it with caution because all tables and materialized views created without an explicit access method in their CREATE statement will default to the specified table access method.
• You must create the pg_tde extension and configure the key provider for all databases before you modify the configuration. Otherwise PostgreSQL won’t find the specified access method and will throw an error.

Here is how you can set the new default table access method:

1. Add the access method to the default_table_access_method parameter:

   via the SQL statementvia the configuration filevia the SET command

   Use the ALTER SYSTEM command. This requires superuser or ALTER SYSTEM privileges.

   ALTER SYSTEM SET default_table_access_method = tde_heap;
   

   Edit the postgresql.conf configuration file and add the value for the default_table_access_method parameter.

   default_table_access_method = 'tde_heap'
   

   You can use the SET command to change the default table access method temporarily, for the current session.

   Unlike modifying the postgresql.conf file or using the ALTER SYSTEM command, the changes you make via the SET command don’t persist after the session ends.

   You also don’t need to have the superuser privileges to run the SET command.

   You can run the SET command anytime during the session.

   SET default_table_access_method = tde_heap;
   

2. Reload the configuration to apply the changes:

   SELECT pg_reload_conf();
   

Limitations of TDE

Limitations of pg_tde

• Keys in the local keyfile are stored unencrypted. For better security we recommend using the Key management storage.

• System tables are currently not encrypted. This means that statistics data and database metadata are currently not encrypted.

• pg_rewind doesn’t work with encrypted WAL for now. We plan to fix it in future releases.

• pg_tde Release candidate is incompatible with pg_tdeBeta2 due to significant changes in code. There is no direct upgrade flow from one version to another. You must uninstall pg_tde Beta2 first and then install and configure the new Release Candidate version.

Important

This is the RC2 version of the extension and it is not meant for production use yet. We encourage you to use it in testing environments and provide your feedback.

Versions and Supported PostgreSQL Deployments

Versions and Supported PostgreSQL Deployments

The pg_tde extension is available for Percona Server for PostgreSQL 17.x, an open source, drop-in replacement for PostgreSQL Community. This version provides the tde_heap access method and offers full encryption capabilities, including encryption of tables, indexes, WAL data, and support for logical replication.

The extension is tightly integrated with Percona Server for PostgreSQL to deliver enhanced encryption functionality that is not available in community builds.

Why choose Percona Server for PostgreSQL?

By using our PostgreSQL distribution, you get:

• Full encryption support through the tde_heap access method, including tables, indexes, WAL data, and logical replication.
• Enhanced performance and enterprise-ready features not available in community builds.
• Regular updates and security patches backed by Percona’s expert support team.
• Professional support and guidance for secure PostgreSQL deployments.

Note

Support for earlier or limited versions of pg_tde (such as tde_heap_basic) has been deprecated.

Still unsure which deployment fits your needs? Contact our experts to find the best solution for your environment.

Get Started: Install pg_tde

Get Started

Install pg_tde

To install pg_tde, use one of the following methods:

Package manager Docker Tar download

The packages are available for the following operating systems:

• Red Hat Enterprise Linux 8 and compatible derivatives
• Red Hat Enterprise Linux 9 and compatible derivatives
• Ubuntu 20.04 (Focal Fossa)
• Ubuntu 22.04 (Jammy Jellyfish)
• Ubuntu 24.04 (Noble Numbat)
• Debian 11 (Bullseye)
• Debian 12 (Bookworm)

Install on Debian or Ubuntu Install on RHEL or derivatives

pg_tde is a part of the Percona Distribution for PostgreSQL Docker image. Use this image to enjoy full encryption capabilities. Check below to get access to a detailed step-by-step guide.

Run in Docker

pg_tde is included in the Percona Distribution for PostgreSQL tarball. Select the below link to access the step-by-step guide.

Install from tarballs

Follow the configuration steps below to continue:

Configure pg_tde

If you’ve already completed these steps, feel free to skip ahead to a later section:

Configure Key Management (KMS) Validate Encryption with pg_tde Configure WAL encryption

Install pg_tde on Debian or Ubuntu

This page explains how to install pg_tde with Percona Distribution for PostgreSQL .

Check the list of supported platforms before continuing.

Preconditions

1. Debian and other systems that use the apt package manager include the upstream PostgreSQL server package (postgresql-17.5) by default. You need to uninstall this package before you install Percona Server for PostgreSQL and pg_tde to avoid conflicts.
2. You need the percona-release repository management tool that enables the desired Percona repository for you.

Install percona-release

1. You need the following dependencies to install percona-release:

   • wget
   • gnupg2
   • curl
   • lsb-release

   Install them with the following command:

       sudo apt-get install -y wget gnupg2 curl lsb-release
   

2. Fetch the percona-release package

       sudo wget https://repo.percona.com/apt/percona-release_latest.generic_all.deb
   

3. Install percona-release

       sudo dpkg -i percona-release_latest.generic_all.deb
   

4. Enable the Percona Distribution for PostgreSQL repository

       sudo percona-release enable-only ppg-17.5 
   

5. Update the local cache

      sudo apt-get update
   

Install pg_tde

Important

The pg_tde extension is a part of the percona-postgresql-17 package. If you installed a previous version of pg_tde from the percona-postgresql-17-pg-tde package, do the following:

1. Drop the extension using the DROP EXTENSION with CASCADE command.

   The use of the CASCADE parameter deletes all tables that were created in the database with pg_tde enabled and also all dependencies upon the encrypted table (e.g. foreign keys in a non-encrypted table used in the encrypted one).

       DROP EXTENSION pg_tde CASCADE;
   

2. Uninstall the percona-postgresql-17-pg-tde package.

After all preconditions are met, run the following command to install pg_tde:

    sudo apt-get install -y percona-postgresql-17 

Next steps

Configure pg_tde

Install pg_tde on Red Hat Enterprise Linux and Derivatives

This tutorial shows how to install pg_tde with Percona Distribution for PostgreSQL.

Make sure you check the list of supported platforms before continuing.

Memory limits for pg_tde keys

The pg_tde uses memory locks (mlocks) to keep internal encryption keys in RAM, both for WAL and for user data.

A memory lock (mlock) is a system call to lock a specified memory range in RAM for a process. The maximum amount of memory that can be locked differs between systems. You can check the current setting with this command:

    ulimit -a 

Memory locking is done only in memory pages. This means that when a process uses mlocks, it locks the entire memory page.

A process can have child processes that share the mlock limits of their parent. In PostgreSQL, the parent process is the one that runs the server. And its child backend processes handle client connections to the server.

If the mlock limit is greater than the page size, a child process locks another page for its operation. However, when the mlock limit equals the page size, the child process cannot run because the max memory limit is already reached by the parent process that used it for reading WAL files. This results in pg_tde failing with the error.

To prevent this, you can change the mlock limit to be at least twice bigger than the memory page size:

• temporarily for the current session using the ulimit -l <value> command.
• set a new hard limit in the /etc/security/limits.conf file. To do so, you require the superuser privileges.

Adjust the limits with caution since it affects other processes running in your system.

Install percona-release

You need the percona-release repository management tool that enables the desired Percona repository for you.

1. Install percona-release:

       sudo yum -y install https://repo.percona.com/yum/percona-release-latest.noarch.rpm 
   

2. Enable the repository.

       sudo percona-release enable-only ppg-17.5
   

Install pg_tde

Important

The pg_tde extension is a part of the percona-postgresql17 package. If you installed a previous version of pg_tde from the percona-pg_tde_17 package, do the following:

1. Drop the extension using the DROP EXTENSION with CASCADE command.

The use of the CASCADE parameter deletes all tables that were created in the database with pg_tde enabled and also all dependencies upon the encrypted table (e.g. foreign keys in a non-encrypted table used in the encrypted one).

   DROP EXTENSION pg_tde CASCADE

1. Uninstall the percona-pg_tde_17 package.

Run the following command to install pg_tde:

    sudo yum -y install percona-postgresql17-server percona-postgresql17-contrib 

Next steps

Configure pg_tde

Configure pg_tde

Before you can use pg_tde for data encryption, you must enable the extension and configure PostgreSQL to load it at startup. This setup ensures that the necessary hooks and shared memory are available for encryption operations.

Note

To learn how to configure multi-tenancy, refer to the Configure multi-tenancy guidelines.

The pg_tde extension requires additional shared memory. You need to configure PostgreSQL to preload it at startup.

1. Configure shared_preload_libraries

You can configure the shared_preload_libraries parameter in two ways:

• Add the following line to the shared_preload_libraries file:

  shared_preload_libraries = 'pg_tde'
  

• Use the ALTER SYSTEM command. Run the following command in psql as a superuser:

  ALTER SYSTEM SET shared_preload_libraries = 'pg_tde';
  

2. Restart the PostgreSQL cluster

Restart the postgresql cluster to apply the configuration.

• On Debian and Ubuntu:

  sudo systemctl restart postgresql.service
  

• On RHEL and derivatives:

  sudo systemctl restart postgresql-17
  

3. Create the extension

After restarting PostgreSQL, connect to psql as a superuser or database owner and run:

CREATE EXTENSION pg_tde;

See CREATE EXTENSION for more details.

Note

The pg_tde extension is created only for the current database. To enable it for other databases, you must run the command in each individual database.

4. (Optional) Enable pg_tde by default

To automatically have pg_tde enabled for all new databases, modify the template1 database:

psql -d template1 -c 'CREATE EXTENSION pg_tde;'

Note

You can use external key providers to manage encryption keys. The recommended approach is to use the Key Management Store (KMS). See the next step on how to configure the KMS.

Next steps

Configure Key Management (KMS)

2.1 Configure Key Management (KMS)

Configure Key Management (KMS)

In production environments, storing encryption keys locally on the PostgreSQL server can introduce security risks. To enhance security, pg_tde supports integration with external Key Management Systems (KMS) through a Global Key Provider interface.

This section describes how you can configure pg_tde to use the local and external key providers. To use an external KMS with pg_tde, follow these two steps:

1. Configure a Key Provider
2. Set the Global Principal Key

Note

While keyfiles may be acceptable for local or testing environments, KMS integration is the recommended approach for production deployments.

Select your prefered configuration from the links below:

KMIP Configuration Vault Configuration Keyring File Configuration (not recommended)

KMIP Configuration

To use a Key Management Interoperability Protocol (KMIP) server with pg_tde, you must configure it as a global key provider. This setup enables pg_tde to securely fetch and manage encryption keys from a centralized key management appliance.

Note

You need the root certificate of the KMIP server and a client key/certificate pair with permissions to create and read keys on the server.

It is recommended to review the configuration guidelines for the HashiCorp Vault Enterprise KMIP Secrets Engine if you’re using Vault.

For testing purposes, you can use a lightweight PyKMIP server, which enables easy certificate generation and basic KMIP behavior. If you’re using a production-grade KMIP server, ensure you obtain valid, trusted certificates from the key management appliance.

Example usage

SELECT pg_tde_add_global_key_provider_kmip(
    'provider-name',
    'kmip-IP', 
    5696,
    '/path_to/server_certificate.pem', 
    '/path_to/client_cert.pem',
    '/path_to/client_key.pem'
);

Parameter descriptions

• provider-name is the name of the provider. You can specify any name, it’s for you to identify the provider
• kmip-IP is the IP address of a domain name of the KMIP server
• port is the port to communicate with the KMIP server. Typically used port is 5696
• server-certificate is the path to the certificate file for the KMIP server
• client_cert is the path to the client certificate.
• client_key is the path to the client key.

The following example is for testing purposes only.

SELECT pg_tde_add_global_key_provider_kmip(
    'kmip','127.0.0.1', 
    5696, 
    '/tmp/server_certificate.pem', 
    '/tmp/client_cert_jane_doe.pem',
    '/tmp/client_key_jane_doe.pem'
);

For more information on related functions, see the link below:

Percona pg_tde Function Reference

Next steps

Global Principal Key Configuration

Fortanix KMIP Server Configuration

pg_tde is compatible with Fortanix Data Security Manager (DSM) via the KMIP protocol. For a full setup guide, see the Fortanix KMIP documentation here.

For more information on adding or modifying the provider, see the Adding or modifying KMIP providers topic.

Vault Configuration

You can configure pg_tde to use HashiCorp Vault as a global key provider for managing encryption keys securely.

Note

This guide assumes that your Vault server is already set up and accessible. Vault configuration is outside the scope of this document, see Vault’s official documentation for more information.

Example usage

SELECT pg_tde_add_global_key_provider_vault_v2(
    'provider-name',
    'url',
    'mount',
    'secret_token_path',
    'ca_path'
);

Parameter descriptions

• provider-name is the name to identify this key provider
• secret_token_path is a path to the file that contains an access token with read and write access to the above mount point
• url is the URL of the Vault server
• mount is the mount point where the keyring should store the keys
• [optional] ca_path is the path of the CA file used for SSL verification

The following example is for testing purposes only. Use secure tokens and proper SSL validation in production environments:

SELECT pg_tde_add_global_key_provider_vault_v2(
    'my-vault',
    'https://vault.vault.svc.cluster.local:8200',
    'secret/data',
    '/path/to/token_file',
    '/path/to/ca_cert.pem'
);

For more information on related functions, see the link below:

Percona pg_tde Function Reference

Next steps

Global Principal Key Configuration

Thales KMIP Server Configuration

pg_tde is compatible with the Thales CipherTrust Manager via the KMIP protocol. For a full setup guide, see the Thales documentation.

For more information on adding or modifying the provider, see the Adding or modifying KMIP providers topic.

Keyring File Configuration

This setup is intended for development and stores the keys unencrypted in the specified data file.

Note

While keyfiles may be acceptable for local or testing environments, KMS integration is the recommended approach for production deployments.

SELECT pg_tde_add_global_key_provider_file(
    'provider-name',
    '/path/to/the/keyring/data.file'
);

The following example is used for testing purposes only:

SELECT pg_tde_add_global_key_provider_file(
    'file-keyring',
    '/tmp/pg_tde_test_local_keyring.per'
);

Next steps

Global Principal Key Configuration

Global Principal Key Configuration

You can configure a default principal key using a global key provider. This key will be used by all databases that do not have their own encryption keys configured. The function both sets the principal key and rotates internal keys as needed.

Create a default principal key

To configure a global principal key, run:

SELECT pg_tde_set_default_key_using_global_key_provider(
    'key-name',
    'global_vault_provider',
    'false'  -- or 'true', or omit entirely
);

Parameter description

• key-name is the name under which the principal key is stored in the provider.
• global_vault_provider is the name of the global key provider you previously configured.
• Third parameter (optional):
  • true requires the key to be newly created. If the key already exists, the function fails.
  • false or omitted (default), allows reuse of an existing key if it exists. If not, a new key is created under the specified name.

How key generation works

If the specified key does not exist, a new encryption key is created under the given name. In this case, the key material (actual cryptographic key) is auto-generated by pg_tde and stored securely by the configured provider.

Note

This process sets the default principal key for the server. Any database without its own key configuration will use this key.

Example

This example is for testing purposes only. Replace the key name and provider name with your values:

SELECT pg_tde_set_key_using_global_key_provider(
    'test-db-master-key',
    'file-vault',
    'false'
);

Next steps

Validate Encryption with pg_tde

Validate Encryption with pg_tde

After enabling the pg_tde extension for a database, you can begin encrypting data using the tde_heap table access method.

Encrypt data in a new table

1. Create a table in the database for which you have enabled pg_tde using the tde_heap access method as follows:

   CREATE TABLE <table_name> (<field> <datatype>) USING tde_heap;
   

   Warning: Example for testing purposes only:

   CREATE TABLE albums (
       album_id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
       artist_id INTEGER,
       title TEXT NOT NULL,
       released DATE NOT NULL
   ) USING tde_heap;
   

   Learn more about table access methods and how you can enable data encryption by default in the Table Access Methods and TDE section.

2. To check if the data is encrypted, run the following function:

   SELECT pg_tde_is_encrypted('table_name');
   

   The function returns t if the table is encrypted and f - if not.

3. (Optional) Rotate the principal key.

To re-encrypt the data using a new key, see Principal key management.

Encrypt existing table

You can encrypt an existing table. It requires rewriting the table, so for large tables, it might take a considerable amount of time.

Run the following command:

ALTER TABLE table_name SET ACCESS METHOD tde_heap;

Important

Using SET ACCESS METHOD drops hint bits which can impact query performance. To restore performance, run:

SELECT count(*) FROM table_name;

This forces PostgreSQL to check every tuple for visibility and reset the hint bits.

Hint

Want to remove encryption later? See how to decrypt your data.

Next steps

Configure WAL Encryption (tech preview)

Configure WAL Encryption (tech preview)

Before turning WAL encryption on, you must follow the steps below to create your first principal key.

Create the principal key

1. Create the pg_tde extension if it does not exist:

   CREATE EXTENSION IF NOT EXISTS pg_tde;
   

2. Set up the key provider for WAL encryption

   With KMIP serverWith HashiCorp VaultWith keyring file

   Make sure you have obtained the root certificate for the KMIP server and the keypair for the client. The client key needs permissions to create / read keys on the server. Find the configuration guidelines for the HashiCorp Vault Enterprise KMIP Secrets Engine.

   For testing purposes, you can use the PyKMIP server which enables you to set up required certificates. To use a real KMIP server, make sure to obtain the valid certificates issued by the key management appliance.

   SELECT pg_tde_add_global_key_provider_kmip('provider-name', 'kmip-addr', 5696, '/path_to/client_cert.pem', '/path_to/client_key.pem', '/path_to/server_certificate.pem');
   

   where:

   • provider-name is the name of the provider. You can specify any name, it’s for you to identify the provider.
   • kmip-addr is the IP address of a domain name of the KMIP server
   • port is the port to communicate with the KMIP server. Typically used port is 5696.
   • server-certificate is the path to the certificate file for the KMIP server.
   • client-cert is the path to the client certificate.
   • client-key is the path to the client key.

   Warning: This example is for testing purposes only:

   SELECT pg_tde_add_key_using_global_key_provider_kmip('kmip', '127.0.0.1', 5696, '/tmp/client_cert_jane_doe.pem', '/tmp/client_key_jane_doe.pem', '/tmp/server_certificate.pem');
   

   SELECT pg_tde_add_global_key_provider_vault_v2('provider-name', 'url', 'mount', 'secret_token_path', 'ca_path');
   

   where:

   • provider-name is the name you define for the key provider
   • url is the URL of the Vault server
   • mount is the mount point where the keyring should store the keys
   • secret_token_path is a path to the file that contains an access token with read and write access to the above mount point
   • [optional] ca_path is the path of the CA file used for SSL verification

   This setup is not recommended, as it is intended for development. The keys are stored unencrypted in the specified data file.

   SELECT pg_tde_add_global_key_provider_file('provider-name', '/path/to/the/keyring/data.file');
   

3. Create principal key

   SELECT pg_tde_set_server_key_using_global_key_provider('key', 'provider-name');
   

4. Enable WAL level encryption using the ALTER SYSTEM command. You need the privileges of the superuser to run this command:

   ALTER SYSTEM SET pg_tde.wal_encrypt = on;
   

5. Restart the server to apply the changes.

   • On Debian and Ubuntu:

   sudo systemctl restart postgresql
   

   • On RHEL and derivatives

   sudo systemctl restart postgresql-17
   

Now WAL files start to be encrypted for both encrypted and unencrypted tables.

For more technical references related to architecture, variables or functions, see: Technical Reference

Technical Reference

Technical Reference

This section covers the internal components and tools that power pg_tde.

Use it to understand how encryption is implemented, fine-tune a configuration, leverage advanced CLI tools and functions for diagnostics and customization.

Architecture GUC Variables Functions

Architecture

pg_tde is a customizable, complete, data at rest encryption extension for PostgreSQL.

Let’s break down what it means.

Customizable means that pg_tde aims to support many different use cases:

• Encrypting either every table in every database or only some tables in some databases
• Encryption keys can be stored on various external key storage servers including Hashicorp Vault, KMIP servers.
• Using one key for everything or different keys for different databases
• Storing every key on the same key storage, or using different storages for different databases
• Handling permissions: who can manage database specific or global permissions, who can create encrypted or not encrypted tables

Complete means that pg_tde aims to encrypt data at rest.

Data at rest means everything written to the disk. This includes the following:

• Table data files
• Indexes
• Sequences
• Temporary tables
• Write Ahead Log (WAL)

Extension means that pg_tde should be implemented only as an extension, possibly compatible with any PostgreSQL distribution, including the open source community version. This requires changes in the PostgreSQL core to make it more extensible. Therefore, pg_tde currently works only with the Percona Server for PostgreSQL - a binary replacement of community PostgreSQL and included in Percona Distribution for PostgreSQL.

Main components

The main components of pg_tde are the following:

• Core server changes focus on making the server more extensible, allowing the main logic of pg_tde to remain separate, as an extension. Core changes also add encryption-awareness to some command line tools that have to work directly with encrypted tables or encrypted WAL files.

  Percona Server for PostgreSQL location

• The pg_tde extension itself implements the encryption code by hooking into the extension points introduced in the core changes, and the already existing extension points in the PostgreSQL server.

  Everything is controllable with GUC variables and SQL statements, similar to other extensions.

• The keyring API / libraries implement the key storage logic with different key providers. The API is internal only, the keyring libraries are part of the main library for simplicity. In the future these could be extracted into separate shared libraries with an open API, allowing the use of third-party providers.

Encryption architecture

Two-key hierarchy

pg_tde uses two kinds of keys for encryption:

• Internal keys to encrypt the data. They are stored in PostgreSQL’s data directory under $PGDATA/pg_tde.
• Higher-level keys to encrypt internal keys. These keys are called principal keys. They are stored externally, in a Key Management System (KMS) using the key provider API.

pg_tde uses one principal key per database. Every internal key for the given database is encrypted using this principal key.

Internal keys are used for specific database files: each file with a different Object Identifier (OID) has a different internal key.

This means that, for example, a table with 4 indexes will have at least 5 internal keys - one for the table, and one for each index.

If a table has additional associated relations, such as sequences or a TOAST table, those relations will also have separate keys.

Encryption algorithm

pg_tde currently uses the following encryption algorithms:

• AES-128-CBC for encrypting database files; encrypted with internal keys.

• AES-128-CTR for WAL encryption; encrypted with internal keys.

• AES-128-GCM for encrypting internal keys; encrypted with the principal key.

Support for other cipher lengths / algorithms is planned in the future.

Encryption workflow

pg_tde makes it possible to encrypt everything or only some tables in some databases.

To support this without metadata changes, encrypted tables are labeled with a tde_heap access method marker.

The tde_heap access method is the same as the heap one. It uses the same functions internally without any changes, but with the different name and ID. In such a way pg_tde knows that tde_heap tables are encrypted and heap tables are not.

The initial decision what to encrypt is made using the postgres event trigger mechanism: if a CREATE TABLE or ALTER TABLE statement uses the tde_heap clause, the newly created data files are marked as encrypted. Then file operations encrypt or decrypt the data.

Later decisions are made using a slightly modified Storage Manager (SMGR) API: when a database file is re-created with a different ID as a result of a TRUNCATE or a VACUUM FULL command, the newly created file inherits the encryption information and is either encrypted or not.

WAL encryption

WAL encryption is controlled globally via a global GUC variable, pg_tde.wal_encrypt, that requires a server restart.

WAL keys also contain the LSN of the first WAL write after key creation. This allows pg_tde to know which WAL ranges are encrypted or not and with which key.

The setting only controls writes so that only WAL writes are encrypted when WAL encryption is enabled. This means that WAL files can contain both encrypted and unencrpyted data, depending on what the status of this variable was when writing the data.

pg_tde keeps track of the encryption status of WAL records using internal keys. When the server is restarted it writes a new internal key if WAL encryption is enabled, or if it is disabled and was previously enabled it writes a dummy key signalling that WAL encryption ended.

With this information the WAL reader code can decide if a specific WAL record has to be decrypted or not and which key it should use to decrypt it.

Encrypting other access methods

Currently pg_tde only encrypts heap tables and other files such as indexes, TOAST tables, sequences that are related to the heap tables.

Indexes include any kind of index that goes through the SMGR API, not just the built-in indexes in PostgreSQL.

In theory, it is also possible to encrypt any other table access method that goes through the SMGR API by similarly providing a marker access method to it and extending the event triggers.

Storage Manager (SMGR) API

pg_tde relies on a slightly modified version of the SMGR API. These modifications include:

• Making the API generally extensible, where extensions can inject custom code into the storage manager
• Adding tracking information for files. When a new file is created for an existing relation, references to the existing file are also passed to the SMGR functions

With these modifications, the pg_tde extension can implement an additional layer on top of the normal Magnetic Disk SMGR API: if the related table is encrypted, pg_tde encrypts a file before writing it to the disk and, similarly, decrypts it after reading when needed.

Key and key provider management

Principal key rotation

You can rotate principal keys to comply with common policies and to handle situations with potentially exposed principal keys.

Rotation means that pg_tde generates a new version of the principal key, and re-encrypts the associated internal keys with the new key. The old principal key is kept as is at the same location, because it may still be needed to decrypt backups or other databases.

Internal key regeneration

Internal keys for tables, indexes and other data files are fixed once a file is created. There’s no way to re-encrypt a file.

There are workarounds for this, because operations that move the table data to a new file, such as VACUUM FULL or an ALTER TABLE that rewrites the file will create a new key for the new file, essentially rotating the internal key. This however means taking an exclusive lock on the table for the duration of the operation, which might not be desirable for huge tables.

WAL internal keys are also fixed to the respective ranges. To generate a new WAL key you need to restart the database.

Internal key storage

Internal keys and pg_tde metadata in general are kept in a single $PGDATA/pg_tde directory. This directory stores separate files for each database, such as:

• Encrypted internal keys and internal key mapping to tables
• Information about the key providers

Also, the $PGDATA/pg_tde directory has a special global section marked with the OID 1664, which includes the global key providers and global internal keys.

The global section is used for WAL encryption. Specific databases can use the global section too, for scenarios where users configure individual principal keys for databases but use the same global key provider. For this purpose, you must enable the global provider inheritance.

The global default principal key uses the special OID 1663.

Key providers (principal key storage)

Principal keys are stored externally in a Key Management Services (KMS). In pg_tdea KMS is defined as an external key provider.

The following key providers are supported:

• HashiCorp Vault KV2 secrets engine
• OpenBao implementation of Vault
• KMIP compatible servers
• A local file storage. This storage is intended only for development and testing and is not recommended for production use.

For each key provider pg_tde requires a detailed configuration including the address of the service and the authentication information.

With these details pg_tde does the following based on user operations:

• Uploads a new principal key to it after this key is created
• Retrieves the principal key from the service when it is required for decryption

Retreival of the principal key is cached so it only happens when necessary.

Key provider management

Key provider configuration or location may change. For example, a service is moved to a new address or the principal key must be moved to a different key provider type. pg_tde supports both these scenarios enabling you to manage principal keys using simple SQL functions.

In certain cases you can’t use SQL functions to manage key providers. For example, if the key provider changed while the server wasn’t running and is therefore unaware of these changes. The startup can fail if it needs to access the encryption keys.

For such situations, pg_tde also provides command line tools to recover the database.

Sensitive key provider information

Important

Authentication details for key providers are sensitive and must be protected. Do not store these credentials in the $PGDATA directory alongside the database. Instead, ensure they are stored in a secure location with strict file system permissions to prevent unauthorized access.

User interface

Setting up pg_tde

To use pg_tde, users are required to:

• Add pg_tde to the shared_preload_libraries in postgresql.conf as this is required for the SMGR extensions
• Execute CREATE EXTENSION pg_tde in the databases where they want to use encryption
• Optionally, enable pg_tde.wal_encrypt in postgresql.conf
• Optionally, disable pg_tde.inherit_global_providers in postgresql.conf (enabled by default)

Adding providers

Keyring providers can be added to either the global or to the database specific scope.

If pg_tde.inherit_global_providers is on, global providers are visible for all databases, and can be used. If pg_tde.inherit_global_providers is off, global providers are only used for WAL encryption.

To add a global provider:

pg_tde_add_global_key_provider_<TYPE>('provider_name', ... details ...)

To add a database specific provider:

pg_tde_add_database_key_provider_<TYPE>('provider_name', ... details ...)

Changing providers

To change a value of a global provider:

pg_tde_change_global_key_provider_<TYPE>('provider_name', ... details ...)

To change a value of a database specific provider:

pg_tde_change_database_key_provider_<TYPE>('provider_name', ... details ...)

These functions also allow changing the type of a provider.

The functions however do not migrate any data. They are expected to be used during infrastructure migration, for example when the address of a server changes.

Note that in these functions do not verify the parameters. For that, see pg_tde_verify_key.

Changing providers from the command line

To change a provider from a command line, pg_tde provides the pg_tde_change_key_provider command line tool.

This tool work similarly to the above functions, with the following syntax:

pg_tde_change_key_provider <dbOid> <providerType> ... details ...

Note that since this tool is expected to be offline, it bypasses all permission checks!

This is also the reason why it requires a dbOid instead of a name, as it has no way to process the catalog and look up names.

Deleting providers

Providers can be deleted by using the

pg_tde_delete_database_key_provider(provider_name)
pg_tde_delete_global_key_provider(provider_name)

functions.

For database specific providers, the function first checks if the provider is used or not, and the provider is only deleted if it’s not used.

For global providers, the function checks if the provider is used anywhere, WAL or any specific database, and returns an error if it is.

This somewhat goes against the principle that pg_tde should not interact with other databases than the one the user is connected to, but on the other hand, it only does this lookup in the internal pg_tde metadata, not in postgres catalogs, so it is a gray zone. Making this check makes more sense than potentially making some databases inaccessible.

Listing/querying providers

pg_tde provides 2 functions to show providers:

• pg_tde_list_all_database_key_providers()
• pg_tde_list_all_global_key_providers()

These functions return a list of provider names, type and configuration.

Provider permissions

pg_tde implements access control based on execution rights on the administration functions.

For keys and providers administration, it provides two pair of functions:

pg_tde_GRANT_database_key_management_TO_role
pg_tde_REVOKE_database_key_management_FROM_role

Creating and rotating keys

Principal keys can be created or rotated using the following functions:

pg_tde_set_key_using_(global/database)_key_provider('key-name', 'provider-name', ensure_new_key)
pg_tde_set_server_key_using_(global/database)_key_provider('key-name', 'provider-name', ensure_new_key)
pg_tde_set_default_key_using_(global/database)_key_provider('key-name', 'provider-name', ensure_new_key)

ensure_new_key is a boolean parameter defaulting to false. If it is true the function might return an error instead of setting the key if it already exists on the provider.

Default principal key

With pg_tde.inherit_global_key_providers, it is also possible to set up a default global principal key, which will be used by any database which has the pg_tde extension enabled, but doesn’t have a database specific principal key configured using pg_tde_set_key_using_(global/database)_key_provider.

With this feature, it is possible for the entire database server to easily use the same principal key for all databases, completely disabling multi-tenency.

Manage a default key

You can manage a default key with the following functions:

• pg_tde_set_default_key_using_global_key_provider('key-name','provider-name','true/false')
• pg_tde_delete_default_key()

Note

pg_tde_delete_default_key() is only possible if there’s no table currently using the default principal key. Changing the default principal key will rotate the encryption of internal keys for all databases using the current default principal key.

Delete a key

The pg_tde_delete_key() function removes the principal key for the current database. If the current database has any encrypted tables, and there isn’t a default principal key configured, it reports an error instead. If there are encrypted tables, but there’s also a global default principal key, internal keys will be encrypted with the default key.

Note

WAL keys cannot be deleted, as server keys are managed separately.

Current key details

pg_tde_key_info() returns the name of the current principal key, and the provider it uses.

pg_tde_server_key_info() does the same for the server key.

pg_tde_default_key_info() does the same for the default key.

pg_tde_verify_key() checks that the key provider is accessible, that the current principal key can be downloaded from it, and that it is the same as the current key stored in memory - if any of these fail, it reports an appropriate error.

Key permissions

Users with management permissions to a specific database (pg_tde_(grant/revoke)_(global/databse)_key_management_(to/from)_role) can change the keys for the database, and use the current key functions. This includes creating keys using global providers, if pg_tde.inherit_global_providers is enabled.

Also the pg_tde_(grant/revoke)_database_key_management_to_role function deals with only the specific permission for the above function: it allows a user to change the key for the database, but not to modify the provider configuration.

Creating encrypted tables

To create an encrypted table or modify an existing table to be encrypted, use the following commands:

CREATE TABLE t1(a INT) USING tde_heap;
ALTER TABLE t1 SET ACCESS METHOD tde_heap;

Changing the pg_tde.inherit_global_keys setting

It is possible for users to use pg_tde with inherit_global_keys = on, refer to global keys / keyrings in databases, and then change this setting to off.

In this case existing references to global providers, or the global default principal key will remain working as before, but new references to the global scope can’t be made.

Typical setup scenarios

Simple “one principal key” encryption

1. Passing the option from the postgres config file the extension: shared_preload_libraries=‘pg_tde’
2. CREATE EXTENSION pg_tde; in template1
3. Adding a global key provider
4. Adding a default principal key using the same global provider
5. Enable WAL encryption to use the default principal key using ALTER SYSTEM SET pg_tde.wal_encrypt=‘ON’
6. Restart the server
7. Optionally: setting the default_table_access_method to tde_heap so that tables are encrypted by default

Database users don’t need permissions to any of the encryption functions: encryption is managed by the admins, normal users only have to create tables with encryption, which requires no specific permissions.

One key storage, but different keys per database

1. Installing the extension: shared_preload_libraries + pg_tde.wal_encrypt
2. CREATE EXTENSION pg_tde; in template1
3. Adding a global key provider
4. Changing the WAL encryption to use the proper global key provider
5. Giving users that are expected to manage database keys permissions for database specific key management, but not database specific key provider management: specific databases HAVE to use the global key provider

Note: setting the default_table_access_method to tde_heap is possible, but instead of ALTER SYSTEM only per database using ALTER DATABASE, after a principal key is configured for that specific database.

Alternatively ALTER SYSTEM is possible, but table creation in the database will fail if there’s no principal key for the database, that has to be created first.

Complete multi tenancy

1. Installing the extension: shared_preload_libraries + pg_tde.wal_encrypt (that’s not multi tenant currently)
2. CREATE EXTENSION pg_tde; in any database
3. Adding a global key provider for WAL
4. Changing the WAL encryption to use the proper global key provider

No default configuration: key providers / principal keys are configured as a per database level, permissions are managed per database

Same note about default_table_access_method as above - but in a multi tenant setup, ALTER SYSTEM doesn’t make much sense.

GUC Variables

The pg_tde extension provides GUC variables to configure the behaviour of the extension:

pg_tde.wal_encrypt

Type - boolean
Default - off

A boolean variable that controls if WAL writes are encrypted or not.

Changing this variable requires a server restart, and can only be set at the server level.

WAL encryption is controlled globally. If enabled, all WAL writes are encrypted in the entire PostgreSQL cluster.

This variable only controls new writes to the WAL, it doesn’t affect existing WAL records.

pg_tde is always capable of reading existing encrypted WAL records, as long as the keys used for the encryption are still available.

Enabling WAL encryption requires a configured global principal key. Refer to the WAL encryption configuration documentation for more information.

pg_tde.enforce_encryption

Type - boolean
Default - off

A boolean variable that controls if the creation of new, not encrypted tables is allowed.

If enabled, CREATE TABLE statements will fail unless they use the tde_heap access method.

Similarly, ALTER TABLE <x> SET ACCESS METHOD is only allowed, if the access method is tde_heap.

Other DDL operations are still allowed. For example other ALTER commands are allowed on unencrypted tables, as long as the access method isn’t changed.

You can set this variable at the following levels:

• global - for the entire PostgreSQL cluster.
• database - for specific databases.
• user - for specific users.
• session - for the current session.

Setting or changing the value requires superuser permissions.

pg_tde.inherit_global_providers

Type - boolean
Default - on

A boolean variable that controls if databases can use global key providers for storing principal keys.

If disabled, functions that change the key providers can only work with database local key providers.

In this case, the default principal key, if set, is also disabled.

You can set this variable at the following levels:

• global - for the entire PostgreSQL cluster.
• database - for specific databases.
• user - for specific users.
• session - for the current session.

Setting this variable doesn’t affect existing uses of global keys. It only prevents the creation of new principal keys using global providers.

Functions

The pg_tde extension provides functions for managing different aspects of its operation:

Permission management

By default, pg_tde is locked down. No one is allowed to do any operations until you grant them permissions. Only superusers may add or alter global key providers.

However, database owners can run the “view keys” and “set principal key” functions on their own databases. You can delegate these rights to other roles with the following commands:

• GRANT EXECUTE
• REVOKE EXECUTE

Key provider management

A key provider is a system or service responsible for managing encryption keys. pg_tde supports the following key providers:

• local file (not recommended for production use)
• Hashicorp Vault / OpenBao
• KMIP compatible providers

Key provider management includes the following operations:

• creating a new key provider,
• changing an existing key provider,
• deleting a key provider,
• listing key providers.

Add a key provider

You can add a new key provider using the provided functions, which are implemented for each provider type.

There are two functions to add a key provider: one function adds it for the current database and another one - for the global scope.

• pg_tde_add_database_key_provider_<type>('provider-name', <provider specific parameters>)
• pg_tde_add_global_key_provider_<type>('provider-name', <provider specific parameters>)

When you add a new provider, the provider name must be unique in the scope. But a local database provider and a global provider can have the same name.

Change an existing provider

You can change an existing key provider using the provided functions, which are implemented for each provider type.

There are two functions to change existing providers: one to change a provider in the current database, and another one to change a provider in the global scope.

• pg_tde_change_database_key_provider_<type>('provider-name', <provider specific parameters>)
• pg_tde_change_global_key_provider_<type>('provider-name', <provider specific parameters>)

When you change a provider, the referred name must exist in the database local or a global scope.

The change functions require the same parameters as the add functions. They overwrite the setting for every parameter except for the name, which can’t be changed.

Provider specific parameters differ for each implementation. Refer to the respective subsection for details.

Some provider specific parameters contain sensitive information, such as passwords. Never specify these directly, use the remote configuration option instead.

Adding or modifying Vault providers

The Vault provider connects to a HashiCorp Vault or an OpenBao server, and stores the keys on a key-value store version 2.

Use the following functions to add the Vault provider:

SELECT pg_tde_add_database_key_provider_vault_v2(
  'provider-name',
  'url',
  'mount',
  'secret_token_path',
  'ca_path'
);

SELECT pg_tde_add_global_key_provider_vault_v2(
  'provider-name',
  'url',
  'mount',
  'secret_token_path',
  'ca_path'
);

These functions change the Vault provider:

SELECT pg_tde_change_database_key_provider_vault_v2(
  'provider-name',
  'url',
  'mount',
  'secret_token_path',
  'ca_path'
);

SELECT pg_tde_change_global_key_provider_vault_v2(
  'provider-name',
  'url',
  'mount',
  'secret_token_path',
  'ca_path'
);

where:

• provider-name is the name of the key provider
• url is the URL of the Vault server
• mount is the mount point on the Vault server where the key provider should store the keys
• secret_token_path is a path to the file that contains an access token with read and write access to the above mount point
• [optional] ca_path is the path of the CA file used for SSL verification

Adding or modifying KMIP providers

The KMIP provider uses a remote KMIP server.

Use these functions to add a KMIP provider:

SELECT pg_tde_add_database_key_provider_kmip(
  'provider-name',
  'kmip-addr',
  port,
  '/path_to/client_cert.pem',
  '/path_to/client_key.pem',
  '/path_to/server_certificate.pem'
);
SELECT pg_tde_add_global_key_provider_kmip(
  'provider-name',
  'kmip-addr',
  port,
  '/path_to/client_certificate.pem',
  '/path_to/client_key.pem',
  '/path_to/server_certificate.pem'
);

These functions change the KMIP provider:

SELECT pg_tde_change_database_key_provider_kmip(
  'provider-name',
  'kmip-addr',
  port,
  '/path_to/client_cert.pem',
  '/path_to/client_key.pem',
  '/path_to/server_certificate.pem'
);
SELECT pg_tde_change_global_key_provider_kmip(
  'provider-name',
  'kmip-addr',
  port,
  '/path_to/client_certificate.pem',
  '/path_to/client_key.pem',
  '/path_to/server_certificate.pem'
);

where:

• provider-name is the name of the provider
• kmip-addr is the IP address or domain name of the KMIP server
• port is the port to communicate with the KMIP server. Most KMIP servers use port 5696.
• server-certificate is the path to the certificate file for the KMIP server.
• client-certificate is the path to the client certificate.
• client-key is the path to the client key.

Note

The specified access parameters require permission to read and write keys at the server.

Adding or modifying local keyfile providers

This provider manages database keys using a local keyfile.

This function is intended for development or quick testing, and stores the keys unencrypted in the specified data file.

Important

Local keyfile providers are not recommended for production environments, they lack the security and manageability of external key management systems.

Add a local keyfile provider:

SELECT pg_tde_add_database_key_provider_file(
  'provider-name',
  '/path/to/the/key/provider/data.file'
);

SELECT pg_tde_add_global_key_provider_file(
  'provider-name',
  '/path/to/the/key/provider/data.file'
);

Change a local keyfile provider:

SELECT pg_tde_change_database_key_provider_file(
  'provider-name',
  '/path/to/the/key/provider/data.file'
);

SELECT pg_tde_change_global_key_provider_file(
  'provider-name',
  '/path/to/the/key/provider/data.file'
);

where:

• provider-name is the name of the provider. You can specify any name, it’s for you to identify the provider.
• /path/to/the/key/provider/data.file is the path to the key provider file.

Delete a provider

These functions delete an existing provider in the current database or in the global scope:

• pg_tde_delete_database_key_provider('provider-name)
• pg_tde_delete_global_key_provider('provider-name)

You can only delete key providers that are not currently in use. An error is returned if the current principal key is using the provider you are trying to delete.

If the use of global key providers is enabled via the pg_tde.inherit_global GUC, you can delete a global key provider only if it isn’t used anywhere, including any databases. If it is used in any database, an error is returned instead.

List key providers

These functions list the details of all key providers for the current database or for the global scope, including all configuration values:

• pg_tde_list_all_database_key_providers()
• pg_tde_list_all_global_key_providers()

Important

All configuration values include possibly sensitive values, such as passwords. Never specify these directly, use the remote configuration option instead.

Principal key management

Use these functions to create a new principal key for a specific scope such as a current database, a global or default scope. You can also use them to start using a different existing key for a specific scope.

Princial keys are stored on key providers by the name specified in this function - for example, when using the Vault provider, after creating a key named “foo”, a key named “foo” will be visible on the Vault server at the specified mount point.

pg_tde_set_key_using_database_key_provider

Creates or reuses a principal key for the current database, using the specified local key provider. It also rotates internal encryption keys to use the specified principal key.

This function is typically used when working with per-database encryption through a local key provider.

SELECT pg_tde_set_key_using_database_key_provider(
  'key-name',
  'provider-name',
  'false' -- or 'true'
);

For the third parameter (true, false, or omitted):

• true: Requires the key to be newly created. If a key with the same name already exists, the function fails.
• false (default if omitted): Reuses the existing key with that name, if present. If the key does not exist, a new key is created.

pg_tde_set_key_using_global_key_provider

Creates or rotates the global principal key using the specified global key provider and the key name. This key is used for global settings like WAL encryption.

SELECT pg_tde_set_key_using_global_key_provider(
  'key-name',
  'provider-name',
  'ensure_new_key'
);

The ensure_new_key parameter instructs the function how to handle a principal key during key rotation:

• If set to true, a new key must be unique. If the provider already stores a key by that name, the function returns an error.
• If set to false (default), an existing principal key may be reused.

pg_tde_set_server_key_using_global_key_provider

Creates or rotates the server principal key using the specified global key provider. Use this function to set a principal key for WAL encryption.

SELECT pg_tde_set_server_key_using_global_key_provider(
  'key-name',
  'provider-name',
  'ensure_new_key'
);

The ensure_new_key parameter instructs the function how to handle a principal key during key rotation:

• If set to true, a new key must be unique. If the provider already stores a key by that name, the function returns an error.
• If set to false (default), an existing principal key may be reused.

pg_tde_set_default_key_using_global_key_provider

Creates or rotates the default principal key for the server using the specified global key provider.

The default key is automatically used as a principal key by any database that doesn’t have an individual key provider and key configuration.

SELECT pg_tde_set_default_key_using_global_key_provider(
  'key-name',
  'provider-name',
  'ensure_new_key'
);

The ensure_new_key parameter instructs the function how to handle a principal key during key rotation:

• If set to true, a new key must be unique. If the provider already stores a key by that name, the function returns an error.
• If set to false (default), an existing principal key may be reused.

Encryption status check

pg_tde_is_encrypted

Tells if a relation is encrypted using the pg_tde extension or not. Returns NULL if a relation lacks storage like views, foreign tables, and partitioned tables and indexes.

To verify that a table is encrypted, run the following statement:

SELECT pg_tde_is_encrypted(
  'table_name'
);

You can also verify if the table in a custom schema is encrypted. Pass the schema name for the function as follows:

SELECT pg_tde_is_encrypted(
  'schema.table_name'
);

This can additionally be used to verify that indexes and sequences are encrypted.

pg_tde_key_info

Displays information about the principal key for the current database, if it exists.

SELECT pg_tde_key_info();

pg_tde_server_key_info

Displays information about the principal key for the server scope, if exists.

SELECT pg_tde_server_key_info();

pg_tde_default_key_info

Displays the information about the default principal key, if it exists.

SELECT pg_tde_default_key_info();

pg_tde_verify_key

This function checks that the current database has a properly functional encryption setup, which means:

• A key provider is configured
• The key provider is accessible using the specified configuration
• There is a principal key for the database
• The principal key can be retrieved from the remote key provider
• The principal key returned from the key provider is the same as cached in the server memory

If any of the above checks fail, the function reports an error.

SELECT pg_tde_verify_key();

pg_tde_verify_server_key

This function checks that the server scope has a properly functional encryption setup, which means:

• A key provider is configured
• The key provider is accessible using the specified configuration
• There is a principal key for the global scope
• The principal key can be retrieved from the remote key provider
• The principal key returned from the key provider is the same as cached in the server memory

If any of the above checks fail, the function reports an error.

SELECT pg_tde_verify_server_key();

pg_tde_verify_default_key

This function checks that the default key is properly configured, which means:

• A key provider is configured
• The key provider is accessible using the specified configuration
• There is a principal key that can be used for any scope
• The principal key can be retrieved from the remote key provider
• The principal key returned from the key provider is the same as cached in the server memory

If any of the above checks fail, the function reports an error.

SELECT pg_tde_verify_default_key();

TDE Operations

pg_tde CLI Tools

pg_tde CLI Tools

The pg_tde extension introduces new command-line utilities and extends some existing PostgreSQL tools to support encrypted WAL and tables. These include:

• pg_tde_change_key_provider: change encryption key provider for a database
• pg_waldump: inspect and decrypt WAL files
• pg_checksums: verify data checksums (non-encrypted files only)

pg_tde_change_key_provider

A tool for modifying the configuration of a key provider, possibly also changing its type.

This tool edits the configuration files directly, ignoring permissions or running postgres processes.

Its only intended use is to fix servers that can’t start up because of inaccessible key providers.

For example, you restore from an old backup and the address of the key provider changed in the meantime. You can use this tool to correct the configuration, allowing the server to start up.

Warning: Use this tool only when the server is offline. To modify the key provider configuration when the server is up and running, use the pg_tde_change_(global/database)_key_provider_<type> SQL functions.

Example usage

To modify the key provider configuration, specify all parameters depending on the provider type in the same way as you do when using the pg_tde_change_(global/database)_key_provider_<type> SQL functions.

The general syntax is as follows:

pg_tde_change_key_provider [-D <datadir>] <dbOid> <provider_name> <new_provider_type> <provider_parameters...>

Parameter description

• [optional] <datadir> is the data directory.pg_tde uses the $PGDATA environment variable if this is not specified
• <provider_name> is the name you assigned to the key provider
• <new_provider_type> can be a file, vault or kmip
• <dbOid>

Depending on the provider type, the additional parameters are:

pg_tde_change_key_provider [-D <datadir>] <dbOid> <provider_name> file <filename>
pg_tde_change_key_provider [-D <datadir>] <dbOid> <provider_name> vault-v2 <url> <mount_path> <token_path> [<ca_path>]
pg_tde_change_key_provider [-D <datadir>] <dbOid> <provider_name> kmip <host> <port> <cert_path> <key_path> [<ca_path>] 

pg_waldump

pg_waldump is a tool to display a human-readable rendering of the Write-Ahead Log (WAL) of a PostgreSQL database cluster.

To read encrypted WAL records, pg_waldump supports the following additional arguments:

• keyring_path is the directory where the keyring configuration files for WAL are stored. The following files are included:
  • 1664_keys
  • 1664_providers

Note

pg_waldump does not decrypt WAL unless the keyring_path is set.

pg_checksums

pg_checksums is a PostgreSQL command-line utility used to enable, disable, or verify data checksums on a PostgreSQL data directory. However, it cannot calculate checksums for encrypted files.

Encrypted files are skipped, and this is reported in the output.

Uninstall pg_tde

If you no longer wish to use TDE in your deployment, you can remove the pg_tde extension. To do so, your user must have the superuser privileges, or a database owner privileges in case you only want to remove it from a single database.

Here’s how to do it:

1. Drop the extension using the DROP EXTENSION command:

   DROP EXTENSION pg_tde;
   

   This command will fail if there are still encrypted tables in the database.

   In this case, you must drop the dependent objects manually. Alternatively, you can run the DROP EXTENSION ... CASCADE command to drop all dependent objects automatically.

   Note that the DROP EXTENSION command does not delete the pg_tde data files related to the database.

2. Run the DROP EXTENSION command against every database where you have enabled the pg_tde extension, if the goal is to completely remove the extension. This also includes the template databases, in case pg_tde was previously enabled there.

3. Remove any reference to pg_tde GUC variables from the PostgreSQL configuration file.

4. Modify the shared_preload_libraries and remove the ‘pg_tde’ from it. Use the ALTER SYSTEM command for this purpose, or edit the configuration file.

   Warning

   Once pg_tde is removed from the shared_preload_libraries, reading any leftover encrypted files will fail. Removing the extension from the shared_preload_libraries is also possible if the extension is still installed in some databases.

   Make sure to do this only if the server has no encrypted files in its data directory.

5. Start or restart the postgresql cluster to apply the changes.

   • On Debian and Ubuntu:

   sudo systemctl restart postgresql
   

   • On RHEL and derivatives

   sudo systemctl restart postgresql-17
   

Configure Multi-tenancy

The steps below describe how to set up multi-tenancy with pg_tde. Multi-tenancy allows you to encrypt different databases with different keys. This provides granular control over data and enables you to introduce different security policies and access controls for each database so that only authorized users of specific databases have access to the data.

If you don’t need multi-tenancy, use the global key provider. See the configuration steps from the Configure pg_tde section.

For how to enable WAL encryption, refer to the Configure WAL Encryption section.

Considerations

You can use external key providers to manage encryption keys. The recommended approach is to use the Key Management Store (KMS). For more information, see Configure Key Management (KMS).

Enable extension

Load the pg_tde at startup time. The extension requires additional shared memory; therefore, add the pg_tde value for the shared_preload_libraries parameter and restart the postgresql cluster.

1. Use the ALTER SYSTEM command from psql terminal to modify the shared_preload_libraries parameter. This requires superuser privileges.

   ALTER SYSTEM SET shared_preload_libraries = 'pg_tde';
   

2. Start or restart the postgresql cluster to apply the changes.

   • On Debian and Ubuntu:

   sudo systemctl restart postgresql-17
   

   • On RHEL and derivatives

   sudo systemctl restart postgresql-17
   

3. Create the extension using the CREATE EXTENSION command. You must have the privileges of a superuser or a database owner to use this command. Connect to psql as a superuser for a database and run the following command:

   CREATE EXTENSION pg_tde;
   

   The pg_tde extension is created for the currently used database. To enable data encryption in other databases, you must explicitly run the CREATE EXTENSION command against them.

   Tip

   You can have the pg_tde extension automatically enabled for every newly created database. Modify the template template1 database as follows:

   psql -d template1 -c 'CREATE EXTENSION pg_tde;'
   

Key provider configuration

You must do these steps for every database where you have created the extension.

1. Set up a key provider.

   With KMIP serverWith HashiCorp VaultWith a keyring file (not recommended)

   Make sure you have obtained the root certificate for the KMIP server and the keypair for the client. The client key needs permissions to create / read keys on the server. Find the configuration guidelines for the HashiCorp Vault Enterprise KMIP Secrets Engine.

   For testing purposes, you can use the PyKMIP server which enables you to set up required certificates. To use a real KMIP server, make sure to obtain the valid certificates issued by the key management appliance.

   SELECT pg_tde_add_database_key_provider_kmip('provider-name','kmip-addr', 5696, '/path_to/client_cert.pem', '/path_to/client_key.pem', '/path_to/server_certificate.pem');
   

   where:

   • provider-name is the name of the provider. You can specify any name, it’s for you to identify the provider.
   • kmip-addr is the IP address of a domain name of the KMIP server
   • port is the port to communicate with the KMIP server. Typically used port is 5696.
   • server-certificate is the path to the certificate file for the KMIP server.
   • client-cert is the path to the client certificate.
   • client-key is the path to the client key.

   Warning: This example is for testing purposes only:

   SELECT pg_tde_add_database_key_provider_kmip('kmip', '127.0.0.1', 5696, '/tmp/client_cert_jane_doe.pem', '/tmp/client_key_jane_doe.pem', '/tmp/server_certificate.pem');
   

   The Vault server setup is out of scope of this document.

   SELECT pg_tde_add_database_key_provider_vault_v2('provider-name', 'url', 'mount', 'secret_token_path', 'ca_path');
   

   where:

   • url is the URL of the Vault server
   • mount is the mount point where the keyring should store the keys
   • secret_token_path is a path to the file that contains an access token with read and write access to the above mount point
   • [optional] ca_path is the path of the CA file used for SSL verification

   Warning: This example is for testing purposes only:

   SELECT pg_tde_add_database_key_provider_file_vault_v2('my-vault','http://vault.vault.svc.cluster.local:8200,'secret/data','hvs.zPuyktykA...example...ewUEnIRVaKoBzs2', NULL);
   

   This setup is intended for development and stores the keys unencrypted in the specified data file.

   SELECT pg_tde_add_database_key_provider_file('provider-name', '/path/to/the/keyring/data.file');
   

   Warning: This example is for testing purposes only:

   SELECT pg_tde_add_database_key_provider_file('file-keyring', '/tmp/pg_tde_test_local_keyring.per');
   

2. Add a principal key

   SELECT pg_tde_set_key_using_database_key_provider('name-of-the-key', 'provider-name','ensure_new_key');
   

   where:

   • name-of-the-key is the name of the principal key. You will use this name to identify the key.
   • provider-name is the name of the key provider you added before. The principal key will be associated with this provider.
   • ensure_new_key defines if a principal key must be unique. The default value true means that you must speficy a unique key during key rotation. The false value allows reusing an existing principal key.

   Warning: This example is for testing purposes only:

   SELECT pg_tde_set_key_using_database_key_provider('test-db-master-key','file-vault','ensure_new_key');
   

   Note

   The key is auto-generated.

Remove Encryption from an Encrypted Table

Method 1. Change the access method

If you encrypted a table with the tde_heap access method and need to remove the encryption from it, run the following command against the desired table (mytable in the example below):

ALTER TABLE mytable SET ACCESS METHOD heap;

Note that the SET ACCESS METHOD command drops hint bits and this may affect performance. Running a plain SELECT count(*) or VACUUM command on the entire table will check every tuple for visibility and set its hint bits. Therefore, after executing the ALTER TABLE command, run a simple count(*) on your tables:

SELECT count(*) FROM mytable;

Check that the table is not encrypted:

SELECT pg_tde_is_encrypted('mytable');

The output returns f meaning that the table is no longer encrypted.

Method 2. Create a new not encrypted table on the base of the encrypted one

Alternatively, you can create a new not encrypted table with the same structure and data as the initial table. For example, the original encrypted table is EncryptedCustomers. Use the following command to create a new table Customers:

CREATE TABLE Customers AS
SELECT * FROM EncryptedCustomers;

The new table Customers inherits the structure and the data from EncryptedCustomers.

(Optional) If you no longer need the EncryptedCustomers table, you can delete it.

DROP TABLE EncryptedCustomers;

FAQ

Why do I need TDE?

Using TDE provides the following benefits:

• Compliance to security and legal regulations like General Data Protection Regulation (GDPR), Payment Card Industry Data Security Standard (PCI DSS), California Consumer Privacy Act (CCPA), Data Protection Act 2018 (DPA 2018) and others
• Encryption of backups. Even when an authorized person gets physical access to a backup, encryption ensures that the data remains unreadable and secure.
• Granular encryption of specific data sets and reducing the performance overhead that encryption brings.
• Additional layer of security to existing security measures

When and how should I use TDE?

If you are dealing with Personally Identifiable Information (PII), data encryption is crucial. Especially if you are involved in areas with strict regulations like:

• financial services where TDE helps to comply with PCI DSS
• healthcare and insurance - compliance with HIPAA, HITECH, CCPA
• telecommunications, government and education to ensure data confidentiality.

Using TDE helps you avoid the following risks:

• Data breaches
• Identity theft that may lead to financial fraud and other crimes
• Reputation damage leading to loss of customer trust and business
• Legal consequences and financial losses for non-compliance with data protection regulations
• Internal threats by misusing unencrypted sensitive data

If to translate sensitive data to files stored in your database, these are user data in tables, temporary files, WAL files. TDE has you covered encrypting all these files.

pg_tde does not encrypt system catalogs yet. This means that statistics data and database metadata are not encrypted. The encryption of system catalogs is planned for future releases.

I use disk-level encryption. Why should I care about TDE?

Encrypting a hard drive encrypts all data, including system, application, and temporary files.

Full disk encryption protects your data from people who have physical access to your device and even if it is lost or stolen. However, it doesn’t protect the data after system boot-up: the data is automatically decrypted when the system runs or when an authorized user requests it.

Another point to consider is PCI DSS compliance for Personal Account Numbers (PAN) encryption.

• PCI DSS 3.4.1 standards might consider disk encryption sufficient for compliance if you meet these requirements:

• Separate the logical data access from the operating system authentication.

• Ensure the decryption key is not linked to user accounts.

Note that PCI DSS 3.4.1 is retiring on March 31, 2025. Therefore, consider switching to PCI DSS 4.0.

• PCI DSS 4.0 standards consider using only disk and partition-level encryption not enough to ensure PAN protection. It requires an additional layer of security that pg_tde can provide.

pg_tde focuses specifically on data files and offers more granular control over encrypted data. The data remains encrypted on disk during runtime and when you move it to another directory, another system or storage. An example of such data is backups. They remain encrypted when moved to the backup storage.

Thus, to protect your sensitive data, consider using TDE to encrypt it at the table level. Then use disk-level encryption to encrypt a specific volume where this data is stored, or the entire disk.

Is TDE enough to ensure data security?

No. Transparent Data Encryption (TDE) adds an extra layer of security for data at rest. You should also consider implementing the following additional security features:

• Access control and authentication
• Strong network security like TLS
• Disk encryption
• Regular monitoring and auditing
• Additional data protection for sensitive fields (e.g., application-layer encryption)

How does pg_tde make my data safe?

pg_tde uses two keys to encrypt data:

• Internal encryption keys to encrypt the data. These keys are stored internally in an encrypted format, in a single $PGDATA/pg_tde directory.
• Principal keys to encrypt internal encryption keys. These keys are stored externally, in the Key Management System (KMS).

You can use the following KMSs:

• HashiCorp Vault. pg_tde supports the KV secrets engine v2 of Vault.
• OpenBao implementation of Vault
• KMIP-compatible server. KMIP is a standardized protocol for handling cryptographic workloads and secrets management

HashiCorp Vault can also act as the KMIP server, managing cryptographic keys for clients that use the KMIP protocol.

Let’s break the encryption into two parts:

Encryption of data files

First, data files are encrypted with internal keys. Each file that has a different Object Identifier (OID) has an internal key. For example, a table with 4 indexes will have 5 internal keys - one for the table and one for each index.

The initial decision on what file to encrypt is based on the table access method in PostgreSQL. When you run a CREATE or ALTER TABLE statement with the USING tde_heap clause, the newly created data files are marked as encrypted, and then file operations encrypt or decrypt the data. Later, if an initial file is re-created as a result of a TRUNCATE or VACUUM FULL command, the newly created file inherits the encryption information and is either encrypted or not.

The principal key is used to encrypt the internal keys. The principal key is stored in the key management store. When you query the table, the principal key is retrieved from the key store to decrypt the table. Then the internal key for that table is used to decrypt the data.

WAL encryption

WAL encryption is done globally for the entire database cluster. All modifications to any database within a PostgreSQL cluster are written to the same WAL to maintain data consistency and integrity and ensure that PostgreSQL cluster can be restored to a consistent state. Therefore, WAL is encrypted globally.

When you turn on WAL encryption, pg_tde encrypts entire WAL files starting from the first WAL write after the server was started with the encryption turned on.

The same 2-key approach is used with WAL as with the table data: WAL pages are first encrypted with the internal key. Then the internal key is encrypted with the global principal key.

You can turn WAL encryption on and off so WAL can contain both encrypted and unencrypted data. The WAL encryption GUC variable influences only writes.

Whenever the WAL is being read (by the recovery process or tools), the decision on what should be decrypted is based solely on the metadata of WAL encryption keys.

Should I encrypt all my data?

It depends on your business requirements and the sensitivity of your data. Encrypting all data is a good practice but it can have a performance impact.

Consider encrypting only tables that store sensitive data. You can decide what tables to encrypt and with what key. The Set up multi-tenancy section in the documentation focuses on this approach.

We advise encrypting the whole database only if all your data is sensitive, like PII, or if there is no other way to comply with data safety requirements.

What cipher mechanisms are used by pg_tde?

pg_tde currently uses a AES-CBC-128 algorithm. First the internal keys in the datafile are encrypted using the principal key with AES-CBC-128, then the file data itself is again encrypted using AES-CBC-128 with the internal key.

For WAL encryption, AES-CTR-128 is used.

The support of other encryption mechanisms such as AES256 is planned for future releases. Reach out to us with your requirements and usage scenarios of other encryption methods are needed.

Is post-quantum encryption supported?

No, it’s not yet supported. In our implementation we reply on OpenSSL libraries that don’t yet support post-quantum encryption.

Can I encrypt an existing table?

Yes, you can encrypt an existing table. Run the ALTER TABLE command as follows:

ALTER TABLE table_name SET ACCESS METHOD tde_heap;

Since the SET ACCESS METHOD command drops hint bits and this may affect the performance, we recommend to run the SELECT count(*) command. It checks every tuple for visibility and sets its hint bits. Read more in the Changing existing table section.

Do I have to restart the database to encrypt the data?

You must restart the database in the following cases to apply the changes:

• after you enabled the pg_tde extension
• to turn on / off the WAL encryption

After that, no database restart is required. When you create or alter the table using the tde_heap access method, the files are marked as those that require encryption. The encryption happens at the storage manager level, before a transaction is written to disk. Read more about how tde_heap works.

What happens to my data if I lose a principal key?

If you lose encryption keys, especially, the principal key, the data is lost. That’s why it’s critical to back up your encryption keys securely and use the Key Management service for key management.

Can I use pg_tde in a multi-tenant setup?

Multi-tenancy is the type of architecture where multiple users, or tenants, share the same resource. It can be a database, a schema or an entire cluster.

In pg_tde, multi-tenancy is supported via a separate principal key per database. This means that a database owner can decide what tables to encrypt within a database. The same database can have both encrypted and non-encrypted tables.

To control user access to the databases, you can use role-based access control (RBAC).

WAL files are encrypted globally across the entire PostgreSQL cluster using the same encryption keys. Users don’t interact with WAL files as these are used by the database management system to ensure data integrity and durability.

Are my backups safe? Can I restore from them?

pg_tde encrypts data at rest. This means that data is stored on disk in an encrypted form. During a backup, already encrypted data files are copied from disk onto the storage. This ensures the data safety in backups.

Since the encryption happens on the database level, it makes no difference for your tools and applications. They work with the data in the same way.

To restore from an encrypted backup, you must have the same principal encryption key, which was used to encrypt files in your backup.

I’m using OpenSSL in FIPS mode and need to use pg_tde. Does pg_tde comply with FIPS requirements? Can I use my own FIPS-mode OpenSSL library with pg_tde?

Yes. pg_tde works with the FIPS-compliant version of OpenSSL, whether it is provided by your operating system or if you use your own OpenSSL libraries. If you use your own libraries, make sure they are FIPS certified.

Release Notes

pg_tde release notes index

pg_tde extension brings in Transparent Data Encryption (TDE) to PostgreSQL and enables you to keep sensitive data safe and secure.

• pg_tde Release Candidate 2 (RC2) (2025-05-29)
• pg_tde Release Candidate (2025-03-27)
• pg_tde Beta2 (2024-12-16)
• pg_tde Beta (2024-06-30)
• pg_tde Alpha1 (2024-03-28)
• pg_tde MVP (2023-12-12)

pg_tde Release Candidate 2 (2025-05-29)

pg_tde extension brings in Transparent Data Encryption (TDE) to PostgreSQL and enables you to keep sensitive data safe and secure.

Get Started

Release Highlights

This release provides the following features and improvements:

• Restricted key provider configuration to superusers

  The database owners can no longer configure key providers directly. Instead, they must refer to the superuser who manages the provider setup. This security improvement clearly separates the responsibilities between users and administrators.

• WAL encryption supports Vault.

  pg_tde now supports using the Vault keyring for secure storage and management of WAL encryption keys.

• Automatic WAL internal key generation at server startup.

  On each server start, a new internal key is generated for encrypting subsequent WAL records (assuming WAL encryption is enabled). The existing WAL records and their keys remain unchanged, this ensures continuity and secure key management without affecting historical data.

• Proper removal of relation-level encryption keys on table drop

  Previously, encrypted relation keys persisted even after dropping the associated tables, potentially leaving orphaned entries in the map file. This is now corrected, when an encrypted table is dropped, its corresponding key is also removed from the key map.

• Fixed external tablespace data loss with encrypted partitions

  An issue was fixed where data could be lost when the encrypted partitioned tables were moved to external tablespaces.

• New visibility and verification functions for default principal keys

  Added additional functions to help you verify and inspect the state of default principal keys more easily.

• Fixed SQL failures caused by inconsistent key provider switching

  An issue was resolved where SQL queries could fail after switching key providers while the server was running. This occurred because principal keys became inaccessible when spread across multiple keyring backends, triggering the single-provider-at-a-time design constraint. pg_tde now enforces consistency during provider changes to prevent a corrupted key state and query errors.

Upgrade considerations

pg_tde Release Candidate 2 is not backward compatible with pg_tde Beta2 due to significant changes in code. This means you cannot directly upgrade from one version to another. You must uninstall pg_tde Beta2 first and then install and configure the new Release Candidate version.

Known issues

• The default mlock limit on Rocky Linux 8 for ARM64-based architectures equals the memory page size and is 64 Kb. This results in the child process with pg_tde failing to allocate another memory page because the max memory limit is reached by the parent process.

To prevent this, you can change the mlock limit to be at least twice bigger than the memory page size:

• temporarily for the current session using the ulimit -l <value> command.
• set a new hard limit in the /etc/security/limits.conf file. To do so, you require the superuser privileges.

Adjust the limits with caution since it affects other processes running in your system.

Changelog

New Features

• PG-817 – Added fuzz testing to pstress to strengthen validation and resilience.
• PG-824 – Ensured fsync is called on pg_tde.map, pg_tde.dat, and FS key provider files.
• PG-830 – Implemented full WAL encryption using Vault keyring.
• PG-831 – Tested WAL recovery and both streaming and logical replication compatibility.
• PG-855 – Added a contributor guide to help new developers engage with pg_tde.
• PG-938 – Evaluated use of pg_basebackup for automated backup validation with pg_tde.
• PG-962 – Automated test cases to validate data integrity after PostgreSQL restart.
• PG-1001 – Verified encryption behavior of temporary tables.
• PG-1099 – Developed automation for bare-metal performance benchmarking.
• PG-1289 – Added test cases for verifying compatibility with different PostgreSQL versions.
• PG-1444 – Implemented support for removing relation-level encryption keys when dropping tables.
• PG-1455 – Introduced random base numbers in encryption IVs for enhanced security.
• PG-1458 – Added visibility and verification functions for default principal keys.
• PG-1460 – Enabled automatic rotation of WAL internal keys on server start.
• PG-1461 – Implemented random IV initialization for WAL keys.
• PG-1506 – Added parameter support for client certificates in KMIP provider configuration.

Improvements

• PG-826 – Documented how to encrypt and decrypt existing tables using pg_tde.
• PG-827 – Fixed CI pipeline tests on the smgr branch.
• PG-834 – Resolved issues with CREATE ... USING pg_tde on the smgr branch.
• PG-1427 – Tested and fixed KMIP implementation for Thales support.
• PG-1507 – Handled ALTER TYPE operations in the TDE event trigger.
• PG-1508 – Fixed encryption state inconsistencies when altering inherited tables.
• PG-1550 – Restricted database owners from creating key providers to improve security.
• PG-1586 – Verified and fixed KMIP compatibility with Fortanix HSM.

Bugs Fixed

• PG-1397 – Fixed segmentation fault during replication with WAL encryption enabled.
• PG-1413 – Resolved invalid WAL magic number errors after toggling encryption.
• PG-1416 – Fixed SQL query failures caused by inconsistent key provider switching.
• PG-1468 – Fixed WAL read failures on replicas after key rotation.
• PG-1491 – Corrected pg_tde_is_encrypted() behavior for partitioned tables.
• PG-1493 – Fixed data loss when encrypted partitioned tables were moved to external tablespaces.
• PG-1503 – Blocked deletion of global key providers still associated with principal keys.
• PG-1504 – Ensured correct encryption inheritance in partitioned tde_heap tables.
• PG-1510 – Used different keys and IVs for PostgreSQL forks to prevent conflicts.
• PG-1530 – Fixed inability to read WAL after toggling WAL encryption.
• PG-1532 – Resolved errors rewriting owned sequences when pg_tde isn’t in the default schema.
• PG-1535 – Prevented server crash on calling pg_tde_principal_key_info().
• PG-1537 – Fixed crash on NULL input in user-facing functions.
• PG-1539 – Handled principal key header verification errors gracefully.
• PG-1540 – Ensured sequences are assigned correct encryption status.
• PG-1541 – Resolved WAL decryption failure after key rotation.
• PG-1543 – Fixed validation error when multiple server keys exist.
• PG-1545 – Resolved error from pg_tde_grant_grant_management_to_role() execution.
• PG-1546 – Fixed incorrect behavior in role grant function.
• PG-1551 – Improved handling of short reads and errors in WAL storage code.
• PG-1571 – Fixed WAL decryption failure due to corrupted or mismatched principal keys.
• PG-1573 – Prevented crash during WAL replay when lock was not held.
• PG-1574 – Ensured encrypted WAL is readable by streaming replica.
• PG-1576 – Resolved crash from malformed JSON in user-facing functions.

pg_tde Release Candidate 1 (2025-03-27)

pg_tde extension brings in Transparent Data Encryption (TDE) to PostgreSQL and enables you to keep sensitive data safe and secure.

Get started

Release Highlights

This release provides the following features and improvements:

• Improved performance with redesigned WAL encryption.

  The approach to WAL encryption has been redesigned. Now, pg_tde encrypts entire WAL files starting from the first WAL write after the server was started with the encryption turned on. The information about what is encrypted is stored in the internal key metadata. This change improves WAL encryption flow with native replication and increases performance for large scale databases.

• Default encryption key for single-tenancy.

  The new functionality allows you to set a default principal key for the entire database cluster. This key is used to encrypt all databases and tables that do not have a custom principal key set. This feature simplifies encryption configuration and management in single-tenant environments where each user has their own database instance.

• Ability to change key provider configuration

  You no longer need to configure a new key provider and set a new principal key if the provider’s configuration changed. Now can change the key provider configuration both for the current database and the entire PostgreSQL cluster using functions. This enhancement lifts existing limitations and is a native and common way to operate in PostgreSQL.

• Key management permissions

  The new functions allow you to manage permissions for global and database key management separately. This feature provides more granular control over key management operations and allows you to delegate key management tasks to different roles.

• Additional information about principal keys and providers

  The new functions allow you to display additional information about principal keys and providers. This feature helps you to understand the current key configuration and troubleshoot issues related to key management.

• tde_heap_basic access method deprecation

  The tde_heap_basic access method has limitations in encryption capabilities and affects performance. Also, it poses a potential security risk when used in production environments due to indexes remaining unencrypted. Considering all the above, we decided to deprecate this access method and remove it in future releases. Use the tde_heap access method instead that is available with Percona Server for PostgreSQL 17 - a drop-in replacement for PostgreSQL Community.

Upgrade considerations

pg_tde Release Candidate is not backward compatible with pg_tde Beta2 due to significant changes in code. This means you cannot directly upgrade from one version to another. You must uninstall pg_tde Beta2 first and then install and configure the new Release Candidate version.

Known issues

• The default mlock limit on Rocky Linux 8 for ARM64-based architectures equals the memory page size and is 64 Kb. This results in the child process with pg_tde failing to allocate another memory page because the max memory limit is reached by the parent process.

  To prevent this, you can change the mlock limit to be at least twice bigger than the memory page size:

  • temporarily for the current session using the ulimit -l <value> command.
  • set a new hard limit in the /etc/security/limits.conf file. To do so, you require the superuser privileges.

  Adjust the limits with caution since it affects other processes running in your system.

• You can now delete global key providers even when their associated principal key is still in use. This known issue will be fixed in the next release. For now, avoid deleting global key providers.

Changelog

New Features

• PG-1234 - Added functions for separate global and database key management permissions.

• PG-1255 - Added functionality to delete key providers.

• PG-1256 - Added single-tenant support via the default principal key functionality.

• PG-1258 - Added functions to display additional information about principal keys / providers.

• PG-1294 - Redesigned WAL encryption.

• PG-1303 - Deprecated tde_heap_basic access method.

Improvements

• PG-858 - Refactored internal/principal key LWLocks to make local databases inherit a global key provider.

• PG-1243 - Investigated performance issues at a specific threshold and large databases and updated documentation about handling hint bits.

• PG-1310 - Added access method enforcement via the GUC variable.

• PG-1361 - Fixed pg_tde relocatability.

• PG-1380 - Added support for pg_tde_is_encrypted() function on indexes and sequences.

Bugs Fixed

• PG-821 - Fixed the issue with pg_basebackup failing when configuring replication.

• PG-847 - Fixed the issue with pg_basebackup and pg_checksum throwing an error on files created by pg_tde when the checksum is enabled on the database cluster.

• PG-1004 - Fixed the issue with pg_checksums utility failing during checksum verification on pg_tde tables. Now pg_checksum skips encrypted relations by looking if the relation has a custom storage manager (SMGR) key.

• PG-1373 - Fixed the issue with potential unterminated strings by using the memcpy() or strlcpy() instead of the strncpy() function.

• PG-1378 - Fixed the issue with toast tables created by the ALTER TABLE command not being encrypted.

• PG-1379 - Fixed sequence and alter table handling in the event trigger.

• PG-1222 - Fixed the bug with confused relations with the same RelFileNumber in different databases.

• PG-1400 - Corrected the pg_tde_change_key_provider naming in help.

• PG-1401 - Fixed the issue with inheriting an encryption status during the ALTER TABLE SET access method command execution by basing a new encryption status only on the new encryption setting.

• PG-1414 - Fixed the error message wording when configuring WAL encryption by referencing to a correct function.

• PG-1450 - Fixed the pg_tde_delete_key_provider() function behavior when called multiple times by ignoring already deleted records.

• PG-1451 -Fixed the issue with the repeating error message about inability to retrieve a principal key even when a user creates non-encrypted tables by checking the current transaction ID in both the event trigger start function and during a file creation. If the transaction changed during the setup of the current event trigger data, the event trigger is reset.

• PG-1473 - Allowed only users with key viewer privileges to execute pg_tde_verify_principal_key() and pg_tde_verify_global_principal_key() functions.

• PG-1474 - Fixed the issue with the principal key reference corruption when reassigning it to a key provider with the same name by setting the key name in vault/kmip getters.

• PG-1476 - Fixed the issue with the server failing to start when WAL encryption is enabled by creating a new principal key for WAL in case only one default key exists in the database.

• PG-1479, PG-1480 - Fixed the issue with the lost access to data after the global key provider change and the server restart by fixing the incorrect parameter order in default key rotation.

• PG-1489 - Fixed the issue with replicating the keys and key provider configuration by creating the pg_tde directory on the replica server. /browse/PG-1476) - Fixed the issue with the server failing to start when WAL encryption is enabled by creating a new principal key for WAL in case only one default key exists in the database.

• PG-1479, PG-1480 - Fixed the issue with the lost access to data after the global key provider change and the server restart by fixing the incorrect parameter order in default key rotation.

• PG-1489 - Fixed the issue with replicating the keys and key provider configuration by creating the pg_tde directory on the replica server.

pg_tde Beta 2 (2024-12-16)

pg_tde extension brings in Transparent Data Encryption (TDE) to PostgreSQL and enables you to keep sensitive data safe and secure.

Get started

Important

This version of Percona Transparent Data Encryption extension is not recommended for production environments yet. We encourage you to test it and give your feedback.

This will help us improve the product and make it production-ready faster.

Release Highlights

With this release, pg_tde extension offers two database specific versions:

• PostgreSQL Community version provides only the tde_heap_basic access method using which you can introduce table encryption and WAL encryption for data in the encrypted tables. Index data remains unencrypted.
• Version for Percona Server for PostgreSQL provides the tde_heapaccess method. using this method you can encrypt index data in encrypted tables thus increasing the safety of your sensitive data. For backward compatibility, the tde_heap_basic method is available in this version too.

Changelog

The Beta 2 version introduces the following features and improvements:

New Features

• Added the tde_heap access method with which you can now enable index encryption for encrypted tables and global WAL data encryption. To use this access method, you must install Percona Server for PostgreSQL. Check the installation guide
• Added event triggers to identify index creation operations on encrypted tables and store those in a custom storage.
• Added support for secure transfer of keys using the OASIS Key Management Interoperability Protocol (KMIP). The KMIP implementation was tested with the PyKMIP server and the HashiCorp Vault Enterprise KMIP Secrets Engine.

Improvements

• WAL encryption improvements:

• Added a global key to encrypt WAL data in global space

• Added WAL key management

• Keyring improvements:

  • Renamed functions to point their usage for principal key management
  • Improved keyring provider management across databases and the global space.
  • Keyring configuration now uses common JSON API. This simplifies code handling and enables frontend tools like pg_waldump to read the code thus improving debugging.

• The pg_tde_is_encrypted function now supports custom schemas in the format of pg_tde_is_encrypted('schema.table');

• Changed the location of internal TDE files: instead of the database directory, now all files are stored in $PGDATA/pg_tde
• Improved error reporting when pg_tde is not added to the shared_preload_libraries
• Improved memory usage of tde_heap_basicduring sequential reads
• Improved tde_heap_basic for select statements
• Added encryption support for (some) command line utilities

Bugs fixed

• Fixed multiple bugs with tde_heap_basic and TOAST records
• Fixed various memory leaks

pg_tde Beta (2024-06-30)

pg_tde extension brings in Transparent Data Encryption (TDE) to PostgreSQL and enables you to keep sensitive data safe and secure.

Get started

Important

This version of Percona Transparent Data Encryption extension is not recommended for production environments yet. We encourage you to test it and give your feedback.

This will help us improve the product and make it production-ready faster.

Release Highlights

Starting with pg_tde Beta, the access method for pg_tde extension is renamed tde_heap_basic. Use this access method name to create tables. Find guidelines in Test TDE tutorial.

Changelog

• Fixed the issue with pg_tde running out of memory used for decrypted tuples. The fix introduces the new component TDEBufferHeapTupleTableSlot that keeps track of the allocated memory for decrypted tuples and frees this memory when the tuple slot is no longer needed.

• Fixed the issue with adjusting a current position in a file by using raw file descriptor for the lseek function. (Thanks to user rainhard for providing the fix)

• Enhanced the init script to consider a custom superuser for the POSTGRES_USER parameter when pg_tde is running via Docker (Thanks to Alejandro Paredero for reporting the issue)

pg_tde Alpha 1 (2024-03-28)

pg_tde extension brings in Transparent Data Encryption (TDE) to PostgreSQL and enables you to keep sensitive data safe and secure.

Get started

Important

This version of Percona Transparent Data Encryption extension is not recommended for production environments yet. We encourage you to test it and give your feedback.

This will help us improve the product and make it production-ready faster.

Release Highlights

The Alpha1 version of the extension introduces the following key features:

• You can now rotate principal keys used for data encryption. This reduces the risk of long-term exposure to potential attacks and helps you comply with security standards such as GDPR, HIPAA, and PCI DSS.

• You can now configure encryption differently for each database. For example, encrypt specific tables in some databases with different encryption keys while keeping others non-encrypted.

• Keyring configuration has undergone several improvements, namely:

  • You can define separate keyring configuration for each database
  • You can change keyring configuration dynamically, without having to restart the server
  • The keyring configuration is now stored in a catalog separately for each database, instead of a configuration file
  • Avoid storing secrets in the unencrypted catalog by configuring keyring parameters to be read from external sources (file, http(s) request)

Improvements

• Renamed the repository and Docker image from postgres-tde-ext to pg_tde. The extension name remains unchanged
• Changed the Initialization Vector (IV) calculation of both the data and internal keys

Bugs fixed

• Fixed toast related crashes
• Fixed a crash with the DELETE statement
• Fixed performance-related issues
• Fixed a bug where pg_tde sent many 404 requests to the Vault server
• Fixed сompatibility issues with old OpenSSL versions
• Fixed сompatibility with old Curl versions

pg_tde MVP (2023-12-12)

The Minimum Viable Product (MVP) version of pg_tde introduces the following functionality:

• Encryption of heap tables, including TOAST
• Encryption keys are stored either in Hashicorp Vault server or in local keyring file (for development)
• The key storage is configurable via separate JSON configuration files
• Replication support

Contributing Guide

Welcome to pg_tde - the Transparent Data Encryption extension for PostgreSQL!

We’re glad that you would like to become a community member and contribute to this project.

You can contribute in one of the following ways:

1. Reach us on our Forums.
2. Submit a bug report or a feature request
3. Submit a pull request (PR) with the code patch
4. Contribute to documentation

Prerequisites

Before submitting code contributions, we ask you to complete the following prerequisites.

1. Sign the CLA

Before you can contribute, we kindly ask you to sign our Contributor License Agreement (CLA). You can do this in on click using your GitHub account.

Note: You can sign it later, when submitting your first pull request. The CLA assistant validates the PR and asks you to sign the CLA to proceed.

2. Code of Conduct

Please make sure to read and agree to our Code of Conduct.

Submitting a pull request

All bug reports, enhancements and feature requests are tracked in Jira. Though not mandatory, we encourage you to first check for a bug report among the issues and in the PR list: perhaps the bug has already been addressed.

For feature requests and enhancements, we do ask you to create a GitHub issue, describe your idea and discuss the design with us. This way we align your ideas with our vision for the product development.

If the bug hasn’t been reported / addressed, or we’ve agreed on the enhancement implementation with you, do the following:

1. Fork this repository
2. Clone this repository on your machine.
3. Create a separate branch for your changes. If you work on a Jira issue, please follow this pattern for a branch name: <PG-123>-name. This makes it easier to track your contribution.

4. Make your changes. Please follow the following guidelines to improve code readability according to the order listed:

   • PostgreSQL coding conventions
   • C style and Coding rules

5. Write the documentation for your changes. See the Write the docs cheat sheet for details.

6. Build the code and test your changes locally.
7. Commit the changes. The commit message guidelines will help you with writing great commit messages
8. Open a pull request to Percona.
9. Our team will review your code and documentation. If everything is correct, will merge it. Otherwise, we will contact you for additional information or with the request to make changes.

Run local tests

When you work, you should periodically run tests to check that your changes don’t break existing code.

To run the tests, use the following command:

source ci_scripts/setup-keyring-servers.sh
ci_scripts/make-test.sh all

You can run tests on your local machine with whatever operating system you have. After you submit the pull request, we will check your patch on multiple operating systems.

Contribute to documentation

pg_tde documentation is written in Markdown language, so you can write the docs for your code changes or edit the existing documentation online via GitHub. If you wish to have more control over the doc process, jump to how to edit documentation locally.

Before you start, learn what Markdown is and how to write it. For your convenience, there’s also a [Markdown cheat sheet] to help you with the syntax.

The doc files are in the documentation/docs directory.

Write the docs

When you write code, make sure to write documentation that explains it for users. As the author, you know best how your code works. To explain your feature or improvement, use the following key points:

1. Feature Description: What is the feature about, and why does a user need it?

2. User Tasks: What tasks can a user solve with this feature?

3. Functionality: How does the feature work?

4. Setup Requirements: How do you set it up? Are there any preconditions for it to work, such as existing setups or external configurations (e.g., what should be configured in a new Key Management Service - KMS)?

5. Setup Steps: What are the setup steps? Explain the commands and parameters used in functions. Give examples of using them. Provide sample outputs for commands.

6. Limitations and Breaking Changes: Are there any known limitations or breaking changes this feature introduces?

Edit documentation online via GitHub

1. Click the Edit this page icon next to the page title. The source .md file of the page opens in GitHub editor in your browser. If you haven’t worked with the repository before, GitHub creates a fork of it for you.
2. Edit the page. You can check your changes on the Preview tab.
3. Commit your changes.
   • In the Commit changes section, describe your changes.
   • Select the Create a new branch for this commit and start a pull request option
   • Click Propose changes.
4. GitHub creates a branch and a commit for your changes. It loads a new page on which you can open a pull request to Percona. The page shows the base branch - the one you offer your changes for, your commit message and a diff - a visual representation of your changes against the original page. This allows you to make a last-minute review. When you are ready, click the Create pull request button.
5. Someone from our team reviews the pull request and if everything is correct, merges it into the documentation. Then it gets published on the site.

Edit documentation locally

This option is for users who prefer to work from their computer and / or have the full control over the documentation process.

The steps are the following:

1. Fork this repository
2. Clone the repository on your machine:

git clone --recursive git@github.com:<your-name>/postgres.git

3. Change the directory to `contrib/pg_tde` and add the remote upstream repository:

```sh
git remote add upstream git@github.com:percona/postgres.git

1. Pull the latest changes from upstream

git fetch upstream

1. Create a separate branch for your changes

git checkout -b <PG-123-my_branch> upstream/TDE_REL_17_STABLE

1. Make changes

2. Commit your changes. The commit message guidelines will help you with writing great commit messages

3. Open a pull request to Percona

Building the documentation

To verify how your changes look, generate the static site with the documentation. This process is called building. You can do it in these ways: - Use Docker - Install MkDocs and build locally

Use Docker

1. Get Docker
2. We use our Docker image to build documentation. Run the following command:

cd contrib/pg_tde/documentation
docker run --rm -v $(pwd):/docs perconalab/pmm-doc-md mkdocs build

If Docker can’t find the image locally, it first downloads the image, and then runs it to build the documentation.

1. Go to the site directory and open the index.html file to see the documentation.

If you want to see the changes as you edit the docs, use this command instead:

cd contrib/pg_tde/documentation
docker run --rm -v $(pwd):/docs -p 8000:8000 perconalab/pmm-doc-md mkdocs serve --dev-addr=0.0.0.0:8000

Wait until you see INFO - Start detecting changes, then enter 0.0.0.0:8000 in the browser’s address bar. The documentation automatically reloads after you save the changes in source files.

Install MkDocs and build locally

1. Install Python.

2. Install MkDocs and required extensions:

   pip install -r requirements.txt
   

3. Build the site:

   cd contrib/pg_tde/documentation
   mkdocs build
   

4. Open site/index.html

Or, to run the built-in web server:

cd contrib/pg_tde/documentation
mkdocs serve

View the site at http://0.0.0.0:8000

Build PDF file

To build a PDF version of the documentation, do the following:

1. Disable displaying the last modification of the page:

   export ENABLED_GIT_REVISION_DATE=false
   

2. Build the PDF file:

   ENABLE_PDF_EXPORT=1 mkdocs build -f mkdocs-pdf.yml
   

   The PDF document is in the site/pdf folder.