Robert Haas <robertmhaas@gmail.com> writes:
> I don't think it's going to be feasible to implement a security
> restriction that keeps untrusted users from hosing the machine with a
> long running CTE; there are nearly infinitely many ways for an
> untrusted user who can run queries to hose the machine, and plugging
> one of them imperfectly is going to get us pretty much nowhere. On
> the other hand, there is perhaps a reasonable argument to be made that
> we should cut off CTE processing at some point to prevent
> *inadvertent* exhaustion of system resources. Or even query
> processing more generally.
Indeed: the real question here is why a recursive CTE is any worse
than, say, an accidentally unconstrained join (or three or four...).
However, we already have a perfectly suitable general mechanism for
that; it's called statement_timeout.
I think we've already had the discussion about whether there should be
a system-wide SUSET maximum statement_timeout, and rejected it on the
grounds that there was not a very clear need for it.
> In fact, we already have some things sort of like this: you can use
> statement_timeout to kill queries that run for too long, and we just
> recently added temp_file_limit to kill those that eat too much temp
> file space. I can see a good case for memory_limit and
> query_cpu_limit and maybe some others.
temp_file_limit got accepted because it was constraining a resource not
closely related to run time. I don't think that it provides a precedent
in support of any of these other ideas.
regards, tom lane