Thread

1. system variable can be edited by all user?

   chris navarroza <canavarroza.work@gmail.com> — 2022-11-22T06:25:31Z

   Hi,
   
   Ive created a read only user (SELECT PRIVILEGE) but it turns out that this
   user can do this queries: SHOW work_mem; SET work_mem='40MB'; How do I
   limit him?
   
   Thanks,
   
   Butching
   

2. Re: system variable can be edited by all user?

   Laurenz Albe <laurenz.albe@cybertec.at> — 2022-11-22T08:43:04Z

   On Tue, 2022-11-22 at 14:25 +0800, chris navarroza wrote:
   > Ive created a read only user (SELECT PRIVILEGE) but it turns out that this user
   > can do this queries: SHOW work_mem; SET work_mem='40MB'; How do I limit him?
   
   We call these "configuration parameters", and there is no way you can prevent
   a database user from changing them for her session.  The recommendation is not
   to give untrustworthy users access to directly run SQL statements on the database.
   
   Yours,
   Laurenz Albe
   
   
   
   

3. Re: system variable can be edited by all user?

   Adrian Klaver <adrian.klaver@aklaver.com> — 2022-11-22T16:57:57Z

   On 11/21/22 22:25, chris navarroza wrote:
   > Hi,
   > 
   > Ive created a read only user (SELECT PRIVILEGE) but it turns out that 
   > this user can do this queries: SHOW work_mem; SET work_mem='40MB'; How 
   > do I limit him?
   
   Short answer is what Laurenz Albe posted.
   
   Long answer is:
   
   From:
   
   https://www.postgresql.org/docs/current/view-pg-settings.html
   
   select name, setting, context from pg_settings where name = 'work_mem';
       name   | setting | context
   ----------+---------+---------
     work_mem | 4096    | user
   
   user
   
        These settings can be set from postgresql.conf, or within a session 
   via the SET command. Any user is allowed to change their session-local 
   value. Changes in postgresql.conf will affect existing sessions only if 
   no session-local value has been established with SET.
   
   
   See page for the other possible values for context and what they mean 
   with regard to privileges.
   
   >
   > Thanks,
   > 
   > Butching
   > 
   
   -- 
   Adrian Klaver
   adrian.klaver@aklaver.com
   
   
   
   
   

4. Re: system variable can be edited by all user?

   pierre.forstmann@gmail.com — 2022-11-26T09:14:03Z

   Hello,
   
   You ccan try to use my extension pg_set_acl:
   https://github.com/pierreforstmann/pg_set_acl
   
   
   Le mar. 22 nov. 2022 à 09:07, chris navarroza <canavarroza.work@gmail.com>
   a écrit :
   
   > Hi,
   >
   > Ive created a read only user (SELECT PRIVILEGE) but it turns out that this
   > user can do this queries: SHOW work_mem; SET work_mem='40MB'; How do I
   > limit him?
   >
   > Thanks,
   >
   > Butching
   >
   >