sql: add system privilege for viewing system tables

[maryliag]

Currently, non-admin don't have permission to system tables.
We do have use cases where we want to use system tables on the console (as read-only) and we want non-admin users to be able to see it, but we don't want them to have access to all system tables.

This feature request is allow user to grant/revoke access to specific system tables, without the need to be admin.

Jira issue: CRDB-23715

Epic CC-25593

[ajwerner]

I don't know how reasonable this is. We ought to leverage other mechanisms like views, virtual views or the synthetic privilege infrastructure to achieve these goals. I believe we now do have a mechanism to grant and revoke privileges on virtual tables/views.

This issue as written seems subject to the XY problem.

[maryliag]

What is the advantage of creating a new view on top of a system table if the view is just a select on that system table?
What is the concern about giving read permission on system tables?

As mentioned on the description, we want to make selections on system tables for non-admins, and the need to create views that do just a select on a system table just for that case seems unnecessary.

The problem: we need to query system tables for non-admins.
My suggestion: allow users to have read permission to some system tables (have to manage just the permission)
Your suggestion: creating a new layer (in this case a view), so that the permission can be done on that. (have to manage a new view and the permission on that view)

Would you mind explaining why the view is a better solution?

[knz]

The reason why we don't want configurable privileges on system tables is a mix of factors:

• we want to assert the current state of descriptors (by comparing them to a known reference) during upgrades
• so we want system table descriptors to be immutable except during upgrades
• privs are currently stored inside descriptors
• therefore we cannot change privs on system tables (outside of upgrade migrations)

If we had a mechanism to store privileges outside of descriptors (like comments) we'd be in a better place. But it would be some work. I believe we already have an issue for that.

[rafiss]

If we had a mechanism to store privileges outside of descriptors (like comments) we'd be in a better place. But it would be some work. I believe we already have an issue for that.

We do have this mechanism - system privileges (described here). We use this, for example, to implement privileges on virtual tables: https://github.com/cockroachdb/cockroach/blob/master/pkg/sql/syntheticprivilege/vtable_privilege.go. It's also used to implement cluster-wide privileges that can be inherited by role membership (unlike role options which are not inherited):

cockroach/pkg/sql/privilege/privilege.go

Line 153 in 05dd384

GlobalPrivileges = List{ALL, BACKUP, RESTORE, MODIFYCLUSTERSETTING, EXTERNALCONNECTION, VIEWACTIVITY, VIEWACTIVITYREDACTED, VIEWCLUSTERSETTING, CANCELQUERY, NOSQLLOGIN, VIEWCLUSTERMETADATA, VIEWDEBUG, EXTERNALIOIMPLICITACCESS, VIEWJOB, MODIFYSQLCLUSTERSETTING, REPLICATION, MANAGETENANT}

[knz]

Rafi, these system privileges are not per system table it seems? I was hoping to see things like "this table is accessible via this priv, this other table is accessible via this other priv" or something like that.

[rafiss]

The framework can be used to implement what you say. For example, for virtual table privileges, we have used the framework to implement per-table privileges.

In addition to @maryliag's request here, let's also keep in mind the one raised by @jordanlewis -- that being, we want to make sure engineers can access system tables in order to handle support escalations for Cloud clusters. For that, we may indeed want a single privilege that gives read-only access to all system tables.

[knz]

We discussed today that indeed the framework can help with the "troubleshooting access" question however we need to be careful about solving the access problem carefully:

• we need a mechanism that also prevents access to other objects, including careful consideration for public
• we need something that blocks indirect access to KV through built-in functions.

@rafiss agreed to explore this topic and come back with technical ideas before we continue the discussion.

[knz]

Coming back to the issue at top:

We do have use cases where we want to use system tables on the console (as read-only)

We discussed that internally. TLDR: while we may wish to build out system privileges to give access to system tables via SQL to, say, SREs or support engineers, they do not seem to be a viable solution to build a console on top of.

Why:

• it makes the front-end code responsible for data filtering. This is a bad security architecture; this means privileged data is leaving the cluster using user credentials . A malicious user with non-admin credentials could craft their own front-end code and see this confidential data. This choice would be blocked by our security auditors.
• it would rely on an extremely high degree of security hygiene on every engineer in the team. This is unrealistic - we'd like to keep the ability to assign projects to newly onboarded or less experienced staff, and we cant' expect that no mistake will ever be made.

[rafiss]

• sql: boostrap a user-controlled o11y db for SQL obs #109125 has been filed, and covers the o11y use case.
• sql: add VIEWSYSTEMTABLE system privilege #109474 has been merged and covers the on-call use case.

I will close this now, but let me know if it needs to stay open.