Join my Laravel for REST API's course on Udemy 👀

List users in PostgreSQL

April 10, 2021  ‐ 2 min read

Listing all the users of a PostgreSQL database is easiest via the interactive terminal. You can start the interactive PostgreSQL terminal with the psql on the command line. If this throws an error with the message psql: error: FATAL: role "..." does not exist you probably haven't yet created a database role for the operating system user you are logged in as. You might want to fix that too. In the mean time you can probably switch to the postgres user with a command like sudo su postgres.

Once you get in the Postgres shell you can use the \du meta-command to show the existing PostgreSQL users. With du being short for "display users" I imagine.

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

postgres=#

If you're just interested in information about a single user you can filter this list on the username.

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

postgres=#

Listing Postgres users with SQL

If you rather take the SQL route over the meta-command there is an option too. If you have rights to query from pg_catalog at least, which shouldn't be a problem if you use the default postgres user.

postgres=# SELECT usename FROM pg_catalog.pg_user;
     usename
------------------
 postgres
 sitetemplate
(2 rows)

postgres=#