In this article, we will see the difference between SESSION_USER and CURRENT_USER in PostgreSQL.
Difference between SESSION_USER and CURRENT_USER in PostgreSQL:
SESSION_USER is the role name of the role that is connected to the database.
CURRENT_USER is the role name of the role that has been explicitly set by a SET
ROLE statement.
Once a connection is established, the two keywords refer to the very same role that is the
one that opened the connection. If the role performs an explicit SET ROLE operation, SESSION_USER
remains unchanged, while CURRENT_USER reflects the last specified role.
Suppose the user david opens a connection to the database. In the beginning, both SESSION_USER and CURRENT_USER hold the same value:
SELECT current_user, session_user;
Now set the role as postgres and verify the current_user and session_user.
SET ROLE postgres;
As you can see, after the SET ROLE statement, CURRENT_USER changed its value to reflect
the role the user is actually playing, while SESSION_USER holds the original value by which
the user connected to the database.