How to Switch Users in psql: A Comprehensive Guide

psql (PostgreSQL’s command-line interface) is the primary tool for interacting with PostgreSQL databases, and switching between database users is a common task for administrators and developers—whether for testing permissions, performing administrative tasks, or isolating workloads.

This guide covers all methods to switch users in psql, explains key differences between approaches, troubleshoots common errors, and outlines best practices to ensure secure and efficient user switching.

Prerequisites

Before proceeding, confirm:

• You have access to a PostgreSQL superuser (e.g., postgres) or a user with sufficient privileges to grant roles.
• The target user exists in PostgreSQL (verify with \\\\du in psql).
• You know the authentication rules for your PostgreSQL instance (e.g., peer, md5, or scram-sha-256 authentication).

Core Methods to Switch Users in psql

There are two primary ways to switch users in psql, each designed for distinct use cases: reconnecting with a new user (full identity switch) and temporarily assuming another user’s privileges (no reconnection).

Method 1: Reconnect with \\\\c (or \\\\connect) – Full User Switch

The \\\\c (short for \\\\connect) command is the standard, most reliable way to switch users in psql. It terminates the current connection and establishes a new one with the target user—effectively logging you out of the current user and into the new one.

Syntax

-- Full syntax: specify database, user, host, and port (optional)
\\\\c [database_name] [target_user] [host] [port]

-- Simplified: switch user while keeping the current database (use "-" as a placeholder for the database)
\\\\c - [target_user]

Step-by-Step Example

Suppose you are currently connected to the test_db database as the postgres user and want to switch to test_user:

-- Current connection context
test_db=> \\\\conninfo
You are connected to database "test_db" as user "postgres" via socket in "/tmp/.s.PGSQL.5432" at port "5432".

-- Switch to the "test_user" user (keep the same database)
test_db=> \\\\c - test_user
Password for user test_user:  -- Enter the password for "test_user" (if prompted)
You are now connected to database "test_db" as user "test_user".

-- Verify the switch
test_db=> \\\\conninfo
You are connected to database "test_db" as user "test_user" via socket in "/tmp/.s.PGSQL.5432" at port "5432".

Key Notes for \\\\c

• Authentication Requirements: The target user must have CONNECT permission on the database (otherwise, you’ll get permission denied for database). You may also need to authenticate (e.g., enter a password) based on your pg_hba.conf rules.
• Full Identity Switch: The session_user (the user associated with the connection) and current_user (the user whose privileges are active) both become the target user.
• Cross-Host Switching: To switch to a remote user, specify the host/port explicitly: \\\\c test_db test_user 192.168.1.100 5432 -- Connect to a remote PostgreSQL instance

Method 2: Temporary Privilege Switch with SET ROLE – No Reconnection

The SET ROLE command lets you temporarily assume the privileges of another user without disconnecting your current session. This is ideal for quick tasks (e.g., running a single administrative query) where you don’t want to terminate your existing connection.

Syntax

-- Assume the privileges of the target user
SET ROLE [target_user];

-- Revert to your original user’s privileges
RESET ROLE;

Step-by-Step Example

Suppose you are logged in as postgres (granted the test_user role) and want to temporarily use test_user’s privileges:

-- Check current user context
test_db=> SELECT session_user, current_user;
 session_user | current_user
--------------+--------------
 postgres     | postgres

-- Switch to "test_user" privileges
test_db=> SET ROLE test_user;
SET

-- Verify the privilege switch (current_user changes, session_user remains)
test_db=> SELECT session_user, current_user;
 session_user | current_user
--------------+--------------
 postgres     | test_user

-- Perform actions with "test_user" privileges (e.g., query a restricted table)
test_db=> SELECT * FROM test_user_restricted_table;  -- Succeeds if test_user has access

-- Revert to original privileges
test_db=> RESET ROLE;
RESET

Critical Prerequisites for SET ROLE

• Your original user must be explicitly granted the target role (run this as a superuser first): GRANT test_user TO postgres; -- Allow postgres to assume the test_user role
• You cannot use SET ROLE to assume a superuser role unless your original user is a superuser (or granted the superuser role).

Key Differences Between \\\\c and SET ROLE

The two methods may seem similar, but they have critical differences in identity, connection state, and functionality:

Aspect	\\\\c - [target_user]	SET ROLE [target_user]
Connection State	Terminates and re-establishes the connection	No reconnection (session remains active)
Session User	Changes to the target user	Remains the original login user
Current User	Changes to the target user	Changes to the target user
Ability to Modify User Attributes	Can alter the target user (e.g., ALTER USER test_user)	Cannot alter the target user (session user is still original)
Audit/Logging	Actions logged under the target user	Actions logged under the original session user
Use Case	Long-term switch to another user	Short-term privilege elevation for specific tasks

Troubleshooting Common User Switch Errors

Switching users in psql often fails due to authentication or permission issues. Below are the most common errors and their fixes:

See also: Mastering the Linux Command Line — Your Complete Free Training Guide

Error 1: FATAL: Peer authentication failed for user "test_user"

Cause: PostgreSQL’s peer authentication (default for local socket connections) requires the OS username to match the PostgreSQL username. If your OS user (e.g., postgres) doesn’t match the target PostgreSQL user (test_user), peer authentication fails.

Fixes:

1. Force password authentication by specifying localhost (bypasses peer auth): \\\\c test_db test_user localhost 5432 -- Prompts for test_user’s password
2. Update pg_hba.conf to use md5/scram-sha-256 instead of peer for local connections: # Edit pg_hba.conf (find path with SHOW hba_file;) sudo vi /var/lib/pgsql/14/data/pg_hba.conf # Replace "peer" with "md5" for local connections local all all md5 # Reload the config (no restart needed) sudo systemctl reload postgresql

Error 2: permission denied to set role "test_user"

Cause: Your original user (e.g., postgres) was not granted the test_user role.

Fix: Grant the role as a superuser:

GRANT test_user TO postgres;

Error 3: permission denied for database "test_db"

Cause: The target user (e.g., test_user) lacks CONNECT permission on the database.

Fix: Grant CONNECT permission as a superuser:

GRANT CONNECT ON DATABASE test_db TO test_user;

Best Practices for Switching Users in psql

To ensure security and avoid errors, follow these best practices:

1. Use Least Privilege

• Avoid switching to the postgres superuser for daily tasks. Instead, create dedicated roles (e.g., test_user_readonly, test_user_admin) with minimal required permissions.
• Use SET ROLE for short tasks (e.g., running one admin query) instead of staying connected as a superuser.

2. Secure Authentication

• Never hardcode passwords or use the PGPASSWORD environment variable (insecure). Use ~/.pgpass for secure password storage: # Create ~/.pgpass (format: host:port:database:user:password) echo "localhost:5432:test_db:test_user:YourSecurePassword123!" >> ~/.pgpass # Set strict permissions (required by PostgreSQL) chmod 600 ~/.pgpass

3. Audit User Switches

• Log all user switches for compliance (enable PostgreSQL logging in postgresql.conf): log_connections = on log_disconnections = on log_statement = 'all' # For debugging (disable in production)

4. Avoid Accidental Privilege Escalation

• Always RESET ROLE after completing tasks with SET ROLE to avoid unintended privilege use.
• Verify your current user context with \\\\conninfo or SELECT session_user, current_user; before running sensitive queries.

Conclusion

Switching users in psql is a fundamental skill, and choosing the right method depends on your use case:

• Use \\\\c - [target_user] for a full, long-term user switch (e.g., working as test_user for an entire session).
• Use SET ROLE [target_user] for a temporary privilege switch (e.g., running a single admin query without disconnecting).

By understanding the differences between these methods, troubleshooting common authentication errors, and following security best practices, you can switch users in psql safely and efficiently—minimizing risk and ensuring compliance with PostgreSQL’s permission model.

Key takeaways:

• \\\\c changes your connection’s identity entirely; SET ROLE only borrows privileges.
• peer authentication requires OS/database username matching (use localhost to bypass it).
• Always grant minimal roles and audit user switches to maintain security.