1. Introduction
When it comes to PostgreSQL, there’s typically more than one way to send requests to our databases. Of course, we can use SQL statements. But we can also use psql meta-commands.
In this tutorial, we’ll explore different ways to list all the PostgreSQL users using the CLI.
2. Using psql Meta-Commands
psql is an interactive, terminal-based tool for interacting with PostgreSQL databases. It comes with multiple options and can be used in various ways. It also features a special set of commands called meta-commands. psql meta-commands start with an unquoted backslash and are directly executed by psql.
We can list all the PostgreSQL users using either of two psql meta-commands: \du and \dg.
\du and \dg were for listing database users and database groups, respectively. However, they became equivalent when users and groups collectively became roles. In other words, we can use \du and \dg interchangeably.
We can execute psql commands in two ways: from the psql shell and directly from Bash.
2.1. Running psql Commands From Bash
To run the \du or \dg meta-commands directly from Bash, we’ll su to our PostgreSQL user first:
$ su - postgres
Then, we run the meta-command using psql and the -c option:
$ psql -c "\du"
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
baeldung | Create role, Create DB | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
If we want the output to include the description column, we adjust \du to \du+:
$ psql -c "\du+"
List of roles
Role name | Attributes | Member of | Description
-----------+------------------------------------------------------------+-----------+-------------
baeldung | Create role, Create DB | {} |
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {} |
With and without the +, \du and \dg only return a list of users we created. If we want to list all the PostgreSQL users, including system users, our meta-command is \duS:
$ psql -c "\duS"
List of roles
Role name | Attributes | Member of
---------------------------+------------------------------------------------------------+--------------------------------------------------------------
baeldung | Create role, Create DB | {}
...truncated...
pg_read_server_files | Cannot login | {}
pg_signal_backend | Cannot login | {}
pg_stat_scan_tables | Cannot login | {}
pg_write_server_files | Cannot login | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
Of course, if we change the meta-command to \duS+, the output includes an extra column.
2.2. Running psql Commands From psql Shell
We can run the same meta-commands in the previous section from the psql shell. When we do, we wouldn’t need psql‘s -c option, as we can just type and execute the meta-commands directly.
To run the meta-commands from our psql shell, we must log into the shell first, and we do this by running psql from our PostgreSQL user:
$ psql
Now that we’re in the psql shell, let’s get the list of users we created using \dg:
=# \dg
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
altschool | Create role, Create DB | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
We can use \dg+ so our output shows the description column:
=# \dg+
List of roles
Role name | Attributes | Member of | Description
-----------+------------------------------------------------------------+-----------+-------------
altschool | Create role, Create DB | {} |
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {} |
Then, to get a list that includes the system users, we run \dgS:
=# \dgS
List of roles
Role name | Attributes | Member of
---------------------------+------------------------------------------------------------+--------------------------------------------------------------
altschool | Create role, Create DB | {}
pg_execute_server_program | Cannot login | {}
...truncated...
pg_signal_backend | Cannot login | {}
pg_stat_scan_tables | Cannot login | {}
pg_write_server_files | Cannot login | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
2.3. Listing Foreign Users
With \du[+S] or \dg[+S], we get a list of local PostgreSQL users. But if we want a list of external/remote users, we use the \deu meta-command:
=# \deu
List of user mappings
Server | User name
---------+----------
baeldung | baeldung
We get the same output when we run it directly from Bash:
$ psql -c "\deu"
List of user mappings
Server | User name
---------+----------
baeldung | baeldung
As with the other meta-commands we’ve used, we can add + to \deu to get more details via an extra column. But we should typically be wary when using \deu+, as it may expose sensitive information.
3. Using an SQL Statement
We can also list all our PostgreSQL users by querying the pg_user system table using an SQL statement:
=# SELECT usename FROM pg_catalog.pg_user;
usename
-----------
postgres
baeldung
(2 rows)
Of course, if we want to list the users with other details besides their username, we could use the * wildcard in place of usename. But then, this may not always be ideal; it’s better to specify the columns we want to see.
4. Conclusion
In this article, we discussed how to list all the users on a PostgreSQL database from CLI.
We saw how to list users with psql meta-commands and with SQL statements. We also highlighted how to list external PostgreSQL users.
Finally, we also learned that we should take caution when using the \deu+ meta-command as it could expose sensitive data.