Which db user to use for monitoring purposes?


(Ronald) #1

Hi,
does a db user exist that is better suited for monitoring than the root user?
Ideally there would be a user that has access to all metadata and performance data of the cluster but preferably not to the user data. It’s OK if that user could see the objects but not the contents of user tables.


(Nikhil Benesch) #2

What sort of metadata and performance data are you asking about? If you mean the internal tables in the system database, you can use the GRANT statement to give a non-root user read-only access.


(Ronald) #3

Mostly things like storage, free space in all databases, connections per database, status for all databases, status of objects in the databases, locking issues, uptime, node availability…
Don’t I need enterprise edition for the grants?


(Tim O'Brien) #4

Enterprise is not required for the grants unless you want to manage the grants by creating a role.


(Ronald) #5

Hi @benesch, @tim-o,

for monitoring it is smart to know how the cluster is constructed so tables like gossip_nodes are needed to find which nodes/instances are part of the cluster, when they are started and if they are alive at all.
When quering those using a none root user (cistats) I get:
Db execution error: only superusers are allowed to read crdb_internal.gossip_nodes

when I try “grant select on table crdb_internal.gossip_nodes to cistats;”
I get: [Code: 0, SQL State: XX000] ERROR: user root does not have GRANT privilege on relation gossip_nodes

So it looks like I am kind of stuck here. It would be very nice to be able to use a user that has read/select privileges on the internal structures, without having access to the contents of the user[s] tables.

I think the root user has too much power for monitoring.

(using v2.1.0-beta.20180827)


(Tim O'Brien) #6

Hey @ik_zelf,

What specific info are you looking to monitor? We do expose a fair amount of the info from the internal tables in the monitoring endpoints. For instance, the info from crdb_internal.gossip_nodes can be found on <adminurl>/_status/nodes.

We can create a product enhancement to accommodate, but before we create a third way of getting monitoring data, I wanted to understand what’s missing from the current endpoints (or why the endpoints won’t serve your needs).

Let me know your thoughts.


(Ronald) #7

Hi @Tim-o,

point is we do database monitoring using SQL. We do this for all kinds of databases like Oracle, postgres, mysql, ms$sql and are now adding cockroachDB to the mix. Our primary focus is on capacity and availability monitoring but this is easily expandable to more application/site specific monitoring. Just take a look at zbxdb, a zabbix SQL database plugin
Using SQL te monitor enables us to shape the info into the format we need, so we can re-use existing templates in which the alerting is configured.

Now that er are on to it … it would be a lot easier if the data in cluster settings table would be in simple units instead of formatted user friendly text.
Like cluster_settings: kv.raft.command.max_size 64 MiB … this would be a lot more usable if this kind of things was stored as just a number and a unit…
Yes, we also track changes in settings because it can have an influence on the performance and the availability if the cluster.
The same is for metrics but as far as I can see, in metrics the measurements are in simple units. It’s just not always clear what the unit is…


(Tim O'Brien) #8

Gotcha. Created a feature request here for tracking: https://github.com/cockroachdb/cockroach/issues/29396

Feel free to create a second request for the usability of cluster settings - I do think that would be valuable for others as well!


(Ronald) #10

Hi @tim-o,

any news on this?


(Tim O'Brien) #11

Hey @ik_zelf - this is not slated for our next major release, 19.1. The github issue is the most up to date record; I’d recommend pinging there if you’d like to request it be prioritized. There hasn’t been a ton of chatter or requests from others.


(Ronald) #12

That is a pity. I think that having some dedicated users for specific tasks can be very beneficent for a database, sure when taking GDPR into account. Now those tasks have to be done by the root user. Think about monitoring, backup, recovery …
It is far better to have users in place that can do that task and not have access to the user data. …


(Piyush Singh) #13

Hey @ik_zelf, we’re exploring initiatives internally to help with developer tooling based on CRDB and I think this issue falls directly under that umbrella. Our initiative will bring together teams from different areas within Cockroach Labs to help improve the developer experience in these kinds of scenarios. We’ll be discussing a few areas in which we can make improvements, and as the PM for the monitoring product area for CRDB, I’ll be bringing this issue up specifically.

To be transparent and realistic about timelines, the soonest this will be addressed will be in the fall of this year (our 19.2 release), as this task group will start meeting after our spring release (19.1). When our initial discussion happens, I will update you on expectations here.


(Ronald) #14

I think that is a good idea. It would also make a nice improvement compared to postgres where you need to be a super user to be able to see anything. As privacy gets more and more attention, I think it is important to make it possible to do as many tasks as possible with accounts that can do the job, without having access to the user data. As a dba, I don’t need access to the data, as long as I can see the statistics of the tables and columns. For monitoring I need to be able to see the configuration, parameters and statistics of the database but the user data … it is better not to see that. Ofcourse, for application monitoring it might be needed to be able to read user data but that is a different piece of cake.