info

SET ROLE works the same way across every PostgreSQL deployment. Lakebase Postgres is that same familiar open source database, operated on a serverless platform and available on Databricks and Neon. Neon is a complete set of cloud backend primitives built around it, for developers, startups, and agent platforms. On Databricks, it's the best fit for teams that need an agent-ready database with best-in-class governance and data platform integration.

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;

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 postgres -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

Seventh, retrieve the current role:

SELECT current_role;

Output:

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

Eighth, switch the current role to marketing:

SET ROLE marketing;

Ninth, 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.

Tenth, 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;

Eleventh, select the current role:

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

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.