Date: Wed, 10 Apr 2024 13:09:48 +0200
From: Matthias Apitz <guru@unixarea.de>
To: pgsql-general@lists.postgresql.org
Subject: mystery with postgresql.auto.conf
Message-ID: <ZhZzfLUm2QtqOURc@pureos>
Reply-To: Matthias Apitz <guru@unixarea.de>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/ZhZzfLUm2QtqOURc%40pureos>
Precedence: bulk


Hello,

I've a Linux development / QA server were three different PostgreSQL
cluster are setup and the corresponding (self built) PostgreSQL
software:

The software is below corresponding directories (always the full tree):

# ls -ld /usr/local/sisis-pap/pgsql-*
drwxr-xr-x 7 bin bin 4096 Mar 21 11:01 /usr/local/sisis-pap/pgsql-13.1
drwxr-xr-x 7 bin bin 4096 Mar 21 11:02 /usr/local/sisis-pap/pgsql-15.1
drwxr-xr-x 7 bin bin 4096 Mar 25 10:54 /usr/local/sisis-pap/pgsql-16.2

The cluster:

# ls -ld /data/pos*
drwxr-xr-x  3 postgres root     4096 May  7  2021 /data/postgresql131
drwxr-xr-x 12 postgres root     4096 Mar 28  2023 /data/postgresql151
drwxr-xr-x  3 postgres postgres 4096 Mar 28 12:32 /data/postgresql162

This is to test our application software for the different Pos versions.

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');

In the 15.1 server such command would give (correctly) an error, because
the software is not there:

# /usr/local/sisis-pap/pgsql-15.1/bin/psql -Usisis sisis
psql (15.1)
Type "help" for help.

sisis=# CREATE EXTENSION pg_tde;
ERROR:  extension "pg_tde" is not available
DETAIL:  Could not open extension control file "/usr/local/sisis-pap/pgsql-15.1/share/extension/pg_tde.control": No such file or directory.
HINT:  The extension must first be installed on the system where PostgreSQL is running.

How was this option set into the file postgresql151/data/postgresql.auto.conf?
And I did not do this by hand, I wasn't even aware until today that this
file exists at all.

	matthias


-- 
Matthias Apitz, ✉ guru@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub