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.
Enter \du command to list all users in PostrgeSQL.
You will see the list of all users and roles.
If you want more information such as description for each user, enter \du+ command.
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.
So you can list all users in PostgreSQL using \du, \du+ commands or above SQL query to pg_catalog.pg_user table.