PostgreSQL

Postgres Drop User

Databases are quite diverse in that they hold many functionalities. One is dropping a specific user from the database system from some of those functionalities. To perform the user dropping from the database, PostgreSQL presents us the DROP USER instruction. This command can be equally used within the pgAdmin GUI and PostgreSQL shell (psql) and works perfectly fine on both utilities. Thus, we have been writing this article for those PostgreSQL users who are naive to a database and looking for ways to drop users from a specific database. So, we will be starting our tutorial with the opening of PostgreSQL pgAdmin Graphical User Interface from the Windows 10 search area. After its opening, the pop-up dialog will appear on your screen to ask for your localhost and database password within the pgAdmin GUI.

Drop User Using PgAdmin GUI:

To drop a user from your database system, you must have a user in it. Therefore, we will initiate creating a new user in our “aqsayasin” database. So, tap on the database name and click the “query tool” icon from the top taskbar of pgAdmin GUI. The query area will be opened on your pgAdmin GUI, and you can add your commands to it. So, to create a new user in our database, we need to use the CREATE USER instruction in it along with the name of a “user” of our own choice.

In this illustration, we have been creating a user “John” in the database. While creating, we can assign it some privileges to make it proper. Therefore, we are assessing the log in right to our new user “John” and adding the password, i.e. “1234567”. We have been adding the log-in right with the keyword “LOGIN” and the password with the “ENCRYPTED PASSWORD” specification. Execute this query using the “run” button from the taskbar. You can see that the user has been created as the success message it returns on execution.

Let’s start dropping users from our database. To do that, you need to utilize the simple DROP USER command and the name of a user to be deleted from the database. Thus, we have been using the username “Johny” to drop the user with this username. If this user exists in our system, it will show the success drop message on the query output area. Otherwise, it will return an error. Thus, after executing the shown command in the query area with the “run” button, we have got an error that the specified username is not found in your database system. It is because we have created a user with the username “John” and not “Johny”.

To avoid the error we have got in the above example, i.e. “role ‘Johny’ does not exist”, we need to modify the DROP USER query. For that, we need to add the “IF EXISTS” condition along with the DROP USER command before th name of a user. On the execution of this command, we will only get a notice “role Johny doesn’t exist, skipping” in the postgreSQL pgAmdin GUI query output area as shown in the image below.

Let’s quickly correct our mistake to delete the user “John” from the database. Use the DROP USER instruction with the username “John” or simply put the IF EXISTS condition along with it to drop this user quickly. We have got the success message on this instruction execution that our user “john” has been dropped from our database system. Thus, the user “john” doesn’t exist anymore.

Let’s take another method to create and drop a user from our database. This method will contain no command to execute. Thus, go to the left side of pgAdmin GUI, having databases listed there. Explore your choice of database and expand the tab “Login/Group Roles”. You will see a list of users already existing in your database.

The below shown pop-up screen will be opened named “Create – Login/Group Role”. Add the name for the user of your choice within the text bar in front of “Name specification”. Add comments or descriptions about the user within this page as well. Go to the privileges section now.

If you want this user to have administrative rights on your database system, you must enable all the below-shown toggle buttons by tapping on them. Now, our user “John” can log in, have superuser rights, can create new users, create databases and tables, and inherit the properties of its parent roles/users. Now, expand the “SQL” query area from this dialogue box.

You will be presented with the CREATE ROLE query for the user “John” in the database having most of the administrative rights.

Now, right-click on the “Login/Group Roles” option at the sidebar and click on the “Refresh” button to refresh the users in our database. You will see the just-created user “John” in this list and the other already existing users. Right-click on the user “John” and tap on the “Delete/Drop” option showing in the pop-up to drop this user. Another dialog will appear to make you confirm the dropping of the user “John”. Tap on the “OK” button to continue.

Drop User Using PostgreSQL Shell (psql):

Open the PostgreSQL shell from the windows 10 search bar, writing “psql”. Add the localhost name, database name to work on, port number, username, and password for a user to use in the PostgreSQL shell. Our command shell for database “aqsayasin” is ready for use.

Let’s start with using the “\du” command to display the list of all users from our database “aqsayasin”. This command shows the total of 2 users in the database “aqsayasin”.

Let’s create a new user, “Ana” with no special rights except LOGIN rights along with a password. Use the CREATE USER command to do so as below. The user has been created now.

Now, we have to create a user “John” with special administrative rights and no replication rights using the instruction shown in the image. The user has been created now.

Let’s create another user, “Lia” with all administrative rights along with the replication rights as per the display instruction in the image. This user is now existing in our database.

Here you have the list of all users in the image.

Let’s drop a user “Ana” with the DROP USER command presented below.

To drop more than 1 user, use the names of all users in the DROP USER command.

Now, the “\du” instruction shows us that all the new users have been dropped successfully.

Conclusion:

This article has been specially created for naive PostgreSQL database users who are looking for simple and easy examples for deleting a user from the database. We have dropped an already existing user using the Postgresql PgAdmin GUI and PostgreSQL shell separately. The DROP USER command has been utilized with the IF EXISTS condition to drop a user. We have also seen how to drop more than 1 user at a time. To sum up, this article has covered all the possible ways to drop a user.

About the author

Aqsa Yasin

I am a self-motivated information technology professional with a passion for writing. I am a technical writer and love to write for all Linux flavors and Windows.