Is it possible to automate the steps to enable pg_tde for all new databases?

PostgreSQL PG_TDE – Transparent Data Encryption (TDE)
1

Something like:

BEGIN;

\c template1
CREATE EXTENSION pg_tde;

SELECT pg_tde_add_database_key_provider_file('file-vault', '/tmp/pg_tde_test_001_basic.per');

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

COMMIT;

For example via docker-entrypoint-initdb.d

I have a tried but see the following error:

FATAL:  pg_tde can only be loaded at server startup. Restart required.

Also see:

2

Hi,

You’re in luck! We just had an internal Lunch & Learn reviewing the current state of pg_tde.

In order to enable a “global” and automated method of encrypting tables …

  • Install & enable the extension which I assume you’ve already accomplished
  • create a template database where you create the extension in this database
# postgresql.conf, server restart required
shared_preload_libraries = pg_tde

create database my_template
\c my_template
create extension pg_tde

Assuming you choose the key provider to be a local file, which is great for learning but discouraged for production environments, execute the following commands in your template database:

select pg_tde_add_global_key_provider_file('my_provider','/var/lib/postgresql/my_key.file' );
select pg_tde_set_default_principal_key('my_provider','provider_global','true';

And now you’re ready. From here on all you need to do is create your tables specifying the tde access method in any of your databases and they are encrypted.

Create your working database:

create db01 with template  my_template;
\c db01
create table t1(id serial, comments text) using tde_heap;

So long as you create new databases using the user-defined template database everything is pretty simple.

Of course there’s far more to it since the API is pretty extensive but this should get you started.

Hope this helps

2 Likes