Thread

1. mystery with postgresql.auto.conf

   Matthias Apitz <guru@unixarea.de> — 2024-04-10T11:09:48Z

   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
   
   
   
   

2. Re: mystery with postgresql.auto.conf

   Ian Lawrence Barwick <barwick@gmail.com> — 2024-04-10T12:08:56Z

   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
   
   
   
   

3. Re: mystery with postgresql.auto.conf

   Matthias Apitz <guru@unixarea.de> — 2024-04-10T12:40:37Z

   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
   
   
   
   

4. Re: mystery with postgresql.auto.conf

   Ian Lawrence Barwick <barwick@gmail.com> — 2024-04-10T12:49:49Z

   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
   
   
   
   

5. Re: mystery with postgresql.auto.conf

   Tom Lane <tgl@sss.pgh.pa.us> — 2024-04-10T14:26:05Z

   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
   
   
   
   

6. Re: mystery with postgresql.auto.conf

   Laurenz Albe <laurenz.albe@cybertec.at> — 2024-04-10T14:33:50Z

   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
   
   
   
   

7. Re: mystery with postgresql.auto.conf

   Greg Sabino Mullane <htamfids@gmail.com> — 2024-04-10T16:34:15Z

   On Wed, Apr 10, 2024 at 8:40 AM Matthias Apitz <guru@unixarea.de> wrote:
   
   > Maybe later I did it accidently against the 15.1 server from the psql
   > 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