Re: SET variable - Permission issues

On Mon, Oct 10, 2011 at 2:55 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:

> On Mon, Oct 10, 2011 at 2:38 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > Any developer who can't think of six ways to DOS the server without
> > changing those settings should be fired on the spot for incompetence.
>
> No kidding. But the point is that if the developer down the hall
> maliciously destroys your database server, you can go through channels
> and get him reprimanded or fired. But if the developer down the hall
> is a new hire who doesn't know beans about PostgreSQL and tries
> setting work_mem to 10GB, well, oops, it was an accident. And then
> another developer gets hired three weeks later and does the same
> thing. And then three months later somebody does it again. After a
> while people no longer remember that in each case it was a developer
> to blame. What they remember is that the DBA let the server go down
> three times.
>

IOW, honest mistakes happen.

Would it be possible to make the SUSET/USERSET property of a GUC modifiable?
So a DBA can do

ALTER USER novice SET CONTEXT OF work_mem TO 'superuser';

Or, maybe

ALTER USER novice SET MAX_VAL OF work_mem TO '1 MB';

and extend it to say

ALTER USER novice SET MIN_VAL OF statement_timeout TO '1';
-- So that the user cannot turn off the timeout

ALTER DATABASE super_reliable SET ENUM_VALS OF synchronous_commit TO 'on';
-- So that the user cannot change the synchronicity of transactions against
this database.

Regards,
--
Gurjeet Singh
EnterpriseDB Corporation
The Enterprise PostgreSQL Company