In PostgreSQL, the owner or the User role for every database object is defined. By default, the User of every database object is “postgres”. However, the user can also be defined and changed. The user has the excess to do some critical activities such as deleting/dropping the database objects, you are able to delete the database objects only if you are the owner of that object. In this article, we will learn how we can switch or change a user from a “postgres” user to something else, so let’s get started with the article.

How to Switch Users in PostgreSQL?

To switch the user you need to run some commands to set a new user role. These commands are described step by step in the portion below. Let’s follow the steps to switch the user:

Step 1: Connect to the Database

First, you have to establish a connection with PostgreSQL. There are two ways to get connected to the database but the simplest way to do this is by opening the psql and pressing the enter key to the first 4 fields, dining this will set them to default. On the very next field where it asks for the password, enter the password that you set for your PostgreSQL installation. The following output will ensure your connection with the database:

Step 2: Display the Current User

Now, execute the following query on the psql terminal to get the current_user and session_user:

SELECT current_user, session_user;

The query has given the following output:

We can see that the current_user and the session_user in our case are postgres.

Step 3: Set a New Role

Now we will set a new role for our database. This can be done by running the following basic syntax:

SET role new_User_name;

You need to add the name of the user that you want to switch to. I will name it “John”. For that, my query will be:

SET role John;

The query will successfully switch the new user as we have set a new role as a user i.e.

We can ensure whether the user is switched or not by using the command we have already used to display the current user and session. The query can be written as:

SELECT current_user, session_user;

The above query returns:

The above output advocated that the user has been switched from the default “Postgres” user to “John”.

Important

Note that you will only be able to switch the user if the user already exists. If the user does not exist and you try switching the user will result in an error:

To see the list of users that exist in your system run the following meta-command:

\du

This meta-command will return the list of all the users existing in the database. The output in my case is:

You can see that only one user exists i.e. the “postgres” which is created by default. This is the reason why we get “ERROR: role <USER> does not exist” errors.

To cater to this, you need to have the user already existing in order to switch it. Otherwise, you can create a user as well by running the following query:

CREATE USER   new_user_name;

I’ll execute the following query:

CREATE USER John;

By executing the above query, we will be able to create a user that can be ensured if the following output is returned:

You can also see the user in the list of users by running the \du meta-command.

Now if we try switching the user, that will be successfully done by using the SET ROLE command like this:

SET role John;

The query will simply switch the user for you, which can be ensured by the SELECT query as executed above.

So this is how we can switch the user in PostgreSQL.

Conclusion

We can switch a user in PostgreSQL by simply executing the SET ROLE command. But the point to be noted is that we can only switch the user if it already exists. Otherwise, switching the user to a user that does not already exist causes an error. So we have to create a user first in order to switch it. In this post, we have learned how to switch the user in PostgreSQL with the proper implementation.