Transparent Data Encryption (TDE)

Encrypting data at rest in a database management system (DBMS) refers to securing data by encrypting it when it is not being used or accessed. This is often done to protect sensitive data from unauthorized access or theft. With transparent data encryption (TDE), the encryption process is transparent to the user, allowing them to access and manipulate the data as usual without worrying about the encryption and decryption process. TDE can be applied at various levels, such as at the database, column, or cell level, depending on the organization’s specific security needs. Implementing TDE can help ensure that sensitive data is protected and only accessible to authorized users with the proper decryption keys.

Disk-level encryption

Disk-level encryption is a security measure that encrypts all data stored on a disk or storage device. The level granularity only encrypts the full or partial disk. This is independent of the database, and external tools can be used to achieve this.

• Disk-level encryption is a security measure that encrypts all data stored on a disk or storage device.
• It ensures that the data is protected against unauthorized access and can only be accessed by authorized users with the proper decryption keys.
• Disk-level encryption is often used to protect data at rest and can be implemented using full or partial disk encryption.
• Several tools are available for implementing disk-level encryption, including BitLocker for Windows, dm-crypt for Linux, and FileVault for MacOS.

Cluster-level encryption

Cluster-level encryption is a security measure that encrypts data stored in a cluster of servers or storage devices. Cluster-level encryption is a security measure that encrypts data stored in a cluster of servers or storage devices.

• It ensures that the data is protected against unauthorized access and can only be accessed by authorized users with the proper decryption keys.
• Cluster-level encryption can help protect data at rest and in motion when transmitted between servers in the cluster.
• Several database management systems support cluster-level encryption, including Oracle, MySQL, Microsoft SQL Server, MongoDB, and Cassandra.

Database-level encryption

Database-level encryption is the encryption of the entire database or all data stored within the database. 

• Database-level encryption is the encryption of an entire database or all data stored within the database.
• It can provide a high level of security for sensitive data and is often used in conjunction with other security measures, such as TDE and cluster-level encryption.
• Several database management systems support database-level encryption, including Oracle, MySQL, and Microsoft SQL Server.

Table-level encryption

Table-level encryption is a security measure that allows users to encrypt specific tables or columns within a database. It can provide a high level of security for sensitive data and is often used in conjunction with other security measures, such as TDE and cluster-level encryption. Here are some critical points about table-level encryption in a relational database management system.

• Table-level encryption allows users to selectively encrypt specific tables or columns within a database rather than encrypting the entire database or all data stored within it.
• It can provide a high level of security for sensitive data while still allowing users to access and manipulate it as usual.
• Table-level encryption is often used with other security measures, such as TDE and cluster-level encryption, to provide a more comprehensive security solution.
• Several RDBMSs, including Oracle, MySQL, and Microsoft SQL Server, support it.
• Implementing table-level encryption may require additional resources and impact performance, so carefully evaluating the trade-offs between security and performance is essential.

Encryption available in databases

Here is a comparison chart of the different levels of encryption available in various relational database management systems:

Encryption Level	Oracle	MySQL	Microsoft SQL Server	PostgreSQL
Cluster-Level	Transparent Data Encryption (TDE)	InnoDB Plugin	Transparent Data Encryption (TDE)	N/A
Database-Level	Transparent Data Encryption (TDE)	Encryption at Rest	Transparent Data Encryption (TDE)	N/A
Table-Level	Transparent Data Encryption (TDE)	Encryption at Rest	Transparent Data Encryption (TDE)	N/A
Column-Level	Transparent Data Encryption (TDE)	Encryption at Rest	Transparent Data Encryption (TDE)	pgcrypto

 

Encryption Level	Amazon RDS (Oracle)	Amazon RDS (MySQL)	Azure SQL Database	Google Cloud SQL (MySQL)
Cluster-Level	Transparent Data Encryption (TDE)	InnoDB Plugin	Transparent Data Encryption (TDE)	N/A
Database-Level	Transparent Data Encryption (TDE)	Encryption at Rest	Transparent Data Encryption (TDE)	Full Disk Encryption, Transparent Data Encryption (TDE), File-level Encryption
Table-Level	Transparent Data Encryption (TDE)	Encryption at Rest	Transparent Data Encryption (TDE)	Transparent Data Encryption (TDE)
Column-Level	Transparent Data Encryption (TDE)	Encryption at Rest	Transparent Data Encryption (TDE)	Transparent Data Encryption (TDE)

 

Key management

Key management is vital to data at rest encryption in a relational database management system. This process involves creating, distributing, and managing keys to encrypt and decrypt data.

Several key management strategies can be used in an RDBMS, including:

• Master Key Management: In this approach, a single master key is used to encrypt and decrypt all data in the RDBMS. This key is usually stored in a secure location, such as a hardware security module (HSM).
• Column-Level Key Management: In this approach, each column of data is encrypted using a separate key. This allows for more granular control over data access and improves performance because only the necessary columns are decrypted when querying data.
• Table-Level Key Management: Similar to column-level key management, this approach uses a separate key for each table in the RDBMS. This allows for even greater data access control and improved performance.

Regardless of which key management strategy is used, it is essential to have a robust system in place to ensure the security of encrypted data. This includes securely storing keys and quickly and easily rotating keys if there is a breach.

In addition to key management, it is also essential to consider the overall security of the RDBMS. This includes using secure protocols for communication, secure authentication methods, and implementing robust access controls to prevent unauthorized access to data.

Overall, key management is critical to data-at-rest encryption in an RDBMS. Organizations can protect their sensitive data from unauthorized access by implementing a strong key management system and taking steps to secure the RDBMS.

Transparent data encryption in PostgreSQL

The scope of TDE in PostgreSQL is limited to only column based. It does not encrypt other aspects of the database, such as table-level and database-level encryption; users may need to implement additional security measures to protect these database components.

PostgreSQL column-level encryption

We have discussed that PostgreSQL only has column-level encryption, which can be utilized by an extension called pgcrypto. This is the bare minimum implementation of encryption. It can only apply columns of the table, not on log files, whole rows, and databases.

The pgcrypto module in PostgreSQL is a powerful tool for securely storing and managing encrypted data within your database. With pgcrypto, you can easily encrypt data using a variety of algorithms and keys and then decrypt the data when needed. One of the key features of pgcrypto is its support for multiple encryption algorithms, including AES, Blowfish, and 3DES. This allows you to choose the best algorithm for your security needs and requirements.

To use pgcrypto, you will first need to install the extension in your PostgreSQL database. This can be done using the CREATE EXTENSION command:

CREATE EXTENSION pgcrypto;

Once the extension is installed, you can use the various functions of pgcrypto to encrypt and decrypt data. For example, you can use the pgp_sym_encrypt function to encrypt data using a symmetric key:

SELECT pgp_sym_encrypt('my secret data', 'my secret key', 'random_iv');

This will return the encrypted data as a bytea data type. The pgp_sym_encrypt function takes three arguments: the data to be encrypted, the symmetric key to be used for the encryption, and an initialization vector (IV) to be used for the encryption. The IV is a random value used to ensure that the same data encrypted with the same key will result in different encrypted outputs.

To decrypt the data, you can use the pgp_sym_decrypt function:

SELECT pgp_sym_decrypt(encrypted_data, 'my secret key', 'random_iv');

This will return the decrypted data as a text data type. The pgp_sym_decrypt function takes the same three arguments as the pgp_sym_encrypt function: the encrypted data, the symmetric key used to encrypt the data, and the IV used for the encryption.

In addition to symmetric key encryption, pgcrypto supports public-key encryption using the pgp_pub_encrypt and pgp_pub_decrypt functions. Public-key encryption is a type of encryption that uses a pair of keys: a public key and a private key. The public key encrypts the data, and the private key decrypts the data.

To use public-key encryption with pgcrypto, you will need to generate a pair of keys using the pgp_gen_key function:

SELECT * FROM pgp_gen_key('my_key', 'rsa', 2048, '', '', 'my@email.com');

This will generate a pair of keys and return them as a record. The pgp_gen_key function takes six arguments: the name of the key, the type of key to generate (in this case, “rsa”), the key size (in bits), optional passphrase and expiration values, and the user’s email address.

Once you have a pair of keys, you can use the pgp_pub_encrypt

Here is a complete example of encrypting and decrypting credit card data.

Now first, create a sample table with the serial number, credit card number, and when we created that card information. Here you can see only a credit card number is essential to hide from unauthorized users. Therefore we will only encrypt that column.

CREATE TABLE tbl_creadit_card (   id serial PRIMARY KEY,   credit_card bytea NOT NULL,   created_at timestamptz NOT NULL DEFAULT NOW() );

Now create a function generate_iv; the purpose of this function is to generate a random initialization vector (IV) that can be used for encryption. An IV is a random value that is used to ensure that the same data encrypted with the same key will result in different encrypted outputs. The generate_iv function can be called whenever an IV is needed for encryption.

CREATE OR REPLACE FUNCTION generate_iv()
RETURNS bytea AS
$$
BEGIN
  RETURN gen_random_bytes(16);
END;
$$
LANGUAGE plpgsql;

The next step is to create encrypt_data. This function takes two input parameters: data of type bytea and key of type text. It returns a bytea data type. Inside the function, it first generates a random initialization vector (IV) using the generate_iv function. This is done using a WITH clause and a subquery. Next, the function uses the pgp_sym_encrypt function to encrypt the data parameter using the key and iv as input. The encrypted data is returned by the function using the SELECT statement. The purpose of this function is to provide a convenient way to encrypt data using a symmetric key and a random IV. You can call the encrypt_data function and pass in the data you want to encrypt and the key for encryption. The function will generate the random IV and perform the encryption for you.

CREATE OR REPLACE FUNCTION encrypt_data(data bytea, key text)
RETURNS bytea AS
$$
BEGIN
  -- Generate a random IV
  WITH random_iv AS (
    SELECT generate_iv() AS iv
  )
  -- Encrypt the data using the key and IV
  SELECT pgp_sym_encrypt(card_number, key, iv)
  FROM random_iv;
END;
$$
LANGUAGE plpgsql;

Insert a row in the table.

INSERT INTO encrypted_data (encrypted_data)
VALUES (encrypt_data('6371-3263-3461-2735', 'MyKey'));

SELECT ed.id, decrypt_data(ed.card_number, 'Mykey') AS card_number, ed.created_at
FROM tbl_creadit_card ed
WHERE ed.id = 1;
 id |  card_number        |     created_at ----+---------------------+----------------------------
  1 | 6371-3263-3461-2735 | 2022-12-20 12:34:56

The pgcrypto extension in PostgreSQL provides several cryptographic functions that perform various types of encryption and decryption. Some of the encryption algorithms that are supported include:

• AES (Advanced Encryption Standard)
• Blowfish
• DES (Data Encryption Standard)
• MD5 (Message Digest Algorithm 5)
• SHA-1 (Secure Hash Algorithm 1)
• SHA-2 (Secure Hash Algorithm 2)
• SHA-3 (Secure Hash Algorithm 3)
• HMAC (Keyed-Hash Message Authentication Code)

PostgreSQL transparent data encryption future

Currently, there is no Transparent Data Encryption available in vanilla PostgreSQL. There have been many discussions in the PostgreSQL community to implement that, but none was committed. But there are some PostgreSQL forks owned by companies that provide these. Here is an example architecture for the Transparent data encryption for PostgreSQL.

Conclusion

Overall, TDE is a powerful tool for protecting sensitive data in a database. By encrypting data at rest and in motion, TDE helps to ensure that the data is secure throughout its lifecycle. Using key management techniques, such as an EMK or an external KMS, users can further secure their encrypted data and maintain control over it.