Required Privileges

To monitor your database(s) with VividCortex, you will need to create a user for VividCortex to connect with. This page describes the necessary privileges that user must have, along with some example statements to create a user with the correct privileges.

You must tell VividCortex what credentials to use to connect. You can examine the credentials you have 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.
  • 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

The VividCortex user requires either SUPERUSER or rds_superuser (for Amazon RDS).

For non-RDS PostgreSQL, the following can be used to create a user:

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

For PostgreSQL for Amazon RDS, you can use the following three statements:

CREATE ROLE vividcortex NOCREATEDB NOCREATEROLE INHERIT LOGIN PASSWORD '<password here>';
GRANT rds_superuser TO vividcortex;
GRANT ALL privileges ON ALL TABLES IN SCHEMA public to rds_superuser;

The SUPERUSER privilege is required in order to:

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

SUPERUSER/rds_superuser is also required for remote monitoring 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 monitoring with VividCortex is by running our pg-offhost-setup shell script. The script will automatically create the required pg_stat tables, a user named vividcortex to be used for monitoring (with the correct privileges), and if run with the --unprivileged option, the necessary monitoring functions described in the next section.

It requires the PostgreSQL interactive terminal, psql, and privileges to access your database as SUPERUSER or rds_superuser. Note that psql will use the credentials configured in the following environment variables or your ~/.pgpass file, in that order of precedence.

PGUSER - the user with SUPERUSER or rds_superuser privileges; used for setup only
PGPASSWORD - the password for that user
PGHOST - the address of the database to connect to
PGDATABASE - the database at that address to connect to (if required for authentication)
PGPORT - the port to connect to

You will also need to specify an environment variable VCPASSWORD which will be the password of the new vividcortex user. If you are setting up local (on-host) monitoring, use the --on-host flag.

Run it as follows to set up a VividCortex user with SUPERUSER or rds_superuser privileges:

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

Run it as follows to set up a VividCortex user WITHOUT SUPERUSER or rds_superuser privileges:

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

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

wget https://docs.vividcortex.com/pg-offhost-setup
sh pg-offhost-setup
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 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, recommended) 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;

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.

Supervisor User Permissions

VividCortex must be run as root or another privileged operating system user because it 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.