Introduction
Administering a database requires removing user accounts, especially if a user account is no longer needed. Deleting an unnecessary Postgres users is an essential database security practice. This action also removes a potentially unnoticeable access point for hackers.
This tutorial explains how to drop a Postgres user in multiple ways.
Prerequisites
- Access to the command line or terminal with sudo privileges.
- Postgres installed, preferably the latest version.
- Postgres users to drop. Follow our tutorial to create Postgres users for testing purposes.
Delete a Postgres User
A user in PostgreSQL has login privileges by default. There are two methods to remove a user, and both require access to the CREATEROLE
rights. To drop a superuser, the SUPERUSER
privilege is mandatory.
The postgres user created during installation has all the necessary privileges by default. The examples below use the postgres user to connect to PostgreSQL.
Method 1: Delete a Postgres User With dropuser Utility
Postgres offers a client utility for removing a user without connecting to the PSQL command-line interface.
To delete a user in Postgres with dropuser
, run the following command in the terminal:
sudo -u postgres dropuser <user> -e
Note: The -e
tag echoes the server’s response, showing whether the command completes successfully. Without the tag, if the command is successful, the terminal does not output a message.
Alternatively, split the command into two steps:
1. Switch to the postgres user (or another user with the correct privileges):
sudo su - postgres
2. Run the dropuser command:
dropuser <name> -e
In both cases, Postgres removes the user.
Method 2: Delete a Postgres User With DROP USER Statement
Another way to delete a user from a Postgres database is using a PSQL statement.
To delete a user, follow the steps below:
1. Connect to the PSQL client via terminal with the following command:
sudo -u postgres psql
The terminal changes to the PSQL console (postgres=#
).
2. List all users and roles with:
du
Locate the user for removal and use the name in the following step.
3. Run the following query to delete a user:
DROP USER <name>;
Alternatively, to check if a user exists before dropping, enter:
DROP USER IF EXISTS <name>;
The client notifies if the role is nonexistent.
4. Display the user list again to confirm the user is gone:
du
The user is no longer on the list of roles. This output indicates a successful deletion.
Delete Multiple Postgres Users
The PSQL client allows deleting more than one Postgres user at once. The instructions below explain how to connect to the PostgreSQL client and delete multiple users.
1. Connect to the PSQL client with:
sudo -u postgres psql
2. List all users with:
du
Locate the users for deletion and use the names in the following step.
3. Delete multiple Postgres users by separating individual users with a comma:
DROP USER [IF EXISTS] <name>, <name>, ... <name>;
The command removes multiple roles at once. Add IF EXISTS
to skip users that are not available.
4. Recheck the user list to confirm all the roles are deleted:
du
The removed users are no longer on the list.
Delete a Postgres User with Dependencies
Attempting to remove a Postgres user with dependencies fails and shows an error.
To safely remove the user, do the following:
1. Assign the object ownership from the error detail to another user. For example, to transfer the objects owned by myuser to postgres, run:
REASSIGN OWNED BY myuser TO postgres;
The query changes the object ownership to the postgres user.
2. Remove the database object connections to the user with:
DROP OWNED BY myuser;
This step also removes any privileges the user has over the object.
3. At the moment, the user no longer has any dependencies. To drop the user, run:
DROP USER myuser;
Since no dependencies exist, the removal is successful.
Delete a Postgres Role
To delete a Postgres role, run the following command in the PSQL client:
DROP ROLE [IF EXISTS] <name>;
The DROP USER
statement is an alias for DROP ROLE
. The Postgres users are roles with LOGIN
permissions. Therefore, both DROP USER
and DROP ROLE
are interchangeable and work for both users and roles.
Conclusion
After following the steps from this guide, you know how to delete a Postgres user and multiple users securely.
Next, learn how to drop a database in PostgreSQL.
Đăng ký liền tay Nhận Ngay Bài Mới
Subscribe ngay
Cám ơn bạn đã đăng ký !
Lỗi đăng ký !
Add Comment