Docker Image does not encrypt?

[Torkolis]

when I run the image it says that the pgpkeyring file does not exist so it does not load any keys.

logs

docker run --name pg-tde3 --rm -e POSTGRES_PASSWORD=mysecretpassword perconalab/postgres-tde-ext
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.utf8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /var/lib/postgresql/data ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Etc/UTC
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok


Success. You can now start the database server using:

  pg_ctl -D /var/lib/postgresql/data -l logfile start

initdb: warning: enabling "trust" authentication for local connections
initdb: hint: You can change this by editing pg_hba.conf or using the option -A, or --auth-local and --auth-host, the next time you run initdb.
waiting for server to start....2023-12-14 12:52:42.940 GMT [49] WARNING:  The file referenced by pg_tde.keyringConfigFile is writable for the database process
2023-12-14 12:52:42.940 GMT [49] LOG:  registered custom resource manager "test_pg_tde_custom_rmgr" with ID 128
2023-12-14 12:52:42.948 GMT [49] WARNING:  Keyring file '/etc/postgresql/pgkeyring' not found, not loading existing keys.
2023-12-14 12:52:42.949 GMT [49] LOG:  starting PostgreSQL 16.1 (Debian 16.1-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit
2023-12-14 12:52:42.953 GMT [49] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2023-12-14 12:52:42.963 GMT [52] LOG:  database system was shut down at 2023-12-14 12:52:42 GMT
2023-12-14 12:52:42.969 GMT [49] LOG:  database system is ready to accept connections
done
server started

/usr/local/bin/docker-entrypoint.sh: sourcing /docker-entrypoint-initdb.d/pg-tde-create-ext.sh
CREATE EXTENSION
CREATE EXTENSION

/usr/local/bin/docker-entrypoint.sh: sourcing /docker-entrypoint-initdb.d/pg-tde-streaming-repl.sh

waiting for server to shut down...2023-12-14 12:52:43.127 GMT [49] LOG:  received fast shutdown request
.2023-12-14 12:52:43.131 GMT [49] LOG:  aborting any active transactions
2023-12-14 12:52:43.133 GMT [49] LOG:  background worker "logical replication launcher" (PID 55) exited with exit code 1
2023-12-14 12:52:43.133 GMT [50] LOG:  shutting down
2023-12-14 12:52:43.137 GMT [50] LOG:  checkpoint starting: shutdown immediate
2023-12-14 12:52:43.182 GMT [50] LOG:  checkpoint complete: wrote 55 buffers (0.3%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.006 s, sync=0.029 s, total=0.049 s; sync files=44, longest=0.012 s, average=0.001 s; distance=178 kB, estimate=178 kB; lsn=0/1517400, redo lsn=0/1517400
2023-12-14 12:52:43.186 GMT [49] LOG:  database system is shut down
done
server stopped

PostgreSQL init process complete; ready for start up.

2023-12-14 12:52:43.243 GMT [1] WARNING:  The file referenced by pg_tde.keyringConfigFile is writable for the database process
2023-12-14 12:52:43.243 GMT [1] LOG:  registered custom resource manager "test_pg_tde_custom_rmgr" with ID 128
2023-12-14 12:52:43.252 GMT [1] WARNING:  Keyring file '/etc/postgresql/pgkeyring' not found, not loading existing keys.
2023-12-14 12:52:43.252 GMT [1] LOG:  starting PostgreSQL 16.1 (Debian 16.1-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit
2023-12-14 12:52:43.252 GMT [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2023-12-14 12:52:43.252 GMT [1] LOG:  listening on IPv6 address "::", port 5432
2023-12-14 12:52:43.259 GMT [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2023-12-14 12:52:43.266 GMT [67] LOG:  database system was shut down at 2023-12-14 12:52:43 GMT
2023-12-14 12:52:43.272 GMT [1] LOG:  database system is ready to accept connections

[Torkolis]

Ok I managed to get it to work, you need to mention in the documentation that we need to set
default_table_access_method = 'pg_tde'
in the /etc/postgresql/postgresql.conf File to automatically encrypt all tables without having to specify the using pg_tde part when creating a new table.

Question: does any1 know if it is possible to specify this per database or tablespace? looks like it is only for the whole database.

Also: Can we have a viable configuration example for vault? I mean which secrets engine would I have to enable and store the data? The documentation says I need to pass the root token, which is problematic, is it possible to make an own approle access method for this? so it does not have access to EVERYTHING in vault? My own experiment fails with:
2023-12-15 12:02:00.696 GMT [98] ERROR: failed to retrieve master key

my config looks like this:

{
        'provider': 'vault-v2',
        'token': '<XXXXXXXXXXXXXXXXXX>',
        'url': 'https://vault.<domain.com>',
        'mountPath': 'secret',
        'caPath': '/etc/postgresql/ca.crt'
}

which occurs upon creation of a table, which is not good I think, it should retrieve the secret upon startup once and keep it in RAM instead of retrieving it on every database interaction.

[Torkolis]

Please mark this as documentation:

1. to enable encryption of all tables by default set default_table_access_method = 'pg_tde' in /etc/postgresql/postgresql.conf (probably even better to ship the docker image with this option enabled).

2. Vault configuration:
   enable kv engine with path "secret" (mounPath parameter from tde_conf.json file)
   optional: create secret "master-key-1" in the engine containing any custom data, entry name "key", if skept pg_tde will create one instead (with some random data?)
   here we can choose to insert root token into pg_tde.conf or create a separate token for pg_rde. If choosing a separate token follow these steps:
   create a policy of any name e.g.: pg_tde:
   path "secret*" { capabilities = ["read"] }
   (["create"] capability also needed if letting pg_tde create master-key-1 instead of manually doing it)
   create token for pg_tde: can only be created from cli (not in browser UI though):

vault login
vault token create -policy=pg_tde

token needs to be kept in mind when it expires