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 isactive
.You’ll also need to have
psql
, the interactive command-line interface for PostgreSQL, installed on your machine. You can use the commandpsql -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
:
1 | sudo su - postgres |
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:
1 | psql new_user -h 127.0.0.1 -d some_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:
1 | CREATE ROLE new_user WITH LOGIN PASSWORD '1234'; |
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:
1 | CREATE USER new_user WITH ENCRYPTED PASSWORD '1234'; |
NOTE: Password strings must be enclosed within single quotation marks ('
).
Either of the commands shown above should return the following response:
1 | CREATE ROLE |
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:
1 | ALTER ROLE new_user CREATEROLE CREATEDB; |
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:
1 | DROP ROLE new_user; |
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:
1 | \du |
You’ll see output that looks like this:
1 2 3 4 5 | List OF roles ROLE name | Attributes | Member OF -----------+------------------------------------------------------------+----------- new_user | CREATE ROLE, CREATE DB | {} postgres | Superuser, CREATE ROLE, CREATE DB, Replication, Bypass RLS | {} |
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.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started