Encrypt Data at Rest with TDE

To meet the demand for protecting user data security, SynxDB introduces the Transparent Data Encryption (TDE) feature. TDE is a technology used by databases to encrypt data files. Literally, it can be broken down into three parts: data, encryption, and transparency. Data refers to database data. The files are ciphertext on disk and plaintext in memory. TDE solves the problem of protecting data at rest, also known as at-rest data encryption. Transparency means that the encryption is transparent to the user, who does not need to change their original operating habits. Both users and applications do not need to be concerned with key management or the encryption/decryption process.

Data encryption scenarios

In China, to ensure internet information security, the state requires relevant service developers to meet certain data security standards, for example:

Cryptography Law of the People’s Republic of China (Effective January 1, 2020)
Baseline for Classified Protection of Cybersecurity (GB/T 22239-2019) (PDF address)

Internationally, some related industries also have regulatory data security standards, for example:

Payment Card Industry Data Security Standard (PCI DSS)
Health Insurance Portability and Accountability Act (HIPAA)
General Data Protection Regulation (GDPR)
California Consumer Protection Act (CCPA)
Sarbanes-Oxley Act (SOX)

Prerequisites

OpenSSL needs to be installed on the SynxDB nodes. Typically, Linux distributions come with OpenSSL pre-installed.

How it works

Basic concepts

Term	Description
DEK (Data Encryption Key)	A data encryption key that exists in the memory of the data warehouse compute nodes. It is generated by the database’s internal random number function and serves as the password for actual data encryption.
Plaintext DEK	Synonymous with DEK, can only be stored in memory.
Master Key	The master key, used to encrypt the DEK.
Ciphertext DEK	The ciphertext DEK is generated by encrypting the plaintext DEK with the Master Key and is persisted to unified storage.

Encryption algorithms

Cryptosystems can be divided into two categories in principle:

Single-key (symmetric) system: The encryption key and decryption key are the same.
Dual-key (asymmetric) (public-key) system: A public key is used for encryption and is public, while a private key is used for decryption and is private. Separating the encryption and decryption capabilities allows for one-to-many and many-to-one communication.

There are two ways to encrypt plaintext messages in a single-key system:

Stream cipher: The plaintext message is encrypted character by character.
Block cipher: The plaintext message is divided into blocks (containing multiple characters), and encrypted block by block.

Key types

A characteristic of stream ciphers is that the key length is the same as the plaintext data length, which is difficult to implement in a database, so it is not considered here.
The biggest advantage of public-key encryption is that it is divided into public and private keys. The public key can be made public, which reduces key management problems. However, its encryption performance is too poor. The encryption performance of block cipher algorithms is hundreds of times that of public-key encryption, so it is not considered here either.
Block ciphers are currently the mainstream encryption algorithms, with the best performance and widest application.

SynxDB supports two block cipher algorithms: AES and SM4.

AES encryption algorithm

The internationally recognized block cipher algorithm is AES (Advanced Encryption Standard). A block cipher algorithm first needs to group data. The encryption length of AES is 128 bits, which is 16 bytes. AES has 3 key lengths: 128, 192, and 256 bits. AES has 5 encryption modes:

ECB mode: Electronic Code Book mode
CBC mode: Cipher Block Chaining mode
CFB mode: Cipher Feedback mode
OFB mode: Output Feedback mode
CTR mode: Counter mode

SM algorithm

SM stands for State Cryptography Administration, referring to Chinese domestic cryptographic algorithms. The main ones are SM1, SM2, SM3, and SM4. The key length and block length are both 128 bits.

SM1 is for symmetric encryption. Its encryption strength is comparable to AES. This algorithm is not public. To call this algorithm, you need to use the interface of an encryption chip.
SM2 is for asymmetric encryption, based on ECC. This algorithm is public. Because this algorithm is based on ECC, its signing speed and key generation speed are faster than RSA. The security strength of ECC 256-bit (SM2 uses a type of ECC 256-bit) is higher than RSA 2048-bit, and its operation speed is also faster than RSA.
SM3 is for message digests. You can understand it by comparing it to MD5. This algorithm is public. The verification result is 256 bits.
SM4 is a block data algorithm for wireless LAN standards. It is for symmetric encryption, and both the key length and block length are 128 bits.

Implementation of transparent data encryption

The transparent data encryption feature is mainly composed of two major modules: the key management module and the storage layer encryption module.

Key management module

The key management module has a two-tier architecture. We use encryption algorithms to encrypt data, and encryption algorithms require keys. To reduce the overhead of repeated encryption and decryption caused by key changes, the industry mostly adopts a multi-layer key management structure.

Two-tier key structure: The two-tier key structure includes a master key and a data encryption key (DEK). The master key is used to encrypt the DEK and is stored outside the database. Users can also create and manage master keys in an external Key Management System (KMS), and SynxDB uses these keys for storage encryption. The plaintext DEK is used to encrypt the database data. It can be encrypted with the master key to generate a ciphertext DEK, which is stored locally in the database.

How to use

SynxDB encrypts data at two granularities:

Cluster-wide encryption, described in this section. You turn it on when you initialize the cluster, and it covers every relation. The master key lives outside the database as a key file.
Tablespace-level encryption, described in Encrypt a single tablespace. You turn it on for individual tablespaces on a running cluster, and each tablespace gets its own data encryption key wrapped by a key held in an external KMS.

The two mechanisms are independent. You can use tablespace-level encryption on a cluster that was initialized without cluster-wide encryption.

SynxDB provides the transparent data encryption feature. Users only need to enable the TDE feature when deploying SynxDB. After that, the data encryption operations are transparent to the user and require no extra attention.

To enable TDE when deploying SynxDB, the user needs to specify the -T parameter when using gpinitsystem to initialize the database. SynxDB supports two encryption algorithms, AES and SM4. The methods to enable them are as follows:

Enable the TDE feature and specify the encryption algorithm as AES:
```
gpinitsystem -c gpinitsystem_config -T AES256
```
Enable the TDE feature and specify the encryption algorithm as SM4:
```
gpinitsystem -c gpinitsystem_config -T SM4
```

How to verify

The transparent data encryption feature means that encryption is transparent to the user. There is no perceptible difference in read and write operations whether the feature is enabled or disabled. The key file is very important data. If the key file is lost, the database will fail to start. This is because after the database starts, it loads the key into memory. Therefore, it is necessary to start and stop the database’s transparent data encryption feature. The key file path is located on the coordinator node.

First, find the data directory of the coordinator node, for example:

COORDINATOR_DATA_DIRECTORY=/home/gpadmin/work/data0/coordinator/gpseg-1

Then find the key file:

[gpadmin@i-uetggb33 gpseg-1]$ pwd
/home/gpadmin/work/data0/coordinator/gpseg-1
[gpadmin@i-uetggb33 gpseg-1]$ ls -l pg_cryptokeys/live/
total 8
-rw------- 1 gpadmin gpadmin 48 Apr 12 10:26 relation.wkey
-rw------- 1 gpadmin gpadmin 48 Apr 12 10:26 wal.wkey

Here, relation.wkey is the key used to encrypt data files, and wal.wkey is the key used to encrypt WAL logs. Currently, only relation.wkey is effective; WAL logs are not yet encrypted.

Verification process

Create a table and insert data:

postgres=# create table ao2 (id int) with(appendonly=true);
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'id' as the database data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.

CREATE TABLE
postgres=# insert into ao2 select generate_series(1,10);
INSERT 0 10

First, stop the running database with gpstop -a, then simulate the scenario of a lost key file:

cd /home/gpadmin/work/data0/coordinator/gpseg-1/pg_cryptokeys/
# Move the key file to another directory
mv live backup

Try to start the database with gpstart -a.

Because the key file is missing, the database fails to start and cannot be read, thus protecting the data. By observing the coordinator node’s database log, you will find the following error log, which mentions encryption keys. Losing the key file is a very serious system error, and we should not allow the database to start successfully.

2024-04-12 10:46:04.703250 CST,,,p24962,th-1601394560,,,,,0,,,seg-1,,,,,"FATAL","XX000","cluster has no data encryption keys",,,,,,,0,,"kmgr.c",298,"Stack trace:
  0x7fd399ee8a28 libpostgres.so errstart + 0x3b4
  0x7fd399ee8672 libpostgres.so errstart_cold + 0x20
  0x7fd399f8eb1e libpostgres.so InitializeKmgr + 0x222
  0x7fd399c27bf1 libpostgres.so PostmasterMain + 0x12ef
  0x40280b postgres <symbol not found> (main.c:289)
  0x7fd398abe555 libc.so.6 __libc_start_main + 0xf5
  0x402289 postgres <symbol not found> + 0x402289
"

Restore the key file:

cd /home/gpadmin/work/data0/coordinator/gpseg-1/pg_cryptokeys/
# Move the key file back
mv backup live

Try to start the database with gpstart -a. It starts successfully, and data can be read successfully.

postgres=# select * from ao2 order by id;
id
----
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
(10 rows)

Encrypt a single tablespace

Instead of encrypting the whole cluster, you can encrypt selected tablespaces on a cluster that is already running. Each encrypted tablespace gets its own data encryption key (DEK). An external Key Management Service (KMS) holds the key encryption key (KEK) that wraps the DEK, so the KEK never has to be stored next to the data.

Every relation you place in an encrypted tablespace is encrypted. Relations in other tablespaces stay unencrypted.

The wrapped DEK is stored as $PGDATA/pg_cryptokeys/tablespaces/<tablespace-oid>.wkey on the coordinator and on every segment. At startup, each node asks the KMS to unwrap its DEKs and keeps the plaintext DEKs in shared memory.

Choose a KMS provider

Set the KMS provider with the tde_kms_provider parameter. Four providers are available:

Provider	Where the KEK comes from	Required parameters	Use it for
`builtin`	Derived from a passphrase in `postgresql.conf`	`tde_kms_builtin_passphrase`	Development and testing
`local_cmd`	Printed by a shell command that you supply	`tde_kms_command`	A secret store that has a command-line client, such as HashiCorp Vault or AWS Secrets Manager
`cosmian`	Cosmian KMS, reached over HTTPS	`tde_kms_host`, `tde_kms_port`, `tde_kms_ca_cert`, `tde_kms_client_cert`, `tde_kms_client_key`	Production with Cosmian KMS
`kmip`	A KMIP 2.x server, reached over the KMIP binary protocol with mutual TLS	`tde_kms_host`, `tde_kms_port`, `tde_kms_ca_cert`, `tde_kms_client_cert`, `tde_kms_client_key`	Production with a KMIP-compliant KMS

With the cosmian and kmip providers, the KMS wraps and unwraps the DEK on the server side, so the KEK never leaves the KMS. Only the wrapped DEK reaches the database.

Note

A cluster uses one provider at a time. tde_kms_provider takes effect only at startup, and tde_kms_host and tde_kms_port apply to whichever provider is active. Because of this, you cannot serve two external KMS instances from the same cluster.

Configure the KMS provider

Set the parameters with gpconfig, then restart the cluster:

gpconfig -c tde_kms_provider -v builtin
gpstop -a -M fast
gpstart -a

Warning

Pass the value to gpconfig without extra quotes, as shown above. If you write -v "'builtin'", the quotes become part of the value, SHOW tde_kms_provider returns 'builtin', the name no longer matches a known provider, and the cluster starts with encryption disabled instead of reporting an error.

SynxDB hides the two parameters that hold secrets from pg_settings, and gpconfig rejects them by default. Add --skipvalidation to set them:

gpconfig -c tde_kms_builtin_passphrase -v "'your-passphrase'" --skipvalidation

Quote the passphrase. postgresql.conf treats an unquoted # as the start of a comment, which silently truncates the value. Quote tde_kms_command the same way: an unquoted command that contains a space stops the cluster from starting.

Each provider checks its required parameters at startup. If one is missing, the node refuses to start and writes the reason to startup.log:

FATAL: tde_kms_ca_cert must be set when tde_kms_provider = 'kmip'

Warning

Set the KMS parameters while the cluster is running. gpconfig reaches the segments through the coordinator, so it cannot repair a configuration that stops the cluster from starting. If that happens, edit postgresql.conf in the coordinator data directory and in every segment data directory by hand, then run gpstart -a.

Built-in provider

tde_kms_provider           = 'builtin'
tde_kms_builtin_passphrase = 'your-passphrase'

The built-in provider derives the KEK from the passphrase, so the KEK is only as well protected as postgresql.conf. Use it to try the feature out, not to protect production data.

Shell command provider

Write a script that prints a 256-bit KEK as 64 hexadecimal characters on standard output and exits with status 0:

#!/bin/sh
KMS_KEY_ID="${1:-default}"
KEY_FILE="/home/gpadmin/tde-kms/kek_${KMS_KEY_ID}.hex"
if [ ! -f "$KEY_FILE" ]; then
    echo "key file not found: $KEY_FILE" >&2
    exit 1
fi
cat "$KEY_FILE"

Point tde_kms_command at the script. %k expands to the kms_key_id of the tablespace, or to the value of tde_kms_default_key_id when CREATE TABLESPACE omits it:

tde_kms_provider       = 'local_cmd'
tde_kms_command        = '/home/gpadmin/tde-kms/get_kek.sh %k'
tde_kms_default_key_id = 'docdemo'

Replace the body of the script with a call to your secret store. SynxDB runs the command with /bin/sh -c on each node, so the script and its credentials must exist on the coordinator and on every segment host.

Cosmian KMS provider

Create a symmetric AES-256 key in Cosmian and note the unique identifier it returns:

ckms sym keys create --algorithm aes --number-of-bits 256 --tag db-tde

The symmetric key was successfully generated.
	Unique identifier: 1598cd1a-1121-4c64-8e36-3eda443b6a63

Copy the CA certificate, the client certificate, and the client key to the same path on every node, make them readable only by the gpadmin user, and configure:

tde_kms_provider       = 'cosmian'
tde_kms_host           = 'kms.example.com'
tde_kms_port           = 9998
tde_kms_ca_cert        = '/home/gpadmin/tde-kms/certs/ca.pem'
tde_kms_client_cert    = '/home/gpadmin/tde-kms/certs/client.pem'
tde_kms_client_key     = '/home/gpadmin/tde-kms/certs/client.key'
tde_kms_default_key_id = '1598cd1a-1121-4c64-8e36-3eda443b6a63'

Warning

Unlike kmip, the cosmian provider does not stop a node from starting when the CA certificate is missing. SynxDB attempts TLS first, and once the handshake fails it falls back to plain HTTP, which sends the data encryption key to the KMS in the clear and defeats the encryption on disk. The cluster reports no error, so confirm that tde_kms_ca_cert took effect after you deploy.

The client certificate and key also authenticate the database to the KMS, and the KMS records the certificate subject as the identity behind each wrap and unwrap request.

KMIP provider

The kmip provider speaks the KMIP binary protocol on the port that the KMIP standard reserves, 5696, and it requires mutual TLS. Configure the CA certificate, the client certificate, and the client key, all of which are mandatory:

tde_kms_provider    = 'kmip'
tde_kms_host        = 'kms.example.com'
tde_kms_port        = 5696
tde_kms_ca_cert     = '/home/gpadmin/tde-kms/certs/ca.pem'
tde_kms_client_cert = '/home/gpadmin/tde-kms/certs/client.pem'
tde_kms_client_key  = '/home/gpadmin/tde-kms/certs/client.key'

Create an encrypted tablespace

Add the encryption_method option to CREATE TABLESPACE:

CREATE TABLESPACE enc_spc LOCATION '/data/enc_spc'
  WITH (encryption_method = 'AES256');

encryption_method accepts AES128, AES192, AES256, and SM4, and the value is not case-sensitive. An unrecognized value fails the statement:

CREATE TABLESPACE bad_spc LOCATION '/data/bad_spc'
  WITH (encryption_method = 'BOGUS');

ERROR:  unknown encryption method "BOGUS"
HINT:  Valid values: AES128, AES192, AES256, SM4.

The statement also fails when no provider is configured, because there is nothing to wrap the DEK with:

ERROR:  no KMS provider configured
HINT:  Set tde_kms_provider in postgresql.conf and restart.

To wrap the DEK with a specific KMS key instead of the one in tde_kms_default_key_id, add kms_key_id:

CREATE TABLESPACE enc_spc LOCATION '/data/enc_spc'
  WITH (encryption_method = 'AES256',
        kms_key_id = '1598cd1a-1121-4c64-8e36-3eda443b6a63');

Then create relations in the tablespace as usual. You read and write them the same way as in any other tablespace:

CREATE TABLE t (id int, secret text) TABLESPACE enc_spc DISTRIBUTED BY (id);
INSERT INTO t SELECT g, 'value_'||g FROM generate_series(1, 500) g;

A tablespace is encrypted or not from the moment you create it. CREATE TABLESPACE is the only statement that generates the DEK, so treat encryption_method as fixed for the life of the tablespace.

Warning

ALTER TABLESPACE accepts encryption_method and reports success, but it changes only the catalog entry. It neither generates nor removes a DEK, so pg_tablespace.spcoptions stops matching what is actually encrypted. Do not use it to turn encryption on or off.

To encrypt data that is already loaded, create the target relation in the encrypted tablespace first, then load the rows into it:

CREATE TABLE t_enc (LIKE t_plain) TABLESPACE enc_spc DISTRIBUTED BY (id);
INSERT INTO t_enc SELECT * FROM t_plain;

CREATE TABLE ... AS works the same way:

CREATE TABLE t_enc TABLESPACE enc_spc AS SELECT * FROM t_plain DISTRIBUTED BY (id);

Warning

Do not use ALTER TABLE ... SET TABLESPACE to move an existing relation into an encrypted tablespace. The statement reports success, but it copies the data unencrypted, and the relation then becomes unreadable and cannot be moved back out. Moving a relation out of an encrypted tablespace into an unencrypted one works correctly.

To decommission an encrypted tablespace, drop its relations and then drop the tablespace. DROP TABLESPACE removes the .wkey file on every node, and the tablespace disappears from pg_tde_tablespace_status. The key in the KMS stays where it is, so a shared key remains available to the other tablespaces that use it.

Check which tablespaces are encrypted

Install the pg_tde extension to get the status view and the key management functions:

CREATE EXTENSION pg_tde;

pg_tde_tablespace_status lists every tablespace DEK that the current node holds in shared memory:

SELECT * FROM pg_tde_tablespace_status ORDER BY spc_name;

 spc_oid | spc_name | enc_method | loaded
---------+----------+------------+--------
   57735 | enc_aes  | AES256     | t
   57736 | enc_sm4  | SM4        | t
(2 rows)

The view omits a tablespace that is unencrypted. The view is restricted to superusers.

Note

The view reports the keys held by the node you are connected to. To read a segment’s own key list, connect to that segment in utility mode:

PGOPTIONS='-c gp_role=utility' psql -h seghost -p 6000 -d mydb \
  -c "SELECT * FROM pg_tde_tablespace_status;"

To see which tablespaces were created with encryption, whether or not their keys loaded, read pg_tablespace:

SELECT spcname, spcoptions FROM pg_tablespace WHERE spcoptions IS NOT NULL;

 spcname |         spcoptions
---------+-----------------------------
 enc_aes | {encryption_method=AES256}
 enc_sm4 | {encryption_method=SM4}

Confirm that the data on disk is encrypted

Write a recognizable string into a table in the encrypted tablespace, force the data out to disk, and search the tablespace directory for that string:

INSERT INTO t SELECT g, 'MAGIC_NEEDLE_'||g FROM generate_series(1, 500) g;
CHECKPOINT;

grep -ra "MAGIC_NEEDLE" /data/enc_spc | wc -l

The count is 0 for an encrypted tablespace. To confirm that the search itself works, run it against a heap table in an unencrypted tablespace, where the count is greater than 0. Keep the -a flag, without which the count is 0 in both cases.

Restore access when the KMS is unavailable

If a node cannot reach the KMS at startup, it starts anyway, but the DEKs it could not unwrap stay unloaded. Reading a relation in an affected tablespace fails:

ERROR:  invalid page in block 0 of relation pg_tblspc/57785/GPDB_2_302605291/57734/16532

The message does not mention keys, so match it by the pg_tblspc/<oid>/ path: the first number is the tablespace OID. Relations in unencrypted tablespaces keep working normally, and so do encrypted tablespaces whose provider is still reachable.

To confirm that the cause is the KMS rather than the data, call tde_open_tablespace(). Its warnings name the KMS endpoint and the tablespace OID:

SELECT tde_open_tablespace('enc_spc');

WARNING:  KMIP: could not connect to kms.example.com:5696: Connection refused
WARNING:  TDE: could not unlock tablespace oid=57791; tablespace will be inaccessible until tde_open_tablespace() is called after the KMS issue is resolved
 f

Fix the KMS or the network path to it, then restart the cluster so that every node reloads its keys:

gpstop -a -M fast
gpstart -a

Notes and limitations

The .wkey file records which provider wrapped it, and SynxDB unwraps with that provider rather than with the current value of tde_kms_provider. Existing encrypted tablespaces therefore keep working after you switch providers, as long as the material the original provider needs is still configured and reachable. Because tde_kms_host and tde_kms_port are shared, moving to a KMS on a different host or port locks the tablespaces that the previous KMS wrapped.
Losing the KMS key means losing the data in every tablespace it wrapped. Revoking or deleting the key in the KMS locks those tablespaces permanently. Back up the KMS key material with the same care as the database itself.
Choose the algorithm with performance in mind. As the figures in Performance evaluation show, AES adds a few percent, while SM4 is several times more expensive. Use SM4 only where a Chinese cryptographic algorithm is required.
Protect tde_kms_client_key and any script that returns a KEK with mode 0400, owned by the gpadmin user.
Tablespace-level encryption does not cover the WAL: full-page images of an encrypted relation reach the WAL in plaintext. Enable cluster-wide encryption as well if the WAL stream and its archives need protection.

Performance evaluation

Enabling the TDE encryption feature can improve the security of data at rest, but it will also affect the read and write performance of accessing the encrypted database. Please choose whether to enable the TDE encryption feature based on your actual situation. Below are the performance test data from TPC-H. The SM4 algorithm is a Chinese domestic algorithm and has a greater performance overhead. It is recommended to use AES256.

Test environment

AWS host, compute node with 16-core CPU / 32G memory / 200G SSD, with 3 segment nodes deployed.

Test data

Encryption algorithm	Dataset size	Storage type	Compression type	Query duration (s)	Performance overhead
None	5G	AO	None	648	0% (Baseline)
AES	5G	AO	None	658	1.5%
SM4	5G	AO	None	2079	220.8%

Encryption algorithm	Dataset size	Storage type	Compression type	Query duration (s)	Performance overhead
None	5G	AO	zstd	663	0% (Baseline)
AES	5G	AO	zstd	665	0.3%
SM4	5G	AO	zstd	4000	503%

Encryption algorithm	Dataset size	Storage type	Compression type	Query duration (s)	Performance overhead
None	10G	AO	None	1160	0% (Baseline)
AES256	10G	AO	None	1212	4.48%
SM4	10G	AO	None	4000	244%

Encryption algorithm	Dataset size	Storage type	Compression type	Query duration (s)	Performance overhead
None	5G	AOCS	None	552	0%
AES	5G	AOCS	None	570	3.2%
SM4	5G	AOCS	None	3578	548%