On Tue, May 5, 2015 at 1:44 PM David G. Johnston <david.g.johns...@gmail.com> wrote:
> 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? > Ahh that was just an example. client_min_message is more helpful for me in debugging issues with application code. > > > 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. > Noted! I agree, its better to be explicit. :) > > > >> >>> >>> 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. > I am sorry. My bad. I thought log_statements and log_connections (and log_disconnections) are allowed to be altered at user level. I was more referring to those. >>> 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. > I am not really a hacker/very good at coding. But I get your message here :) > > David J. > >
