Validate Encryption with pg_tde

After enabling the pg_tde extension for a database, you can begin encrypting data using the tde_heap table access method.

Encrypt data in a new table

1. 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.

2. 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.

3. (Optional) Rotate the principal key.

To re-encrypt the data using a new key, 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;

Important

Using SET ACCESS METHOD drops hint bits which can impact query performance. To restore performance, run:

    SELECT count(*) FROM table_name;

This forces PostgreSQL to check every tuple for visibility and reset the hint bits.

Hint

Want to remove encryption later? See how to decrypt your data.

Next steps

Configure WAL encryption (tech preview)