PostgreSQL DROP ROLE Statement

Summary: In this tutorial, you’ll learn how to remove a role using the PostgreSQL DROP ROLE statement.

PostgreSQL DROP ROLE Statement Overview #

The DROP ROLE statement allows you to remove a role. Here’s the syntax of the DROP ROLE statement:

DROP ROLE [IF EXISTS] role_name;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax:

• First, specify the role name you want to remove.
• Second, use the IF EXISTS option to remove the role only if it exists.

If you want to drop multiple roles at once, you can list them out in the DROP ROLE statement:

DROP ROLE role_name1, role_name2;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Only a superuser role can drop other superuser roles.

A role with CREATEROLE privilege and ADMIN OPTION can drop a non-superuser role.

If a role owns any objects or has privileges in any database, you cannot drop it.

To drop it, you need to:

• Transfer the ownership to other roles using REASSIGN OWNED statement.
• Remove all privileges associated with the role using DROP OWNED statement.

PostgreSQL DROP ROLE Statement Example #

The following example demonstrates how to properly drop a role (samurai) by reassigning ownership of objects and revoking privileges.

Session 1

Step 1. Connect to your PostgreSQL server:

psql -U postgresCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Step 2. Create a new database called devdb:

CREATE DATABASE devdb;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Step 3. Switch the current database to devdb:

\c devdbCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Step 4. Create a new role called samurai that we will remove later.

CREATE ROLE samurai 
LOGIN PASSWORD 'securepassword';Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Step 5. Grant database connection on the devdb and CREATE privilege on the public schema to the samurai role:

GRANT CONNECT ON DATABASE devdb TO samurai;
GRANT USAGE, CREATE ON SCHEMA public TO samurai;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Session 2

Step 6. Open a new session and connect to the devdb using the samurai role:

psql -U samurai -d devdbCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Step 7. Use the samurai role to create a new table:

CREATE TABLE projects (
    id INT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    start_date DATE,
    end_date DATE
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Session 1

Step 8. Attempt to drop the samurai role:

DROP ROLE samurai;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

PostgreSQL issued the following error:

ERROR: role "samurai" cannot be dropped because some objects depend on it
DETAIL: privileges for schema public
privileges for database devdb
owner of table projectsCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Step 9. Trasnfer ownership to postgres role and revoke privileges from the role samurai:

REASSIGN OWNED BY samurai TO postgres;
DROP OWNED BY samurai;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Step 10. Terminate active session of the role samurai:

SELECT
  pg_terminate_backend(pg_stat_activity.pid)
FROM
  pg_stat_activity
WHERE
  pg_stat_activity.usename = 'samurai';Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Step 10. Rerun the DROP ROLE statement:

DROP ROLE samurai;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The statement drops the role samurai successfully.

Summary #

• Use the DROP ROLE statement to drop a role.
• Use the REASSIGN OWNED BY statement to transfer ownership from one role to another.
• Use the DROP OWNED BY statement to revoke privileges owned by a role.