Re: Limiting user from changing its own attributes

From: "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com>
To: Sameer Kumar <sameer(dot)kumar(at)ashnik(dot)com>
Cc: Jim Nasby <Jim(dot)Nasby(at)bluetreble(dot)com>, PostgreSQL General Discussion Forum <pgsql-general(at)postgresql(dot)org>
Subject: Re: Limiting user from changing its own attributes
Date: 2015-05-05 05:44:07
Message-ID: CAKFQuwY4ohgM38KU_nQGUOoV6_diJYiaq81SdLozYYpB2mGRJQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Sameer Kumar 2015-05-05 06:03:00 Re: Limiting user from changing its own attributes
Previous Message Sameer Kumar 2015-05-05 05:23:45 Re: Limiting user from changing its own attributes