Re: SET variable - Permission issues

On Sat, Oct 8, 2011 at 12:30 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Josh <josh(at)schemaverse(dot)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 specific
> configuration variables from being adjusted is going to help you
> against an uncooperative user.  You'd be better off to rethink the
> "let them issue SQL queries directly" part of your design.
>
> 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 amount of resources an
> uncooperative user can consume.  If we did try to do that, quite a
> lot of design decisions would have to be revisited, and there would
> be a number of unpleasant tradeoffs to be made.  GUC privilege levels
> are just the tip of the iceberg.

Yeah. For example, if somebody writes a complicated query against a
gigantic table that runs for a long time, is that because they're
trying to DOS the server, or because they have a legitimate need for
the results of that query, and it just so happens to be an expensive
query? That's not really a question a computer can answer, and if you
restrict users to running only queries that are so short and simple
that they can't be used to DOS the box, you'll likely also be locking
out a significant percentage of legitimate user needs.

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 even that seems like
a lot of work for a fairly marginal benefit....

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company