How to check connected user on psql

To get information about current connection from the psql command prompt:

\conninfo

This displays more informations, though.

To change user:

\c - a_new_user

‘-’ substitutes for the current database.

To change database and user:

\c a_new_database a_new_user

The SQL command to get this information:

SELECT current_user;

Examples:

postgres=# \conninfo
You are connected to database "postgres" as user "postgres" via socket in "/var/run/postgresql" at port "5432"

postgres=# \c a_new_database a_new_user
psql (12.1 (Ubuntu 12.1-1.pgdg16.04+1), server 9.5.20)
You are now connected to database "a_new_database" as user "a_new_user".

a_new_database=# SELECT current_user;
 current_user 
--------------
 a_new_user
(1 row)


This page list few interesting functions and variables.
https://www.postgresql.org/docs/current/static/functions-info.html


To view your current authorisation:

SELECT session_user, current_user;

To change to another role to which you’ve been granted access:

SET ROLE <role_name>;

For instance, if you’ve granted the postgres role to myuser with…

GRANT postgres TO myuser;

… then myuser can temporarily “su” to postgres with:

SET ROLE postgres;

Note (1): Superusers can SET ROLE to any other role without explicitly being granted access to it.

Note (2): Using SET ROLE changes only current_user and not session_user.

To return to your default (session) role:

RESET ROLE;

If you’re logged in as a superuser, you can change the authorisation profile for the entire session with:

SET SESSION AUTHORIZATION <role_name>;

This changes both current_user and session_user.

Postgres authorisation is a complicated (arguably over-complicated) topic. I suggest reading through the official documentation.

Edit: To make things simpler, I define the following alias in my ~/.psqlrc:

\set whoami 'SELECT session_user, current_user, :''HOST'' host, :''PORT'' port, :''DBNAME'' dbname;’

Now I can simply type :whoami at the psql prompt to see my authorisation details.

Tags:

Postgresql