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.