Re: SET variable - Permission issues

Gurjeet Singh <singh(dot)gurjeet(at)gmail(dot)com> writes:
> On Mon, Oct 10, 2011 at 1:06 PM, Joe Conway <mail(at)joeconway(dot)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 settings could be restricted by the DBA, there would be a much lower
>> chance of this happening. There are undoubtedly other holes to fill, but
>> it seems like a worthy cause.

> Even in a controlled environment, say in a company where only legit apps
> developed in-house are run on the DB, a DBA would want peace of mind that
> the developers are not setting these GUCs at runtime (which is often even
> recommended in case of work_mem) to bypass a policy set by the DBA and are
> capable of bringing the DB down to its knees.

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.

I could get interested in this if it weren't that the problem is so
open-ended as to be basically insoluble. The only solution to the OP's
problem that's not got more holes than a wheel of Swiss cheese is to not
let untrustworthy people have direct SQL access to the server. It *does
not improve security* to close a couple of obvious holes and leave a
bunch of other avenues to the same end open. All it does is give you a
false sense of security.

regards, tom lane