Required Privileges

VividCortex is designed to run with the fewest possible privileges. The agent does not make any changes to your database servers – it does not enable or disable logging, for example.

In all of the following examples we assume you have created the VividCortex agent user in the database as vividcortex with permission to connect from any host. If you created a different user, then you need to modify the examples accordingly.

You must tell VividCortex what credentials to use to connect. You can examine the credentials you’ve specified by navigating to the Inventory page and clicking the ‘gear’ icon next to the host whose credentials you want to check.

Select your database technology to view the required privileges (Redis and Cassandra do not require any special privileges in order to monitor):

Privileges Required for MySQL

The following are the required privileges for MySQL:

GRANT SELECT, PROCESS, SHOW VIEW, REPLICATION CLIENT ON *.* TO vividcortex@'%';

The privileges are required for the following purposes:

  • SELECT ON *.* is necessary for running EXPLAIN to capture execution plans for query samples. In the case of off-host monitoring, we also need SELECT privileges to retrieve query information from the PERFORMANCE_SCHEMA.
  • PROCESS ON *.* is necessary for running SHOW PROCESSLIST, SHOW ENGINE INNODB STATUS, and equivalent statements against INFORMATION_SCHEMA and PERFORMANCE_SCHEMA tables. (The exact queries are version-dependent, and we are sensitive to the performance impact of some of these queries. We run them only in cases such as when the server is stalled already and it’s important to capture this data to prevent future stalls; we don’t run them continually.)
  • SHOW VIEW is necessary for running EXPLAIN on queries which use a view.
  • REPLICATION CLIENT ON *.* is necessary to see replication failures and failure errors.

If you have SELECT ... INTO OUTFILE statements, the FILE privilege is required in order to EXPLAIN those queries.

If you are using the heartbeat table for replication monitoring, you will also need the following:

GRANT SELECT, INSERT, UPDATE ON vividcortex.heartbeat TO vividcortex@'%';

The SELECT, INSERT, UPDATE privileges are necessary to write and read the sample heartbeat rows which are used to reconstruct the replication topology.

Privileges Required for PostgreSQL

For PostgreSQL, a VividCortex user requires the following privileges and can be created like this:

CREATE ROLE vividcortex SUPERUSER NOCREATEDB NOCREATEROLE INHERIT LOGIN PASSWORD '<password here>';

The SUPERUSER privilege is required in order to:

  • fetch data from pg_stat_activity
  • show EXPLAIN / execution plans
  • show lock metrics

Additionally, SUPERUSER (or rds_superuser for Amazon RDS) is required when monitoring in the off-host configuration, because the pg_stat_statements extension requires this privilege in order to view query text for all users. (For instructions on enabling pg_stat_statements please refer to the off-host installation page).

The easiest way to prepare PostgreSQL for an off-host configuration with VividCortex is running the pg-offhost-setup shell script. It requires PostgreSQL interactive terminal, psql, and privileges to access your database as SUPERUSER or rds_superuser. Note that psql will use the credentials configured in your ~/.pgpass file and PGUSER/PGPASSWORD/PGDATABASE/PGPORT environment variables.

Run it without any command-line arguments to get help:

wget https://docs.vividcortex.com/pg-offhost-setup
sh pg-offhost-setup

The script will automatically create the required pg_stat tables, a user named vividcortex to be used for monitoring, and if run with the --unprivileged option, the necessary monitoring functions described in the next section.

Monitoring PostgreSQL Without SUPERUSER

While we STRONGLY recommend monitoring with a user configured as SUPERUSER or rds_superuser as this will provide the easiest installation, the most complete data, easiest EXPLAIN functionality, and allow for configuration-free upgrades, VividCortex can be configured to run without this privilege. To do this, VividCortex can utilize a set of automatically-created monitoring tables which we will query instead of the pg_stat tables directly.

Note: If you do not grant the monitoring user the SUPERUSER role and want to capture EXPLAIN information, you will need to grant explicit CONNECT/USAGE/SELECT privileges to every object.

If you ran the pg-offhost-setup script (above) with the --unprivileged option, these monitoring tables have been created automatically. These tables are not required if you created a privileged (i.e. SUPERUSER) user for VividCortex to monitor with.

To use this option, execute the commands defined in the appropriate script below with a SUPERUSER authorized user:

For PostgreSQL v9.6 and higher: create-stat-functions-v96.sql

For PostgreSQL v9.2 - v9.5: create-stat-functions-v92.sql

For PostgreSQL v9.0 - v9.1: create-stat-functions-v90.sql

For PostgreSQL v8.4: create-stat-functions-v84.sql

The above scripts will define a vividcortex schema and create the necessary monitoring functions. Once you have created the schema, grant your monitoring user access to the schema with the following command:

GRANT USAGE ON SCHEMA vividcortex TO <your monitoring user here>;

Note that if your monitoring user is not named vividcortex, you need to add the vividcortex schema to the user’s search path:

ALTER ROLE <your monitoring user here> SET search_path TO vividcortex,public;
Agent Actions

The agent does the following:

  • SHOW ALL to watch for configuration changes.
  • SELECT ... FROM pg_stat_statements to monitor PostgreSQL queries on hosts that are not local
  • SELECT ... FROM pg_stat_database and SELECT ... FROM pg_stat_bgwriter and SELECT ... FROM pg_stat_activity to capture various status metrics exposed by PostgreSQL.
  • The agent will attempt to EXPLAIN some samples of queries periodically.

Privileges Required for MongoDB

For MongoDB, VividCortex requires a user with the clusterMonitor and readAnyDatabase roles.

Here’s how you’d create an appropriate user from MongoDB’s console client. Use the admin database and create the user with the clusterMonitor and readAnyDatabase roles, like this:

use admin
db.createUser(
   {
     user: "vividcortex",
     pwd: "vividcortex",
     roles: [ "clusterMonitor", "readAnyDatabase" ]
   }
)

The roles are used for the following purposes:

  • clusterMonitor is required for running commands like serverStatus, replSetGetStatus, and currentOp, as well as fetching database/collection stats for the instance.
  • readAnyDatabase is required for fetching query plans for operations, doing index analysis, and retrieving collection sizes.

Agent User Permissions

The agent must be run as root or another privileged operating system user, because they must be able to use libpcap or WinPcap to capture packets from network interfaces. Some per-process data captured from e.g. the /proc filesystem in Linux, or from system calls in FreeBSD, also requires root privileges to observe.