PostgreSQL CURRENT_USER

Summary: in this tutorial, you will learn how to use the PostgreSQL CURRENT_USER function to return the name of the currently logged-in database user.

Introduction to the PostgreSQL CURRENT_USER function

The PostgreSQL CURRENT_USER is a function that returns the name of the currently logged-in database user.

Here’s the syntax of the CURRENT_USER function:

CURRENT_USERCode language: SQL (Structured Query Language) (sql)

The function returns the name of the current effective user within the session.

In other words, if you use the SET ROLE statement to change the role of the current user to the new one, the CURRENT_USER will reflect the new role.

In PostgreSQL, a role with the LOGIN attribute represents a user. Therefore, we use the terms role and user interchangeably.

To get the original user who connected to the session, you use the SESSION_USER function.

PostgreSQL CURRENT_USER function example

First, open the command prompt on Windows or a terminal on Unix-like systems and connect to the PostgreSQL server using psql:

psql -U postgresCode language: SQL (Structured Query Language) (sql)

Second, use the CURRENT_USER function to get the currently logged-in user:

SELECT CURRENT_USER;Code language: SQL (Structured Query Language) (sql)

Output:

 current_user
--------------
 postgres
(1 row)Code language: SQL (Structured Query Language) (sql)

Third, create a new role called bob:

CREATE ROLE bob 
WITH LOGIN PASSWORD 'SecurePass1';Code language: JavaScript (javascript)

Fourth, change the role of the current user to bob:

SET ROLE bob;Code language: SQL (Structured Query Language) (sql)

Fifth, execute the CURRENT_USER function:

SELECT CURRENT_USER;Code language: SQL (Structured Query Language) (sql)

It returns bob instead:

 current_user
--------------
 bob
(1 row)Code language: SQL (Structured Query Language) (sql)

Six, use the SESSION_USER function to retrieve the original user who connected to the session:

SELECT SESSION_USER;Code language: SQL (Structured Query Language) (sql)

Output:

 session_user
--------------
 postgres
(1 row)Code language: SQL (Structured Query Language) (sql)

The SESSION_USER function returns postgres, not bob.

Summary

• Use the CURRENT_USER function to return the current effective user within the session.
• Use the SESSION_USER function to return the original user who connected to the session.