PostgreSQL 101: Connected Users
one of the PostgreSQL system views pg_stat_activity It maintains a list of all currently running PostgreSQL background processes. This information includes which queries are running, the connected users and when they connected, as well as the start time of the active transaction and queries.
To check if a specific user is connected to a PostgreSQL database:
SELECT dbname FROM pg_stat_activity WHERE usename = 'tolga';
If this query returns a result, it means the user tolga is connected to the system. The value returned for the dbname column shows the database or databases to which the user tolga is connected.
“If we are looking for the answer to the question ”Which computer are they connected from?"
Generally, multiple processes might be connecting to the same database with the same username. In this case, you might want to know through which remote system the connections are being made. pg_stat_activity Its view also shows information about this. Namely, the IP address and connection ports of the connected system are located in this view. You can see who is connecting from where to databases connected with the same username as the IP address. On the other hand, port numbers show different sessions of the user with the same name connecting from the same remote client. In this case, the following query:
SELECT dbname, username, client_addr, client_port FROM pg_stat_activity ;
client_addr and client_port can show the respective client and its port, even including the process ID on the remote machine.
