Re: limiting resources to users

* Greg Smith (greg(at)2ndquadrant(dot)com) wrote:
> 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.

Perhaps we can stick the pid in client_addr? I havn't got a good
solution at the moment for UNIX domain sockets, which really does
suck. The best I've found, which is by no means a good solution,
is:

===# netstat -np | grep -A1 .PGSQL | grep -B1 <pid of psql>
(On a Linux system)

I havn't actually been following this thread, so I'm not sure what the
OP was after. For other sockets (IPv4, IPv6), IP+port can be used, of
course.

Thanks,

Stephen