Discussed/discovered in #77740, in the context of a conversation with a CC infra engineer who was asking:

is there a way for me to discover how the customer has customized their cluster settings themselves, short of spawning a SQL pod for them?

As currently designed in the setting RFC, no currently it is not possible to inspect the current value of a tenant's cluster setting from the admin tenant.

As defined, SHOW SETTING FOR TENANT has two different shortcomings:

1. if there is no override configured, the statement is not able to inspect the current value of a tenant's cluster setting, as observed from the tenant's SQL. The logic is only able to observe overrides, but not the actual customization within the tenant's own system.settings keyspace.
2. as designed, the statement's output provides the wrong impression that it does, in fact, show the setting's value from the tenant's perspective. This is because the statement syntax is SHOW SETTING FOR TENANT, not SHOW SETTING OVERRIDE FOR TENANT. This is very likely to mislead SREs and other administrative engineers who wish to inspect how a tenant is effectively configured.

There are two different ways forward:

• rename the statement to SHOW SETTING OVERRIDE FOR TENANT. This would solve problem (2) but fail to solve problem (1).
• invent a mechanism through which, when there is no override defined, the SHOW SETTING FOR TENANT statement can inspect the actual customization performed by a tenant. This would solve both problems (1) and (2).

Arguably, ALTER TENANT SET CLUSTER SETTING has a similar shortcoming: it is not currently possible to change a tenant's own customization of a cluster setting "remotely" in a way that the tenant can change it themselves to another value later. The semantics (as defined by the RFC) only provision a mechanism for overrides.

cc @ajstorm @RaduBerinde

Jira issue: CRDB-13859

Epic CRDB-14080