Transparent Data Encryption
Transparent data encryption (TDE) is an optional feature supported by EDB Postgres Advanced Server and EDB Postgres Extended Server from version 15.
It encrypts any user data stored in the database system. This encryption is transparent to the user. User data includes the actual data stored in tables and other objects as well as system catalog data such as the names of objects.
What's encrypted with TDE?
TDE encrypts:
The files underlying tables, sequences, indexes, including TOAST tables and system catalogs, and including all forks. These files are known as data files.
The write-ahead log (WAL).
Various temporary files that are used during query processing and database system operation.
Implications
Any WAL fetched from a server using TDE, including by streaming replication and archiving, is encrypted.
A physical replica is necessarily encrypted (or not encrypted) in the same way and using the same keys as its primary server.
If a server uses TDE, a base backup is automatically encrypted.
The following aren't encrypted or otherwise disguised by TDE:
Metadata internal to operating the database system that doesn't contain user data, such as the transaction status (for example, pg_subtrans and pg_xact).
The file names and file system structure in the data directory. That means that the overall size of the database system, the number of databases, the number of tables, their relative sizes, as well as file system metadata such as last access time are all visible without decryption.
Data in foreign tables.
The server diagnostics log.
Configuration files.
Implications
Logical replication isn't affected by TDE. Publisher and subscriber can have different encryption settings. The payload of the logical replication protocol isn't encrypted. (You can use SSL.)
How does TDE affect performance?
The performance impact of TDE is low. For details, see the Transparent Data Encryption Impacts on EDB Postgres Advanced Server 15 blog.
How does TDE work?
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