How does TDE encrypt data?

TDE prevents unauthorized viewing of data in operating system files on the database server and on backup storage. Data becomes unintelligible for unauthorized users if it's stolen or misplaced.

Data encryption and decryption is managed by the database and doesn't require application changes or updated client drivers.

EDB Postgres Advanced Server and EDB Postgres Extended Server provide hooks to key management that's external to the database. These hooks allow for simple passphrase encrypt/decrypt or integration with enterprise key management solutions. See Securing the data encryption key for more information.

How does TDE encrypt data?

EDB TDE uses OpenSSL to encrypt data files with the AES encryption algorithm. In Windows systems, TDE uses OpenSSL 3. In Linux systems, TDE uses the OpenSSL version installed in the host operating system. To check the installed version, run openssl version. For more information, see the OpenSSL documentation. If you're using a custom build not provided by the OpenSSL community, consult your vendor's documentation.

Starting with version 16, EDB TDE introduces the option to choose between AES-128 and AES-256 encryption algorithms during the initialization of the Postgres cluster. The choice between AES-128 and AES-256 hinges on balancing performance and security requirements. AES-128 is commonly advised for environments where performance efficiency and lower power consumption are pivotal, making it suitable for most applications. Conversely, AES-256 is recommended for scenarios demanding the highest level of security, often driven by regulatory mandates.

TDE uses AES-128-XTS or AES-256-XTS algorithms for encrypting data files. XTS uses a second value, known as the tweak value, to enhance the encryption. The XTS tweak value with TDE uses the database OID, the relfilenode, and the block number.

For write-ahead log (WAL) files, TDE uses AES-128-CTR or AES-256-CTR, incorporating the WAL's log sequence number (LSN) as the counter component.

Temporary files that are accessed by block are also encrypted using AES-128-XTS or AES-256-XTS. Other temporary files are encrypted using AES-128-CBC or AES-256-CBC.

How is data stored on disk with TDE?

In this example, the data in the tbfoo table is encrypted. The pg_relation_filepath function locates the data file corresponding to the tbfoo table.

insert into tbfoo values ('abc','123');
INSERT 0 1

select pg_relation_filepath('tbfoo');

 pg_relation_filepath
----------------------
 base/5/16416

Grepping the data looking for characters doesn't return anything. Viewing the last five lines returns the encrypted data:

$ hexdump -C 16416 | grep abc
$

$ hexdump -C 16416 | tail -5
00001fc0  c8 0f 1d c8 9a 63 3d dc  7d 4e 68 98 b8 f2 5e 0a  |.....c=.}Nh...^.|
00001fd0  9a eb 20 1d 59 ad be 94  6e fd d5 6e ed 0a 72 8c  |.. .Y...n..n..r.|
00001fe0  7b 14 7f de 5b 63 e3 84  ba 6c e7 b0 a3 86 aa b9  |{...[c...l......|
00001ff0  fe 4f 07 50 06 b7 ef 6a  cd f9 84 96 b2 4b 25 12  |.O.P...j.....K%.|
00002000