Re: Limiting user from changing its own attributes

[Sameer Kumar <sameer.kumar@xxxxxxxxxx>]

On Sat, Apr 11, 2015 at 12:57 AM David G. Johnston <david.g.johnston@xxxxxxxxx> wrote:

On Fri, Apr 10, 2015 at 9:01 AM, Sameer Kumar <sameer.kumar@xxxxxxxxxx> 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.​