Securing your data can be a tough task. Unlike some other databases, PostgreSQL does not have a native solution to encrypt data on disk transparently. Percona is developing PG_TDE to encrypt that data using the AES-CTR encryption algorithm. This code is in development, and we need your help testing it.
This blog will cover what you need to get PG_TDE running in a Docker container.
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.
Docker and PG_TDE
You will need Docker installed on your system before you continue. Please refer to the documentation for your operating system. The complete documentation can be found at HERE.
The first step is to direct Docker to download and run PG_TDE:
1 | docker run --name pg-tde -e POSTGRES_PASSWORD=mysecretpassword -d perconalab/postgres-tde-ext |
Now we can connect to the PostgreSQL instance:
1 | docker run -it --rm postgres psql -h 172.17.0.2 -U postgres |
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'); |
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.
