PostgreSQL - DROP ROLE

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:

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

DROP ROLE geeksforgeeks;

Output:

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.