PostgreSQL – How to Change Role within a Database Session
You can change the current role within a database session in PostgreSQL database using SET ROLE statement . Changing the role allows you to temporarily assume the permissions and privileges associated with a different role, which can be useful for specific tasks or actions that require different access rights. This can be particularly helpful in a multi-user database environment with different user roles and access levels.
Here’s the basic syntax for using the SET ROLE statement:
SET ROLE role_name;where role_name is the name of the role you want to set(or assume). To switch to a different role, you must have the appropriate privileges to do so, and the target role must exist in the database.
Here’s an example of how to use the SET ROLE statement:
SET ROLE admin_role;This statement sets the current session’s role to “admin_role.”
Using the SET ROLE statement should be done with caution, as it can affect security and access control. It’s typically used in situations where specific tasks require temporarily elevated privileges and is often used in conjunction with stored procedures or functions that need different permissions.
To revert to the original role you can use the RESET ROLE statement:
RESET ROLE;This will revert to the role of the logged-in user.
