How to List Users in PostgreSQL
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:
\ducommand will show all of the existing PostgreSQL users.
\du __user_name__command will list a specific username, if it exists.
create role __rolename__meta command will create a role against an existing PostgreSQL username.
create role __user_name__ noinherit login password __the_password__command will create a role with a username and password.
set role __user__command adds or changes a user role.
grant __user1_role__ to __user2_role__command will set
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
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
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
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
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.
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