Often, when designing an application, a login role is used to configure database connections and connection tools. Another level of security needs to be implemented to ensure that the user who uses the application is authorized to perform a certain task. This logic is often implemented in application business logic.
The database's role system can also be used to partially implement this logic by delegating the authentication to another role after the connection is established or reused, using the SET SESSION AUTHORIZATION statement or SET ROLE command in a transaction block, as follows:
postgres=# SELECT session_user, current_user;
session_user | current_user
--------------+--------------
postgres | postgres
(1 row)
postgres=# SET SESSION AUTHORIZATION test_user;
SET
postgres=> SELECT session_user, current_user;
session_user | current_user...