info

Listing users with \du or by querying pg_catalog.pg_user works the same on any PostgreSQL deployment, so you can apply these techniques wherever your database runs. If you're an enterprise looking for managed Postgres built for the AI era, Lakebase delivers the performance, security, and native Lakehouse integration your teams need to govern roles and data at scale. If you're a developer or startup who needs to ship fast and scale on demand, Neon gives you the best Postgres platform to do it.

Summary: in this tutorial, you will learn how to use the PostgreSQL list user command to show all users in a PostgreSQL database server.

Listing users using the psql tool

First, connect to the PostgreSQL database server using the postgres user:

psql -U postgres

It will prompt you for a password:

Password:

Once you enter the password for the postgres user, you will see the following PostgreSQL command prompt:

postgres=#

Second, use the \du to list all user accounts (or roles) in the current PostgreSQL database server:

\du

If you want to show more information, you can use the \du+ command:

postgres=#\du+

The \du+ command adds column called description.

Listing users using SQL statement

The following statement returns all users in the current database server by querying data from the pg_catalog.pg_user catalog:

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;

Summary

  • Use \du or \du+ psql command to list all users in the current database server.
  • Use the SELECT statement to query the user information from the pg_catalog.pg_user catalog.