SQL: Add support for SET ROLE statement

[jkosh44]

Feature request

SET ROLE allows a user to change the active role of their current session.

See: https://www.postgresql.org/docs/15/sql-set-role.html

[jkosh44]

Version 16 will have a couple of changes to role membership:

• postgres/postgres@3d14e171
• postgres/postgres@e3ce2de0

[jkosh44]

For a good overview on the role hierarchy in PostgreSQL take a look here: https://github.com/postgres/postgres/blob/9089287aa037fdecb5a52cec1926e5ae9569e9f9/src/backend/utils/init/miscinit.c#L461-L493

[jkosh44]

I started updating the session variable framework to support a role session variable here: #20353

I never merged it, but it might be a good starting place.

[jkosh44]

The SHOW ROLE semantics are a not what you may expect in PostgreSQL. SHOW ROLE shows the current role if it has been altered by SET ROLE, otherwise it shows none.

postgres=# SELECT current_role;
 current_role 
--------------
 postgres
(1 row)

postgres=# SHOW ROLE;
 role 
------
 none
(1 row)

postgres=# SET ROLE r1;
SET
postgres=> SELECT current_role;
 current_role 
--------------
 r1
(1 row)

postgres=> SHOW ROLE;
 role 
------
 r1
(1 row)

postgres=> RESET ROLE;
RESET
postgres=# SELECT current_role;
 current_role 
--------------
 postgres
(1 row)

postgres=# SHOW ROLE;
 role 
------
 none
(1 row)

This may suggest that the role GUC starts off as NULL in PostgreSQL. To model that we could make the role session variable of type Option<RoleId> with a default value of None. The current role can be derived as current_role.unwrap_or_else(|| session_role).