Postgres Switch User

PostgreSQL is a multi-user database that allows more than one user to connect and perform the operations on the database objects. This means that you will encounter such scenarios where you need to switch between specific users that are created on the server.

Switching users allows us to access and manage the databases and perform various administrative tasks. This tutorial guides you through switching users in PostgreSQL including the command-line and graphical user interface (GUI) methods.

Requirements:

Before proceeding with this tutorial, you should have the following requirements:

Installed and running PostgreSQL Server on your system
Basic understanding of SQL queries and basic command-line knowledge
PgAdmin 4 or later for graphical use

Let us proceed!

PostgreSQL Switch User – PSQL Utility

One of the most common ways to interact with a PostgreSQL cluster is using the PSQL utility. This command-line utility that is provided by PostgreSQL allows you to connect and manage the server using a CLI interface.

As demonstrated in the following step, we can use this utility to switch between different database users.

Connect to the Server

Start by launching a terminal or command prompt on your system. Next, connect to the server using the psql utility as shown in the following command:

psql -U <username> -d database_name

Replace the username and the database_name with your target user and database, respectively.

This prompts you for a password to authenticate the specified user. Upon a successful authentication, you should be dropped into a PostgreSQL shell with the currently selected database and user as set during login.

Switch the User

To switch the users using the PSQL utility, you can use the “\c” command followed by the target username.

\c <target_username>

Ensure that the target_username exists on the server and that you have sufficient permissions to connect to the defined user.

For example, to switch to the Postgres user, we can run the following command:

\c postgres

This should return a message which indicates that we have successfully switched to the Postgres user:

You are now connected to database "postgres" as user "postgres".

Verify the User Switch

To verify that you successfully switched to the target user, you can execute the following command to display the current user:

select current_user;

Example Output:

current_user
--------------
postgres
(1 row)

The result shows the name of the currently logged-in user.

PostgreSQL Switch User – PgAdmin 4

If you are using the pgAdmin utility, you can quickly switch between different users as outlined in the following steps:

Start by launching the pgAdmin utility on your system. Next, connect to the target server and provide the credentials as necessary.

In the next step, locate the server that you want to connect to and expand its tree structure by clicking the arrow next to it.

Locate and expand the “Login/Group Roles” node within the server tree. This node contains the list of user roles that are associated with the PostgreSQL server.

Right-click on the user that you want to switch to and select “Connect” from the context menu.

NOTE: In the later version of pgAdmin, the option to switch the users from the context menu is missing.

Conclusion

Switching users in PostgreSQL is a straightforward process that can be accomplished using the command-line interface or a PostgreSQL GUI tool like pgAdmin. In this post, we explored both of these methods using the step-by-step instructions.