Re: Limiting user from changing its own attributes - Mailing list pgsql-general

Sorry about the long silence on this.

On Mon, Apr 13, 2015 at 3:34 PM David G. Johnston <david.g.johnston@gmail.com> wrote:

On Sun, Apr 12, 2015 at 10:23 PM, Sameer Kumar <sameer.kumar@ashnik.com> wrote:

On Mon, Apr 13, 2015 at 1:03 PM Jim Nasby <Jim.Nasby@bluetreble.com> wrote:

No. I suspect the community would support at least a hook for GUC
changes, if not a full-on permissions system. A hook would make it
fairly easy to add event trigger support.

I hope someone out there is listening :)

I hope I have made my concern clear, I currently don't have a way to control users from changing the parameter values for their own settings, which allows each user to set in-appropriate values e.g. for work_mem.

If work_mem is the only example you can describe then I'm doubtful that any kind of urgency is going to be associated with this request.

I guess any parameter which can be altered at the user level should have some limitations e.g. one may not want to allow a user to reset its own level of client messages. 

As an admin one may want to have control over what a user can alter and what the user can not alter. 

e.g. 

"

ALTER USER my_app_user  restrict set for client_min_messages;

  Your actual request does nothing because the same user can simply issue "SET work_mem" at session start and bypass the user defaults that you want to prevent

 

My point is the limitations imposed should not be just at user level but should also be inherited by all sessions made by that user.

 

You haven't provided enough meat for anyone to offer advice regarding the scenario you are encountering that you think has "restrict alter role" as a solution. 

To put it very simply as a DBA one would want to be in control of what the users in that environment can change about themselves and what they can not.

 

If you want to take the time to actually paint us a picture then maybe suggestions or motivation for change will result.  But, in the end, the current architecture of PostgreSQL means that people with credentials to the database have the capability to DoS the server.  work_mem is simply one possible avenue and, in reality, one where an inappropriate value can be either too large or too small.

Yes! Plus as an user I can change the logging parameter for my account (which as well is risky in many environment)

The useful solution here is not restring work_mem but rather having a process in place that provides data regarding excessive memory utilization AND disk I/O and associating that data with the work_mem value and executing user.  The event triggers would also allow for monitoring, without setting an excessive log_statements level, changes and their values.

But that is more of a cure, won't it be a good idea to have some level of preventive measures to ensure DBAs can control which user can change which "user-level" value (and even better if we can attach a limit to it).