SET_ROLE(7pg17)

🎀 SET_ROLE 💅

okay so bestie, welcome to SET_ROLE(7pg17) — that's the PostgreSQL 17 manual page, section 7, and today we're talking about literally becoming someone else for the rest of your session. identity crisis, but make it SQL ✨

---

✨ NAME ✨

SET_ROLE — set the current user identifier of the current session 🫶

Like, it changes who you are as far as the database is concerned. main character swap. 🔥

---

🌸 SYNOPSIS 🌸

SET [ SESSION | LOCAL ] ROLE role_name
SET [ SESSION | LOCAL ] ROLE NONE
RESET ROLE

three little lines. iconic. 💅

---

🦋 DESCRIPTION 🦋

So. This command sets the current user identifier of the current SQL session to be role_name. And yes, the role name can be written as either an identifier or a string literal — both are valid, we love flexibility. 💖

After SET ROLE, permissions checking for SQL commands is carried out as though the named role were the one that had logged in originally. you are, for all intents and purposes, that role now. ate. 👑

but wait — heads up, bestie 🙈 — SET ROLE and SET SESSION AUTHORIZATION are exceptions. The permissions checks for those two continue to use the current session user and the initial session user (the authenticated user), respectively. so they don't play by the same rules. iykyk.

The current session user must have the SET option for the specified role_name — either directly, or indirectly via a chain of memberships with the SET option. and if the session user is a superuser? any role can be selected. no cap. 🌈

The SESSION and LOCAL modifiers act the same as for the regular SET command. ⭐

Now the reset-ish forms, which are lowkey the best part:

• SET ROLE NONE sets the current user identifier to the current session user identifier, as returned by session_user.
• RESET ROLE sets the current user identifier to the connection-time setting specified by the command-line options, ALTER ROLE, or ALTER DATABASE, if any such settings exist. Otherwise, RESET ROLE sets the current user identifier to the current session user identifier.

and the slay detail: these forms can be executed by any user. 🫶

---

💜 NOTES 💜

okay this is the part where i hold your hand, because the privilege stuff is giving layers. 🧚

Using this command, it is possible to either add privileges or restrict one's privileges. Both! Choose your adventure!

• If the session user role has been granted memberships WITH INHERIT TRUE, it automatically has all the privileges of every such role. In this case, SET ROLE effectively drops all the privileges except for those which the target role directly possesses or inherits.
• On the other hand, if the session user role has been granted memberships WITH INHERIT FALSE, the privileges of the granted roles can't be accessed by default. However, if the role was granted WITH SET TRUE, the session user can use SET ROLE to drop the privileges assigned directly to the session user and instead acquire the privileges available to the named role.
• And if the role was granted WITH INHERIT FALSE, SET FALSE — then the privileges of that role cannot be exercised either with or without SET ROLE. 💀 dead end, bestie, no workaround here.

Also important: SET ROLE has effects comparable to SET SESSION AUTHORIZATION, but the privilege checks involved are quite different. And SET SESSION AUTHORIZATION determines which roles are allowable for later SET ROLE commands, whereas changing roles with SET ROLE does not change the set of roles allowed to a later SET ROLE. different vibes, different machinery. ⚙️

Two more caveats, and these matter fr fr:

• SET ROLE does not process session variables as specified by the role's ALTER ROLE settings; this only happens during login. (login-only behaviour, we don't do reruns 🎀)
• SET ROLE cannot be used within a SECURITY DEFINER function.

---

🔥 EXAMPLES 🔥

look at her go. 💅

SELECT SESSION_USER, CURRENT_USER;

 session_user | current_user
--------------+--------------
 peter        | peter

SET ROLE 'paul';

SELECT SESSION_USER, CURRENT_USER;

 session_user | current_user
--------------+--------------
 peter        | paul

see how session_user stayed peter but current_user became paul? that's the whole point. the session user is who logged in, the current user is who you're being right now. 🦋✨

---

🌈 COMPATIBILITY 🌈

PostgreSQL allows identifier syntax ("rolename"), while the SQL standard requires the role name to be written as a string literal.

SQL does not allow this command during a transaction; PostgreSQL does not make this restriction because there is no reason to. (we're just chill like that 💅)

The SESSION and LOCAL modifiers are a PostgreSQL extension, as is the RESET syntax. 🎀

---

👑 SEE ALSO 👑

• SET SESSION AUTHORIZATION (SET_SESSION_AUTHORIZATION(7)) — the sibling command, definitely go read it. 🫶

---

and that's the page, bestie! 🌸 this was PostgreSQL 17.11, dated 2026 in the footer — mwah, go forth and roleplay responsibly 💖💅✨