How to List Users in PostgreSQL

Introduction

This tutorial will explain how to list users in a PostgreSQL. The PSQL is a useful PostgreSQL utility that enables the user to interactively query databases and assign, change and audit users roles as needed. The PSQL utility supports an assortment of meta commands that enable users to write scripts and automate a wide range of tasks within the PostgreSQL database.

Prerequisites for Listing users in PostgreSQL

• The PostgreSQL server must be properly installed, configured and running. Download PostgreSQL For Linux and Windows systems here

Common PostgreSQL User Commands

Following are the most commonly used PostgreSQL user-related meta commands:

• The \du command will show all of the existing PostgreSQL users.
• The \du __user_name__ command will list a specific username, if it exists.
• The create role __rolename__ meta command will create a role against an existing PostgreSQL username.
• The create role __user_name__ noinherit login password __the_password__ command will create a role with a username and password.

• The set role __user__ command adds or changes a user role.

• The grant __user1_role__ to __user2_role__ command will set user1_role to user2_role.

How to Create Users Roles in PostgreSQL

Upon installation, the PostgreSQL application will create a superuser named postgres by default. Therefore, this superuser will be used until other users, or roles, are created whenever connecting to PostgreSQL.

To create a PostgreSQL role, login to PostgreSQL server using the command line and superuser role and then execute the following command:

As shown in the above image, a new user named my_user_temp was created using the create user command.

How to Grant Roles to a User in PostgreSQL

With the sample user name my_user_temp created, this section will explain how to grant roles to this user.

First, all of the database within the PostgreSQL server must be listed. Execute this with the \l meta-command as shown in the following image:

This example will try to connect to the database testdatabase using the my_temp_user role and then perform a basic SELECT query to an existing table named client_info.

The results should resemble the following:

Notice that the image shows that PostgreSQL detected the user my_temp_user does not have permission to perform a query against the client_info table. This created the ERROR: permission denied for table client_info message.

Now assign a role to the user so the user will be able to interact with the testdatabase database table. To do this, end the current session using the \q meta-command and then login again using the superuser role postgres.

Now execute the following command to grant privileges to the user(s) in PostgreSQL:

Now connect again using the my_temp_user role. Next, as shown below, perform a basic query to the table client_info:

How to List Users in PostgreSQL

Users are listed within the PostgreSQL server to allow them to perform a simple audit and obtain the following information:

• How many users exist within the PostgreSQL server.

• The privileges and roles assigned to each individual user.

• If a monitor or user is no longer active and can be subject to removal or archiving.

• If all users were given the proper number of levels or privileges.

To list all users within the PostgreSQL server, execute the PostgreSQL meta-command \du:

The above image shows all of the existing users within the PostgreSQL server. Now the system can easily be audited to determine if all of the users were correctly created and assigned the proper roles.

Conclusion

This tutorial explained how to list users in a Postgresql. The article specifically covered common PostgreSQL user commands and how to use create, assign and change user roles to PostgreSQL users. The tutorial also explained how to list all users for audit. Remember that the PostgreSQL application will create and use the superuser name postgres by default until other users are created.