Using PostgreSQL Shell:
Let’s make a fresh start by listing the PostgreSQL users in the PostgreSQL shell. So, tap on the search area, write “psql”, and click on the shown “MySQL” shell application to quickly launch it. The shell will appear on your screen, asking for your local hostname. Write your server name add the name of a database existing in your SQL. Use the port number “5432” and any user with its username. We have been using the “aqsayasin” username and adding its password.
To list all the current existing users in your PostgreSQL shell is quite easy. We have to utilize the simple “\du” command. It will list all the current users of your PostgreSQL database on your shell screen, as demonstrated. This “\du” command cannot be used at the PostgreSQL PgAdmin GUI interface. The output shows a table with usernames listed as “Role name” with many of their properties.
We have 2 users currently existing in our database, i.e. aqsayasin and Postgres. Both the users contain the same privileges like superuser, create role, Create Db, Replication. The “Postgres” database also contains the Bypass RLS rights. None of the users belongs to any group as per the “Member of” column is empty in the below table.
We have seen a way to list all the current existing users in our PostgreSQL shell. Now, we will see how a single user, along with its role attributes, can be listed in the PostgreSQL Shell. For this, you need to use the same “\du” three-character command along with the name of a specific user. In this illustration, we have been fetching the record for user “aqsayasin” as shown. It shows its privileges without showing any other useful details.
The same query of “\du” along with the name of a user can be applied for other users, i.e. “Postgres” as shown in the attached black-screen photo.
Let’s search for the user that is not existing in our database right now and see how the “\du” command behaves in the PostgreSQL shell. For instance, we have been searching for the username “john” using the “\du” command in the shell. After executing this command, we have shown the empty “List of roles” table as an output below. This means the listing of the specific user that is not available in our database can never lead you to an error.
Let’s say you want to see the description of all the users you have in your database using the same “\du” command. For this, you need to utilize the “+” sign in concatenated with the “\du” command within the shell query area. You can see we have tried this command and got another column named “Description” within the “List of Roles” table for users. Although the “Description” column is empty, it implies that at the time of creating users, we haven’t added any description for our users. But we can do that as well.
This was all about the most basic information regarding the database users we have in our database. PostgreSQL came up with another command that can list a lot more than this basic information regarding users. That command is “Select” instruction using the “pg_catalog.pg_user” default table. Using the SELECT instruction with “*” specifying the name of a default standard table for users can help you to list all the other information regarding users that you cannot get with the “\du” command. So, we have tried this command in our PostgreSQL shell as shown in the image and got the shown result. It returns many columns along with user information. The “usesysid” column shows the ID for all the existing users. All the values like “t” and “f” stand for true and false for specific user rights beneath the columns “usecreatedb”, “usesuper”, “userepl”, and “usebypassrls” i.e. if the user has those rights or not. The “passwd” column shows the encrypted password for users. Another two columns are empty, i.e. “valuntil” and “useconfig”.
There is another quite easy way to list all the users in the PostgreSQL shell. This method uses a simple list command via the “\l” 2 character command. After using this command, we have seen that it displays the usernames beneath the column “Name”, the Owner database the users belong to, the user encoding type, Collate column, and Ctype column to show some extravagant information. The last column is the “Access privileges” command to show us the path to get the information regarding the specific user rights from our system database.
Using PgAdmin PostgreSQL GUI:
We can also use the PostgreSQL pgAdmin GUI to list the users from our database. But, we will start with the creation of a new user first. Therefore, expand the database tab from the left corner of pgAdmin GUI, and you will see the option “Login/Group Roles”. Right-click on this option, hover over the “Create” option, and tap on the “Login/Group Role…” option.
A screen named “Create – Login/Group Role” will be open on your pgAdmin screen, as shown. Within its “General” tab, you have to add the name of a user you want to create within the “Name” text bar and add comments or descriptions about this user as demonstrated.
Below its “Definition” tab, add the password for this particular user, the expiry date for this user account, and its connection limit. We have been using the default expiration, i.e. No expiry for a particular user and connection limit to “-1” i.e. you can log in as much time as you want.
When it comes to the Privileges tab, we have many rights to assign to this particular user. Tap on the sliding button in front of each option to enable it. We have assigned almost all the rights to test users.
Within the SQL section, you can see the query that has been auto-generated for this particular user creation, i.e. test. Tap on the Save button to finally create this user.
Refresh the “Login/Group Roles” tab from the left corner, and you will get the list of all existing users in your database, including “test”, “aqsyasin”, and “Postgres” as beneath.
To list all the users using the query, you have to use the same “SELECT” instruction using the “pg_catalog.pg_user” keyword on the query area. The list of users has been displayed.
Conclusion:
An immense amount of hard work has been added to this article to demonstrate how to list all the PostgreSQL users in it. We have tried this topic within the PostgreSQL “psql” shell and PostgreSQL GUI “pgAdmin”. We have tried the “\du” command, SELECT instruction with the “pg_catalog.pg_user” database table, and the simple “\l” list command to display the users, their privileges, and official information.
