When managing a PostgreSQL database, it’s essential to understand how to effectively manage users, including deleting or dropping users when they are no longer needed. This ensures that access to your database is tightly controlled and that only authorized users can perform operations within your database environment.
Deleting a PostgreSQL user, often called “dropping a user,” is a straightforward task that requires careful consideration to ensure the database’s integrity and security are maintained.
In this detailed tutorial, we will discuss how you can delete a PostgreSQL user. We will cover the idea of dropping a single and multiple users. Finally, we will show you how to manage the dependencies involved in the process.
Before going into the details of the processes, let’s check out the prerequisites for the process.
Table Of Contents
- The Prerequisites
- Delete a Postgres User
- Delete Several PostgreSQL Users at a Time
- Eliminate a PostgreSQL User with Associated Dependencies
- Remove a PostgreSQL Role
- Conclusion
- FAQs
The Prerequisites
Make sure you have the following before you try one or more processes we will discuss later on in this tutorial:
- A server running a mainstream Linux distribution
- A user account with root or sudo privileges. This user account should ideally have root access to the PostgreSQL database.
- You should have access to a recent PostgreSQL version to ensure compatibility and access to the latest features and security patches.
- You should have several existing PostgreSQL users that you can delete without disrupting your operations.
Delete a Postgres User
We recommend the DROP USER command to delete a PostgreSQL database user (also known as dropping a user). This action removes the specified user from the PostgreSQL current database management system.
We will discuss two methods of deleting a PostgreSQL user.
Method #1: Use the DROP USER Command
Open the terminal and log in to the PostgreSQL Database shell (psql) with the following command:
# psql -U [username] [database_name],
Note that [username] is your PostgreSQL username, and the [database_name] is the specific database you want to work on. If you’re working on the default database, you might omit the [database_name] parameter.
Execute the DROP USER Command
Now, you can execute the DROP USER username command. Remember to replace username with the name of the user you wish to delete. For instance, DROP USER Sonika will remove the user named Sonika.
Here’s the command syntax you can use to delete a PostgreSQL user:
# sudo -u postgres dropuser username -e
Replace the username with the actual PostgreSQL username you intend to delete.
The -e flag is used to echo the command being sent to the server, providing additional visibility into the process.
You can follow this same output by following an alternative two-step approach.
Start by transiting to the postgres user (or any other user possessing similar permissions):
# su - postgres
Next, execute the following dropuser command. Remember to replace name with the target user name. The -e flag echoes the SQL command to the screen:
# dropuser name -e
Either of these methods will successfully remove the specified PostgreSQL user.
Considerations and Best Practices
Check for Dependencies: Before dropping a user, ensure that the user does not own any database objects (like tables or functions) or has any active connections. If the user owns objects, you may need to transfer ownership to another user or drop those objects separately.
Close Active Connections: Ensure the user you are planning to drop has no active database connections. You should terminate existing connections before dropping the user.
Use DROP USER IF EXISTS: If the user doesn’t exist, the command will stop and throw an error. We recommend using the DROP USER IF EXISTS username command to avoid these errors. This command attempts to drop the user but does not return an error if the user is not found.
Backup Before Dropping: Consider taking a backup of any important data associated with the user or the database before proceeding with the deletion. This is a safety measure to prevent accidental loss of data.
Method #2: Dropping Users with CASCADE
The second method involves using the DROP USER SQL statement within PostgreSQL to remove a user.
This method requires executing SQL commands directly through the PostgreSQL command-line interface (psql) or within a SQL client connected to your PostgreSQL database.
Here are the major steps in this method that safely and effectively remove a PostgreSQL user.
Step #1: Log into PostgreSQL Shell
First, open the terminal and log into the PostgreSQL database using the psql shell. You can do this by executing the following command to launch the shell:
#sudo -u postgres psql
Next, log into the PostgreSQL shell with the following command. Remember to replace admin_user with your PostgreSQL administrative user name, and database_name with the name of the database you wish to connect to. If you’re working directly with the PostgreSQL server and not a specific database, you can omit the –d database_name part.
postgres =# psql -U admin_user -d database_name
Step #2: Verify the User Exists (Optional)
Before attempting to delete the user, you may want to ensure that the user indeed exists.
For this, execute the following SQL command:
postgres=# SELECT * FROM pg_catalog.pg_user WHERE username = 'username_to_delete';
Replace username_to_delete with the name of the user you plan to remove. This command lists the user’s details if they exist.
Step #3: Execute the DROP USER Command
Now, run the DROP USER statement to remove the target user from PostgreSQL. The syntax of the statement is as follows:
postgres=# DROP USER username_to_delete;
For instance, run the following command to drop the user named Tom:
postgres=# DROP USER Tom;
Step #4: Confirm Deletion (Optional)
After executing the DROP USER command, you might want to verify that the user has been successfully removed. You can do this by rerunning the command mentioned in Step #2.
If the user no longer exists, the query will return no results.
Important Considerations
Object Ownership: Check if the user owns any database objects (e.g., tables, schemas). If so, you’ll need to reassign or drop these objects before you can successfully remove the user.
Active Sessions: Ensure the user does not have active sessions. If there are active connections, we recommend disconnecting these before proceeding.
Use of CASCADE: If you decide to use the DROP USER username_to_delete CASCADE; statement, be aware that this will drop the user and any objects they own. This option should be used with caution as it can lead to the removal of significant portions of your database schema and data.
Delete Several PostgreSQL Users at a Time
If you have several user accounts to remove from the database, you can imagine the time required to complete the job by applying the previous methods.
Fortunately, the PostgreSQL command-line interface (CLI) provides the functionality to eliminate multiple PostgreSQL user accounts in a single operation.
Here are the steps of the process that uses the PostgreSQL CLI.
Step #1: Access the PostgreSQL CLI
Begin by opening your terminal and entering the following command to log into the PostgreSQL CLI as the postgres user:
# sudo -u postgres psql
Step #2: Display Current Users
Next, list all existing users to identify the ones you wish to remove by executing the following command:
postgres=# \du
Note down the usernames of the accounts you plan to delete.
Step #3: Execute Deletion of Multiple Users
To remove several users at once, use the DROP USER statement, listing the usernames separated by commas. We recommend adding the IF EXISTS option to gracefully handle any usernames that do not exist:
# DROP USER [IF EXISTS] username1, username2, ... usernameN;
Replace username1, username2, … usernameN with the actual usernames of the accounts you’re targeting for deletion.
Step #4: Verify Removal of User Accounts
After executing the delete operation, it is always a good idea to confirm that the specified users have indeed been removed. For this, run the following command to list the current users:
postgres=# \du
If the previous delete operation was successful, the deleted accounts will not appear in the list.
Eliminate a PostgreSQL User with Associated Dependencies
When you attempt to delete a PostgreSQL user who is the owner of database objects or has specific dependencies, the operation will not succeed and will result in an error message.
We will now outline a simple process you can follow to remove the dependencies before removing the user account.
Step #1: Transfer Ownership of Objects
If the user you wish to delete owns database objects, you must first reassign ownership of these objects to another user.
For instance, to transfer all objects owned by myuser to the postgres user, execute the following SQL command:
postgres=# REASSIGN OWNED BY myuser TO postgres:
This command changes the ownership of all objects from myuser to postgres, ensuring that database integrity is maintained.
Step #2: Remove Database Object Connections to the Users
After transferring ownership, the next step involves removing any connections the user might have to database objects. This includes revoking any privileges assigned to the user account by running the following command:
# DROP OWNED BY myuser;
This command effectively strips the user of all privileges and associations with database objects, clearing the path for user deletion.
Step #3: Delete the User
At this point, all dependencies are resolved, and the user no longer owns any objects or holds any privileges. You can now safely remove the user from PostgreSQL by executing the following command:
# DROP USER myuser;
Remove a PostgreSQL Role
You can eliminate a PostgreSQL role by executing the following command at the PostgreSQL command-line interface (psql):
# DROP ROLE [IF EXISTS] roleName;
Replace roleName with the actual name of the role you wish to remove. The DROP ROLE command can be used to delete both users and roles within PostgreSQL.
It’s noteworthy that the DROP USER command serves as a synonym for DROP ROLE, reflecting the fact that in PostgreSQL, users are essentially roles with the added capability to log into the PostgreSQL shell and the database. This interchangeability means that both DROP USER and DROP ROLE commands can be utilized to delete roles, regardless of their login permission status.
Conclusion
By understanding the concepts of user management, database administrators can ensure that their PostgreSQL databases remain secure, efficient, and well-organized.
It’s clear that managing user roles and permissions is a crucial aspect of database administration. We’ve learned that PostgreSQL treats users as roles with login capabilities, allowing the DROP USER and DROP ROLE commands to be used interchangeably for removing these entities. The process demands careful preparation, including transferring ownership of objects and ensuring no dependencies hinder the deletion.
At RedSwitches, we take pleasure in assisting our valued customers with their unique server needs. Our range of customizable bare metal servers is designed to provide you with reliable server infrastructure for your projects.
We understand the importance of competitive pricing, and that’s why we offer dedicated server options at affordable rates. When you choose us, you can expect a seamless experience as we prioritize swift delivery, typically fulfilling orders approved on the same day. Whether you require a dedicated servers, a traffic-friendly 10Gbps dedicated server, or a high-performance bare metal server, we are here to be your trusted hosting partner.
FAQs
Q. What is the basic command to delete a PostgreSQL user?
The basic command for deleting a PostgreSQL user is DROP USER username;
Q. Can I delete a user with active database connections?
No, you cannot directly delete a user with active database connections. You first need to terminate these connections using the pg_terminate_backend function or by disconnecting the user’s sessions.
Q. Is there a way to delete a user if I’m not sure they exist?
Yes, to safely attempt deletion without causing an error if the user does not exist, you can use the command DROP USER IF EXISTS username;.
Q. How can I transfer object ownership before deleting a user?
Before deleting a user who owns database objects, you can transfer ownership using the REASSIGN OWNED BY old_user TO new_user; command, where old_user is the user to be deleted, and new_user is the user receiving ownership.
Q. What should I do if I cannot delete a user due to dependency issues?
If a user cannot be deleted due to owning objects in the database, you must either transfer ownership of these objects to another user or use DROP OWNED BY username; to remove any dependent objects and privileges, followed by DROP USER username;.
Q. Can I delete multiple users at once?
Yes, to delete multiple users at once, use the DROP USER username1, username2, …; command, listing each username separated by commas.
Q. What’s the difference between DROP USER and DROP ROLE?
In PostgreSQL, there is no practical difference between DROP USER and DROP ROLE commands; both can be used interchangeably to delete users or roles. Users are a type of role with login permission.
Q. How do I list all users before deciding whom to delete?
To list all users in the PostgreSQL database, use the \du command in the psql command-line interface. This will display all users and their attributes.
Q. Are there any precautions I should take before deleting a user?
Yes, ensure that the user does not own any critical database objects or have any active connections. It’s also wise to back up any critical data related to the user before deletion.
Q. Can the deletion of a PostgreSQL user be undone?
No, the action cannot be undone once a user is deleted. This is why it’s critical to ensure that you truly wish to remove the user and have taken all necessary precautions, such as backing up data and transferring object ownership, beforehand.
