pg_tde: Transparent Database Encryption for PostgreSQL

The PostgreSQL extension provides data at rest encryption. It is currently in an experimental phase and is under active development. We need your feedback!

Table of contents

1. Overview
2. Documentation
3. Percona Server for PostgreSQL
4. Build from sources
5. Run in docker
6. Setting up
7. Helper functions

Overview

Transparent Data Encryption offers encryption at the file level and solves the problem of protecting data at rest. The encryption is transparent for users allowing them to access and manipulate the data and not to worry about the encryption process. As a key provider, the extension supports the keyringfile and Hashicorp Vault.

This extension provides two access methods with different options:

tde_heap_basic access method

• Works with community PostgreSQL 16 and 17 or with Percona Server for PosgreSQL 17
• Encrypts tuples and WAL
• Doesn't encrypt indexes, temporary files, statistics
• CPU expensive as it decrypts pages each time they are read from bufferpool

tde_heap access method

• Works only with Percona Server for PostgreSQL 17
• Uses extended Storage Manager and WAL APIs
• Encrypts tuples, WAL and indexes
• Doesn't encrypt temporary files and statistics yet
• Faster and cheaper than tde_heap_basic

Documentation

Full and comprehensive documentation about pg_tde is available at https://percona.github.io/pg_tde/.

Percona Server for PostgreSQL

Percona provides binary packages of pg_tde extension only for Percona Server for PostgreSQL. Learn how to install them or build pg_tde from sources for PSPG in the documentation.

Building from sources for community PostgreSQL

1. Install required dependencies (replace XX with 16 or 17)

• On Debian and Ubuntu:

  sudo apt install make gcc autoconf git libcurl4-openssl-dev postgresql-server-dev-XX

• On RHEL 8 compatible OS:

  sudo yum install epel-release
  yum --enablerepo=powertools install git make gcc autoconf libcurl-devel perl-IPC-Run redhat-rpm-config openssl-devel postgresqlXX-devel

• On MacOS:

  brew install make autoconf curl gettext postresql@XX

2. Install or build postgresql 16 or 17

3. If postgres is installed in a non standard directory, set the PG_CONFIG environment variable to point to the pg_config executable

4. Clone the repository, build and install it with the following commands:

   git clone https://github.com/percona/pg_tde

5. Compile and install the extension

   cd pg_tde
   make USE_PGXS=1
   sudo make USE_PGXS=1 install

Run in Docker

There is a docker image with pg_tde based community PostgreSQL 16

docker run --name pg-tde -e POSTGRES_PASSWORD=mysecretpassword -d perconalab/pg_tde

Docker file is available here

See Make Builds for Developers for more info on the build infrastructure.

Setting up

1. Add extension to the shared_preload_libraries:

   1. Via configuration file postgresql.conf

      shared_preload_libraries=pg_tde 
      

   2. Via SQL using ALTER SYSTEM command

      ALTER SYSTEM SET shared_preload_libraries = 'pg_tde';

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

   • On Debian and Ubuntu:

     sudo systemctl restart postgresql.service

   • On RHEL 8 compatible OS (replace XX with your version):

     sudo systemctl restart postgresql-XX.service

3. CREATE EXTENSION with SQL (requires superuser or a database owner privileges):

   CREATE EXTENSION pg_tde;

4. Create a key provider. Currently pg_tde supports File and Vault-V2 key providers. You can add the required key provider using one of the functions.

   -- For Vault-V2 key provider
   -- pg_tde_add_key_provider_vault_v2(provider_name, vault_token, vault_url, vault_mount_path, vault_ca_path)
   SELECT pg_tde_add_key_provider_vault_v2(
       'vault-provider',
       json_object( 'type' VALUE 'remote', 'url' VALUE 'http://localhost:8888/token' ),
       json_object( 'type' VALUE 'remote', 'url' VALUE 'http://localhost:8888/url' ),
       to_json('secret'::text), NULL);
   
   -- For File key provider
   -- pg_tde_add_key_provider_file(provider_name, file_path);
   SELECT pg_tde_add_key_provider_file('file','/tmp/pgkeyring');

   Note: The File provided is intended for development and stores the keys unencrypted in the specified data file.

5. Set the principal key for the database using the pg_tde_set_principal_key function.

   -- pg_tde_set_principal_key(principal_key_name, provider_name);
   SELECT pg_tde_set_principal_key('my-principal-key','file');

6. Specify tde_heap_basic access method during table creation

   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_basic;

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

   ALTER TABLE table_name SET access method  tde_heap_basic;

Latest test release

To download the latest build of the main branch, use the HEAD release from releases.

Builds are available in a tar.gz format, containing only the required files, and as a deb package. The deb package is built against the pgdg16 release, but this dependency is not yet enforced in the package.

Helper functions

The extension provides the following helper functions:

pg_tde_is_encrypted(tablename)

Returns t if the table is encrypted (uses the tde_heap_basic access method), or f otherwise.