pg_tde

This is an experimental encrypted access method for PostgreSQL 16. We need your feedback!

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.

Documentation

Find more information about pg_tde in the documentation.

Installation steps

1. Build and install the plugin with make from source, or download a release and install the package
2. pg_tde needs to be loaded at the start time. The extension requires additional shared memory; therefore, add the pg_tde value for the shared_preload_libraries parameter and restart the postgresql instance.

Use the ALTER SYSTEM command from psql terminal to modify the shared_preload_libraries parameter.

ALTER SYSTEM SET shared_preload_libraries = 'pg_tde';

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

• On Debian and Ubuntu:

sudo systemctl restart postgresql.service

• On RHEL 8 compatible OS:

sudo systemctl restart postgresql-16.service

4. Create the extension using the CREATE EXTENSION command. Using this command requires the privileges of a superuser or a database owner. Connect to psql as a superuser for a database and run the following command:

CREATE EXTENSION pg_tde;

5. 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 VARCHAR(128),
                        vault_token TEXT,
                        vault_url TEXT,
                        vault_mount_path TEXT,
                        vault_ca_path TEXT);

-- For File key provider
FUNCTION pg_tde_add_key_provider_file(
                        provider_name VARCHAR(128), 
                        file_path TEXT);

Example: Add a File key provider and name it file.

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. 6. Set the principal key for the database using the pg_tde_set_principal_key function.

FUNCTION pg_tde_set_principal_key (
                principal_key_name VARCHAR(255), 
                provider_name VARCHAR(255));

Example: Set the principal key named my-principal-key using the file as a key provider.

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

7. You are all set to create encrypted tables. For that, specify USING pg_tde_basic access method in the CREATE TABLE statement. For example:

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

Build from source

1. To build pg_tde from source code, you require the following:

• On Debian and Ubuntu:

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

• On RHEL 8 compatible OS:

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

• On MacOS:

brew install make autoconf curl gettext postresql@16

2. Install or build postgresql 16 (see reference commit below)

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-Lab/pg_tde

Compile and install the extension

cd pg_tde
./configure
make USE_PGXS=1
sudo make USE_PGXS=1 install

On RHEL 8 compatible OS:

PATH=$PATH:/usr/pgsql-16/bin/ make USE_PGXS=1
sudo PATH=$PATH:/usr/pgsql-16/bin/ make USE_PGXS=1 install

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

Install from package

1. Download the latest release package

wget https://github.com/Percona-Lab/pg_tde/releases/download/latest/pgtde-pgdg16.deb

2. Install the package

sudo dpkg -i pgtde-pgdg16.deb

Run in Docker

You can find docker images built from the current main branch on Docker Hub. Images build on top of postgres:16 official image. To run it:

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

It builds and adds pg_tde extension to Postgres 16. Relevant postgresql.conf and tde_conf.json are created in /etc/postgresql/ inside the container. This dir is exposed as volume.

See https://hub.docker.com/_/postgres on usage.

You can also build a docker image manually with:

docker build . -f ./docker/Dockerfile -t your-image-name

Helper functions

The extension provides the following helper functions:

pg_tde_is_encrypted(tablename)

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

Base commit

This is based on the heap code as of the following commit:

commit a81e5516fa4bc53e332cb35eefe231147c0e1749 (HEAD -> REL_16_STABLE, origin/REL_16_STABLE)
Author: Amit Kapila <[email protected]>
Date:   Wed Sep 13 09:48:31 2023 +0530

    Fix the ALTER SUBSCRIPTION to reflect the change in run_as_owner option.

FAQ

What would be the best way to encrypt an existing table using pg_tde?

• That can be done by using the ALTER TABLE command and changing the table's access method to pg_tde_basic. ALTER TABLE table_name SET access method pg_tde_basic; This command rewrites the table, so for large tables, it might take a considerable amount of time. Unfortunately, rewriting the table is currently the only available option.