Command to list PostgreSQL user accounts?

Question

There are createuser & dropuser commands:

createuser - define a new PostgreSQL user account
dropuser - remove a PostgreSQL user account

Is there a corresponding way to list the user accounts?

These two commands do not require the user to invoke psql nor understand details of using it.

Answer 1

Use the psql shell and:

\deu[+] [PATTERN] such as:

postgres=# \deu+
      List of user mappings
 Server | User name | FDW Options 
--------+-----------+-------------
(0 rows)

And for all users:

postgres=# \du 
                              List of roles
 Role name  |                   Attributes                   | Member of 
------------+------------------------------------------------+-----------
 chpert.net |                                                | {}
 postgres   | Superuser, Create role, Create DB, Replication | {}

Also such as MySQL, you can do :

$ psql -c "\du"
                             List of roles
 Role name |                   Attributes                   | Member of 
-----------+------------------------------------------------+-----------
 chpert    |                                                | {}
 postgres  | Superuser, Create role, Create DB, Replication | {}
 test      |                                                | {}

Answer 2

To limit the output to the username only, simply do the following either from the psql shell or from the local shell/terminal as

psql -c "SELECT u.usename AS "User Name" FROM pg_catalog.pg_user u;"

While the quick method, as pointed out in a previous answer, displays 3 columns; including User Name, List of Roles Attributes, and Member of role group(s).

psql -c "\du"

Finally, as pointed out in a subsequent answer, the PostgreSQL commands run in the background for the \du command show those "default" columns by calling:

SELECT u.usename AS "User name",
  u.usesysid AS "User ID",
  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;

(what psql does behind the scenes when you type \du)

The last part was copied from the PostgreSQL Mailing List.

Answer 3

For a simpler answer...

From within psql:

\du

From the terminal:

psql -c '\du'

It helps me to remember that d is often used to describe something and u is for users.

Answer 4

SQL Solution which is normally done by psql \du:

SELECT u.usename AS "User name",
  u.usesysid AS "User ID",
  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;

Ref: https://www.postgresql.org/message-id/[email protected]