PostgreSQL Tutorial: DROP ROLE

August 7, 2023

Summary: In this tutorial, you will learn how to use the PostgreSQL DROP ROLE statement to remove a role.

Table of Contents

Introduction to PostgreSQL DROP ROLE statement

To remove a specified role, you use the DROP ROLE statement:

DROP ROLE [IF EXISTS] target_role;

In this syntax:

  • Specify the name of the role that you want to remove after the DROP ROLE keywords.
  • Use the IF EXISTS option if you want PostgreSQL to issue a notice instead of an error when you remove a role that does not exist.

To remove a superuser role, you need to be a superuser. To drop non-superuser roles, you need to have the CREATEROLE privilege.

When you remove a role referenced in any database, PostgreSQL will raise an error. In this case, you have to take two steps:

  • First, either remove the database objects owned by the role using the DROP OWNED statement or reassign the ownership of the database objects to another role using the REASSIGN OWNED statement.
  • Second, revoke any permissions granted to the role.

The REASSIGN OWNED statement reassigns the ownership of all dependent objects of a target role to another role. Because the REASSIGN OWNED statement can only access objects in the current database, you need to execute this statement in each database that contains objects owned by the target role.

After transferring the ownerships of objects to another role, you need to drop any remaining objects owned by the target role by executing the DROP OWNED statement in each database that contains objects owned by the target role.

In other words, you should execute the following statements in sequence to drop a role:

-- execute these statements in the database that contains
-- the object owned by the target role
REASSIGN OWNED BY target_role TO another_role;
DROP OWNED BY target_role;

-- drop the role
DROP ROLE target_role;

Let’s see the following example.

PostgreSQL DROP ROLE example

In this example:

  • First, we will create a new role called alice and use this role to create a table named customers.
  • Then, we will show you step by step how to remove the role alice from the PostgreSQL database server.

We’ll use the psql tool. However, you can use any client tool of your choice.

Step 1. Setting a new role and database

First, login to PostgreSQL using the postgres role:

psql -U postgres

Second, create a new role called alice:

postgres=# create role alice with login password 'Abcd1234';

Third, grant createdb privilege to alice:

postgres=# alter role alice createdb;

Fourth, create a new database called sales:

postgres=# create database sales;

Exit the current session:

postgres=# \q

Step 2. Using the new role to create database objects

First, login to the PostgreSQL database server using the alice role:

psql -U alice -W sales

Second, create a new table in the sales database:

create table customers(
    customer_id int generated always as identity,
    customer_name varchar(150) not null,
    primary key(customer_id)
);

Third, show the table list in the sales database:

sales=> \dt
        List of relations
Schema |   Name    | Type  | Owner
--------+-----------+-------+-------
public | customers | table | alice
(1 row)

Finally, quit the current session:

postgres=# \q

Step 3. Removing the role alice

First, login to the PostgreSQL database server using the postgres role:

psql -U postgres

Second, attempt to drop the role alice:

postgres=# drop role alice;

PostgreSQL issued the following error:

ERROR:  role "alice" cannot be dropped because some objects depend on it
DETAIL:  2 objects in database sales

The role alice cannot be dropped because it has dependent objects.

Third, switch to the sales database:

postgres=# \c sales

Fourth, reassign owned objects of alice to postgres:

sales=# reassign owned by alice to postgres;

Fifth, drop owned objects by alice:

sales=# drop owned by alice;

Sixth, drop the role alice:

sales=# drop role alice;

Seventh, list the current roles:

sales=#\du

You will see that the role alice has been removed.

Finally, quit the current session:

sales=#\q

Summary

  • Use the PostgreSQL DROP ROLE statement to remove a specified role.
  • If a role has dependent objects, use the REASSIGN OWNED and DROP OWNED statements in sequence to remove dependent objects of the role before executing the DROP ROLE statement.

Search

Categories

Tags