91

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.

125

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      |                                                | {}
  • 22
    from terminal: psql -c '\du' – Frank Henard Oct 8 '15 at 17:51
5

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.

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.

2

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/1121195544.8208.242.camel@state.g2switchworks.com

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.