Re: pg_terminate_backend and pg_cancel_backend by not administrator user

On Sun, May 29, 2011 at 10:56:02AM -0400, Josh Kupershmidt wrote:
> On Sun, May 29, 2011 at 5:04 AM, Noah Misch <noah(at)leadboat(dot)com> wrote:
> > What risks arise from unconditionally allowing these calls for the same user's
> > backends? ?`pg_cancel_backend' ought to be safe enough; the user always has
> > access to the standard cancellation protocol, making the SQL interface a mere
> > convenience (albeit a compelling one). ?`pg_terminate_backend' does open up
> > access to a new behavior, but no concrete risks come to mind.
>
> Looking around, I see there were real problems[1] with sending SIGTERM
> to individual backends back in 2005 or so, and pg_terminate_backend()
> was only deemed safe enough to put in for 8.4 [2]. So expanding
> pg_terminate_backend() privileges does make me a tad nervous.

The documentation for the CREATE USER flag would boil down to "omit this flag
only if you're worried about undiscovered PostgreSQL bugs in this area". I'd
echo Tom's sentiment from the first thread, "In any case I think we have to
solve it, not create new mechanisms to try to ignore it."

> Reading through those old threads made me realize this patch would
> give database owners the ability to kill off autovacuum workers. Seems
> like we'd want to restrict that power to superusers.

Would we? Any old user can already stifle VACUUM by holding a transaction open.