sql: enable changing the current user of a session with SET ROLE

[jordanlewis]

Add support for the SET ROLE command and variants from PostgreSQL. This command sets or resets the user for the current session.

This should also support SET LOCAL ROLE, which changes the role just for the current transaction. This is related to, but not dependent on SET LOCAL for session variables: #32562

At least one client, Flyway, requires support for this syntax. Graphile uses this too.

Epic CRDB-7217

[bdarnell]

It looks like flyway only requires RESET ROLE, which is a trivial noop if we don't support SET ROLE.

[dianasaur323]

Looks like this is RBAC, which we will work on in 1.2. cc @nstewart

[nstewart]

RBAC is scheduled to be an enterprise feature, so we should definitely try to decouple that effort from the apparently small amount of work required to unblock FlywayDB, which should work for all users. I'll note this consideration in our RBAC record. cc @awoods187

[jordanlewis]

FWIW this isn't a blocker for FlywayDB anymore, since their team created an adapter that works around our limitations.

[cfsnyder]

@jordanlewis do you have a link for information about the Flyway adapter that you mentioned?

[jordanlewis]

@cfsnyder it'll be part of their next major release. Adapter is probably the wrong word - it'll be part of the main Flyway product.

[mberhault]

SET ROLE is not part of the initial RBAC implementation (users cannot specify that they want to run as a specific role. Not entirely needed as privileges are currently always inherited. SET ROLE may be needed once NOINHERIT is possible).

Since this is no longer needed for Flyway, I propose we close it.

[awoods187]

Moving to later in case we need it, but agree with Marc that its not needed at the moment cc @vivekmenezes

[mberhault]

Unassigning. This need to be prioritized. If desired, this would be an easy project for someone on the sql team.

[aodhan-domhnaill]

Can I get guidance on how to add support for this feature?

[otan]

Unfortunately, due to the way we parse SESSION_USER:

cockroach/pkg/sql/parser/sql.y

Lines 11528 to 11531 in 3ea1633

	| SESSION_USER
	{
	$$.val = &tree.FuncExpr{Func: tree.WrapFunction("current_user")}
	}

, we may need two releases to push this out and work as expected. Since SESSION_USER and CURRENT_USER is different with the introduction of this PR, changing the builtin it parses to would not work in a mixed version state as any new builtin we introduce needs on release to make it in.

We can also just ship it and say SESSION_USER returns the wrong thing (for one release), use SESSION_USER() for the correct result.

[otan]

#68973 has this working, but with the above limitation.