Skip to content

What is the difference between session_user and current_user in PostgreSQL?

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?

Questions about your own setup? A dbi services consultant can look at your case.

Talk to an expert (opens in a new tab)
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.

Let’s work together

Ready to operate at
the next level?

Tell us about your challenge. We’ll bring the depth, the expertise, and the Swiss precision.

Open source Team

Open source Team

Posts published under the Open source Team account are collective contributions by the dbi services open infrastructure consultants. The practice covers open-source databases and platforms, PostgreSQL first of all, along with MySQL, MariaDB, and Linux, from architecture, replication, and high availability to performance tuning and operations under service level agreements, and contributes actively to the PostgreSQL community.View Author posts

721 posts since September 2011

Most read in Database

The Database posts our readers open most: the all-time favourites, and what is trending right now.

Leave a Comment

Your email address will not be published. Required fields are marked *