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.