How To Delete User In PostgreSQL

In this article, I’m going to walk you through the professional methods for removing a user in PostgreSQL, ensuring you maintain data integrity and avoid orphaned objects.

How To Delete User In PostgreSQL

Before we dive into the commands, let’s clear up a common point of confusion for those transitioning from other SQL dialects. In PostgreSQL, a User is technically just a Role that has the LOGIN attribute.

Key Role Attributes

• SUPERUSER: Can bypass all permission checks.
• CREATEDB: Can create new databases.
• CREATEROLE: Can create or delete other roles.
• LOGIN: Allows the role to be used as a connection identity (making it a “user”).

The Pre-Deletion: Checking Dependencies

The biggest mistake people make is trying to drop a user without checking what that user owns. In PostgreSQL, if a user owns a table, a view, or a sequence, the system will protect those objects by preventing the user from being deleted.

Step 1: Identify Owned Objects

Before you run any “DROP” commands, you need to see the extent of the user’s footprint. I usually run a query against the system catalogs to find all objects owned by the target user.

SQL

SELECT nspname AS schema_name, relname AS object_name, relkind AS type
FROM pg_class c
JOIN pg_roles r ON r.oid = c.relowner
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE r.rolname = 'target_user_name';

Step 2: Check Granted Privileges

If the user has been granted specific permissions on objects owned by others, those permissions need to be revoked to keep the security catalog clean.

Method 1: The “Drop Owned” Strategy (The Cleanest Way)

PostgreSQL provides a very powerful command specifically designed for this purpose: DROP OWNED BY. This is my preferred method when offboarding a contractor or a service account that is no longer needed.

Reassigning Ownership

Usually, you don’t want the data to disappear; you just want the user gone. In this case, you must reassign the objects to a new owner (perhaps a “Service_Admin” account) before dropping the old one.

1. Reassign Everything:SQLREASSIGN OWNED BY old_user TO admin_user;
2. Drop Remaining Dependencies:This removes any privileges (Grants) the user had on objects they didn’t own.SQLDROP OWNED BY old_user;
3. The Final Act:SQLDROP ROLE old_user;

Method 2: Manual Deletion via SQL Shell (psql)

If you prefer a more granular approach, or if you’re working in a strict environment where you need to log every individual change, you can manually strip the user of their “powers.”

The Step-by-Step Manual Workflow

• Revoke Membership: If the user belongs to any groups (e.g., a “Developers” role), remove them first.
  • REVOKE developers FROM user_name;
• Revoke Schema Usage: Ensure they can’t access schemas like public.
  • REVOKE ALL ON SCHEMA public FROM user_name;
• Drop the User:
  • DROP USER user_name;

Command	Action	Risk Level
DROP ROLE	Deletes the role entity.	Low (if no dependencies)
REASSIGN OWNED	Transfers ownership to another role.	Medium (affects object metadata)
DROP OWNED	Deletes objects and revokes privileges.	High (can delete data!)

Handling Common Errors: “Role Cannot Be Dropped”

1. Database-Level Permissions

Even if a user owns no tables, they might have CONNECT or CREATE permissions on a specific database. You must revoke these at the database level.

SQL

REVOKE ALL ON DATABASE production_db FROM user_name;

2. Shared Dependencies (Common in Clusters)

If you are using a managed service like AWS RDS or Azure Database for PostgreSQL, sometimes system-level roles have dependencies you can’t see easily. Always check pg_shdepend to find global dependencies that span multiple databases within the cluster.

3. Active Sessions

You cannot delete a user who is currently logged in. If an application server in a Virginia data center still has an open pool connection, the DROP will hang or fail.

To terminate active sessions:

SQL

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE usename = 'target_user_name';

Best Practices for User Lifecycle Management

• Never Use Individual Owners: Never let an individual person own a production table. Always assign ownership to a Group Role (e.g., app_owner). This way, when a developer leaves the company, you just remove them from the group.
• The “Disable First” Rule: Instead of immediately dropping a user, I always revoke their LOGIN privilege first. This “soft-deletes” them. If a critical cron job breaks, I can simply re-enable login without having to recreate the entire user and their permissions.
  • ALTER ROLE user_name NOLOGIN;
• Audit Regularly: Use automated scripts to find roles with no active logins in the last 90 days. In the USA, many compliance frameworks (like SOC2) require this kind of access review.

Frequently Asked Questions

What is the difference between DROP USER and DROP ROLE?

In PostgreSQL, DROP USER is actually just an alias for DROP ROLE. They perform the exact same action. The term “User” is used primarily for convenience and compatibility with other SQL standards.

Will DROP OWNED BY delete my tables?

Yes. If the user owns a table and you run DROP OWNED BY, PostgreSQL will drop that table. Always use REASSIGN OWNED BY first if you want to keep the data.

Can I recover a deleted user?

No. Once a role is dropped, its OID (Object Identifier) is gone. Even if you create a new user with the same name, it will have a different OID and will not automatically inherit the old user’s permissions or ownership.

Summary Checklist for Deleting a PostgreSQL User

To ensure a smooth transition, follow this checklist:

1. Verify identity: Confirm you have the correct username.
2. Terminate Connections: Kill any active PIDs for that user.
3. Reassign Data: Transfer ownership of tables/sequences to a permanent admin role.
4. Clear Privileges: Run DROP OWNED BY to clean up remaining grants.
5. Soft-Delete (Optional): Set to NOLOGIN for a 24-hour “cooling off” period.
6. Final Drop: Execute DROP ROLE.

Wrapping Up

Managing users in PostgreSQL is an art of managing dependencies. By utilizing REASSIGN OWNED and DROP OWNED. You can navigate the complexities of database ownership without breaking your application or losing critical data.

You may also like the following articles:

• How to create user in PostgreSQL
• PostgreSQL list users
• How to set user password in PostgreSQL
• How To Check PostgreSQL Version
• What Is Postgresql Used For