VIEWACTIVITYREDACTED should redact query text literals for SHOW SESSIONS

[gtr]

This issue tracks adding redaction for the SHOW SESSIONS command and the ListSessions endpoint.

In accordance with #103560, users with the VIEWACTIVITYREDACTED privilege should see a redacted version of the active_queries field from the SHOW SESSIONS response.

The current behavior allows a user with VIEWACTIVITYREDACTED to see the full query:

root@localhost:26257/defaultdb> GRANT SYSTEM VIEWACTIVITYREDACTED TO  gerardo;
GRANT

Time: 157ms total (execution 156ms / network 0ms)

root@localhost:26257/defaultdb> SELECT pg_sleep(1000);

Run SHOW SESSIONS from gerardo:

gerardo@localhost:26257/defaultdb> SELECT session_id, active_queries, user_name from [SHOW SESSIONS];
             session_id            |                              active_queries                               | user_name
-----------------------------------+---------------------------------------------------------------------------+------------
  1770d8d0e573a2f00000000000000001 | SELECT pg_sleep(1000)                                                     | root
  1770d8d2f8f465100000000000000001 | SELECT session_id, active_queries, user_name FROM [SHOW CLUSTER SESSIONS] | gerardo
(2 rows)

Time: 8ms total (execution 7ms / network 0ms)

SELECT pg_sleep(1000) should have its literals redacted, e.g. SELECT pg_sleep(_).

Jira issue: CRDB-29633

[gtr]

Update on this issue:

I put the fix for the VIEWACTIVITY system privilege bug in #106590. This should close its linked issue. However, I think there still needs to be some changes in the ListSessions endpoint, particularly from the UI perspective. Currently, the UI does not issue a ListSessionsRequest when refreshing session data in the SQL Activity pages. This means that the request coming into the server's ListSessions endpoint will always have an empty username field.

This is the current behavior:

• For a non-admin user, a ListSessionsRequest with an empty username field is taken to mean that the user is requesting to view their own sessions:

  cockroach/pkg/server/status.go

  Lines 193 to 197 in b19302f

  	// For non-superusers, requests with an empty username is
  	// implicitly a request for the client's own sessions.
  	if reqUsername.Undefined() {
  	reqUsername = sessionUser
  	}

  
  This was the crux of the previous VIEWACTIVITY system privilege bug linked above.
• For an admin user, a ListSessionsRequest with an empty username field means that the user is requesting to view all sessions:

  cockroach/pkg/server/status.go

  Lines 208 to 209 in b19302f

  	// The empty username means "all sessions".
  	showAll := reqUsername.Undefined()

Furthermore, for any SHOW SESSIONS request made from the SQL shell, the server misinterprets that the user making the request is root, even if we are logged into the SQL shell from a non-admin user. See: #45018. Effectively, this does not allow a user logged into the SQL shell to access its correct permissions since the ListSessions endpoint will always be interpreted as root. This is the root cause of this issue.

I'm leaning to move away from using this empty username field as an indicator to "show all sessions" and instead relying on the permissions that the given username has to determine which sessions to show. This will also require the UI to attach a username to any calls to ListSessions. It should default to root if running in insecure mode.