sql: expose metrics in internal tables for monitoring

[tim-o]

Request from user @ikzelf on the forum

Currently, CRDB only allows root access to crdb_internal. Root cannot grant permissions to other users. Ronald can comment further, but this is currently preventing him from implementing a monitoring solution using SQL, which he's implemented for Postgres, Mysql, and other databases.

Jira issue: CRDB-4870

[tim-o]

Tagging as pgcompat for now, since that's a relevant fit - feel free to retag if there's a better home.

[ikzelf]

Ab other option would be to add a specific user for monitoring purposes. That user should be able to connect and read the tables/views required to be able to figure out the database structure, sizes, performance metrics and parameters. Maybe it should be able to describe tables, perform explain plans but it should not be able to read user data.

[ikzelf]

Any news on this? In our current world, where privacy concerns are getting more attention, it would be a smart move to allow for definition of users that can perform their tasks without automatically having access to user data. For monitoring we don't need to read the user tables, as long as we can explain the plans. For backup/recovery we don't need to read the user tables, as long as we can recover the database...

[knz]

@ikzelf there are two separate issues:

1. user grants on virtual tables -- today this infrastructure does not exist in CockroachDB and would need to be added. This will not happen in 19.1 and will be considered for 19.2

2. all of crdb_internal is considered as experimental and subject to change without notice and is thus unsuitable to build external tools upon (unless you're OK with your tools breaking between crdb minor versions!).

Regarding point 2 - this is the main reason why we are not willing to support you on this issue. However, what we can do is the following:

1. you tell us what you are monitoring and why
2. we build a public interface that exposes the information you need and that's not experimental
3. we ensure that the data is available for non-root users.

How does this sound?

[ikzelf]

That sounds good, great.

Things I would like to be able to monitor is

1. sizing of the various files for all databases on all nodes
2. size limits for all those files on all nodes (probably filesystem sizes)
3. actual used bytes in all those files on all nodes
4. memory usage and limits on all nodes
5. number of sessions per dbuser per node
6. parameters in use per node
7. age and volume of last backup (if possible at all)
8. node status/availability
9. sort/tempdb usage

This all is mostly aiming on resource and availability monitoring/alerting

[knz]

@piyush-singh @awoods187 @rolandcrosby can you capture the above requirements in the telemetry roadmap and cross-check what we already have in prometheus. I think the following should happen:

• for all the things we already have in prometheus exports, ensure the metrics are documented/discoverable
• for all the new things, plan adding metrics accordingly.

I think all of these can be put in metrics (i.e. not sql telemetry).

[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
5 days to keep the issue queue tidy. Thank you for your contribution
to CockroachDB!

[rafiss]

v22.2 will support grants on virtual tables. I've renamed the issue to capture the request for being able to do monitoring by referencing internal tables, and moving this to the SQL Observability team.