Data encryption consumes a significant part of the CPU resources and adds complexity to collaboration with technical support. To minimize these repercussions, Postgres Pro Enterprise allows encrypting only confidential data by moving them to special tablespaces. Encryption and decryption processes happen on the fly and do not cause any DBMS unavailability for the users. It also doesn't affect applications, working with DBMS, as all the queries to the DB encrypted data is processed transparently, as if it was unencrypted.

pgpro_tde calls an OpenSSL library to encrypt and decrypt data with AES algorithm. It uses OpenSSL, provided within the certified OS. It encrypts data files using the AES-256-GCM symmetric key algorithm that provides both data authenticity (integrity) and confidentiality. It encrypts WAL files using AES-256-CTR, incorporating LSN (log sequence number) as the counter component.

OpenSSL utilizes vectorization command extensions like AVX-512 for GCM encryption method. With this extension AES-256-GCM gets a dramatic performance boost. Check if your hardware and virtualization software support this extended commands set to ensure superior TDE performance.

Postgres Pro Enterprise encrypts relation files in the dedicated tablespaces using respective tablespace encryption keys, and encrypts WAL files using a special WAL encryption key. These keys are unique sequences of bytes and the system keeps them securely encrypted in the $PGDATA/pg_encryption/keys file. On start-up, the system calls for an external security system to decrypt this file data and then keeps these keys in its memory.

The external security system, mentioned above, has its own main master key, which it uses to encrypt and decrypt keyset of the system's tablespace and WAL encryption keys. The master key is generated by and stored in an external key management system. For more details, see Section G.10.10.

There is one key set file per cluster named keys that contains all the tablespace and WAL encryption keys. The system keeps it located under the $PGDATA/pg_encryption folder. When new keys are created (e.g. after rotation), the system calls an external system to encrypt them and stores them at the end of the same file. Postgres Pro Enterprise never stores the keys in plaintext without encryption.

To boost security even more, mind the key rotation that allows limiting the data encrypted with a single tablespace encryption key. Rotation generates a new tablespace key, which encrypts all the new data. The old data is still available encrypted by means of the old keys it was encrypted with. When the DBMS updates a data file page, it re-encrypts it with a new key. Keys rotation occurs on-the-fly and doesn't require restarting the database server. To rotate a specific tablespace keys, run this command:

SELECT pg_rotate_encryption_key(tablespace oid)

Or

select pg_rotate_encryption_key((select oid from pg_tablespace where spcname='my-encrypted-tablespace-name'))

If your master key expired or was compromised, a new master key must be generated and applied. The way it can be done depends on your selected solution. For demonstration purposes, HashiCorp Vault is used.

Postgres Pro Enterprise stores keys under ${PGDATA}/pg_encryption/keys. For safety purposes, all the keys in this file are encrypted with a master kay that is store in the HashiCorp Vault. To set the vault up, the listener address an the instance token are required. The setup is done with the environment variables, here VAULT_ADDR and VAULT_TOKEN are given as an example:

echo VAULT_ADDR='http://127.0.0.1:8200' > /etc/env.d/99vault
echo VAULT_TOKEN="hvs.C77DySgOTjliYqmtp3yA4osP" >> /etc/env.d/99vault

Enable transit with HashiCorp Vault Transit Secrets Engine:

vault secrets enable transit

Create a new key and give it a new unique name:

vault write -f transit/keys/pg-tde-new-master-key

When a new key is issued, you should re-encrypt your keyset file with the new master key.

cat keys | vault write -field=plaintext transit/decrypt/pg-tde-master-key ciphertext=- | vault write -field=ciphertext transit/encrypt/pg-tde-new-master-key plaintext=- > keys

By default, HashiCorp Vault works with the 256-bit keys. If any other keys are required, address HashiCorp Vault server admins to update the settings.

To update your tablespace keys, the DBMS admin must call the SQL function pg_rotate_encryption_key with a tablespace oid to generate a new key:

postgres=# \db+
                                                      List of tablespaces
    Name    |  Owner   |                 Location                 | Access privileges |     Options     |  Size  | Description
------------+----------+------------------------------------------+-------------------+-----------------+--------+-------------
 encrypted  | postgres | /var/lib/pgpro/ent-17/meta/pg_encryption |                   | {encryption=on} | 68 kB  |
 pg_default | postgres |                                          |                   |                 | 22 MB  |
 pg_global  | postgres |                                          |                   |                 | 667 kB |
(3 rows)

postgres=# select * from pg_tablespace where spcname='encrypted';
  oid  |  spcname  | spcowner | spcacl |   spcoptions
-------+-----------+----------+--------+-----------------
 24587 | encrypted |       10 |        | {encryption=on}
(1 row)

postgres=# select pg_rotate_encryption_key(24587);
 pg_rotate_encryption_key
--------------------------
 t
(1 row)

Postgres Pro Enterprise stores keys under ${PGDATA}/pg_encryption/encryption_keys. For safety purposes, all the keys in this file are encrypted with a master key that is store in the HashiCorp Vault.

To set the vault up, the listener address an the instance token are required. The setup is done with the environment variables, here VAULT_ADDR and VAULT_TOKEN are given as an example:

echo VAULT_ADDR='http://127.0.0.1:8200' > /etc/env.d/99vault

echo VAULT_TOKEN="hvs.C77DySgOTjliYqmtp3yA4osP" >> /etc/env.d/99vault

Enable transit with HashiCorp Vault Transit Secrets Engine:

vault secrets enable transit

Create a key and give it a name:

vault write -f transit/keys/pg-tde-master-key

By default, HashiCorp Vault works with the 256-bit keys. If any other keys are required, address HashiCorp Vault server admins to update the settings.

To make sure everything is set up properly, run express tests:

If set up correctly, >${PGDATA}/pg_encryption/encryption_keys will contain this type of data:

vault:v1:rdaF+sSfkKn9HBzdalat7mnynRRR6b00FQS159PCUMGmMZJYMNm/hWxbFFIeV8C43nO3UV4dmgn7pxnJkwo4IYq2+sUHSWT/

It is advised that HashiCorp Vault is already integrated and managed by our company admins. However, for testing purposes you may need an independent instance of the vault server that is configured for the Postgres Pro Enterprise TDE specifically.

apt-get install -y wget net-tools apt-transport-https

wget -O- https://apt.releases.hashicorp.com/gpg | gpg --dearmor > /tmp/hashicorp.gpg

mv /tmp/hashicorp.gpg /etc/apt/trusted.gpg.d/

chown root:root /etc/apt/trusted.gpg.d/hashicorp.gpg

chmod ugo+r /etc/apt/trusted.gpg.d/hashicorp.gpg

chmod go-w /etc/apt/trusted.gpg.d/hashicorp.gpg

echo "deb https://apt.releases.hashicorp.com $(lsb_release -cs) main" | tee /etc/apt/sources.list.d/hashicorp.list

apt-get update

apt-get install vault

Vault v1.15.6, built 2024-09-17T15:25:10Z

systemctl enable vault --now

systemctl status vault

typeset -x VAULT_SKIP_VERIFY=true

api_addr                = "https://127.0.0.1:8200"
cluster_addr            = "https://127.0.0.1:8210"
cluster_name            = "local-vault-cluster"
disable_mlock           = true
ui                      = true

backend "raft" {
        node_id = "local-vault-server"
        path = "/var/lib/vault"
}

listener "tcp" {
        address = "[::]:8200"
        cluster_address = "[::]:8210"
        tls_cert_file = "/var/lib/vault/cert.pem"
        tls_key_file  = "/var/lib/vault/key.pem"
}

listener "tcp" {
        address = "127.0.0.1:8201"
        cluster_address = "127.0.0.1:8210"
        tls_disable = 1
}

openssl req -x509 -newkey rsa:4096 -sha256 -days 365 \

    -nodes -keyout /var/lib/vault/key.pem -out /var/lib/vault/cert.pem \

    -subj "/CN=localhost" \

    -addext "subjectAltName=DNS:localhost,IP:127.0.0.1"

vault operator init -key-shares=1 -key-threshold=1

vault operator unseal key

vault login token