Roles or users play a very crucial part in databases as they help us manage the database objects. In PostgreSQL, the concept of roles or users is used to manage the database access privileges. While working with Postgres, checking the connected users is a routine task for the database administrators. To do this, a system view named “pg_stat_activity” is used in PostgreSQL.

This article explains how to check if a particular user is connected with the Postgres server or not using practical demonstration.

How to Check if a User is Connected to the Postgres Server or Not?

In Postgres, the “pg_stat_activity” is a system view that helps us determine the active queries. To check the active users via the “pg_stat_activity”, use the following syntax:

SELECT usename, datname, state
FROM pg_stat_activity 
WHERE usename='user_name';

Specify the user name of your choice in place of the 'user_name' option. If the stated query retrieves any result, this means the specified user is connected to the server.

Example: Checking User Status

This example will utilize the “pg_stat_activity” view to check if the “postgres” user is connected to the PostgreSQL server or not:

SELECT usename, datname, state
FROM pg_stat_activity 
WHERE usename='postgres';

The output shows that the “postgres” user is connected and currently in an active state. Let’s execute the same query one more time for the “sample_user”:

SELECT usename, datname, state
FROM pg_stat_activity 
WHERE usename='sample_user';

The output didn’t retrieve anything, which means the “sample_user” is not connected to the Postgres server.

That was all about checking if the user is connected with the Postgres server or not.

Conclusion

In Postgres, the “pg_stat_activity” is a system view that helps us determine the active queries. To check the active users via the “pg_stat_activity”, use the “SELECT usename, datname, state FROM pg_stat_activity WHERE usename='user_name';” command. If the stated query retrieves any result, this means the specified user is connected to the server. However, if it didn’t retrieve anything, this means the selected user is not connected to the Postgres server. This article has presented a comprehensive guide on how to check if a user is connected to the Postgres server or not.