How to List All Users in PostgreSQL Quickly and Easily

To list all users in PostgreSQL, you can query the pg_catalog.pg_user system catalog or use the command \du in the psql terminal. Both methods show the list of database roles and users available in your PostgreSQL instance.

Syntax

You can list all users by querying the system catalog pg_catalog.pg_user or by using the psql meta-command \du.

• SELECT * FROM pg_catalog.pg_user; returns detailed user info.
• \du shows a summary of roles and their attributes.

sql

SELECT * FROM pg_catalog.pg_user;

Example

This example shows how to list all users by running a SQL query and using the psql command.

sql

/* SQL query to list all users */
SELECT usename AS username, usesysid AS user_id, usecreatedb AS can_create_db, usesuper AS is_superuser FROM pg_catalog.pg_user;

-- In psql terminal, simply type:
\du

Output

 username | user_id | can_create_db | is_superuser 
----------+---------+---------------+--------------
 postgres |      10 | t             | t            
 user1    |    16384 | f             | f            
(2 rows)

                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB                          | {}        
 user1     |                                                            | {}        
(2 rows)

Common Pitfalls

Common mistakes when listing users include:

• Trying to query pg_user without sufficient permissions.
• Confusing roles with users; PostgreSQL treats them similarly but roles can have different attributes.
• Using \du outside the psql terminal, which will not work.

sql

/* Wrong: Running \du in a non-psql shell */
-- This will cause an error because \du is a psql command, not SQL.

/* Right: Use SQL query if not in psql */
SELECT usename FROM pg_catalog.pg_user;

Quick Reference

Command	Description
SELECT * FROM pg_catalog.pg_user;	List all users with details
\du	Show roles and attributes in psql terminal
SELECT usename FROM pg_catalog.pg_user;	List usernames only
\du+	Show roles with additional info in psql

Key Takeaways

Use SELECT from pg_catalog.pg_user to list all PostgreSQL users with details.

The \du command works only inside the psql terminal to show roles and users.

PostgreSQL treats users as roles with login privileges.

Ensure you have the right permissions to query user information.

Avoid using psql meta-commands outside the psql environment.