# How to Find Logged In Users in PostgreSQL

> To find the logged-in users in Postgres, the “pg_stat_activity” view is used. While to find all Postgres users, a system table named "pg_user" is used.

PostgreSQL allows us to find/check all the users or only those users that are currently logged into Postgres. To find all users, a system table named “ **pg_user** ” is used, while to find only currently logged-in users, a system view named “ **pg_stat_activity** ” is used in Postgres.

This blog post will explain how to find logged-in users in Postgres. For that purpose, the below-listed concepts will be covered in this write-up:

  * How to Find Users in Postgres?
  * How to Find Currently Logged-in Users in Postgres?



 **How to Find Users in Postgres?**

Use the pg_user table to find all the users, including ordinary users, superusers, etc. The pg_user table has various columns, as shown below:

\- **usesysid** : It represents the user ID.  
\- **usename** : It represents the user name.  
\- **usersuper** : It retrieves a boolean value: “t” if the user is a superuser, and “f” if the user is not a superuser.  
\- **usecreatedb:** It retrieves a boolean “ **t** ” or “ **f** ”: “t” if a user has privileges to create a database and “f” if the user doesn’t create a database.  
\- **passwd** : It represents the user password.  
\- **valuntil** : It shows the validity date of the password.  
\- **usecatupd** : It retrieves a boolean value “ **t** ” or “ **f** ”: “ **t** ” represents the user can update the system catalogs, while “ **f** ” represents the user can’t update the system catalogs.  
\- **userepl** : It retrieves a boolean value, “ **t** ” or “ **f** ”, which signifies whether the user can initiate replication or not.  
\- **useconfig** : It shows the session defaults for the runtime configuration variable.

 **Example: How Do I Find the Users in Postgres?**

In this example, we will find user names, IDs, and createdb privileges using the pg_user table:
    
    
    SELECT usename, usesysid, usecreatedb
    FROM pg_user;

The output shows that the pg_user table retrieves the “ **user names** ”, their “ **system ids** ”, and “ **create database** ” privileges successfully.

 **How to Find Currently Logged-in Users in Postgres?**

Use the pg_stat_activity to find all users currently connected/logged in to PostgreSQL. The “ **pg_stat_activity** ” consists of the following columns that are used to get all the information regarding a user:

\- **usesysid** : It represents the user ID.  
\- **usename** : It represents the user name.  
\- **pid** : It represents the Process ID.  
\- **datid** : It indicates the database ID where the process is executing.  
\- **datname** : It represents the database name.  
\- **client_hostname** : It indicates the client's hostname.  
\- **client_port:** It retrieves the client's port number.  
\- **client_addr** : It shows the client's address.  
\- **application_name:** It represents the application name.  
\- **query** : It represents a current query.  
\- **state** : It shows the query state.  
\- **query_start** : It represents the query’s start time.  
\- **state_change** : This column shows the time when the state of the query was changed.  
\- **waiting:** It retrieves a boolean value( **t** or **f** ) that indicates the query's waiting status.

 **Note** : To view processes owned by other users, you must have superuser privileges.

 **Example: How Do I Find the Currently Logged-in Users in Postgres?**

In this example, we will find the names and IDs of the users currently logged in to Postgres:
    
    
    SELECT DISTINCT usename, usesysid
    FROM pg_stat_activity;

The above snippet shows that currently, two users are logged into PostgreSQL.

 **Conclusion**

To find the logged-in users in Postgres, the “pg_stat_activity” view is used. The “ **pg_stat_activity** ” view consists of various columns that are used to get all the information regarding a user, such as usesysid, usename, pid, etc. In PostgreSQL, to find all the users, including ordinary users, superusers, etc., use the pg_user table. This blog post explained how to find the logged in users in PostgreSQL via practical demonstration.

---
[View this page online](https://www.commandprompt.com/education/how-to-find-logged-in-users-in-postgresql/)