Re: changing sort_mem on the fly?

On Fri, Jan 28, 2005 at 02:43:13AM -0500, Tom Lane wrote:
> Neil Conway <neilc(at)samurai(dot)com> writes:
> > A malicious user who can execute SELECT queries can already consume an
> > arbitrary amount of memory -- say, by disabling GEQO and self-joining
> > pg_class to itself 50 times. I'm not sure that letting users modify
> > sort_mem/work_mem actually increases the risk from malicious users.
>
> The correct place for a sysadmin to limit memory usage would be in the
> ulimit settings the postmaster starts under. Of course, Neil's argument
> still holds in general: anyone who can write arbitrary queries is not
> going to have any difficulty in soaking up unreasonable amounts of
> resources. Trying to restrict that would probably make the system less
> useful rather than more so.

Just to clarify, I wasn't suggesting limiting sort_mem to only admins, I
was only pointing out that it's possible for a user to starve the box of
memory with it. I'd also point out that IMHO it's far more likely that
someone could accidentally hose the box with sort_mem than with a
SELECT. In the year 2020 when there's a bunch of bored developers
sitting around wondering what to do, it might be nice to have the
ability to apply some security to GUC settings, possibly just being able
to restrict them to super-user, and having a SECURITY DEFINER function
users can call instead that could apply permissions.
--
Jim C. Nasby, Database Consultant decibel(at)decibel(dot)org
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"