How to Use Postgres to Drop a User from a Database Cluster

Introduction

When you’re administering a CockroachDB database cluster, you may find yourself needing to drop a particular user from the cluster. It’s easy to remove a CockroachDB user with the DROP USER command. In this article, we’ll take a closer look at the Postgres DROP USER command and check out some examples of its use.

Prerequisites

Before you proceed with this tutorial, make sure you have PostgreSQL installed and running, and confirm that you also have access to the psql command-line interface for PostgreSQL. It’s also helpful to have some fundamental knowledge of database management systems in order to understand the examples in this article.

Open and access the command-line interface in PostgreSQL

In PostgreSQL, the user called postgres has full superuser privileges and thus can access everything that resides in your PostgreSQL installation.

The following command will let you enter the psql command-line interface as the postgres superuser:

Creating user in PostgreSQL for us to remove with the DROP USER command

Once you’re connected to psql, you’ll be able to create a user with the WITH LOGIN attribute. If you want to have a secured role, you can also add the ENCRYPTED PASSWORD attribute to the CREATE USER statement.

Here’s an example of the CREATE USER command:

To verify that your new user was created successfully, use the command \du. You should receive output that looks like the following:

Dropping a user in PostgreSQL

Only a superuser with the privileges to alter users, roles and groups can DROP, or delete, another Postgres user.

The basic syntax for the DROP USER SQL statement is:

NOTE: You can also drop a ROLE in the same way that you would a USER.

You can delete or remove the user that you created earlier in this tutorial, or you can delete any user on your system that you’re no longer using. We’ll use the following command to drop a user using the DROP USER statement:

In this example, the DROP USER command would drop the user called newuser in the PostgreSQL database.

To verify that the user was actually dropped, use the \du command and you’ll see that newuser does not exist.

To exit the psql interface, enter the command \q in PostgreSQL.

PostgreSQL DROP USER CASCADE

If a user has privileges that have been granted to them, it can be only dropped by using the command DROP CASCADE in the terminal.

Let’s look at an example:

A user cannot be dropped if there is still a reference to it in any database of the cluster; you’ll receive an error if you attempt to delete it. You’ll need to drop all the objects on their own before you drop the user and remove the privileges the user has been granted on the other objects.

Drop a ROLE in Postgres

A Postgres “role” is very similar to a Postgres “user”, but a key distinction exists. While a role can be either a group or a user, roles that have login privileges are defined as “users”.

To drop a Postgres ROLE, just use the DROP command syntax in the same way that you would to delete a user:

Conclusion

Deleting a user in PostgreSQL is a permanent operation, so it’s important for database administrators to know how to perform the task correctly. In this article, we walked you through the process of dropping a user in PostgreSQL, and we provided examples of the Postgres DROP USER command to illustrate the process. With our examples to guide you, you’ll be ready to drop users from your own PostgreSQL database.