Where the open source database community meets: Use code PERCONA75 and secure your spot for Percona Live. Register

Downloads

Blog

Transparent Data Encryption: The Best Way to Secure Your Data in PostgreSQL

July 8, 2025

Author

Share this Post:

Welcome to the open source implementation of PostgreSQL Transparent Data Encryption!

This question was posed on the PostgreSQL forum, and the good news is that it’s actually pretty easy to do!

Q: Is it possible to automate the steps to enable pg_tde for all new databases?
A: Yes!

Here’s the routine:

- Part I: Download Percona Distribution for PostgreSQL and the pg_tde extension

- Part II: Configure pg_tde, permitting the encryption of tables

Part I

Getting pg_tde

The best way to get pg_tde is through Percona’s Distribution of PostgreSQL, which includes a curated collection of packages and extensions for enterprise production environments.

The long-term goal is to enable pg_tde across all Postgres implementations, irrespective of source repository. Community efforts are currently underway to tweak the Postgres base code, making this possible.

Here’s what you need to do on Ubuntu 24.04 (noble)…

#Make certain the packages are updated:<br />apt update && apt upgrade -y

1	#Make certain the packages are updated:<br />apt update && apt upgrade -y

You’ll need to install Percona Distribution for PostgreSQL:

apt install -y curl wget gnupg2

1	apt install -y curl wget gnupg2

Installing Percona Distribution for PostgreSQL, among many other packages and extensions, includes the pg_tde extension.

wget https://repo.percona.com/apt/percona-release_latest.generic_all.deb<br />dpkg -i percona-release_latest.generic_all.deb

1	wget https://repo.percona.com/apt/percona-release_latest.generic_all.deb<br />dpkg -i percona-release_latest.generic_all.deb

Note: Pay attention to the distribution version as the repository is constantly being updated.

#Enable the repository, you must use at least postgres version 17.5<br />percona-release enable ppg-17.5 release

1	#Enable the repository, you must use at least postgres version 17.5<br />percona-release enable ppg-17.5 release

Install PostgreSQL, and the data cluster is created and started automatically:

apt install -y percona-postgresql-17

1	apt install -y percona-postgresql-17

Enable pg_tde on the data cluster and restart the service:

pg_conftool 17 main postgresql.auto.conf set shared_preload_libraries 'pg_tde'<br />systemctl restart postgresql@17-main

1	pg_conftool 17 main postgresql.auto.conf set shared_preload_libraries 'pg_tde'<br />systemctl restart postgresql@17-main

Part II

Enabling and configuring table encryption across the data cluster

Enabling table encryption requires creating the extension in the database.

Here are the steps:

- Step 1: Create the template database and create the extension pg_tde

- Step 2:
  - - Create your database i.e., db01
  - - Add the “global” key provider via one of the two following methods:
      1. keyfile (localhost)
      1. key management server
        In production, it is recommended to use a key management server, such as, for example, but not limited to, Hashicorp’s vault.
  - - Set the key

- Step 3: Create the user-defined database, log in, and create the table(s)/relation(s) as required

There are two methods for registering the provider.

1. Per database

1. Globally, across the data cluster

To keep things as simple as possible, we’ll use the global approach: register once, use everywhere.

Step 1: Create the template database

One can save effort by creating a “template” database; execute the following as postgres:

createdb tde_template<br />psql tde_template -c 'create extension pg_tde'

1	createdb tde_template<br />psql tde_template -c 'create extension pg_tde'

Step 2: Add the key provider and set the key

Recall, we’re using a local keyfile, which can be located anywhere on the physical host that the PostgreSQL process owner can access.

createdb tde_template<br />psql tde_template -c 'create extension pg_tde'

1	createdb tde_template<br />psql tde_template -c 'create extension pg_tde'

Step 3: Create the user-defined database and encrypted tables

# Now let's create our encrypted database<br />createdb --template=tde_template db01

1	# Now let's create our encrypted database<br />createdb --template=tde_template db01

-- database db01:<br />-- add global provider and set the key<br />select pg_tde_add_global_key_provider_file('provider_global','/var/lib/postgresql/key_global.file');<br />select pg_tde_create_key_using_global_key_provider('key_global','provider_global');<br />select pg_tde_set_key_using_global_key_provider('key_global','provider_global');

-- database db01: -- add global provider and set the key select pg_tde_add_global_key_provider_file('provider_global','/var/lib/postgresql/key_global.file'); select pg_tde_create_key_using_global_key_provider('key_global','provider_global'); select pg_tde_set_key_using_global_key_provider('key_global','provider_global');

-- validate<br />select * from pg_tde_list_all_global_key_providers();<br /> id | name            | type | options<br />----+-----------------+------+--------------------------------------------------<br /> -1 | provider_global | file | {"path" : "/var/lib/postgresql/key_global.file"}<br /><b>-- -- -- --</b><br />select * from pg_tde_key_info(); <br />  key_name  |  provider_name  | provider_id |       key_creation_time <br />------------+-----------------+-------------+------------------------------- <br /> key_global | provider_global |          -1 | 2025-07-02 21:06:00.875074+00

-- validate select * from pg_tde_list_all_global_key_providers(); id | name | type | options ----+-----------------+------+-------------------------------------------------- -1 | provider_global | file | {"path" : "/var/lib/postgresql/key_global.file"} -- -- -- -- select * from pg_tde_key_info(); key_name | provider_name | provider_id | key_creation_time ------------+-----------------+-------------+------------------------------- key_global | provider_global | -1 | 2025-07-02 21:06:00.875074+00

Creating the encrypted table is easy, as all that’s required is specifying the relation’s access method, i.e., “tde_heap”. Note that you can have both encrypted and unencrypted tables in the same database:

-- database db01<br /><br />create table t1 (<br />id serial primary key,<br />comments text default 'hello world, t1',<br />t_stamp timestamptz default now()<br />) using tde_heap;<br /><br />-- check table is encrypted<br />select * from pg_tde_is_encrypted('t1');<br />insert into t1 values(default,default,default)<br />                     ,(default,default,default) <br />                     ,(default,default,default);<br />-- validate<br />table t1;<br /><br />

-- database db01 create table t1 ( id serial primary key, comments text default 'hello world, t1', t_stamp timestamptz default now() ) using tde_heap; -- check table is encrypted select * from pg_tde_is_encrypted('t1'); insert into t1 values(default,default,default) ,(default,default,default) ,(default,default,default); -- validate table t1;

Since both the provider and key are global, it means creating new, encrypted databases requires minimal effort.

createdb db02 --template tde_template

1	createdb db02 --template tde_template

-- database db02<br /><br />select pg_tde_set_key_using_global_key_provider('key_global','provider_global');<br /><br />create table t2 ( <br />id serial primary key, <br />comments text default 'hello world, t2', <br />t_stamp timestamptz default now() ) using tde_heap; <br /><br />-- check table is encrypted <br />select * from pg_tde_is_encrypted('t2'); <br /><strong><br /></strong>-- add records <br />insert into t2 values(default,default,default) <br />                     ,(default,default,default) <br />                     ,(default,default,default); <br />-- validate <br />table t1;

-- database db02 select pg_tde_set_key_using_global_key_provider('key_global','provider_global'); create table t2 ( id serial primary key, comments text default 'hello world, t2', t_stamp timestamptz default now() ) using tde_heap; -- check table is encrypted select * from pg_tde_is_encrypted('t2'); -- add records insert into t2 values(default,default,default) ,(default,default,default) ,(default,default,default); -- validate table t1;

Conclusion

The next step in your journey into PostgreSQL transparent data encryption is to familiarize yourself with the full API and learn how to work with the key management server. References are included below.