public inbox for [email protected]help / color / mirror / Atom feed
Re: mystery with postgresql.auto.conf 6+ messages / 5 participants [nested] [flat]
* Re: mystery with postgresql.auto.conf @ 2024-04-10 12:08 Ian Lawrence Barwick <[email protected]> 0 siblings, 1 reply; 6+ messages in thread From: Ian Lawrence Barwick @ 2024-04-10 12:08 UTC (permalink / raw) To: Matthias Apitz <[email protected]>; +Cc: [email protected] Hi 2024年4月10日(水) 20:10 Matthias Apitz <[email protected]>: (...) > 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 ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: mystery with postgresql.auto.conf @ 2024-04-10 12:40 Matthias Apitz <[email protected]> parent: Ian Lawrence Barwick <[email protected]> 0 siblings, 2 replies; 6+ messages in thread From: Matthias Apitz @ 2024-04-10 12:40 UTC (permalink / raw) To: Ian Lawrence Barwick <[email protected]>; +Cc: [email protected] 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, ✉ [email protected], http://www.unixarea.de/ +49-176-38902045 Public GnuPG key: http://www.unixarea.de/key.pub ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: mystery with postgresql.auto.conf @ 2024-04-10 12:49 Ian Lawrence Barwick <[email protected]> parent: Matthias Apitz <[email protected]> 1 sibling, 1 reply; 6+ messages in thread From: Ian Lawrence Barwick @ 2024-04-10 12:49 UTC (permalink / raw) To: Matthias Apitz <[email protected]>; +Cc: [email protected] 2024年4月10日(水) 21:40 Matthias Apitz <[email protected]>: > > 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 ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: mystery with postgresql.auto.conf @ 2024-04-10 14:26 Tom Lane <[email protected]> parent: Ian Lawrence Barwick <[email protected]> 0 siblings, 1 reply; 6+ messages in thread From: Tom Lane @ 2024-04-10 14:26 UTC (permalink / raw) To: Ian Lawrence Barwick <[email protected]>; +Cc: Matthias Apitz <[email protected]>; [email protected] Ian Lawrence Barwick <[email protected]> writes: > 2024年4月10日(水) 21:40 Matthias Apitz <[email protected]>: >> 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 ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: mystery with postgresql.auto.conf @ 2024-04-10 14:33 Laurenz Albe <[email protected]> parent: Tom Lane <[email protected]> 0 siblings, 0 replies; 6+ messages in thread From: Laurenz Albe @ 2024-04-10 14:33 UTC (permalink / raw) To: Tom Lane <[email protected]>; Ian Lawrence Barwick <[email protected]>; +Cc: Matthias Apitz <[email protected]>; [email protected] On Wed, 2024-04-10 at 10:26 -0400, Tom Lane wrote: > Ian Lawrence Barwick <[email protected]> writes: > > 2024年4月10日(水) 21:40 Matthias Apitz <[email protected]>: > > > 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 ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: mystery with postgresql.auto.conf @ 2024-04-10 16:34 Greg Sabino Mullane <[email protected]> parent: Matthias Apitz <[email protected]> 1 sibling, 0 replies; 6+ messages in thread From: Greg Sabino Mullane @ 2024-04-10 16:34 UTC (permalink / raw) To: Matthias Apitz <[email protected]>; +Cc: Ian Lawrence Barwick <[email protected]>; [email protected] On Wed, Apr 10, 2024 at 8:40 AM Matthias Apitz <[email protected]> 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 ^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2024-04-10 16:34 UTC | newest] Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2024-04-10 12:08 Re: mystery with postgresql.auto.conf Ian Lawrence Barwick <[email protected]> 2024-04-10 12:40 ` Matthias Apitz <[email protected]> 2024-04-10 12:49 ` Ian Lawrence Barwick <[email protected]> 2024-04-10 14:26 ` Tom Lane <[email protected]> 2024-04-10 14:33 ` Laurenz Albe <[email protected]> 2024-04-10 16:34 ` Greg Sabino Mullane <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox