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 setuser1_role
touser2_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:
1 | psql postgres |
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.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started