On Mon, Apr 13, 2015 at 1:03 PM Jim Nasby <Jim.Nasby@xxxxxxxxxxxxxx> 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.
