PostgreSQL Tutorial: Show Users

August 7, 2023

Summary: in this tutorial, you will learn how to use the PostgreSQL list user command to show all users in a PostgreSQL database server.

Table of Contents

Listing users using the psql tool

First, connect to the PostgreSQL database server using the postgres user:

$ psql -U postgres

It will prompt you for a password:

Password:

Once you enter the password for the postgres user, you will see the following PostgreSQL command prompt:

postgres=#

Second, use the \du to list all user accounts (or roles) in the current PostgreSQL database server:

postgres=# \du

img

If you want to show more information, you can use the \du+ command:

postgres=#\du+

The \du+ command adds an additional column called description.

Listing users using SQL statement

The following statement returns all users in the current database server by querying data from the pg_catalog.pg_user catalog:

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;

img

Summary

  • Use \du or \du+ psql command to list all users in the current database server.
  • Use the SELECT statement to query the user information from the pg_catalog.pg_user catalog.

Search

Categories

Tags