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:

psql postgres

The images shows how to create the user my_temp_user in PostgreSQL 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:

list of database within the PostgreSQL server using the `\l` meta-command

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:

Image that shows an error when a user has no assigned privileges to access a database. 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:

Image that shows a successful operation of granting ROLE to a user in PostgreSQL

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

Image shows that the usr `my_temp_user` was able to query 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 images shows all the existing users and their respective roles 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.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.