How to List Users in PostgreSQL

Introduction

When you’re working with a PostgreSQL database, there will be times when you want to see all the users for that database. This task is easy to accomplish with the use of a simple command. In this article, we’ll take a closer look at users and roles, and we’ll show you how to list users in PostgreSQL.

Prerequisites

Before we can log into PostgreSQL and try out some examples, there are a few essential prerequisites that need to be in place:

• You’ll need to have PostgreSQL installed on your device. If you need to determine whether the service is installed, use the command service postgresql status, which will let you know if the status is active.

• You’ll also need to have psql, the interactive command-line interface for PostgreSQL, installed on your machine. You can use the command psql -V to verify that the tool is installed.

Accessing PostgreSQL Using the ‘psql’ Command-line Interface

We’ll need to access a database in order to get a list of users in PostgreSQL. You can use the following sequence of commands to access a PostgreSQL database on your local machine using psql:

After you enter this command, you’ll be prompted for the password. Once you provide the password and press RETURN, you’ll have access to PostgreSQL.

Next, type the following command to enter psql and access your database:

Again, you’ll be prompted for the user’s password. Simply enter it and press RETURN.

Create a New Role Using the Superuser in PostgreSQL

If you’re planning to create a user in PostgreSQL, you’ll need the privileges of the SUPERUSER. Use the following psql command in the terminal to create a user with a login password:

You can also just use the CREATE USER statement. Let’s see how we can set an encrypted password for a PostgreSQL user using that SQL statement instead:

NOTE: Password strings must be enclosed within single quotation marks (').

Either of the commands shown above should return the following response:

NOTE: Starting in version 9.4 of PostgreSQL, the CREATE USER SQL statement is simply an alias for CREATE ROLE; however, when you execute the CREATE USER statement , PostgreSQL will also log you in as the newly-created user.

Logging into a PostgreSQL Database Cluster as a User

The LOGIN process is an authorization procedure that will determine if a user has the correct ROLE to login and connect to a PostgreSQL database. When logging into a PostgreSQL database, the ROLE is treated as the user.

Now that we’ve created a user or role, let’s try to change the attributes or the privileges associated with it. We’ll use the psql command-line syntax shown below:

NOTE: psql will return ERROR: role "new_user" does not exist if the user in question does not yet exist.

The ALTER command is used to change the attributes of the role.

Our new_user is now an individual user that can have ownership of database objects and can also have database privileges. It can be considered a ‘user’ or ‘group’ depending on how it will be used.

Removing the New Role for a PostgreSQL User

To remove a role, the DROP ROLE command is used. Let’s check out an example of how to do that:

List the Users in PostgreSQL

Now that we’ve created, modified, and deleted users and roles, we can try listing users in our PostgreSQL database. To display all of the users in your database, type the command shown below:

You’ll see output that looks like this:

You can also use \dg, \dg+ or \du+ to display GROUP, USER and ROLE information.

Conclusion

If you’re planning to store data in a PostgreSQL database, it’s important to know how to manage users and roles. In addition to being able to create, modify and delete both users and roles, you’ll also need to know how to retrieve a list of users in a PostgreSQL database. In this article, we learned the simple command used to obtain that list. With the examples provided in this tutorial, you’ll be prepared to manage the users in your own PostgreSQL database environment.