PostgreSQL / Short guides
How to create and delete users in PostgreSQL
Introduction
More often than not, when working with a database you will need to be able to create and delete users. Whether working in a team that has multiple members needing access to the database or creating and deleting test users to simulate database privileges, knowing how to add and remove database users is important.
In this short guide, we will cover the basics of creating and deleting database users in PostgreSQL. These commands will have you ready to get started on granting database access and establish a foundation for future role management.
How do you create PostgreSQL users?
PostgreSQL has the CREATE USER psql command and createuser for the command line. Both options define a new database user account. In order to create users, you need to be a superuser role for the database cluster. Alternatively, any user with the CREATE ROLE privilege is also able to create new users.
The basic syntax for createuser looks like this:
...and for CREATE USER:
The options component of either method is where you can define parameters such as whether this user can create databases, is a superuser, and the need for a password when connecting to the server.
The command line syntax can look something like for creating user1:
In addition to creating users, it is important to know the different privileges that can be granted so users are only able to work with the database in specified ways. For the purpose of this guide we are just covering the basics to get started, but you can read more about granting privileges in PostgreSQL for more advanced settings.
How do you delete PostgreSQL users?
With the ability now to create users, it is important to also know how to delete a user when their access is no longer needed. Similar to creating a user, there are two ways to delete one. You can use both the command line utility dropuser or the psql command DROP USER.
The basic syntax for dropuser is:
There are additional options that you can include for parameters such as the username to login as to conduct the user drop or including a clause for if the user exists.
The basic psql command option syntax looks like:
A common syntax using the command line would look something like:
Conclusion
In this quick guide, we covered the basics of creating and deleting users for PostgreSQL databases as well as some additional options.
Once your users are created, we have additional informative articles diving deeper in user role management in PostgreSQL as well as user authentication.
