2011/6/2 Noah Misch <n...@leadboat.com>: > On Wed, Jun 01, 2011 at 10:26:34PM -0400, Josh Kupershmidt wrote: >> On Wed, Jun 1, 2011 at 5:55 PM, Noah Misch <n...@leadboat.com> wrote: >> > On Sun, May 29, 2011 at 10:56:02AM -0400, Josh Kupershmidt wrote: >> >> 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." >> >> I do agree with Tom's sentiment from that thread. But, if we are >> confident that pg_terminate_backend() is safe enough to relax >> permissions on, then I take it you agree we should plan to extend this >> power to all users? > > Yes; that's what I was trying to say. > > Having thought about this some more, I do now see a risk. Currently, a > SECURITY > DEFINER function (actually any function, but that's where it matters) can trap > query_canceled. By doing so, the author can ensure that only superusers and > crashes may halt the function during a section protected in this way. One > might > use it to guard a series of updates made over dblink. pg_terminate_backend() > breaks this protection. I've never designed something this way; it only > suffices when you merely sort-of-care about transactional integrity. Perhaps > it's an acceptable loss for this feature? > >> And if so, is this patch a good first step on that path? >
Understand that the pg_terminate_backend() is able to kill process that need not to be killed. I suppose that looking inside the internal postgreql table in order to not allow a normal db owner to kill a superuser connection can avoid this problem? > Yes. > >> >> 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. >> >> This is true, though it's possible we might at some point want a >> backend process which really shouldn't be killable by non-superusers >> (if vacuum/autovacuum isn't one already.) Actually, I could easily >> imagine a superuser running an important query on a database getting >> peeved if a non-superuser were allowed to cancel/terminate his >> queries. > > That's really a different level of user isolation than we have. If your > important query runs on a database owned by someone else, calls functions > owned > by someone else, or reads tables owned by someone else, you're substantially > at > the mercy of those object owners. That situation probably is unsatisfactory > to > some folks. Adding the possibility that a database owner could cancel your > query seems like an extension of that codependency more than a new exposure. > If I am the database owner I need to be able to manage my DB. Ok for superuser connection (and internal administrative process like autovacuum) I am the developer, not the DBA, so sometimes, when I wrong something, I need to kill my session if I wrong something .... Can we suppose, in a more generic case, that an user can kill connection only from the same user even if this is not the database owner? Best Regards, Torello -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
