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 requires superuser privileges. Allowing an application to connect as a superuser is dangerous because the SET SESSION AUTHORIZATION and SET ROLE commands can be reset using the RESET ROLE and RESET SESSION commands, respectively, allowing the application to gain superuser privileges.

To understand how the PostgreSQL role system can be used to implement authentication and authorization, we will use the role system in the car portal app. In the car portal application, several groups of users can be classified as web_app_user, public_user, registered_user, seller_user, and admin_user. The web_app_user is used to configure business logic connection tools; the public_user, registered_user, and seller_user are used to distinguish users. The public_user group can access only public information, such as advertisements, but cannot add ratings as registered_user nor create advertisements as seller_user. admin_user is a super role to manage all of the application's content, such as filtering out spams and deleting the users that do not adhere to the website's policies. When the car web portal application connects to the database, the web_app_user user is used. After this, car_portal invokes the SET ROLE command based on the user class. This authentication method is known as proxy authentication.

The following examples demonstrate how a role system can be used to implement proxy authentication. The first step is to create roles and assign role memberships and privileges:

CREATE ROLE web_app_user LOGIN NOINHERIT;
CREATE ROLE public_user NOLOGIN;
GRANT SELECT ON car_portal_app.advertisement_picture, car_portal_app.advertisement_rating , car_portal_app.advertisement TO public_user;
GRANT public_user TO web_app_user;
GRANT USAGE ON SCHEMA car_portal_app TO web_app_user, public_user;

The NOINHERIT option for the web_app user does not allow the user to inherit the permissions of role membership; however, web_app can change the role to public user, as in the following example:

$ psql car_portal -U web_app_user
psql (10.0)
Type "help" for help.

car_portal=> SELECT * FROM car_portal_app.advertisement;
ERROR: permission denied for relation advertisement
car_portal=> SET ROLE public_user;
SET
car_portal=> SELECT * FROM car_portal_app.advertisement;
 advertisement_id | advertisement_date | car_id | seller_account_id 
------------------+--------------------+--------+-------------------
(0 rows)

car_portal=> SELECT session_user, current_user;
 session_user | current_user 
--------------+--------------
 web_app_user | public_user
(1 row)