How to List All Users in PostgreSQL

Last updated on August 29th, 2024 at 05:12 am

Sometimes you may need to see all users in database as a part of database management and website administration. In fact, it is a best practice for database administrators to regularly keep an eye on the user accounts that have access to your database, along with their permissions. Otherwise, people tend to keep adding more and more users to their database, without any control, and this leads to a disaster. In this article, we will look at how to list all users in PostgreSQL. You can also use these steps to list users and roles or list users and permissions in PostgreSQL.

How to List All Users in PostgreSQL

There are two ways to list all users in PostgreSQL – using psql command and using SQL queries. We will look at both these approaches.

1. Using psql command

In this approach, we will log into PostgreSQL console and issue a command to list all users in our system. Log into PostgreSQL using postgres user.

$ psql -U postgres

You will be prompted for password. Enter password to log into PostgreSQL.

postgres-#

Enter \du command to list all users in PostrgeSQL.

postgres-# \du

You will see the list of all users and roles that have access to.

If you are a system administrator as shown above, you will be able to see list of all users, including read only users. If you are just a normal user, you will only see your own details.

If you want more information such as description for each user, enter \du+ command.

postgres-# \du+

Also read : How to Check PostgreSQL Version

2. List all users in PostgreSQL using SQL query

In PostgreSQL, pg_catalog.pg_user tables stores information about all users. We will query this table to get a list of all users.

Here is the SQL query to list all users in PostgreSQL.

SELECT usename AS role_name,
 CASE
  WHEN usesuper AND usecreatedb THEN
    CAST('superuser, create database' AS pg_catalog.text)
  WHEN usesuper THEN
    CAST('superuser' AS pg_catalog.text)
  WHEN usecreatedb THEN
    CAST('create database' AS pg_catalog.text)
  ELSE
    CAST('' AS pg_catalog.text)
 END role_attributes
FROM pg_catalog.pg_user
ORDER BY role_name desc;

Also read : How to Change User to Superuser in PostgreSQL

You will see an output similar to one below.

If you want to update user password in PostgreSQL, you can do it with ALTER USER or ALTER ROLE statements.

Conclusion

In this article, we have learnt a couple of easy ways to quickly see a table of all users in PostgreSQL database. You can view all users in PostgreSQL using \du, \du+ commands or above SQL query to pg_catalog.pg_user table. It is important for database administrators to run this command from time to time, to get a list of all users that have access to their system, review their permissions and modify/remove them if not needed.

Ubiq makes it easy to visualize data, and monitor them in real-time dashboards. Try Ubiq for free.