Re: mystery with postgresql.auto.conf

Hi

2024年4月10日(水) 20:10 Matthias Apitz <guru(at)unixarea(dot)de>:
(...)
> End of March I started to investigate the TDE extension pg_tde within
> the 16.2 server. And only this software contains this extension:
>
> # find /usr/local/sisis-pap/pgsql** | grep pg_tde
> /usr/local/sisis-pap/pgsql-16.2/lib/pg_tde.so
> /usr/local/sisis-pap/pgsql-16.2/share/extension/pg_tde--1.0.sql
> /usr/local/sisis-pap/pgsql-16.2/share/extension/pg_tde.control
>
> Today I wanted to start the 15.1 server and it failed with:
>
> 2024-04-10 11:32:32.179 CEST [14017] FATAL: could not access file "pg_tde": No such file or directory
> 2024-04-10 11:32:32.181 CEST [14017] LOG: database system is shut down
>
> I investigated the reason and found that the pg_tde extension was
> enabled also in the 15.1 server's file postgresql.auto.conf
>
> # ls -l pos*/data/postgresql.auto.conf
> -rw------- 1 postgres postgres 88 May 7 2021 postgresql131/data/postgresql.auto.conf
> -rw------- 1 postgres postgres 124 Mar 28 11:35 postgresql151/data/postgresql.auto.conf
> -rw------- 1 postgres postgres 124 Mar 28 12:58 postgresql162/data/postgresql.auto.conf
>
> # cat postgresql151/data/postgresql.auto.conf
> # Do not edit this file manually!
> # It will be overwritten by the ALTER SYSTEM command.
> shared_preload_libraries = 'pg_tde'
>
> How is this possible? I only used in the 16.2 server the SQL commands:
>
> sisis=# CREATE EXTENSION pg_tde;
> sisis=# SELECT pg_tde_add_key_provider_file('file','/tmp/pgkeyring');
> sisis=# SELECT pg_tde_set_master_key('my-master-key','file');

The simplest explanation is that you (or someone), when configuring pg_tde,
accidentally executed (as per the instructions [*]):

ALTER SYSTEM SET shared_preload_libraries = 'pg_tde';

in the 15.1 instance, rather than the 16.2 instance. This will have
resulted in the
entry in the 15.1 postgresql.auto.conf.

[*] https://github.com/Percona-Lab/pg_tde?tab=readme-ov-file#installation-steps

Regards

Ian Barwick