Dropping a role is a crucial feature in PostgreSQL to remove all the privileges in the database. It is required when the user does not need a specific role in the future. For this purpose, the “DROP ROLE” statement is utilized with the role name to delete a particular role in PostgreSQL.

This guide comprises various steps to explain the usage of the “DROP ROLE” statement in PostgreSQL. So, let’s begin.

Prerequisite: Existing Roles in PostgreSQL

PostgreSQL provides an interesting statement named “\du” that displays all the existing roles with attributes in the database:

\du;

The above snippet shows all the available roles within the “postgres” database.

Drop Existing Role in PostgreSQL

In PostgreSQL, the “DROP ROLE” statement is utilized to drop an existing role. For this purpose, specify the DROP ROLE statement followed by the role's name:

DROP ROLE mentor_role;

On successful execution of the above statement, the “mentor_role” will be dropped from the database:

A message “DROP ROLE” is displayed as an output which confirms that the mentor_role has been dropped from the database.

ERROR: Specific Role Does Not Exist in PostgreSQL

The "DROP ROLE" statement in PostgreSQL displays an error if a role doesn’t exist in the database:

DROP ROLE simple_role;

In the above snippet, we utilized the DROP ROLE statement to drop a role named “simple_role”:

The output verifies that the specified role doesn’t exist in the database.

Drop Dependency of Role in PostgreSQL

By using the "DROP OWNED" statement, a dependency role can be dropped in a Postgres database.

Before dropping any dependencies, the user can verify the list of roles in the existing database via the “\du” statement:

\du;

We will try to drop the “hr_role” using the below statement:

DROP ROLE hr_role;

An error occurred stating that the selected role can’t be dropped because some other objects depend on the selected role. To avoid such an error, the “DROP OWNED” statement can be used:

DROP OWNED by hr_role;

Now the user can execute the “DROP ROLE” statement to drop any specific role:

DROP ROLE hr_role;

The output shows that the hr_role has been dropped successfully. Run the below-given command to verify if the selected role has been dropped or not:

\du ;

The “\du” statement displays the number of existing roles in the database:

Output authenticates that the “hr_role” has been successfully dropped from the targeted database.

Conclusion

In PostgreSQL, the “DROP ROLE” statement is used to drop an existing role with its attributes. In the case of dependent objects, the DROP OWNED statement is used to drop all the objects. In this write-up, the "DROP ROLE" and "DROP OWNED" statements are demonstrated using practical examples.