Re: Limiting user from changing its own attributes

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?​

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.