DROP ROLE in PostgreSQL | Delete Users & Safely Remove Roles

Learn how to use DROP ROLE command to remove users and roles in PostgreSQL. Includes ownership rules, REASSIGN OWNED BY syntax, and safe role deletion methods.

In PostgreSQL, DROP ROLE is used to remove (delete) a role/user from the system.

🔹 What is DROP ROLE?

👉 It is a command used to:

• Delete a role (user or group)
• Remove its access to the database system

🔹 Why Do We Use DROP ROLE?

✅ 1. Remove Unused Users

• When a user leaves (e.g., student graduates, employee resigns)

✅ 2. Improve Security

• Remove unnecessary accounts to prevent misuse

✅ 3. Clean Database System

• Keep roles organized and manageable

🔹 Basic Syntax

DROP ROLE role_name;

🔹 Simple Example (Beginner)

Step 1: Create a Role

CREATE ROLE test_user WITH LOGIN PASSWORD '123';

Step 2: Drop the Role

DROP ROLE test_user;

✔️ The role is now removed from PostgreSQL

⚠️ Important Rules (Very Important)

You CANNOT drop a role if it:

❌ 1. Owns a database

Example:

ALTER DATABASE dvdrental OWNER TO test_user;

Then:

DROP ROLE test_user;  -- ❌ ERROR

❌ 2. Owns tables or objects

❌ 3. Has active connections

🔹 How to Safely Drop a Role

Step 1: Reassign ownership

REASSIGN OWNED BY test_user TO postgres;

Step 2: Remove permissions

DROP OWNED BY test_user;

Step 3: Drop role

DROP ROLE test_user;

🔹 Example with dvdrental

-- Create role
CREATE ROLE trainee WITH LOGIN PASSWORD '123';

-- Give access
GRANT CONNECT ON DATABASE dvdrental TO trainee;

-- Remove role safely
REASSIGN OWNED BY trainee TO postgres;
DROP OWNED BY trainee;
DROP ROLE trainee;

🧠 Key Concept

👉 A role cannot be deleted if it is still linked to database objects

🧑‍🏫 Teaching Tip

Ask students: 👉 “Why did DROP ROLE fail?”

This helps them understand:

• Ownership
• Dependencies
• Security concepts

🚀 Bonus (Check Roles Before Deleting)

SELECT rolname FROM pg_roles;

🧪 Lab: Understanding DROP ROLE in PostgreSQL

🎯 Learning Objectives

Students will learn:

• How to safely delete roles
• Why DROP ROLE may fail
• How to resolve dependency errors
• Difference between ownership and permissions

🔹 Scenario

You are managing a system where temporary users (trainees, interns, students) must be removed after use.

🔹 Step 1: Create a Test Role

CREATE ROLE trainee WITH LOGIN PASSWORD '123';

Verify:

SELECT rolname FROM pg_roles;

🔹 Step 2: Drop the Role (Basic Case)

```sql id=”mqy8mx” DROP ROLE trainee;

✔️ Works because:

* No ownership
* No permissions
* No dependencies

---

### 🔹 Step 3: Create Role Again

```sql
CREATE ROLE trainee WITH LOGIN PASSWORD '123';

🔹 Step 4: Assign Permissions (dvdrental)

GRANT CONNECT ON DATABASE dvdrental TO trainee;

Try dropping:

```sql id=”yxczgi” DROP ROLE trainee;

👉 ❓ Ask students:

* Does it work or fail?

✔️ Usually it **fails** due to dependencies

---

# 🔹 Step 5: Fix the Error

```sql
DROP OWNED BY trainee;
DROP ROLE trainee;

🔥 Critical Learning Section

🔹 Step 6: Ownership Problem (Very Important)

Create role again:

CREATE ROLE trainee WITH LOGIN PASSWORD '123';

Assign ownership:

ALTER DATABASE dvdrental OWNER TO trainee;

Now try:

DROP ROLE trainee;

❌ This will FAIL

🧠 Why It Fails?

👉 Because:

• Role owns the database
• PostgreSQL prevents deletion to avoid data loss

🔹 Step 7: Resolve Ownership Issue

REASSIGN OWNED BY trainee TO postgres;

Then:

DROP OWNED BY trainee;

“Remove everything that this role (trainee) owns in the current database.”

This includes:

• Tables
• Views
• Sequences
• Functions
• Permissions (grants)

Finally:

DROP ROLE trainee;

✔️ Now it works

🔍 Step 8: Investigate Ownership

SELECT datname, pg_catalog.pg_get_userbyid(datdba) AS owner
FROM pg_database;

🧪 Student Tasks

✅ Task 1: Basic Deletion

• Create role temp_user
• Drop it immediately

✅ Task 2: Permission Dependency

• Create role intern
• Grant SELECT on a table in dvdrental
• Try dropping → observe error
• Fix and drop

✅ Task 3: Ownership Dependency

• Create role manager
• Assign database ownership
• Try dropping → observe error
• Fix using REASSIGN OWNED

✅ Task 4: Table Ownership Test

ALTER TABLE film OWNER TO trainee;

Try:

DROP ROLE trainee;

👉 Fix it properly

🧠 Key Concepts Summary

⚠️ Common Errors (Teach Students)