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. For this reason, we are able to connect with minimal privileges, although some optional features require more permissions to work.

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.

Privileges Required for MySQL

The following are the recommended privileges for best results:

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

The privileges are used 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 are using the heartbeat table for replication monitoring, 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

Unless monitoring off-host, VividCortex doesn’t require any special privileges for PostgreSQL. A VividCortex user can be created like this:

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

However, some functionality does need SUPERUSER and will be disabled or constrained if monitoring user is created as NOSUPERUSER:

  • pg_stat_activity: requires SUPERUSER or helper functions.
  • lock metrics: requires SUPERUSER or helper functions.
  • explain / execution plans: requires SUPERUSER or explicit CONNECT/USAGE/SELECT privileges to every object it is allowed to inspect.

Off-Host Monitoring Privileges

To enable off-host monitoring, the pg_stat_statements extension for remote (non-TCP-capture) monitoring needs to be enabled for your server. To enable it, you need to add pg_stat_statements to the parameter shared_preload_libraries in the postgresql.conf file:

shared_preload_libraries = 'pg_stat_statements'

Monitoring via the pg_stat_statements extension requires SUPERUSER privileges (or rds_superuser if you’re using Amazon RDS) in order to view query text from other users. If your database only has one user issuing queries, you can optionally specify that user as the monitoring user for VividCortex. However, beware that in this configuration, queries from any users added in the future will not be captured!

For instructions on enabling pg_stat_statements on Amazon RDS please refer to the off-host installation page.

Monitoring Without SUPERUSER Privileges

Optionally, functions can be defined for the monitoring user’s use that allow it to monitor the server without SUPERUSER privileges.

Scripted

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
Manual

If running the pg-offhost-setup script is not an option for any reason, then you can always set it up manually by following the instructions in this section.

To configure the monitoring user without SUPERUSER privileges, 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.