Re: limiting resources to users

Craig Ringer wrote:
> On 1/12/2009 11:33 AM, Greg Smith wrote:
>> 1) If you spawn the psql process with bash using "&", you can then find
>> its pid with "$!", then chain through the process tree with ps and
>> pg_stat_activity as needed to figure out the backend pid.
>
> I feel like I'm missing something obvious here. How can walking the
> process tree starting with the psql pid help you find the backend pid?
> The backend and client have no relationship in the process tree. At
> some point you have to match the (ip,port) tuple for the client's
> connection against pg_stat_activity - what you've listed separately as
> (4). Even that won't help if a unix socket is in use, since
> client_addr is null and client_port is -1.
It turns out I completely misremembered what I did the last time, so
you're right to call me on this.  As I confirmed when I tried to do this
myself again, the two port numbers are completely unrelated via anything
you can see in ps.  What I actually did was look at pg_stat_activity,
note what processes were around, spawn the new one, and then look at
pg_stat_activity again to figure out which it is.  Not really a great
approach, but a usable one for some situations.

--
Greg Smith    2ndQuadrant   Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com  www.2ndQuadrant.com