PostgreSQL – Delete or Drop User

PostgreSQL – Delete or Drop User

In current PostgreSQL versions, database users are implemented as roles with the LOGIN attribute. To delete a PostgreSQL user account, use the DROP ROLE SQL command. The dropuser command-line utility performs the same operation from a terminal.

Important: Older PostgreSQL material may show DROP USER. The original examples are retained below for reference, but for modern PostgreSQL installations, prefer DROP ROLE.

PostgreSQL DROP ROLE syntax for deleting a user

DROP ROLE [ IF EXISTS ] role_name [, ...];

Replace role_name with the PostgreSQL user or role you want to remove. PostgreSQL allows multiple role names in one statement.

• IF EXISTS prevents an error when the specified role does not exist. PostgreSQL issues a notice instead.
• A terminating semicolon is required when running the statement in psql or another SQL client.
• The role must not own database objects or have dependencies that prevent its removal.

Check the PostgreSQL user before deleting it

You can list roles in psql before running the delete command.

\du

You can also query the system catalog and filter for a specific role.

SELECT rolname, rolcanlogin
FROM pg_roles
WHERE rolname = 'lini';

Delete one PostgreSQL user with DROP ROLE

The following statement removes the role named lini when it exists.

DROP ROLE IF EXISTS lini;

When the command succeeds, PostgreSQL removes the role from the database cluster. Roles are cluster-wide, so the account is no longer available in any database managed by that PostgreSQL server.

Delete multiple PostgreSQL users in one statement

Separate role names with commas to remove several PostgreSQL users at once.

DROP ROLE IF EXISTS lini, kiku, jobin;

PostgreSQL checks each named role. With IF EXISTS, a missing role produces a notice rather than stopping the statement with an undefined-role error.

Resolve owned objects before dropping a PostgreSQL user

PostgreSQL will not drop a role that still owns database objects or has certain dependent privileges. A common error states that the role cannot be dropped because other objects depend on it.

When the objects should be preserved, reassign their ownership to another role and then remove privileges belonging to the old role. Run these commands in every database where the role owns objects or has grants.

REASSIGN OWNED BY lini TO postgres;
DROP OWNED BY lini;
DROP ROLE lini;

REASSIGN OWNED transfers ownership of objects in the current database. DROP OWNED then removes privileges granted to the old role and drops any remaining objects it owns in that database. Review the affected objects before running DROP OWNED, especially in production systems.

If the role’s objects are no longer needed, you may use DROP OWNED BY role_name without reassigning them. This can delete tables, schemas, and other objects owned by the role, so it should not be used without checking the consequences.

Required privileges for DROP ROLE

A PostgreSQL superuser can remove any role except the current session user. A non-superuser generally needs the CREATEROLE privilege and suitable administrative authority over the target role. You also cannot drop the role you are currently using; connect as another administrator first.

Delete a PostgreSQL user from the command line

The dropuser utility is a wrapper around DROP ROLE. Run it from the operating-system terminal, not from inside the psql SQL prompt.

dropuser lini

Use interactive mode to request confirmation before deletion.

dropuser --interactive lini

Connection options such as --host, --port, and --username can be supplied when the PostgreSQL server is not reached through the default local connection.

Legacy DROP USER syntax

The following syntax and examples appeared in older PostgreSQL documentation and are retained here because they are part of the original tutorial.

 DROP USER [ IF EXISTS ] username [, ...]

Here, username is the name of the user to remove. The optional IF EXISTS clause checks whether the user is present before attempting the operation. Multiple names can be separated with commas.

Legacy example: delete one PostgreSQL user

Consider a PostgreSQL server where two users are listed as shown below.

In this legacy example, the user lini is removed with the following command.

 DROP USER IF EXISTS lini;

The screenshot shows the user being deleted from PostgreSQL.

Legacy example: delete multiple PostgreSQL users

Consider a PostgreSQL server where four users are listed as shown below.

In this legacy example, the users lini, kiku, and jobin are removed in one statement.

 DROP USER IF EXISTS lini, kiku, jobin;

The screenshot shows the selected users being removed from PostgreSQL.

PostgreSQL user deletion errors and fixes

Error or issue	Likely cause	Action
role does not exist	The role name is incorrect or has already been removed.	Check \du or use DROP ROLE IF EXISTS.
role cannot be dropped because some objects depend on it	The role owns objects or has dependent privileges.	Use REASSIGN OWNED and DROP OWNED in the relevant databases.
permission denied to drop role	The current account lacks sufficient role-management privileges.	Connect as a superuser or an administrator authorized to manage the target role.
current user cannot be dropped	You are connected as the role being removed.	Reconnect with another administrator account and retry.

PostgreSQL DROP ROLE FAQs

Does DROP ROLE delete the databases owned by the user?

No. PostgreSQL normally refuses to drop a role that owns a database or other objects. Transfer ownership or remove the owned objects before dropping the role.

What is the difference between DROP ROLE and DROP OWNED?

DROP ROLE removes the role itself. DROP OWNED removes objects owned by the role in the current database and revokes privileges granted to that role. It does not remove the role account.

Can I delete a PostgreSQL user who is connected?

An active session does not automatically transfer or remove the role’s dependencies. Disconnect the user’s sessions when necessary, resolve owned objects and privileges, and then run DROP ROLE from another administrator account.

Is dropuser different from DROP ROLE?

No. The dropuser client utility sends the equivalent role-removal command to PostgreSQL. Use DROP ROLE in SQL clients and dropuser in a terminal.

PostgreSQL user removal checklist

• Confirm the exact role name with \du or pg_roles.
• Check whether the role owns databases, schemas, tables, sequences, or other objects.
• Decide whether owned objects should be reassigned or deleted.
• Run ownership and privilege cleanup in every affected database.
• Connect as a different authorized administrator before dropping the role.
• Verify that the role no longer appears in \du.

Summary of deleting PostgreSQL users

Use DROP ROLE to delete one or more PostgreSQL users in current PostgreSQL versions. Add IF EXISTS when a missing role should not cause an error. If PostgreSQL reports dependencies, reassign or remove the role’s owned objects and privileges before retrying. The terminal-based dropuser utility provides the same role-removal functionality. See the official PostgreSQL DROP ROLE documentation and continue with this PostgreSQL Tutorial.