Re: [HACKERS] SET variable - Permission issues

2011-10-11 Thread Bruce Momjian
Kevin Grittner wrote: Joe Conway m...@joeconway.com wrote: On 10/10/2011 01:52 PM, Gurjeet Singh wrote: 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

Re: [HACKERS] SET variable - Permission issues

2011-10-11 Thread Kevin Grittner
Bruce Momjian br...@momjian.us wrote: Kevin Grittner wrote: Joe Conway m...@joeconway.com wrote: On 10/10/2011 01:52 PM, Gurjeet Singh wrote: 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

Re: [HACKERS] SET variable - Permission issues

2011-10-11 Thread Joe Conway
On 10/11/2011 11:53 AM, Kevin Grittner wrote: Bruce Momjian br...@momjian.us wrote: Is this a TODO? We might not want to make work_mem SUSET, but it would allow administrators to control this. Well, we've identified a few people who like the idea, but I'm not sure we have the degree of

Re: [HACKERS] SET variable - Permission issues

2011-10-11 Thread Dimitri Fontaine
Kevin Grittner kevin.gritt...@wicourts.gov writes: Well, we've identified a few people who like the idea, but I'm not sure we have the degree of consensus we normally look for before putting something on the TODO list. After the discussion on this thread, are there still any *objections* to

Re: [HACKERS] SET variable - Permission issues

2011-10-11 Thread Tom Lane
Kevin Grittner kevin.gritt...@wicourts.gov writes: Dimitri Fontaine dimi...@2ndquadrant.fr wrote: Adding the information visible at the right places is a fun project in itself, too :) I was thinking a couple new columns in pg_settings (and what backs it) would be the main thing, but I

Re: [HACKERS] SET variable - Permission issues

2011-10-11 Thread Kevin Grittner
Tom Lane t...@sss.pgh.pa.us wrote: This isn't exactly a trivial matter. What happens for instance if you try to change the limit, and there are already active values outside the limit in some processes? I would certainly vote for enforcing on the SET and not causing an error on the attempt

Re: [HACKERS] SET variable - Permission issues

2011-10-11 Thread Joe Conway
On 10/11/2011 02:07 PM, Kevin Grittner wrote: Tom Lane t...@sss.pgh.pa.us wrote: This isn't exactly a trivial matter. What happens for instance if you try to change the limit, and there are already active values outside the limit in some processes? I would certainly vote for enforcing

Re: [HACKERS] SET variable - Permission issues

2011-10-11 Thread Tom Lane
Joe Conway m...@joeconway.com writes: On 10/11/2011 02:07 PM, Kevin Grittner wrote: I would certainly vote for enforcing on the SET and not causing an error on the attempt to change the limit. ... What problems do you see with that? Yeah, I don't know why it need be handled any different

Re: [HACKERS] SET variable - Permission issues

2011-10-10 Thread Joe Conway
On 10/09/2011 09:09 PM, Robert Haas wrote: Having said that, I do think it might be useful to have ways of controlling the values that users can set for GUC values, not so much as a guard against an all-out assault (which is probably futile) but as a way for DBAs to enforce system policy. But

Re: [HACKERS] SET variable - Permission issues

2011-10-10 Thread Gurjeet Singh
On Mon, Oct 10, 2011 at 1:06 PM, Joe Conway m...@joeconway.com wrote: On 10/09/2011 09:09 PM, Robert Haas wrote: Having said that, I do think it might be useful to have ways of controlling the values that users can set for GUC values, not so much as a guard against an all-out assault

Re: [HACKERS] SET variable - Permission issues

2011-10-10 Thread Tom Lane
Gurjeet Singh singh.gurj...@gmail.com writes: On Mon, Oct 10, 2011 at 1:06 PM, Joe Conway m...@joeconway.com wrote: Currently customer A can set work_mem = some very large number; and set statement_timeout = 0; and run a big query effectively DOS'ing customers B, C, and D. If these two

Re: [HACKERS] SET variable - Permission issues

2011-10-10 Thread Robert Haas
On Mon, Oct 10, 2011 at 2:38 PM, Tom Lane t...@sss.pgh.pa.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

Re: [HACKERS] SET variable - Permission issues

2011-10-10 Thread Gurjeet Singh
On Mon, Oct 10, 2011 at 2:55 PM, Robert Haas robertmh...@gmail.com wrote: On Mon, Oct 10, 2011 at 2:38 PM, Tom Lane t...@sss.pgh.pa.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

Re: [HACKERS] SET variable - Permission issues

2011-10-10 Thread Joe Conway
On 10/10/2011 01:52 PM, Gurjeet Singh wrote: On Mon, Oct 10, 2011 at 2:38 PM, Tom Lane 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. Perhaps, but I think our long term goal at least should be to

Re: [HACKERS] SET variable - Permission issues

2011-10-10 Thread Kevin Grittner
Joe Conway m...@joeconway.com wrote: On 10/10/2011 01:52 PM, Gurjeet Singh wrote: 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

Re: [HACKERS] SET variable - Permission issues

2011-10-09 Thread Robert Haas
On Sat, Oct 8, 2011 at 12:30 PM, Tom Lane t...@sss.pgh.pa.us wrote: Josh j...@schemaverse.com writes: [ unhappy about users being able to freely adjust work_mem etc ] Really, if you're letting users issue arbitrary SQL queries, there simply isn't any way to prevent them from beating your

Re: [HACKERS] SET variable - Permission issues

2011-10-09 Thread Tom Lane
Robert Haas robertmh...@gmail.com writes: On Sat, Oct 8, 2011 at 12:30 PM, Tom Lane t...@sss.pgh.pa.us wrote: The reason that the specific variables you mention (as well as some others that bear on such things) are USERSET and not SUSET is precisely that we are not trying to constrain the

[HACKERS] SET variable - Permission issues

2011-10-08 Thread Josh
Hello all, While working on an application I have been developing for about two years now, I have come across a base-limitation in PostgreSQL for three separate problems. I was talking with other members of the community recently and they agreed that the issue has some merit so I thought I

Re: [HACKERS] SET variable - Permission issues

2011-10-08 Thread Tom Lane
Josh j...@schemaverse.com writes: [ unhappy about users being able to freely adjust work_mem etc ] Really, if you're letting users issue arbitrary SQL queries, there simply isn't any way to prevent them from beating your server into the ground. I don't think that inserting a hack to prevent