LIST USERS
POSTGRESQL

Postgres List Users: Two Different Approaches 

intro

A PostgreSQL server can have several users, and there are different ways to list them all. Explore three different approaches to list users in Postgres.

Tools used in the tutorial
Tool Description Link
Dbvisualizer DBVISUALIZER
TOP RATED DATABASE MANAGEMENT TOOL AND SQL CLIENT
PostgreSQL logo POSTGRESQL
THE POSTGRESQL DATABASE

Keeping track of users is a common task that database administrators must frequently perform. For PostgreSQL, there are a couple of Postgres list users procedures available, and the best one depends on your experience and the specific use case.

In this article, you will explore two different approaches to getting the list of users in PostgreSQL:

  • With a command-line command
  • With a query

Let’s learn how to list users in Postgres!

List All Users in Postgres With psql

psql stands for "PostgreSQL interactive terminal” and is a tool for interacting with a PostgreSQL server via the command line. In detail, it allows you to:

  • Add databases, tables, and users.
  • Run SQL queries in  the database.
  • Retrieve metadata information about the database objects.

Now that you know what psql is, follow the steps below to run the Postgres list users \du command:

  1. Open the terminal.
  2. Connect to your PostgreSQL server with the following psql command:

psql -U <username>

Replace <username> with the username of the PostgreSQL user you want to log in with. For example, replace it with the “postgres” username. After that, psql will ask you to enter the user's password. Type in it and press Enter to log into the server.

  1. List all the users with:

\du

This command will produce a table containing the users in the PostgreSQL server:

The table is returned by the "\du" command.
The table returned by the "\du" command.

Use \du+ to get additional information about each user, such as their description:

The table returned by the "\du+" command.
The table returned by the "\du+" command. Note the "Description" column.

Now for the second way - we will use a SQL query to list the users in our database instances.

List Users in Postgres With a Query

The pg_user view of the pg_catalog schema under the postgres default database provides access to information about database users. 

Launch the query below in a client supporting PostgreSQL, such as DbVisualizer, to get the list of all the users:

Copy
        
1 SELECT * FROM pg_catalog.pg_user;

The above Postgres list users query will return the following result:

Listing users in DbVisualizer.
Listing users in DbVisualizer.

Here’s what information each column of pg_catalog.pg_user contains:

  • usename: The name of the user.
  • usesysid: The number used by PostgreSQL to uniquely identify the user.
  • usecreatedb: true if the user has the CREATEDB role attribute, false otherwise.
  • usesuper: true if the user is a superuser, false otherwise.
  • userepl: true if the user has the permissions to launch streaming replication, false otherwise.
  • passwd: A placeholder for the user password.
  • valuntil: The expiry time of the user password.
  • useconfig: Session defaults for run-time configuration variables.

Congrats! You just saw two effective ways to list users in Postgres.

Conclusion

In this article, you saw two different Postgres list users methods. As seen here, getting the list of users in a PostgreSQL server is a simple task. There are two methods to achieve that, and here we dug into both of them. The first involves a command-line command in psql, while the second requires a specific query. 

To avoid using the CLI and better understand the result of your queries, you should adopt a powerful database client like DbVisualizer. This tool supports dozens of databases and comes with complete query optimization capabilities, a drag-and-drop UI to build queries, and ER schema representation functionality. Download DbVisualizer for free!

FAQ

How to list superusers in Postgres?

To list superusers in Postgres, you can query the pg_roles catalog table with a specific filter as follows:

Copy
        
1 SELECT * 2 FROM pg_catalog.pg_user 3 4 WHERE usesuper = true;

This will return only users with the SUPERUSER attribute.

How to identify and list the currently logged-in users in a PostgreSQL database?

To identify and list the currently logged-in users in PostgreSQL, run the query below on the pg_catalog.pg_stat_activity system view:

Copy
        
1 SELECT * 2 FROM pg_catalog.pg_stat_activity 3 WHERE state = 'active';
Identify and list the currently logged-in users.
The logged in user is the one used by DbVisualizer to connect to the database.

What role does authentication play when listing users in PostgreSQL?

Authentication ensures security by verifying the identity of the user attempting to access user-related information. Thus, by enforcing authentication PostgreSQL restricts unauthorized access, contributing to secure user listing.

Is listing a large number of Postgres users fast?

The speed of listing numerous PostgreSQL users depends on various factors, including server performance, database size, and hardware resources. At the same time, it can be considered a fast operation as it involves a simple query on a system view. 

What tools make it easier to list all users in Postgres?

A database client such as DbVisualizer provides a visual interface for executing queries. This tool offers SQL query execution capabilities, allowing you to run the "Postgres list users" query easily. In addition, it offers export functions, SQL history, and more to improve your PostgreSQL database management experience.

Dbvis download link img
About the author
Antonello Zanini

Antonello is a software engineer, and often refers to himself as a technology bishop. His mission is to spread knowledge through writing.

The Table Icon
Sign up to receive The Table's roundup
More from the table
Title Author Tags Length Published
title

5 Ways to Split a String in PostgreSQL

author Antonello Zanini tags POSTGRESQL Split 6 min 2024-02-29
title

PostgreSQL CASE: A Comprehensive Guide

author Leslie S. Gyamfi tags CASE POSTGRESQL 7 min 2024-02-22
title

Discover All PostgreSQL Data Types

author Antonello Zanini tags DATA TYPES POSTGRESQL 12 min 2024-02-05
title

A Complete Guide to pg_dump With Examples, Tips, and Tricks

author Antonello Zanini tags pg_dump POSTGRESQL 8 min 2024-01-25
title

A Comprehensive Guide to Data Types in Postgres

author Leslie S. Gyamfi tags DATA TYPES POSTGRESQL 10 min 2024-01-23
title

SQL DISTINCT: A Comprehensive Guide

author Bonnie tags DISTINCT POSTGRESQL SQL 5 MINS 2024-01-11
title

Date Formatting in Postgres: A Comprehensive Guide

author Leslie S. Gyamfi tags DATE POSTGRESQL 7 MINS 2023-12-11
title

Casting in PostgreSQL: Handling Data Type Conversions Effectively

author Leslie S. Gyamfi tags CAST POSTGRESQL 5 MINS 2023-12-07
title

PostgreSQL Materialized Views: A Beginner's Guide

author Ochuko Onojakpor tags POSTGRESQL VIEWS 7 MINS 2023-12-04
title

Using the EXPLAIN plan to analyze Query execution in PostgreSQL

author Bonnie tags EXPLAIN POSTGRESQL 6 MINS 2023-09-21

The content provided on dbvis.com/thetable, including but not limited to code and examples, is intended for educational and informational purposes only. We do not make any warranties or representations of any kind. Read more here.

Cookie policy

We use cookies to ensure that we give you the best experience on our website. However you can change your cookie settings at any time in your browser settings. Please find our cookie policy here ↗