PostgreSQL differs from certain other databases as it lacks a native solution to encrypt data on disk transparently. At Percona, we are in the process of developing PG_TDE, a tool designed to encrypt data using the AES-CTR encryption algorithm. This code is currently under development, and we are seeking your assistance in testing it.
This blog will cover what you need to get PG_TDE running on a Ubuntu 22.04 LTS environment.
But first, some background information.
Transparent Data Encryption
PG_TDE is an experimental transparent data encryption access method for PostgreSQL 16 and beyond that I reviewed last October. There have been a lot of changes that covered in this post. This software is under active development. In the spirit of open and transparent communication, we would appreciate your feedback and invite PostgreSQL users to test the extension and provide feedback via the GitHub repository or in the forum.
We need your help
Transparent Data Encryption (TDE) offers encryption at the file level and solves the problem of protecting data at rest. This capability is available in other databases but not provided in upstream, vanilla Postgres.
Percona has received user feedback that this would be a feature, so we are working on this as an open-source extension for Postgres that anyone can deploy. Percona co-founder Peter Zaitsev’s blog on why PostgreSQL needs TDE highlights the technical and business reasons why you might want TDE. Since PostgreSQL doesn’t have TDE features yet, Percona will provide the TDE feature as an extension to PostgreSQL.
But we need your help. Transparent Data Encryption is a complex environment that needs extensive testing. Please download PG_TDE to a test machine, test the pg_tde, and let us know your experience – good, bad, or indifferent. Percona would like this code hammered as hard as possible to ensure it is robust and ready to become part of a future version of PostgreSQL. Please test.
Ubuntu and PG_TDE
You will need an Ubuntu 22.04 LTS system before you continue, and other similar Linux distros should work similarly. I also installed the ‘generic’ PostgreSQL from the Ubuntu distro. Percona has a wonderful distribution of PostgreSQL that I usually use, but I wanted to see if PG_TDE worked with Ubuntu.
The first step is to get PG_TDE. Please note that the documentation can be found at HERE.
1 | wget https://github.com/Percona-Lab/postgres-tde-ext/releases/download/latest/pgtde-pgdg16.deb |
Install the pgtde-pgdg16.deb
1 | sudo dpkg -i pgtde-pgdg16.deb |
The needed libraries need to be preloaded, so login to your instance as postgres and issue the command:
1 | ALTER SYSTEM SET shared_preload_libraries = 'pg_tde'; |
Restart PostgreSQL
1 | sudo systemctl restart postgresql.service |
As postgres, log into your PostgreSQL instance and get ready to test.
Test
Let us start this test by creating a database and connecting to it.
1 2 3 4 5 6 | postgres=# dt Did not find any relations. postgres=# create database tdetest; CREATE DATABASE postgres=# c tdetest; You are now connected to database "tdetest" as user "postgres". |
I borrow heavily from the blog Using the Transparent Data Encryption Extension PG_TDE With PostgreSQL for the next several steps as they beautifully illustrate how to take advantage of PG_TDE. Please note in the first CREATE TABLE the use of ‘USING pg_tde’ to encrypt the data.
1 2 3 4 | detest=#CREATE TABLE test_enc (id SERIAL, t VARCHAR(32), PRIMARY KEY(id)) USING pg_tde; detest=#INSERT INTO test_enc (t) VALUES ('foobar'), ('barfoo'); detest=#CREATE TABLE test_basic (id SERIAL, t VARCHAR(32), PRIMARY KEY(id)); detest=#INSERT INTO test_basic (t) VALUES ('foobar'), ('barfoo'); |
If you see an error message about the pg_tde access method missing, please use the following:
1 2 3 4 | ERROR: access method "pg_tde" does not exist tdetest=# create extension pg_tde; CREATE EXTENSION tdetest=# |
You might be asking yourself if there is any discernable difference when you request data from the encrypted version versus the unencrypted. Nope.
1 2 3 4 5 6 7 8 9 10 11 12 13 | detest=#SELECT * FROM test_enc; id | t ----+-------- 1 | foobar 2 | barfoo (2 rows) detest=#SELECT * FROM test_basic; id | t ----+-------- 1 | foobar 2 | barfoo (2 rows) |
But you can use pgtde_is_encrypted(<table name>) to determine if the table is encrypted.
1 2 3 4 5 6 7 8 9 10 11 | tde2=# select pgtde_is_encrypted('test_basic'); pgtde_is_encrypted -------------------- f (1 row) tde2=# select pgtde_is_encrypted('foo_enc'); pgtde_is_encrypted -------------------- t (1 row) |
Please test
Percona is committed to delivering PG_TDE, but we need your help. The more instances of this software running and from which we get feedback, the better the product will be. This is your chance to help drive PostgreSQL forward by hammering on this code as hard as possible. This will also help keep you from being an ‘edge case’ where some obscure problem delays your ability to protect your data.
