On Mon, May 4, 2015 at 10:23 PM, Sameer Kumar <sameer.ku...@ashnik.com> wrote:
> Sorry about the long silence on this. > > On Mon, Apr 13, 2015 at 3:34 PM David G. Johnston < > david.g.johns...@gmail.com> wrote: > >> On Sun, Apr 12, 2015 at 10:23 PM, Sameer Kumar <sameer.ku...@ashnik.com> >> wrote: >> >>> On Mon, Apr 13, 2015 at 1:03 PM Jim Nasby <jim.na...@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; > I make a point below but the server should not care what level of logging messages the client wants to receive...is there some kind of security issue with a overly verbose specification here? Are you concerned about resource utilization by said clients? 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. > While that can be inferred it is good to be explicit in order to communicate understanding of the current reality. > >> >> 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. > OK. This is already the case for numerous things and a blanket statement like this doesn't help others understand where we are lacking. > >> 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) > You seem to need a better understanding of what limitations are already in place. While it is true you can alter "client_min_messages" you cannot alter "log_min_messages" (in addition to quite a few other log related settings). http://www.postgresql.org/docs/9.4/static/runtime-config-logging.html You make a claim that altering client_min_messages is "risk in many environment [sic]" but provide zero substantiation for that claim. >> 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). > > Most likely such a patch would be accepted by the community. If you are depending on someone else writing it the muted response to this thread should be discouraging. David J.
