Required Privileges

To monitor your database(s) with Database Performance Monitor, you will need to create a user for DPM 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 DPM 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 does 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.

You can optionally give the DPM user INSERT, UPDATE, and DELETE privileges, which will allow DPM to run EXPLAIN on those kinds of queries. We do not use these privileges for any purpose other than running EXPLAIN.

Privileges Required for PostgreSQL

The DPM 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 DPM 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, DPM can be configured to run without this privilege. To do this, DPM 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 DPM to monitor with.

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

For PostgreSQL v13 and above: create-stat-functions-v13.sql

For PostgreSQL v9.6 - v12: create-stat-functions-v96.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, DPM 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.

Privileges Required for SQL Server

The following are the required privileges for SQL Server and RDS SQL Server: CONNECT SQL, VIEW SERVER STATE, VIEW DATABASE STATE

The privileges are required for the following purposes:

  • CONNECT SQL is a standard SQL permission and is required to connect to SQL Servers.
  • VIEW SERVER STATE and VIEW DATABASE STATE grant access to Dynamic Management Views (sys.dm_*) and are required for polling, metrics, etc. Monitoring will not start without these priveleges.

To guarantee the needed privileges are granted, we recommend creating a new user for use with DPM by running our user creation script. This will create a user with the required privileges described above.

Run the user creation script

To use the user creation script, you will need admin access to the SQL Server.

  1. Download the SQL Server user creation script here: https://docs.vividcortex.com/mssql-create-user.ps1
  2. Save the script to an easily accessible place on the SQL Server you want to monitor.
  3. Open a PowerShell prompt and change path to where the script was saved.

  4. Run the following command and follow the onscreen instructions:

    .\mssql-create-user.ps1 -u [admin username] -dpmuser [username] -datasource localhost -database [databasename]

Privileges Required for Azure SQL

The following are the required privileges for Azure SQL DB and Azure SQL Managed Instance.

Azure SQL DB

For Azure SQL DB, you must use the DPM agent with an Azure SQL DB user who has the sysadmin role.

Azure SQL Managed Instance

CONNECT SQL is a standard SQL permission and is required to connect to an Azure SQL Managed Instance. * VIEW SERVER STATE and VIEW DATABASE STATE grant access to Dynamic Management Views (sys.dm_*) and are required for polling, metrics, etc. Monitoring will not start without these priveleges.

To guarantee the needed privileges are granted, we recommend creating a new user for use with DPM by running our user creation script. This will create a user with the required privileges described above.

Run the user creation script

To use the user creation script, you will need admin access to the Azure SQL Managed Instance.

  1. Download the user creation script here: https://docs.vividcortex.com/mssql-create-user.ps1
  2. Save the script to an easily accessible place on the Azure SQL Managed Instance you want to monitor.
  3. Open a PowerShell prompt and change path to where the script was saved.

  4. Run the following command and follow the onscreen instructions:

    .\mssql-create-user.ps1 -u [admin username] -dpmuser [username] -datasource localhost -database [databasename]

Privileges Required for Document DB

The following are the required privileges for DocumentDB: AmazonRDSReadOnlyAccess, CloudWatchReadOnlyAccess.

The privileges are required for the following purposes:

  • AmazonRDSReadOnlyAccess is required to access the DocumentDB cluster.
  • CloudWatchReadOnlyAccess is required to gather metrics for the cluster instances.

The DocumentDB user should also have the following privileges: clusterMonitor, readAnyDatabase.

These user privileges are required for the following purpose:

  • clusterMonitor and readAnyDatabase are required to read queries and gather metrics.

Supervisor User Permissions

Before the March 23, 2021 update, DPM must be run as root or another privileged operating system user because it must be able to use libpcap 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.

After the March 23, 2021 update, root access is now only needed to install the DPM agent. After installation with the --user parameter, DPM can run as a non-root user. See the On-host installation guide and Off-host installation guide for instructions.

Non-root user access permissions

Non-root users running installed agents (installed with the --user parameter) must have read, write, and execute permissions over the following folders on the agent machine:

  • /etc/vividcortex

  • /var/lock/vividcortex

  • /var/log/vividcortex

  • /var/run/vividcortex