In PostgreSQL, each user has their own specific role in managing operations in the database. In certain situations, it is difficult to find the user’s roles and privileges. To deal with such situations, PostgreSQL provides the “\du” and “\du+” statements that are used to show the list of users along with their attributes and descriptions.
This write-up will teach you how to show users in Postgres via the below-listed methods:
- Method 1: Show Users in PostgreSQL via SQL Shell
- Method 2: Show Users in PostgreSQL via pg Admin
Let's begin with the first method.
Method 1: Show Users in PostgreSQL via SQL Shell
Open the SQL, provide the login information, and to display the list of users, execute the statement provided below:
The above “\du” statement is utilized to show the list of all the users/roles in the selected database:
From the output, users can clearly notice that the “\du” command retrieves the list of users. For displaying the description of the role, the below statement can be utilized:
Executing the above statement will display additional information about the user’s roles:
This way, you can get the list of users with or without a description using the SQL SHELL.
Method 2: Show Users in PostgreSQL via pgAdmin
The pgAdmin facilitates easiness to the users through the graphical representation. To show the list of users on the specified database, select the “Query Tool” option as given below.
For this, a “Query” window is displayed on which the user can execute any statement. Let’s run the below-given query to show the list of users in Postgres via pgAdmin:
SELECT * FROM pg_catalog.pg_user;
The “SELECT” statement is utilized to get all the user information from the database through “pg_catolog.pg_user”:
The above query retrieved a result set having eight users whose names are given in the “usename” column. Here are the details of all the columns:
- usename: it contains the name of users (postgres is the default database of PostgreSQL).
- usesysid: specify the unique identification of users.
- usecreatedb: identify the possibility of creating a database through boolean variables (true or false).
- usesuper: represent that the user is a superuser or not via a boolean value.
- userepl: refers that the user can initialize replication or not via the boolean value.
- passwd: user can specify the password in text format.
It is all about the guidelines of PostgreSQL.
Run the “\du” and “\du+” statements from the SQL Shell(psql) to show the list of users along with their attributes and descriptions. Or you can use the “pg_catolg.pg_user” with the collaboration of the “SELECT” statement to show the users via pgAdmin. This article has explained all essential methods to show the detail of users in PostgreSQL.