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.
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:
CREATE USER <name> <options>
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
createuser --createdb --password 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.
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
The basic syntax for
There are additonal 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:
DROP USER <name> <options>
A common syntax using the command line would look something like:
dropuser --if-exists user1
In this quick guide, we covered the basics of creating and deleting users for PostgreSQL databases as well as some additional options.