PostgreSQL DROP ROLE (original) (raw)

Last Updated : 15 Jul, 2025

In **PostgreSQL, the **DROP ROLE statement is used to remove a role from the database. Let us look at the process of dropping a role, including important considerations and steps to ensure smooth execution.

**Syntax

The basic syntax of the **DROP ROLE Statement in **PostgreSQL is:

**DROP ROLE [IF **EXISTS] target_role;

Parameters

Let's analyze the above syntax:

**target_role: The name of the role to be deleted.
**IF EXISTS: An optional clause that prevents an error if the role does not exist, issuing a notice instead.

Important Conisderations

It is important to note that to remove a **superuser role, one needs to be a **superuser.
To remove **non-superuser roles, you will need the privilege to create a role using the **CREATE **ROLE statement.

If we accidentally try to remove a role that has been referenced in any database, PostgreSQL raises an error. In this case, one has to take two steps:

**Step 1: You either need to remove the database objects owned by that particular role using the **DROP OWNED **statement or reassign the ownership of the database objects to another role using the **REASSIGN OWNED statement.
**Step 2: You will also be needing to revoke all other permissions granted to the role.

In other words, follow the below statements exactly as shown to drop a role:

**REASSIGN OWNED BY target_role **TO another_role; **DROP **OWNED BY target_role; **DROP **ROLE target_role;

**PostgreSQL DROP ROLE Example

Let us take a look at an example of the DROP ROLE Statement in PostgreSQL to better understand the concept.

Here, we will create a new role called '**geeksforgeeks' and use this role to create a table named '**employees'. Then, we will show the step by step guide on how to remove the role '**geeksforgeeks' from the PostgreSQL database server.

Create and Configure the Role

1. First, log in to PostgreSQL using the Postgres role:

psql -U postgres

2. Second, create a new role called '**geeksforgeeks' using the below statement:

**CREATE ROLE geeksforgeeks **WITH LOGIN PASSWORD 'geeks12345';

3. Third, grant _createdb privilege to geeksforgeeks using the below statements:

**ALTER ROLE geeksforgeeks CREATEDB;

4. Fourth, create a new database called _sales:

**CREATE DATABASE sales;

Create a Table Using the Role

Now, login to the PostgreSQL database server using the geeksforgeeks role as shown below:

psql -U geeksforgeeks -W sales

Add a new table to the _Sales database:

**CREATE TABLE employee( employee_id INT GENERATED ALWAYS **AS IDENTITY, employee_name VARCHAR(150) **NOT NULL, **PRIMARY KEY(employee_id) );

Now show the list of tables in the sales database using the below command:

\du

This will show you the following result:

PostgreSQL DROP ROLE Example

Now log in as Postgres and attempt to drop the geeksforgeeks role:

**DROP ROLE geeksforgeeks;

**Output:

PostgreSQL DROP ROLE Example

Important Points About PostgreSQL DROP ROLE Statement

Use the IF EXISTS clause to avoid errors when running scripts that may try to drop non-existent roles.

Only a superuser can drop another superuser role. To drop a non-superuser role, you need the **CREATE ROLE**privilege.

Use the **REASSIGN OWNED**statement to transfer ownership of database objects to another role.

Before dropping a role, ensure that all permissions granted to the role are revoked.