When you want to know the currently connected user in PostgreSQL for your session there are two system information functions which seem to do the same thing. There is “session_user” and there is “current_user”. So what is the difference between those or is there no difference at all?

Looking at my users in PostgreSQL all I have right now is the “postgres” superuser:

postgres=# \du
                             List of roles
 Role name |                         Attributes                         
-----------+------------------------------------------------------------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS

Both system information functions return the same result:

postgres=# select current_user;
 current_user 
--------------
 postgres
(1 row)

postgres=# select session_user;
 session_user 
--------------
 postgres
(1 row)

Let’s create a new login role:

postgres=# create user a with login password 'a';
CREATE ROLE
postgres=# \du
                             List of roles
 Role name |                         Attributes                         
-----------+------------------------------------------------------------
 a         | 
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS

What do those functions return when we connect with the new user?

postgres=# \c postgres a
You are now connected to database "postgres" as user "a".
postgres=> select current_user;
 current_user 
--------------
 a
(1 row)

postgres=> select session_user;
 session_user 
--------------
 a
(1 row)

Same story as above, both return the same result. Things get different if you use “set role“:

postgres=# \c postgres postgres
You are now connected to database "postgres" as user "postgres".
postgres=# set role a;
SET
postgres=> select current_user,session_user;
 current_user | session_user 
--------------+--------------
 a            | postgres
(1 row)

The session user is still “postgres”, but the current user now is “a”. This also means, that permission checks are now done for user “a” and not for “postgres” anymore:

postgres=> create table t(a int);
ERROR:  permission denied for schema public
LINE 1: create table t(a int);
                     ^
postgres=> create schema a;
ERROR:  permission denied for database postgres
postgres=>

Going back to the initial state can be done with “set role none”:

postgres=> set role none;
SET
postgres=# select current_user,session_user;
 current_user | session_user 
--------------+--------------
 postgres     | postgres
(1 row)

There is also “set session authorization“, which behave slightly different:

postgres=# set session authorization a;
SET
postgres=> select current_user, session_user;
 current_user | session_user 
--------------+--------------
 a            | a
(1 row)

postgres=>

The current user might also change in security definer functions, but the session user can’t. The current user is the one which is relevant for permission checking.

Post Views: 1,020
Thumbnail [60x60]
by
Daniel Westermann