cli,sql: stop using RPC conns in CLI client commands

[knz]

Today the CLI admin commands use a mix of RPC and SQL connections.
(They don't use the HTTP interface.)

For example, node decommission is RPC-only. node ls is SQL-only. debug zip uses both RPC and SQL.

We want everything to use a single protocol, ideally SQL. The reason why we want SQL is that the SQL server has the strongest authentication policies. IT would also enable using Kerberos tokens with the CLI client commands (as requested by customers, e.g. cockroach workloads - cc @BramGruneir )

Discussed this with @bdarnell . We can achieve this as follows: define SQL built-in functions for the various CLI commands. The built-in function would generate the command's results. The client-side program would then become a "dumb" SQL client that just issues the appropriate SQL queries.

The other advantage of this approach is that it then becomes possible to "run CLI admin commands" from a SQL shell, for example a UI tool.

Note that this feature might be rendered more secure by combining it with #51453.

---

A technical implementation detail for this approach: whatever service we make available via a SQL built-in function should also simultaneously become available over the HTTP REST API, using a similarly (ideally, identically) named endpoint.

For example, if I have a "command" SELECT crdb_internal.admin_node_status() I should have /api/v2/node/status (or something like that).

With this system, the debug zip command would send a SQL request for crdb_internal.admin_debug_zip() (or something like that) and there would also be a HTTP endpoint /api/v2/debug/zip (or something like that), which is incidentally a requirement for #51008.

cc @celiala who might be interested.

Jira issue: CRDB-6312

[BramGruneir]

I am 100% behind this.
We should also make this a policy going forward.

[bdarnell]

In some cases a virtual table might make more sense than a built-in function. For example, cockroach node status already displays its results in a tabular form; it should really become an alias for SELECT * from crdb_internal.admin_node_status instead of SELECT crdb_internal.admin_node_status().

[knz]

Built-in functions can return tables (we have those already, called set-returning functions)

[knz]

Here's the analysis of the current behavior, with what we can aim for:

Command	Port used	New port used
node ls	SQL	same
node status	SQL	same
node decommission	RPC	SQL via new built-in, with HTTP option
node recommission	RPC	SQL via new built-in, with HTTP option
node drain	RPC	SQL via new built-in, with HTTP option
init	RPC	SQL TBD, with HTTP option
quit (deprecated)	RPC	(REMOVED)
nodelocal	SQL	same
userfile	SQL	same
gen haproxy	RPC	SQL
other gen sub-commands	N/A (entirely local)	N/A
workload	SQL	same
import	SQL	same
debug tsdump	RPC	SQL via new built-in, with HTTP option
debug doctor	SQL	same
debug gossip-values	RPC	SQL TBD, with HTTP option
debug zip	SQL and RPC	SQL with HTTP option

There's a tricky bit: for gossip-values, init and possibly others, we want the option via HTTP and/or SQL to work even when the cluster is not initialized, and even when some system ranges are unavailable:

• if we want this to work via SQL, we need a way to establish a SQL connection and route a RPC-over-SQL command without engaging the regular SQL machinery, including a KV-less authentication story.

• if we want this to work via HTTP, we need a KV-less authentication story.

[github-actions]

We have marked this issue as stale because it has been inactive for
18 months. If this issue is still relevant, removing the stale label
or adding a comment will keep it active. Otherwise, we'll close it in
10 days to keep the issue queue tidy. Thank you for your contribution
to CockroachDB!