PostgreSQL Tutorial: SET ROLE

July 10, 2024

Summary: in this tutorial, you will learn how to use the SET ROLE statement to temporarily change the current role within a database session.

Table of Contents

Introduction to the PostgreSQL SET ROLE statement

The SET ROLE statement allows you to temporarily change the current role within a database session.

Here’s the syntax of the SET ROLE statement:

SET ROLE role_name;

In this syntax, you specify the name of the role to which you want to switch.

The role_name must be a role of which the current session user is a member.

If the session user is a superuser, you can switch to any role.

We’ll take an example of using the SET ROLE statement.

First, connect to the dvdrental database using psql:

psql -U postres -d dvdrental

Second, create a group role called marketing:

CREATE ROLE marketing;

Third, grant the SELECT privilege on the film table:

GRANT SELECT ON film TO marketing;

Fourth, create a role called lily that is a member of the marketing role:

CREATE ROLE lily 
WITH LOGIN PASSWORD 'SecurePass1'
IN ROLE marketing;

Fifth, connect to the dvdrental database using the lily role in a separate session:

psql -U lily -d dvdrental

Sixth, retrieve the current role:

SELECT current_role;

Output:

 current_role
--------------
 lily
(1 row)

Seventh, switch the current role to marketing:

SET ROLE marketing;

Eight, retrieve the current role:

 current_role
--------------
 marketing
(1 row)

Output:

 current_role
--------------
 marketing
(1 row)

The output indicates that the current role is marketing, not lily due to the SET ROLE statement.

If you attempt to switch the current role to a superuser such as postgres, you’ll get an error because the current role is not a superuser role.

Ninth, switch the current role to postgres:

SET ROLE postgres;

Output:

ERROR:  permission denied to set role "postgres"

To set the current role back to the original one, you use the RESET ROLE statement:

RESET ROLE;

Tenth, select the current role:

 current_role
--------------
 lily
(1 row)

The current role is back to lily.

Use the SET ROLE statement to temporarily change the current role within a database session.
Use the RESET ROLE statement to reset the role to the original one.