Often, when designing an application, a user 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:
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 --------------+-------------- test_user | test_user (1 row)
The SET ROLE requires a role membership, while SET SESSION AUTHORIZATION...