Re: Limiting user from changing its own attributes

On Tue, May 5, 2015 at 1:44 PM David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com>
wrote:

> On Mon, May 4, 2015 at 10:23 PM, Sameer Kumar <sameer(dot)kumar(at)ashnik(dot)com>
> wrote:
>
>> Sorry about the long silence on this.
>>
>> On Mon, Apr 13, 2015 at 3:34 PM David G. Johnston <
>> david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
>>
>>> On Sun, Apr 12, 2015 at 10:23 PM, Sameer Kumar <sameer(dot)kumar(at)ashnik(dot)com>
>>> wrote:
>>>
>>>> On Mon, Apr 13, 2015 at 1:03 PM Jim Nasby <Jim(dot)Nasby(at)bluetreble(dot)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.
>
>