How to list all the users in PostgreSQL

PostgreSQL is a free relational database management system, which emphasizes flexibility and SQL compliance. It is sometimes referred to as Postgres. Viewing every user in the database could be essential for database management and website administration needs. This Answer will examine how to list each user in PostgreSQL. These methods may also list PostgreSQL users and roles or users and permissions.

We will list every user in PostgreSQL using two different techniques so that we can choose which is the best and easiest to use.

1. SQL queries

2. The psql command

SQL queries

The first method we will use to list all the users in PostgreSQL is by writing SQL queries. The database user's information is accessible via pg_user. We must access the pg_catalog.pg_user user columns containing data on all users to obtain a list of PostgreSQL users using SQL.

Let's see this in action.

Coding example

Explanation

• Line 1: We select the usename using an alias username.

• Line 2: We introduced the CASE expression, which is the same as the if/else statement in most programming languages.

• Line 3: The usesuper is a boolean type for a "superuser", so what we are saying here is that if the user is a "superuser", perform the operations on lines 4 and 6.

• Line 4: We print out the "superuser" using CAST here because we have to perform a conversion between two data types.

• Line 5: We use the ELSE statement.

• Line 6: We print out "No user".

• Line 7: We commit this transaction using the END keyword.

• Line 8: We'll fetch all the results from the pg_user table which gives us access to information about database users.

• Line 9: Lastly, we order the result in ascending order.

We get an output username and user_attribute with a user named postgres who has the attribute "superuser". Note that this may be different on your computer.

There is another way to achieve the same result using SQL. Using this method we will select the fields we want directly from the pg_user table.

Coding example

Explanation

• Line 1: We select the usesysid using an alias id, where usesysid is the ID of the user.

• Line 2: We select the usename using an alias name, where usename is the user name.

• Line 3: We select usesuper using an alias super_user, where usesuper is a boolean to check if the user is a superuser.

• Line 4: We select passwd using an alias password_sha1, where passwd is the password in text but we can't see it because it is hashed.  

• Line 5: The pg_shadow contains information about database users.

• Line 6: Lastly, we order the result by their username.

The psql command

We can also list the users in PostgreSQL by interacting directly via the shell or the terminal. There is a command called psql that can be called upon to list existing users in our PostgreSQL shell.

The \du command is used to achieve this purpose, and we get an output containing a table with "role name" and other properties.

First, use the Postgres user to connect to the PostgreSQL server.

We will need to supply the password for the user.

We will then use the \du command to display a list of all user accounts (or roles) on the active PostgreSQL server.

The \du+ command, which adds a new column named description, can be used to display more information.

Here is an example of how it works:

Most of the time, we will want to list all the users in the PostgreSQL server, but the most important of them is to check the number of users we have connected to the database and the role they all possess. Using the \du or \du+ commands, or with the help of the SELECT statement to query the user data from the pg_catalog.pg_user catalog and we get a list showing the list of all users on the current database server.