Three questions about Percona Postgresql

PostgreSQL PG_TDE – Transparent Data Encryption (TDE)
1

Q1:

I already install Community PG16.8 at Oracle linux 9.5 and with some tables. I want to use tde_heap function to encrypt my tables. here are my setup below:

  1. Install Percona Postgresql 17.4.1
  2. Use pg_dump to export my PG16.8 tables
  3. Import to Percona Postgresql17.4.1
  4. Do alter table “table-name” set access method tde_heap for each table

It seems work, but if I have a mount of data and tables, what is the best way to do that?

Q2:

Every time when I rotate the tde_key, is it necessary to restart the Percona Postgresql service?

Q3:

When I want to change from on-premises to public cloud, is there any Percona Postgresql original services on public cloud (GCP/AWS/Azure) can be subscribed? Or I just need to install the linux OS and Percona Postgresql on it then import the data?

2

Hi Kevin

Thanks for giving pg_tde a go.

There are two ways you could optimise the data transfer and encryption process in Q1:

Firstly - you could write the data only once. Changing the access method rewrites all the data in the table, so you are currently writing it out twice - once when you import and once when you encrypt. If you loaded the schema first, then changed the access method of the target table before importing the data, you would only be writing the data to disk once.

If you’re copying all the data over from your old system, you could also avoid exporting and importing, but it’s a bit more of process.

  • Build a new Postgres 16.x system on your target OS
  • Set the new system up as a replica of the current 16.8
  • Wait for it to catch up
  • Break the replication link
  • Install Percona Distribution for PostgreSQL 17
  • Upgrade to 17 with pg_upgrade
  • Change the access method on those tables which you want to encrypt
3

Hi, answering some of your questions:

want to use tde_heap function to encrypt my tables.

Seems you’ve answered that question pretty well

when I rotate the tde_key, is it necessary to restart the Percona Postgresql service?

Depends, can you provide example steps how you rotate the key?

is there any Percona Postgresql original services on public cloud (GCP/AWS/Azure) can be subscribed?

Nope, not at this time

Hope this helps

4

Hi Robert:

Thx for your comment first. According to your question：

Depends, can you provide example steps how you rotate the key?

My step of rotate tde_key are below:

  1. backup the old key and set the permission
    1.1 cp tde_key tde_key.bak-20250430[quote=“Robert_Bernier, post:3, topic:37766, full:true”]
    1.2 chown mypguser:mypguser tde_key.bak-20250430
    1.3 chmod 600 tde_key.bak-20250417

  2. generate new key and set the permission
    2.1 openssl rand -out tde_key_new 292
    2.2 chown mypguser:mypguser tde_key_new
    2.3 chomd 600 tde_key_new

  3. connect to DB and change the key
    3.1 /opt/pgdistro/percona-postgresql17/bin/psql -d postgres

    3.2 SELECT pg_tde_add_key_provider_file(‘file-provider-new’, ‘/usr/local/pgsql/keyring/tde_key_new’);

    3.3 SELECT pg_tde_set_principal_key(‘tde_key_new’,‘file-provider-new’,‘true’);

In this situation, is it necessary to restart the Percona Postgresql service or would you give me some examples that should be restart or not to restart?

Thx!

5

Hi,
Apologies for the delay. Coincidentally, I’ve been working on courseware for pg_tde.

First off: generating a new key using openssl won’t work, you’ll have two choices; either copy the new key under a new name and use it or add another provider altogether.

However you create your 2nd key you can rotate the key using this function:

– update provider, keep the provider name but point it to the new key
select *
from
pg_tde_change_key_provider_file
(
‘my-provider’,
‘/var/lib/postgresql/tde_key_new’
);

Validate the change using these two functions:

– validate
select * from pg_tde_list_all_key_providers();
select * from pg_tde_principal_key_info();

Although it should work, the old key is still in RAM so for the time being a server restart guarantees it picks up the new key location. Usually, I run a query on pre-existing tables in order to confirm everything worked out.

One last caveat: remember keyrings are good for figuring out how to work with pg_tde but for production purposes you “must” use either external files or KMIP. The whole point of TDE is to prevent someone who has direct access to the data cluster itself from stealing it. Having a keying file locally installed on the file system kinda defeats the purpose.

Hope this helps.

6

Hi Robert:
I think I have a little confuse about the relationship between the key provider and the principal key.
According to your description " you’ll have two choices; either copy the new key under a new name and use it or add another provider altogether.", I will try to describe my opinion, if I am wrong, please correct me. Thx!

If this is my statement below(two keys):

[root@vbox keyring]# ls
tde_key  tde_key_new

[root@vbox keyring]# pwd
/usr/local/pgsql/keyring

postgres=# SELECT pg_tde_principal_key_info();
 (my-master-key,file-provider,1,"2025-04-14 14:25:18.656093+08")

postgres=# SELECT pg_tde_list_all_key_providers();
 (1,file-provider,file,"{""type"" : ""file"", ""path"" : ""/usr/local/pgsql/keyring/tde_key""}")

==============
The first choice:
Just like what you said, I can use this command to rotate my key.

postgres=# select pg_tde_change_key_provider_file('file-provider','/usr/local/pgsql/keyring/tde_key_new');
                               1

postgres=# SELECT pg_tde_principal_key_info();
 (my-master-key,file-provider,1,"2025-04-14 14:25:18.656093+08")

postgres=# SELECT pg_tde_list_all_key_providers();
 (1,file-provider,file,"{""type"" : ""file"", ""path"" : ""/usr/local/pgsql/keyring/tde_key_new""}")

==============
The Second choice:
I need to create a new key provider to link with the tde_key_new.

postgres=# SELECT pg_tde_add_key_provider_file('file-provider-new', '/usr/local/pgsql/keyring/tde_key_new');
                            2

postgres=# SELECT pg_tde_set_principal_key('my-master-key','file-provider-new','true');
 t

postgres=# SELECT pg_tde_principal_key_info();
 (my-master-key,file-provider-new,2,"2025-05-02 15:54:09.893777+08")

postgres=# SELECT pg_tde_list_all_key_providers();
 (1,file-provider,file,"{""type"" : ""file"", ""path"" : ""/usr/local/pgsql/keyring/tde_key""}")
 (2,file-provider-new,file,"{""type"" : ""file"", ""path"" : ""/usr/local/pgsql/keyring/tde_key_new""}")

When I need rotate the “key”, it seems should be the key under the /keyring path, not the my-master-key, right?

What is the situation that I need to change the principal key?

image
image1090×493 68.6 KB

I think this info is very important right?
image

If I want to use it in my environment, it seems I need to copy the tde_key to another server to store it? Would you give me a example?

image
image1074×853 97.6 KB

Thx!

7

Hi Alastair:
Apologies for the delay. I try to edit the table.sql that I exported from community postgresql16.8.
From

image
image535×540 26.8 KB

To

image
image539×546 28 KB

Then I import it to the percona postgresql.

image
image821×535 24.4 KB

Connect to database and check the pg168_table is successful imported. And I check the filepath

image
image617×318 21.1 KB

I think this is work. It’s encrypted!

image
image291×162 6.91 KB

image
image630×279 23.5 KB

From your suggestion:

* Build a new Postgres 16.x system on your target OS
* Set the new system up as a replica of the current 16.8
* Wait for it to catch up
* Break the replication link
* Install Percona Distribution for PostgreSQL 17
* Upgrade to 17 with pg_upgrade
* Change the access method on those tables which you want to encrypt

It seems I can upgrade community postgresql 16.8 to percona postgresql17 through pg_upgrade?

8

Hi Kevin,

In that step, there is an easy way to check if the table is encrypted by the following query:

SELECT pg_tde_is_encrypted('pg168_table');

It seems I can upgrade community postgresql 16.8 to percona postgresql17 through pg_upgrade?

Yes, it is possible. You need to install the Percona packages and run the pg_upgrade as usual. I’d add only one note. In future upgrades, if you are not using pg_tde yet and start using it, you need to perform the upgrade with pg_tde_upgrade; see the documentation page for more details. Below you can see an exemple which a perform the upgrade from PostgreSQL 16 to Percona PostgreSQL 17.

[root@node0 /]# dnf list --installed *postgresql*
Installed Packages
percona-postgresql17.x86_64                                                        1:17.10-1.el9                                                       @ppg-17-release-x86_64
percona-postgresql17-libs.x86_64                                                   1:17.10-1.el9                                                       @ppg-17-release-x86_64
percona-postgresql17-server.x86_64                                                 1:17.10-1.el9                                                       @ppg-17-release-x86_64
postgresql16.x86_64                                                                16.14-2PGDG.rhel9.8                                                 @pgdg16               
postgresql16-libs.x86_64                                                           16.14-2PGDG.rhel9.8                                                 @pgdg16               
postgresql16-server.x86_64                                                         16.14-2PGDG.rhel9.8                                                 @pgdg16               
[postgres@node0 ~]$ /usr/pgsql-17/bin/pg_upgrade --old-bin=/usr/pgsql-16/bin --new-bin=/usr/pgsql-17/bin --old-data=/var/lib/pgsql/16/data --new-data=/var/lib/pgsql/17/data 
Performing Consistency Checks
-----------------------------
Checking cluster versions                                     ok
Checking database user is the install user                    ok
Checking database connection settings                         ok
Checking for prepared transactions                            ok
Checking for contrib/isn with bigint-passing mismatch         ok
Checking data type usage                                      ok
Checking for not-null constraint inconsistencies              ok
Creating dump of global objects                               ok
Creating dump of database schemas                             
                                                              ok
Checking for presence of required libraries                   ok
Checking database user is the install user                    ok
Checking for prepared transactions                            ok
Checking for new cluster tablespace directories               ok

If pg_upgrade fails after this point, you must re-initdb the
new cluster before continuing.

Performing Upgrade
------------------
Setting locale and encoding for new cluster                   ok
Analyzing all rows in the new cluster                         ok
Freezing all rows in the new cluster                          ok
Deleting files from new pg_xact                               ok
Copying old pg_xact to new server                             ok
Setting oldest XID for new cluster                            ok
Setting next transaction ID and epoch for new cluster         ok
Deleting files from new pg_multixact/offsets                  ok
Copying old pg_multixact/offsets to new server                ok
Deleting files from new pg_multixact/members                  ok
Copying old pg_multixact/members to new server                ok
Setting next multixact ID and offset for new cluster          ok
Resetting WAL archives                                        ok
Setting frozenxid and minmxid counters in new cluster         ok
Restoring global objects in the new cluster                   ok
Restoring database schemas in the new cluster                 
                                                              ok
Copying user relation files                                   
                                                              ok
Setting next OID for new cluster                              ok
Sync data directory to disk                                   ok
Creating script to delete old cluster                         ok
Checking for extension updates                                ok

Upgrade Complete
----------------
Optimizer statistics are not transferred by pg_upgrade.
Once you start the new server, consider running:
    /usr/pgsql-17/bin/vacuumdb --all --analyze-in-stages
Running this script will delete the old cluster's data files:
    ./delete_old_cluster.sh