Test Transparent Data Encryption¶

Enabling pg_tde extension for a database creates the table access method tde_heap . This access method enables you to encrypt the data.

Here’s how to do it:

Encrypt data in a new table¶

Create a table in the database for which you have enabled pg_tde using the tde_heap access method as follows:
```
CREATE TABLE <table_name> (<field> <datatype>) USING tde_heap;
```
Warning: Example for testing purposes only:
```
CREATE TABLE albums (
album_id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
artist_id INTEGER,
title TEXT NOT NULL,
released DATE NOT NULL
) USING tde_heap;
```
Learn more about table access methods and how you can enable data encryption by default in the Table access methods section.
To check if the data is encrypted, run the following function:
```
SELECT pg_tde_is_encrypted('table_name');
```
The function returns t if the table is encrypted and f - if not.
Rotate the principal key when needed, see Principal key management)

Encrypt existing table¶

You can encrypt an existing table. It requires rewriting the table, so for large tables, it might take a considerable amount of time.

Run the following command:

ALTER TABLE table_name SET ACCESS METHOD tde_heap;

Note that the SET ACCESS METHOD command drops hint bits and this may affect the performance. Running a plain SELECT count(*) or VACUUM commands on the entire table will check every tuple for visibility and set its hint bits. Therefore, after executing the ALTER TABLE command, run a simple count(*) on your tables:

SELECT count(*) FROM table_name;

Hint

If you no longer wish to use pg_tde or wish to switch to using the tde_heap_basic access method, see how you can decrypt your data.