Grant more rights to users with AL privileges (seeing other users jobs / ability to set session to other user)

[proddata]

Use case: As a CrateDB user with admin privileges on cluster level I want to be able to run certain commands like setting a session to another user with SET SESSION AUTHORIZATION username, as I already have the ability to basically drop, recreate and login as this user anyway. Also changing the password of an existing user should be possible, for pretty much the same reason. This might be needed to stop a long running job (e.g. import of large datasets)

Feature description:

Allow the following Statements for users with AL privileges:

• SET [ SESSION | LOCAL ] SESSION AUTHORIZATION username This should also make it possible to run KILL ALL for jobs started by username through the logged in user.

Further users with AL privileges should be able to see all running jobs within sys.jobs (maybe except of superuser crate), to identify long running jobs and have a general picture - as admin user - what is happening in the cluster.

[mfussenegger]

If https://github.com/crate/crate/issues/16033 gets implemented, would you still think that AL ought to be able to impersonate users or see all jobs?

[proddata]

If https://github.com/crate/crate/issues/16033 gets implemented, would you still think that AL ought to be able to impersonate users or see all jobs?

I would probably tend to stick with PostgreSQL and keep it a superuser privilege:

PG Docs The session user identifier can be changed only if the initial session user (the authenticated user) had the superuser privilege. Otherwise, the command is accepted only if it specifies the authenticated user name.

---

Regarding the "see all jobs" part: This is currently already possible with AL so I am a bit more hesitant. However I was also wondering if this global view on sys.jobs should not obfuscate / strip literals in the stmt column.