Subject: Re: mystery with postgresql.auto.conf

From: Ian Lawrence Barwick <[email-masked]>
Date: Wed, 10 Apr 2024 21:08:56 +0900

Hi

2024年4月10日(水) 20:10 Matthias Apitz <[email-masked]>:
(...)
> 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

---

From: Matthias Apitz <[email-masked]>
Date: Wed, 10 Apr 2024 14:40:37 +0200

> entry in the 15.1 postgresql.auto.conf.

Here are my notes from the testing pg_tde:

Install sisis-pap v73 and create a PostgreSQL 16.2 cluster the
usual way, load a database dump into it (all done on
srap21dxr1.dev.oclc.org)

I followed exactly https://github.com/Percona-Lab/pg_tde?tab=readme-ov-file

psql -Usisis sisis
psql (16.2)
Type "help" for help.

sisis=# ALTER SYSTEM SET shared_preload_libraries = 'pg_tde';

(PostgreSQL restart)
...

The notes have been done by cut&paste into a text file. The psql
was fired up against the 16.2 server as it says above. And we also
have never two servers up at the same time.

Maybe later I did it accidently against the 15.1 server from the psql
history. I just tested it in the 15.1 server: it does not give any
error:

psql -Usisis sisis
psql (15.1)
Type "help" for help.

sisis=# ALTER SYSTEM SET shared_preload_libraries = 'pg_tde';
ALTER SYSTEM
sisis=#

and the file gets modified :-(

Why it does not give an error because the shared lib isn't there?

	matthias

-- 
Matthias Apitz, ✉ [email-masked], http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub

---

From: Ian Lawrence Barwick <[email-masked]>
Date: Wed, 10 Apr 2024 21:49:49 +0900

> Why it does not give an error because the shared lib isn't there?

ALTER SYSTEM is a way of modifying the PostgreSQL configuration file
via SQL; just as when you modify it manually, changes are not applied
until you actually reload the configuration.

See: https://www.postgresql.org/docs/current/sql-altersystem.html

Regards

Ian Barwick

---

From: Tom Lane <[email-masked]>
Date: Wed, 10 Apr 2024 10:26:05 -0400

> See: https://www.postgresql.org/docs/current/sql-altersystem.html

Even if you had issued a reload, you would not have noticed the
faulty setting without looking into the postmaster's log for
warning messages.  The system wouldn't get in your face about it
until you did a postmaster restart.

			regards, tom lane

---

From: Laurenz Albe <[email-masked]>
Date: Wed, 10 Apr 2024 16:33:50 +0200

> until you did a postmaster restart.

An alternative to looking at the log file is to

  SELECT * FROM pg_file_settings WHERE error IS NOT NULL;

after you reload.

Yours,
Laurenz Albe

---

From: Greg Sabino Mullane <[email-masked]>
Date: Wed, 10 Apr 2024 12:34:15 -0400

On Wed, Apr 10, 2024 at 8:40 AM Matthias Apitz <[email-masked]> wrote:

> history.

Yes, as shown by the timestamps from your first post:

-rw------- 1 postgres postgres 124 Mar 28 11:35
> postgresql151/data/postgresql.auto.conf
>

This is also a good reason to set your log_statement to 'ddl', which will
put the ALTER SYSTEM change into your database logs.

Cheers,
Greg