Monitoring and Diagnosis - PostgreSQL 9 Administration - page 187

Is the user connected?
Here we show how to learn if a certain database user is currently connected to database.
Getting ready
Make sure that you are logged in as a superuser.
How to do it...
Issue the following query to see if the user bob is connected:
SELECT datname FROM pg_stat_activity WHERE usename = 'bob';
If this query returns any rows, then database user bob is connected to database. The returned
value is the name of the database to which the user is connected.
How it works...
PostgreSQL system view pg_stat_activity keeps a track of all running PostgreSQL
backends, including what queries are running, who is connected and when they were
connected, and when the current transaction and current query were started.
There's more...
There is more information in the pg_stat_activity view than just username. For example,
lets hel the user who asks.
What if I want to know "is that computer connected?"
Often, several different processes may connect as the same database user. In that case, you
actually want to know if there is a connection from a specific connection.
You still can get this information from pg_stat_activity view, as it includes the connected
client's IP addresses and ports. The port is only needed in case you have more than one
connection from the same client computer and need to do further digging to see which
process there connects to which database. Run the following:
SELECT datname,usename,client_addr,client_port FROM pg_stat_activity ;
The client_addr and client_port help you look up the exact computer, and even the
process on that computer that has connected to this database.