Kiến Thức Linux

How to Delete a Postgres User (Drop User)

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.

how to delete a postgres user

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
postgres dropuser output

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
postgres user drop user output

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
sudo -u postgres psql client connection

The terminal changes to the PSQL console (postgres=#).

2. List all users and roles with:

du
display user table postgres

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>;
drop user psql output

Alternatively, to check if a user exists before dropping, enter:

DROP USER IF EXISTS <name>;
drop user if exists psql output

The client notifies if the role is nonexistent.

4. Display the user list again to confirm the user is gone:

du
display user table dropped user

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>;
drop multiple users postgres

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.

drop user 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;
reassign owned psql

The query changes the object ownership to the postgres user.

2. Remove the database object connections to the user with:

DROP OWNED BY myuser;
drop owned by psql

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;
drop user after error

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

Click here to post a comment

Đă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ý !