After enabling TDE and creating encrypted table still unencrypted data visible in DB file

PostgreSQL PG_TDE – Transparent Data Encryption (TDE)
1

Hello all,

I have enabled PG_TDE and stored the key in Oracle Key Vault. Next I created a table ENC_TEST with access method TDE_HEAP.
test=# SELECT pg_tde_is_encrypted(‘enc_test’) encrypted;
encrypted

t
(1 row)

This shows the table is supposed to be encrypted.

When I go to the pg_wal directory and run strings on the datafile after an insert like this:
test=# insert into enc_test values (‘john doe’);

INSERT 0 1

[postgres@percona pg_wal]$ strings 000000010000000000000001 |grep “john doe”

john doe

What is wrong?

2

Did you enable WAL encryption? 4. Configure WAL Encryption (tech preview) - Percona Transparent Data Encryption for PostgreSQL
Table encryption is different from WAL encryption.

Table encryption:

postgres=# CREATE TABLE test_encrypted (id SERIAL, name varchar(20)) USING tde_heap;
CREATE TABLE
postgres=#
postgres=# INSERT INTO test_encrypted (name) VALUES ('Alice');
INSERT 0 1
postgres=# INSERT INTO test_encrypted (name) VALUES ('Bob');
INSERT 0 1
postgres=# INSERT INTO test_encrypted (name) VALUES ('Charlie');
INSERT 0 1
postgres=# SELECT * FROM test_encrypted;
 id |  name
----+---------
  1 | Alice
  2 | Bob
  3 | Charlie
(3 rows)

postgres=# SELECT pg_relation_filepath('test_encrypted');
 pg_relation_filepath
----------------------
 base/5/16433

bash-5.1$ cd /data/db/base/5/
bash-5.1$ ls -la 16433
-rw------- 1 postgres root 8192 Jul 25 15:07 16433
bash-5.1$ strings 16433
$zc6
c{,w
.r8[G
}#$m
...
1 Like
3

Thank you Matthew,

I will look at this after my holidays.

Regards,
Frank

4

Hi @fborden - To enable WAL encryption, you need to perform below steps. Assuming you will be using OKV, find the steps below

Step 1: Add a global key provider
SELECT pg_tde_add_global_key_provider_kmip(
‘OKV’,
‘10.0.0.107’,
5696,
‘/var/lib/pgsql/pg_okv/ssl/cert.pem’,
‘/var/lib/pgsql/pg_okv/ssl/key.pem’,
‘/var/lib/pgsql/pg_okv/ssl/CA.pem’
);

Step 2: Create a Key using the database key provider created in Step 1
SELECT pg_tde_create_key_using_global_key_provider(
‘percona-key’,
‘OKV’
);

SELECT pg_tde_set_server_key_using_global_key_provider(‘percona-key’,‘OKV’);

ALTER SYSTEM SET pg_tde.wal_encrypt=‘ON’

Restart PG server to enable WAL encryption.

Note: Enabling WAL encryption ensures that all writes into the WAL files are encrypted from the point WAL encryption is enabled. It does not encrypt previously written data into WAL files.