Thread: Question about user/database-level parameters

Question about user/database-level parameters

From
Japin Li
Date:
Hi, hackers

When I try to modify the parameters for all users in the following command [1]
(the library doesn't exist), and I quit the connection, I cannot log in the
database, how can I bypass this checking?

I find those parameters loaded by process_settings(), and it seems no way to
disable this loading process.  If we could bypass this checking, how can we
fix these parameters?

[1]
postgres=# ALTER ROLE all SET local_preload_libraries TO fdafd;
ALTER ROLE
postgres=# \q

$ psql postgres
psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: FATAL:  could not access file
"$libdir/plugins/fdafd":No such file or directory
 

-- 
Regrads,
Japin Li.
ChengDu WenWu Information Technology Co.,Ltd.



Re: Question about user/database-level parameters

From
Kyotaro Horiguchi
Date:
At Mon, 01 Aug 2022 18:24:33 +0800, Japin Li <japinli@hotmail.com> wrote in 
> 
> Hi, hackers
> 
> When I try to modify the parameters for all users in the following command [1]
> (the library doesn't exist), and I quit the connection, I cannot log in the
> database, how can I bypass this checking?
> 
> I find those parameters loaded by process_settings(), and it seems no way to
> disable this loading process.  If we could bypass this checking, how can we
> fix these parameters?
> 
> [1]
> postgres=# ALTER ROLE all SET local_preload_libraries TO fdafd;
> ALTER ROLE
> postgres=# \q
> 
> $ psql postgres
> psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: FATAL:  could not access file
"$libdir/plugins/fdafd":No such file or directory
 

Can you run the server in single-user mode?  That mode doesn't try
loading libraries.

regareds.

-- 
Kyotaro Horiguchi
NTT Open Source Software Center



Re: Question about user/database-level parameters

From
Japin Li
Date:
On Tue, 02 Aug 2022 at 13:01, Kyotaro Horiguchi <horikyota.ntt@gmail.com> wrote:
> At Mon, 01 Aug 2022 18:24:33 +0800, Japin Li <japinli@hotmail.com> wrote in 
>> 
>> Hi, hackers
>> 
>> When I try to modify the parameters for all users in the following command [1]
>> (the library doesn't exist), and I quit the connection, I cannot log in the
>> database, how can I bypass this checking?
>> 
>> I find those parameters loaded by process_settings(), and it seems no way to
>> disable this loading process.  If we could bypass this checking, how can we
>> fix these parameters?
>> 
>> [1]
>> postgres=# ALTER ROLE all SET local_preload_libraries TO fdafd;
>> ALTER ROLE
>> postgres=# \q
>> 
>> $ psql postgres
>> psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: FATAL:  could not access file
"$libdir/plugins/fdafd":No such file or directory
 
>
> Can you run the server in single-user mode?  That mode doesn't try
> loading libraries.
>

Yeah, the single-user mode works.  Thank you very much!
However, if the database is in production, we cannot go into single-user mode,
should we provide an option to change this behavior on the fly?

-- 
Regrads,
Japin Li.
ChengDu WenWu Information Technology Co.,Ltd.



Re: Question about user/database-level parameters

From
Tom Lane
Date:
Japin Li <japinli@hotmail.com> writes:
> Yeah, the single-user mode works.  Thank you very much!
> However, if the database is in production, we cannot go into single-user mode,
> should we provide an option to change this behavior on the fly?

There is not, and never will be, a version of Postgres in which
it's impossible for a superuser to shoot himself in the foot.

Test your settings more carefully before applying them to a
production database that you can't afford to mess up.

            regards, tom lane



Re: Question about user/database-level parameters

From
Japin Li
Date:
On Tue, 02 Aug 2022 at 13:44, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Japin Li <japinli@hotmail.com> writes:
>> Yeah, the single-user mode works.  Thank you very much!
>> However, if the database is in production, we cannot go into single-user mode,
>> should we provide an option to change this behavior on the fly?
>
> There is not, and never will be, a version of Postgres in which
> it's impossible for a superuser to shoot himself in the foot.
>
> Test your settings more carefully before applying them to a
> production database that you can't afford to mess up.
>

Thanks for your explanation!  Got it.

-- 
Regrads,
Japin Li.
ChengDu WenWu Information Technology Co.,Ltd.



Re: Question about user/database-level parameters

From
"Daniel Verite"
Date:
    Japin Li wrote:

> However, if the database is in production, we cannot go into single-user
> mode, should we provide an option to change this behavior on the fly?

It already exists, through PGOPTIONS, which appears to work
for local_preload_libraries, in a quick test.

That is, you can log in by invoking psql with:
PGOPTIONS="-c local_preload_libraries="
and issue the ALTER USER to reset things back to normal
without stopping the instance.


Best regards,
--
Daniel Vérité
https://postgresql.verite.pro/
Twitter: @DanielVerite



Re: Question about user/database-level parameters

From
Japin Li
Date:
On Thu, 04 Aug 2022 at 19:29, Daniel Verite <daniel@manitou-mail.org> wrote:
>     Japin Li wrote:
>
>> However, if the database is in production, we cannot go into single-user
>> mode, should we provide an option to change this behavior on the fly?
>
> It already exists, through PGOPTIONS, which appears to work
> for local_preload_libraries, in a quick test.
>
> That is, you can log in by invoking psql with:
> PGOPTIONS="-c local_preload_libraries="
> and issue the ALTER USER to reset things back to normal
> without stopping the instance.

Oh, great! Thank you very much!

-- 
Regrads,
Japin Li.
ChengDu WenWu Information Technology Co.,Ltd.