Обсуждение: [MASSMAIL]mystery with postgresql.auto.conf

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
suchfile 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

Hi

2024年4月10日(水) 20:10 Matthias Apitz <guru@unixarea.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

El día miércoles, abril 10, 2024 a las 09:08:56 +0900, Ian Lawrence Barwick escribió:

> > # 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.

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, ✉ guru@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub

2024年4月10日(水) 21:40 Matthias Apitz <guru@unixarea.de>:
>
> El día miércoles, abril 10, 2024 a las 09:08:56 +0900, Ian Lawrence Barwick escribió:
>
> > > # 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.
>
> 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?

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

Ian Lawrence Barwick <barwick@gmail.com> writes:
> 2024年4月10日(水) 21:40 Matthias Apitz <guru@unixarea.de>:
>> 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

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

On Wed, 2024-04-10 at 10:26 -0400, Tom Lane wrote:
> Ian Lawrence Barwick <barwick@gmail.com> writes:
> > 2024年4月10日(水) 21:40 Matthias Apitz <guru@unixarea.de>:
> > > 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
>
> 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.

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