How to Delete a Postgres User (dropuser & DROP USER)

When managing PostgreSQL, deleting users isn’t just routine—it’s a crucial security step. Removing outdated or unnecessary accounts helps close potential access points, reducing the risk of unauthorized access from both insider threats and external attackers. By ensuring only the right users have permissions, you maintain a more secure and controlled PostgreSQL environment.

Prerequisites

There are a couple of requirements you must meet so you can delete users:

• You must have the latest Postgres version installed to access the latest security patches and features. It’s also essential for compatibility purposes. 
• Your user account must have CREATEROLE privileges with the ADMIN OPTION enabled on the target role. Alternatively, your account should be a superuser. 

NOTE: Before dropping a user, you must ensure there aren’t any active sessions. If there are, you can stop them by using the following command:

SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE usename = 'username'

Method 1: dropuser Utility

This method lets you remove a user without having to connect to the PSQL command-line interface (CLI). It lets you delete users directly from the shell. The dropuser utility is a Bash script wrapper around the DROP USER command via PSQL. This essentially means the script must find PSQL. 

Using the dropuser utility is as easy as running the following command in the PSQL client terminal:

dropuser username

Say you want to delete user "sean" from the database. The syntax will look like this:

dropuser sean

When using this method, you can leverage the option -e. It tells the server to show a message or response as to whether the command has successfully deleted the user. It also comes in handy for debugging when errors occur because you know what caused them. 

For example, if you run the command dropuser sean -e and it deletes the user, you will receive a response that looks like this:

DROP ROLE

Method 2: DROP USER SQL Command

Unlike the dropuser utility, DROP USER must be executed within a Postgres database client. 

Step 1: Open the psql CLI and log in to the database client:

sudo -u postgres psql

The terminal will change to postgress=#

Step 2: This step is optional because it just lists all the existing users in the database. To do so, run the command as shown below:

\du

Step 3: Once you spot the user you want to delete, use the DROP USER command as shown below:

DROP USER username 

So, if the username of the user you want to delete is “kim,” your command should look like this:

DROP USER kim

You can prevent receiving an error if the user doesn’t exist by adding the IF EXISTS option in the command as follows:

DROP USER IF EXISTS kim

Step 4: If you need to verify that you’ve successfully deleted the user, you can run the \du command again. (This is again optional.)

How to Handle Dependencies for Smooth Deletion

If you don’t find and resolve dependencies before deleting a Postgres user, you’ll receive an error message. Here are various ways to deal with this: 

List the Databases the User Owns

You can’t delete a user from a Postgres database if they own it and any other. To check if they do, use this syntax: 

SELECT datname   
 FROM pg_database  
 WHERE datdba = (SELECT oid FROM pg_roles WHERE rolname = 'username')

If the user owns a database, you can resolve this issue by either:

• Dropping the database 

DROP DATABASE database_name

• Changing ownership to another user: 

ALTER DATABASE database_name OWNER TO new_owner

Check the Objects the User Owns

To list any objects the user may own, use this command: 

SELECT 
    n.nspname AS schema_name, 
    c.relname AS object_name, 
    c.relkind AS object_type  
 FROM 
    pg_class c 
 JOIN 
    pg_namespace n ON n.oid = c.relnamespace 
 WHERE 
    c.relowner = (SELECT oid FROM pg_roles WHERE rolname = 'username')

You can solve the issue of owned dependencies by dropping them using the DROP DOWN command or reassigning them using the REASSIGN OWNED BY TO command. It’s worth noting, though, that you should only drop the dependencies if you no longer need them. 

Here is how the commands should look:

DROP OWNED BY username 
 REASSIGN OWNED BY username TO new_owner

Check if the User Has Granted Privileges

Attempting to delete a user with privileges can fail and cause errors. Additionally, other users may experience access and operation errors if they depend on the privileges connected to the deleted user. You can use the following command to check:

SELECT grantee, privilege_type, table_schema, table_name 
 FROM information_schema.role_table_grants 
 WHERE grantee = 'username'

You can revoke the user’s privileges (if any) by using this command:

REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM username

Can I Delete Multiple Users At the Same Time?

Yes, it’s possible to quickly delete several users at once instead of one by one. You can do this by using the DROP USER command. For instance, if you need to delete users sean, mary, carlos, ray, and kim, you would use the syntax as shown below:

DROP USER IF EXISTS sean, mary, carlos, ray, kim

What Are Some Errors I Should Expect to Encounter?

As already established, you may experience errors when trying to delete users in a Postgres database. These include:

“role "username" cannot be dropped because some objects depend on it”

You may receive this error if the user you’re trying to delete owns database objects. To resolve it, you can either drop the objects (if you no longer need them) or change the objects’ ownership. 

“role "username" does not exist” 

This error can occur for two different reasons. Either you misspelled the username or you had already deleted it. Adding the IF EXISTS option to the delete command can help prevent this error. 

“permission denied to drop role”

If you don’t have sufficient privileges, you may not have permission to drop users. Using a superuser account or having a superuser account give you CREATOROLE privileges can solve this problem. 

Best Practices

While we’ve already covered some things you can do to prevent errors, there’s more you can do to guarantee a smooth deletion process. Best practices you can adopt include:

• Confirm Deletion: There are multiple things you can do to verify that you’ve successfully deleted the user(s) you want. The /du command lists all the users in the database, and from there, you can check that the user isn’t included. Alternatively, you can include the -e option in the delete command to return a message indicating the action was successful. 
• Backup First: Before you start deleting users and objects, always back up the database. A backup lets you retrieve data you may have accidentally deleted.

Delete Postgres User: How StrongDM Helps

While the commands in this guide will get the job done, manually deleting Postgres users can be tedious and time-consuming. StrongDM simplifies and automates access management, eliminating the need for manual intervention.

Here’s how StrongDM streamlines the process:

• Just-In-Time (JIT) Access: No more manually adding and removing users. StrongDM grants access only when it’s needed, reducing the risk of lingering credentials.
• Centralized Access Control: Get complete visibility into your Postgres databases. See who has access, track activity, and revoke permissions instantly—without wrestling with SQL commands.

Ready to simplify database access management? Book a demo today.