How to List All Users in PostgreSQL

Sometimes you may need to see all users in database as a part of database management and website administration. In this article, we will look at how to list all users in PostgreSQL. You can also use these steps to list users and roles or list users and permissions in PostgreSQL.

How to List All Users in PostgreSQL

There are two ways to list all users in PostgreSQL – using psql command and using SQL queries. We will look at both these approaches.

Using psql command

Log into PostgreSQL using postgres user.

$ psql -U postgres

You will be prompted for password. Enter password to log into PostgreSQL.

postgres-#

Enter \du command to list all users in PostrgeSQL.

postgres-# \du

You will see the list of all users and roles.

If you want more information such as description for each user, enter \du+ command.

postgres-# \du+

Also read : How to Check PostgreSQL Version

List all users in PostgreSQL using SQL query

In PostgreSQL, pg_catalog.pg_user tables stores information about all users. We will query this table to get a list of all users.

Here is the SQL query to list all users in PostgreSQL.

SELECT usename AS role_name,
 CASE
  WHEN usesuper AND usecreatedb THEN
    CAST('superuser, create database' AS pg_catalog.text)
  WHEN usesuper THEN
    CAST('superuser' AS pg_catalog.text)
  WHEN usecreatedb THEN
    CAST('create database' AS pg_catalog.text)
  ELSE
    CAST('' AS pg_catalog.text)
 END role_attributes
FROM pg_catalog.pg_user
ORDER BY role_name desc;

Also read : How to Change User to Superuser in PostgreSQL

You will see an output similar to one below.