Re: Limiting user from changing its own attributes

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;
or
[
ALTER USER my_app_user restrict set for client_min_messages in
('DEBUG');
and
ALTER USER my_app_user restrict set for work_mem limit to 10MB;

]

"

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.

>
> 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.

> 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)

>
> 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).

>
> If you really care about their "set role" aspect you can at least setup
> cron shell script to query the catalog and report any undesirable settings
> and maybe even remove the offending entry. You are still open to "SET
> work_mem" during the session though...
>
> David J.
>
>
>
>
>