PostgreSQL SET ROLE Statement

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

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;Code language: SQL (Structured Query Language) (sql)

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.

PostgreSQL SET ROLE statement example

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

First, connect to the dvdrental database using psql:

psql -U postres -d dvdrentalCode language: SQL (Structured Query Language) (sql)

Second, create a group role called marketing:

CREATE ROLE marketing;Code language: SQL (Structured Query Language) (sql)

Third, grant the SELECT privilege on the film table:

GRANT SELECT ON film TO marketing;Code language: SQL (Structured Query Language) (sql)

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

CREATE ROLE lily 
WITH LOGIN PASSWORD 'SecurePass1'
IN ROLE marketing;Code language: SQL (Structured Query Language) (sql)

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

psql -U lily -d dvdrentalCode language: SQL (Structured Query Language) (sql)

Seventh, retrieve the current role:

SELECT current_role;Code language: SQL (Structured Query Language) (sql)

Output:

 current_role
--------------
 lily
(1 row)Code language: SQL (Structured Query Language) (sql)

Eight, switch the current role to marketing:

SET ROLE marketing;Code language: SQL (Structured Query Language) (sql)

Ninth, retrieve the current role:

 current_role
--------------
 marketing
(1 row)Code language: SQL (Structured Query Language) (sql)

Output:

 current_role
--------------
 marketing
(1 row)Code language: SQL (Structured Query Language) (sql)

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.

Tenth, switch the current role to postgres:

SET ROLE postgres;Code language: SQL (Structured Query Language) (sql)

Output:

ERROR:  permission denied to set role "postgres"Code language: SQL (Structured Query Language) (sql)

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

RESET ROLE;Code language: SQL (Structured Query Language) (sql)

Eleventh, select the current role:

 current_role
--------------
 lily
(1 row)Code language: SQL (Structured Query Language) (sql)

The current role is back to lily.

Summary

• 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.