Re: Limiting user from changing its own attributes

On Sat, Apr 11, 2015 at 12:57 AM David G. Johnston <
david(dot)g(dot)johnston(at)gmail(dot)com> wrote:

> On Fri, Apr 10, 2015 at 9:01 AM, Sameer Kumar <sameer(dot)kumar(at)ashnik(dot)com>
> wrote:
>
>> Hi,
>>
>> In PostgreSQL a user can alter itself to change its user level
>> parameters. e.g. I can alter the user to change work_mem -
>>
>>
>> psql -U user1 -d postgres
>> postgres=# alter user user user1 set work_mem to '1024000';
>>
>
> ​Is this a typo? - the above has a syntax error...​
>

Yes that is a typo. Sorry about that.

>
> ALTER ROLE
>> postgres=#
>>
>> Is there a way I restrict this behavior? or atleast put a restriction on
>> the certain parameters e.g. work_mem to be not set to too high?
>>
>>
> ​Not that I'm aware of - and the ability to change parameters is not
> limited to ALTER ROLE.
>
> Setting "work_mem" too low can be just as problematic as setting it too
> high. This one could probably be solved readily enough but you sound like
> you are looking for some blanket capability to either add targeted security
> about GUCs or setup a way to alter generically the "upper_bound,
> lower_bound" ​properties of numeric variables.
>

Yes either an upper bound to which users can set their own values to.

> Upper is somewhat easier but currently the system would only recognize a
> global constraint.
>

Does it? Even though my work_mem in postgresql.conf is 1MB, the user can
alter itself to set its own work_mem to 1GB. Or did I interpret your
statement wrongly?

>
> ​David J.​
>
>
>