Support SET SESSION AUTHORIZATION

[kokosing]

This is follow up of #2309 (comment)

This could be used to set session user or set session extra credential.

We'd want this to use standard syntax like SET SESSION AUTHORIZATION. The SQL standard doesn't seem to define the access model and leaves it up to the implementation:

If V is not equal to the current value of the SQL-session user identifier of the current SQL-session context, then the restrictions on the permissible values for V are implementation-defined.

It's more complicated because there are two different models, similar to setuid vs seteuid in UNIX. The PostgreSQL implementation is like seteuid -- the access check is based on the originally authenticated user. Thus, it is possible to temporarily become an unprivileged user, then switch back.

The model you want is like setuid -- the access check is based on the current user. Thus, switching users permanently revokes all of privileges of the current user. For example, user A could switch to user B, but then would not be allowed to switch back to A.

PostgreSql docs: https://www.postgresql.org/docs/current/sql-set-session-authorization.html

[phd3]

From #2671 (comment)

Relevant parts of the SQL spec:

4.40.1.1 SQL-session authorization identifiers

An SQL-session has a user identifier called the SQL-session user identifier. When an SQL-session is initiated, the SQL-session user identifier is determined in an implementation-defined manner, unless the session is initiated using a <connect statement>. The value of the SQL-session user identifier can never be the null value. The SQL-session user identifier can be determined by using SESSION_USER.

An SQL-session context contains a time-varying sequence of cells, known as the authorization stack, each cell of which contains a user identifier, a role name, or both. This stack is maintained using a “last-in, first-out” discipline, and effectively only the top cell is visible. When an SQL-session is started, by explicit or implicit execution of a <connect statement>, the authorization stack is initialized with one cell, which contains only the user identifier known as the SQL-session user identifier; a role name, known as the SQL-session role name may be added subsequently.

Execution of a <routine invocation>, <externally-invoked procedure>, triggered action, <execute statement>, or <direct SQL statement> X pushes a new cell onto the top of the authorization stack. This new cell might be a copy of the existing top cell, a routine authorization identifier, or the authorization identifier of a module owner, schema owner, or prepared statement owner, as prescribed by the applicable General Rules. After execution of X completes, the top cell is removed.

The contents of the top cell in the authorization stack of the current SQL-session context determine the privileges for the execution of each SQL-statement. The user identifier, if any, in this cell is known as the current user identifier; the role name, if any, is known as the current role name. They may be determined using CURRENT_USER and CURRENT_ROLE, respectively.

At a given time, there may be no current user identifier or no current role name, but at least one or the other is always present.
NOTE 103 — The privileges granted to PUBLIC are available to all of the <authorization identifier>s in the SQL-environment.

The <set session user identifier statement> changes the value of the current user identifier and of the SQL- session user identifier. The <set role statement> changes the value of the current role name.

The term current authorization identifier denotes an authorization identifier in the top cell of the authorization stack.

19.2 <set session user identifier statement>

1. If a <set session user identifier statement> is executed and an SQL-transaction is currently active, then an exception condition is raised: invalid transaction state — active SQL-transaction.
2. Let S be <value specification> and let V be the character string that is the value of TRIM ( BOTH ' ' FROM S )
3. If V does not conform to the Format and Syntax Rules of an , then an exception condition is raised: invalid authorization specification.
4. If V is not equal to the current value of the SQL-session user identifier of the current SQL-session context, then the restrictions on the permissible values for V are implementation-defined.
5. If the current user identifier and the current role name are restricted from setting the user identifier to V, then an exception condition is raised: invalid authorization specification.
6. The SQL-session user identifier of the current SQL-session context is set to V.
7. The current user identifier is set to V.
8. The current role name is removed.
   NOTE 723 — The current role name is also the SQL-session role name.

To summarize:

• There's a session user that's established when the session starts. That can be obtained with the SESSION_USER expression.
• There's a an authorization stack that starts with a single cell when the session is initiated. Actions such as calling a routine, or executing a query push a new cell on the stack -- the value of the cell can came from a variety of places, but typically, it's a copy of the current cell.
• Cells in the authorization stack can contain a user, a role or both. The values can be inspected via the CURRENT_USER and CURRENT_ROLE expressions, respectively.
• The value of the user identifier in the current cell can be changed via SET SESSION AUTHORIZATION. It is implementation-defined whether the current user is allowed to change the current user to the desired one.

[phd3]

EDIT: updated comment here : #2512 (comment)

@martint Wanted to discuss about roles and authorizationUser. Based on the spec above, would the following behavior make sense ?

Terminology: in Trino world, we will have the following in the "Identity" : original user (i.e. X-Trino-User), authorization user (i.e. X-Trino-Authorization-User introduced), and roles (R).

• if client sends originalUser=S, role=R and query is SET SESSION AUTHORIZATION T => check if (user=S, role=R) is allowed to impersonate T. If the check succeeds, the query is success and next query executes as (user=T, role={}).
• for next queries, client will send originalUser=S, user=T, roles=R (any query) => then R is ignored after impersonation check, and identity will not use any roles.
• RESET SESSION AUTH will revert to (user=S, role=R).
• If client sends originalUser=S, authorizationUser=T and query is SET ROLE x => the role gets validated against S. (This part seems a bit iffy to me - but not sure if there's a better solution. May be SET ROLE spec will make it clear? )

The above seems to be consistent with the fact that we don't propagate roles in view session. It also removes role as mentioned in spec. (i.e. It assumes that when client sends originalUser=S, authorizationUser=T, role=R => S is setting role R and then (S,R) is setting authorization user T. rather than S setting T and then S/T setting R. ) However - one disadvantage is that - we're essentially not allowing roles to be used corresponding to the authorization user.

[phd3]

FWIW - I looked at Postgres doc and it seems they consider the authorization user to determine what is alllowed in SET ROLE. But I'm not sure if they're entirely following the spec as they seem to change current_user even with SET ROLE - which Trino isn't doing.

SET ROLE has effects comparable to SET SESSION AUTHORIZATION, but the privilege checks involved are quite different. Also, 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.

https://www.postgresql.org/docs/current/sql-set-role.html
https://www.postgresql.org/docs/current/sql-set-session-authorization.html

[baohe-zhang]

I did some investigation on how other databases handle roles handover during set session authorization.

PostgreSQL
PostgreSQL has 2 concepts of users, session_user and current_user. The session_user is normally the user who initiated the current database connection; but superusers can change this setting with SET SESSION AUTHORIZATION. The current_user is the user identifier that is applicable for permission checking. Normally it is equal to the session user, but it can be changed with SET ROLE. And the SET ROLE permission check is based on the session_user.

RESET ROLE sets the current user identifier to the current session user identifier.
RESET SESSION AUTHORIZATION reset the session and current user identifiers to be the originally authenticated user name.

Example:
// login in as u1, session_user=u1, current_user=u1

SET ROLE r1; // Check if session_user u1 can set role r1, if yes session_user=u1, current_user=r1

SET SESSION AUTHORIZATION u2; // Check if the original session_user u1 has the superuser privilege, if yes session_user=u2, current_user=u2

SET ROLE r2; // Check if current session_user u2 can set role r2, if yes session_user=u2, current_user=r2

RESET ROLE; // session_user=u2, current_user=u2

RESET SESSION AUTHORIZATION; // session_user=u1, current_user=u1

IBM DB2
IBM DB2 has 2 concepts of users, SYSTEM_USER and SESSION_USER. SYSTEM_USER is the authenticated user and can't be changed during the connection. SESSION_USER is the authorization ID and is used for data access authorization. SESSION_USER can be updated by the SET SESSION AUTHORIZATION command and the check is based on SYSTEM_USER's impersonation privilege.

In IBM DB2, all roles that are granted to a user are always enabled and cannot be disabled. The SET ROLE command is to verify whether the authorization ID (SESSION_USER) is a member of a specific role.

// login in as u1, SYSTEM_USER=u1, SESSION_USER=u1, ROLE=<all u1's roles>

SET SESSION AUTHORIZATION u2; // Check if the SYSTEM_USER u1 has the privilege to impersonate u2, if yes SYSTEM_USER=u1, SESSION_USER=u2, ROLE=<all u2's roles>

SET SESSION AUTHORIZATION SYSTEM_USER; // Switch back to SYSTEM_USER u1. SYSTEM_USER=u1, SESSION_USER=u1, ROLE=<all u1's roles>

MySQL, Oracle, SQL Server
SET SESSION AUTHORIZATION is not supported.

[phd3]

SET ROLE spec - courtesy Martin:

[phd3]

@martint Given this specs above - did another attempt at defining the behavior. would the following be reasonable ? (Using this terminology : X-Trino-User=sessionUser, X-Trino-Authorization-User=authorizationUser, X-Trino-Role=role)

• AuthorizationUser and sessionUser is the same if the former is not explicitly given by client
• Role is logically always tied to the authorizationUser
• For an incoming query - if headers corresponding to sessionUser, authorizationUser are different and role R is present:
  • check if (user, role=None) can set authorizationUser
  • check if authorizationUser can set role R.
  • Run a query with identity (user=authorizationUser, role=R). [If no roles - then use authorizationUser’s enabled roles]
• When SET ROLE R is executed:
  • check against authorizationUser value.
  • Set Role header.
  • Next queries execute using (authorizationUser, role=R)
• When RESET ROLE is executed:
  • No changes to the authorizationUser.
  • Remove role.
• When SET SESSION AUTHORIZATION X is executed =>
  • If sessionUser is the same as authorizationUser:
    • Check for impersonation of X against existing (sessionUser, role)
    • If so - set the header. Remove role. Next queries execute as (x, none).
  • Else:
    • Check for impersonation against existing (sessionUser, none).
    • If so - set the header. Remove role. next queries execute as (x, none).
• When RESET SESSION AUTHORIZATION is executed:
  • If sessionUser != authorization user:
    • Remove Authorization user.
    • Also remove the role.
    • Next queries should run as sessionUser.
  • else:
    • like above % Keep the role as is.

[baohe-zhang]

Hi @martint @kokosing , Could you review the behavior of the handover of the role proposed by @phd3 when you get a chance?

[kokosing]

When SET SESSION AUTHORIZATION X is executed =>
 - If sessionUser is the same as authorizationUser:
    - Check for impersonation of X against existing (sessionUser, role)

Shouldn't we have Check for impersonation of X against existing (sessionUser, none) as Role is logically always tied to the authorizationUser?

As I understand using authorizedUser in SET ROLE check and (session user, role) in SET SESSION AUTHORIZATION makes possible to:

SET SESSION AUTHORIZATION u1;
SET ROLE r1;
SET SESSION AUTHORIZATION u2;
SET ROLE r2;

Considering a case where session user can impersonate u1 and r1 can impersonate u2, while u1 can set role r1 and u2 can set role r1. While direct transition to (u2, r2) could be not possible.

SET SESSION AUTHORIZATION u2; -- this would fails

[baohe-zhang]

Hi @kokosing

SET SESSION AUTHORIZATION u1;
SET ROLE r1;
SET SESSION AUTHORIZATION u2;
SET ROLE r2;

SET SESSION AUTHORIZATION u2; in this case would fail as well.

When SET SESSION AUTHORIZATION X is executed =>

• If sessionUser is the same as authorizationUser:
  • Check for impersonation of X against existing (sessionUser, role)
  • If so - set the header. Remove role. Next queries execute as (x, none).
• Else:
  • Check for impersonation against existing (sessionUser, none).
  • If so - set the header. Remove role. next queries execute as (x, none).

In this case, the authorizationUser is u1, and is different from the session user, hence the set session authorization u2 will only check against (sessionUser, none), instead of (sessionUser, role). And the check will fail as sessionUser can't impersonate u2.