PostgreSQL List Users?

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

To list all user accounts in the PostgreSQL database server, you use the \du psql command as follows:

1
2
3
4
5
postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

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

1
2
3
4
5
postgres=# \du+
                                          List of roles
 Role name |                         Attributes                         | Member of | Description
-----------+------------------------------------------------------------+-----------+-------------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}        |

Behind the scenes, PostgreSQL issues the following SQL statement when you use the \du command:

1
2
3
4
5
6
7
8
9
10
SELECT u.usename AS "Role name",
  CASE WHEN u.usesuper AND u.usecreatedb THEN CAST('superuser, create
database' AS pg_catalog.text)
       WHEN u.usesuper THEN CAST('superuser' AS pg_catalog.text)
       WHEN u.usecreatedb THEN CAST('create database' AS
pg_catalog.text)
       ELSE CAST('' AS pg_catalog.text)
  END AS "Attributes"
FROM pg_catalog.pg_user u
ORDER BY 1;

Comments