Re: [HACKERS] Cross-backend signals and administration (Was: Re: pg_terminate_backend for same-role)

2012-03-28 Thread Tom Lane
Kevin Grittner kevin.gritt...@wicourts.gov writes: Andres Freund and...@anarazel.de wrote: On Tuesday, March 27, 2012 07:51:59 PM Kevin Grittner wrote: As Tom pointed out, if there's another person sharing the user ID you're using, and you don't trust them, their ability to cancel your

Re: [HACKERS] Cross-backend signals and administration (Was: Re: pg_terminate_backend for same-role)

2012-03-28 Thread Robert Haas
On Wed, Mar 28, 2012 at 10:02 AM, Tom Lane t...@sss.pgh.pa.us wrote: Kevin Grittner kevin.gritt...@wicourts.gov writes: Andres Freund and...@anarazel.de wrote: On Tuesday, March 27, 2012 07:51:59 PM Kevin Grittner wrote: As Tom pointed out, if there's another person sharing the user ID you're

Re: [HACKERS] Cross-backend signals and administration (Was: Re: pg_terminate_backend for same-role)

2012-03-28 Thread Tom Lane
Robert Haas robertmh...@gmail.com writes: I'm coming around to the point of view that we should just make pg_terminate_backend()'s behavior consistent with pg_cancel_backend() and call it good. Yeah, the issues that are really of concern are not ones that that function in itself can address.

Re: [HACKERS] Cross-backend signals and administration (Was: Re: pg_terminate_backend for same-role)

2012-03-27 Thread Noah Misch
On Mon, Mar 26, 2012 at 07:53:25PM -0400, Robert Haas wrote: I think the more important question is a policy question: do we want it to work like this? It seems like a policy question that ought to be left to the DBA, but we have no policy management framework for DBAs to configure what they

Re: [HACKERS] Cross-backend signals and administration (Was: Re: pg_terminate_backend for same-role)

2012-03-27 Thread Magnus Hagander
On Tue, Mar 27, 2012 at 11:04, Noah Misch n...@leadboat.com wrote: On Mon, Mar 26, 2012 at 07:53:25PM -0400, Robert Haas wrote: I think the more important question is a policy question: do we want it to work like this?  It seems like a policy question that ought to be left to the DBA, but we

Re: [HACKERS] Cross-backend signals and administration (Was: Re: pg_terminate_backend for same-role)

2012-03-27 Thread Noah Misch
On Tue, Mar 27, 2012 at 02:58:26PM +0200, Magnus Hagander wrote: On Tue, Mar 27, 2012 at 11:04, Noah Misch n...@leadboat.com wrote: On Mon, Mar 26, 2012 at 07:53:25PM -0400, Robert Haas wrote: I think the more important question is a policy question: do we want it to work like this? ?It

Re: [HACKERS] Cross-backend signals and administration (Was: Re: pg_terminate_backend for same-role)

2012-03-27 Thread Tom Lane
Noah Misch n...@leadboat.com writes: On Mon, Mar 26, 2012 at 07:53:25PM -0400, Robert Haas wrote: I think the more important question is a policy question: do we want it to work like this? The DBA can customize policy by revoking public execute permissions on pg_catalog.pg_terminate_backend

Re: [HACKERS] Cross-backend signals and administration (Was: Re: pg_terminate_backend for same-role)

2012-03-27 Thread Daniel Farina
On Mon, Mar 26, 2012 at 4:53 PM, Robert Haas robertmh...@gmail.com wrote: I think the more important question is a policy question: do we want it to work like this?  It seems like a policy question that ought to be left to the DBA, but we have no policy management framework for DBAs to

Re: [HACKERS] Cross-backend signals and administration (Was: Re: pg_terminate_backend for same-role)

2012-03-27 Thread Robert Haas
On Tue, Mar 27, 2012 at 1:26 PM, Daniel Farina dan...@heroku.com wrote: On Mon, Mar 26, 2012 at 4:53 PM, Robert Haas robertmh...@gmail.com wrote: I think the more important question is a policy question: do we want it to work like this?  It seems like a policy question that ought to be left to

Re: [HACKERS] Cross-backend signals and administration (Was: Re: pg_terminate_backend for same-role)

2012-03-27 Thread Robert Haas
On Tue, Mar 27, 2012 at 1:46 PM, Alvaro Herrera alvhe...@commandprompt.com wrote: Excerpts from Robert Haas's message of mar mar 27 14:38:47 -0300 2012: On Tue, Mar 27, 2012 at 1:26 PM, Daniel Farina dan...@heroku.com wrote: On Mon, Mar 26, 2012 at 4:53 PM, Robert Haas robertmh...@gmail.com

Re: [HACKERS] Cross-backend signals and administration (Was: Re: pg_terminate_backend for same-role)

2012-03-27 Thread Alvaro Herrera
Excerpts from Robert Haas's message of mar mar 27 14:38:47 -0300 2012: On Tue, Mar 27, 2012 at 1:26 PM, Daniel Farina dan...@heroku.com wrote: On Mon, Mar 26, 2012 at 4:53 PM, Robert Haas robertmh...@gmail.com wrote: I think the more important question is a policy question: do we want it

Re: [HACKERS] Cross-backend signals and administration (Was: Re: pg_terminate_backend for same-role)

2012-03-27 Thread Daniel Farina
On Tue, Mar 27, 2012 at 10:46 AM, Alvaro Herrera alvhe...@commandprompt.com wrote: Isn't it the case that many web applications run under some common database user regardless of the underlying webapp user?  I wouldn't say that's an unimportant case.  Granted, the webapp user wouldn't have

Re: [HACKERS] Cross-backend signals and administration (Was: Re: pg_terminate_backend for same-role)

2012-03-27 Thread Kevin Grittner
Robert Haas robertmh...@gmail.com wrote: Daniel Farina dan...@heroku.com wrote: Is there a hypothetical DBA that doesn't want a mere-mortal user to be able to signal one of their own backends to do cancel query, rollback the transaction, then close the socket? If so, why? Setting aside

Re: [HACKERS] Cross-backend signals and administration (Was: Re: pg_terminate_backend for same-role)

2012-03-27 Thread Daniel Farina
On Tue, Mar 27, 2012 at 10:48 AM, Daniel Farina dan...@heroku.com wrote: On Tue, Mar 27, 2012 at 10:46 AM, Alvaro Herrera alvhe...@commandprompt.com wrote: Isn't it the case that many web applications run under some common database user regardless of the underlying webapp user?  I wouldn't say

Re: [HACKERS] Cross-backend signals and administration (Was: Re: pg_terminate_backend for same-role)

2012-03-27 Thread Andres Freund
On Tuesday, March 27, 2012 07:51:59 PM Kevin Grittner wrote: Well, I guess if you have different people sharing the same user-ID, you probably wouldn't want that. As Tom pointed out, if there's another person sharing the user ID you're using, and you don't trust them, their ability to

Re: [HACKERS] Cross-backend signals and administration (Was: Re: pg_terminate_backend for same-role)

2012-03-27 Thread Kevin Grittner
Andres Freund and...@anarazel.de wrote: On Tuesday, March 27, 2012 07:51:59 PM Kevin Grittner wrote: Well, I guess if you have different people sharing the same user-ID, you probably wouldn't want that. As Tom pointed out, if there's another person sharing the user ID you're using, and

Re: [HACKERS] Cross-backend signals and administration (Was: Re: pg_terminate_backend for same-role)

2012-03-27 Thread Andrew Dunstan
On 03/27/2012 03:14 PM, Kevin Grittner wrote: Andres Freundand...@anarazel.de wrote: On Tuesday, March 27, 2012 07:51:59 PM Kevin Grittner wrote: Well, I guess if you have different people sharing the same user-ID, you probably wouldn't want that. As Tom pointed out, if there's another

Re: [HACKERS] Cross-backend signals and administration (Was: Re: pg_terminate_backend for same-role)

2012-03-27 Thread Daniel Farina
On Tue, Mar 27, 2012 at 1:30 PM, Andrew Dunstan and...@dunslane.net wrote: Well, that does sort of leave an arguable vulnerability.  Should the same user only be allowed to kill the process from a connection to the same database? It might be a reasonable restriction in theory, but I doubt

Re: [HACKERS] Cross-backend signals and administration (Was: Re: pg_terminate_backend for same-role)

2012-03-26 Thread Magnus Hagander
On Tue, Mar 20, 2012 at 18:48, Daniel Farina dan...@heroku.com wrote: On Tue, Mar 20, 2012 at 10:13 AM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: Maybe we should just not worry about this. That's been my reaction right along.  There's no evidence that PID

Re: [HACKERS] Cross-backend signals and administration (Was: Re: pg_terminate_backend for same-role)

2012-03-26 Thread Tom Lane
Magnus Hagander mag...@hagander.net writes: I wasn't aware that was the reason there. I think it was the general leftovers from previous times. When we first created pg_terminate_backend() there was a general thought that it might not be safe to just SIGTERM a backend to make it quit. Not

Re: [HACKERS] Cross-backend signals and administration (Was: Re: pg_terminate_backend for same-role)

2012-03-26 Thread Daniel Farina
On Mon, Mar 26, 2012 at 1:57 PM, Tom Lane t...@sss.pgh.pa.us wrote: I'm not.  I still wouldn't trust SIGTERMing an individual backend in a production database.  It'll probably work, but what if it doesn't? Best-case scenario is you'll need to do a panic shutdown to clear the stuck lock or

Re: [HACKERS] Cross-backend signals and administration (Was: Re: pg_terminate_backend for same-role)

2012-03-26 Thread Robert Haas
On Mon, Mar 26, 2012 at 4:57 PM, Tom Lane t...@sss.pgh.pa.us wrote: I'm not sure - perhaps we're past that worry these days? I'm not.  I still wouldn't trust SIGTERMing an individual backend in a production database.  It'll probably work, but what if it doesn't? Best-case scenario is you'll

Re: [HACKERS] Cross-backend signals and administration (Was: Re: pg_terminate_backend for same-role)

2012-03-26 Thread Tom Lane
Daniel Farina dan...@heroku.com writes: On Mon, Mar 26, 2012 at 1:57 PM, Tom Lane t...@sss.pgh.pa.us wrote: I'm not. I still wouldn't trust SIGTERMing an individual backend in a production database. Okay, it was my precise intention to hand this to users so that not only could they cancel

Re: [HACKERS] Cross-backend signals and administration (Was: Re: pg_terminate_backend for same-role)

2012-03-26 Thread Tom Lane
Robert Haas robertmh...@gmail.com writes: I think the more important question is a policy question: do we want it to work like this? It seems like a policy question that ought to be left to the DBA, but we have no policy management framework for DBAs to configure what they do or do not wish

Re: [HACKERS] Cross-backend signals and administration (Was: Re: pg_terminate_backend for same-role)

2012-03-20 Thread Daniel Farina
On Mon, Mar 19, 2012 at 9:08 PM, Robert Haas robertmh...@gmail.com wrote: It's after midnight here so maybe I'm being slow, but I don't understand what problem the SessionId solves.  ISTM that you could solve the problem like this: 1. Acquire ProcArrayLock in exclusive mode, to keep the set

Re: [HACKERS] Cross-backend signals and administration (Was: Re: pg_terminate_backend for same-role)

2012-03-20 Thread Robert Haas
On Tue, Mar 20, 2012 at 4:35 AM, Daniel Farina dan...@heroku.com wrote: I chose the SessionId mostly because I didn't have a great sense around how hot the ProcArrayLock is, and it was easy to add a fine-grained spinlock to just get the flavor of the idea. It's fairly hot, but terminating or

Re: [HACKERS] Cross-backend signals and administration (Was: Re: pg_terminate_backend for same-role)

2012-03-20 Thread Tom Lane
Robert Haas robertmh...@gmail.com writes: Well, I'm not sure it would save anything meaningful to read the PID after releasing the lock even if it were safe, so I'd be inclined to keep things simple. But on further reflection I had us using the PID to find the target PGPROC in the first

Re: [HACKERS] Cross-backend signals and administration (Was: Re: pg_terminate_backend for same-role)

2012-03-20 Thread Robert Haas
On Tue, Mar 20, 2012 at 12:48 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: Well, I'm not sure it would save anything meaningful to read the PID after releasing the lock even if it were safe, so I'd be inclined to keep things simple.  But on further

Re: [HACKERS] Cross-backend signals and administration (Was: Re: pg_terminate_backend for same-role)

2012-03-20 Thread Tom Lane
Robert Haas robertmh...@gmail.com writes: Maybe we should just not worry about this. That's been my reaction right along. There's no evidence that PID recycling is a problem in the real world. regards, tom lane -- Sent via pgsql-hackers mailing list

Re: [HACKERS] Cross-backend signals and administration (Was: Re: pg_terminate_backend for same-role)

2012-03-20 Thread Daniel Farina
On Tue, Mar 20, 2012 at 10:13 AM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: Maybe we should just not worry about this. That's been my reaction right along.  There's no evidence that PID recycling is a problem in the real world. I'm entirely willing to

Re: [HACKERS] Cross-backend signals and administration (Was: Re: pg_terminate_backend for same-role)

2012-03-19 Thread Robert Haas
On Sat, Mar 17, 2012 at 8:28 PM, Daniel Farina dan...@heroku.com wrote: This thread evolved out of an attempt to implement pg_terminate_backend for non-superusers.  I thought -- probably erroneously -- that the major objection to that was the known possibility of a PID-cycling race condition,

Re: [HACKERS] Cross-backend signals and administration (Was: Re: pg_terminate_backend for same-role)

2012-03-18 Thread Magnus Hagander
On Sun, Mar 18, 2012 at 01:28, Daniel Farina dan...@heroku.com wrote: Noah offered me these comments: This patch still changes the policy for pg_terminate_backend(), and it does not fix other SIGINT senders like processCancelRequest() and ProcSleep().  If you're concerned about PID-reuse