Re: Limiting user from changing its own attributes

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

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

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.

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.