The following topic outlines the required views, tables, and functions needed to properly monitor each PostgreSQL database resource.

  • PostgreSQL Required Views, Tables, and Functions
  • Creating a Least-Privileged User (LPU)

PostgreSQL Required Views, Tables, and Functions

The table below lists PostgreSQL database resource kinds and their required views, tables, or functions needed to monitor each resource correctly. Prior to configuring an adapter instance for the Management Pack, ensure the following PostgreSQL function/query functionality is added for your PostgreSQL resources.

Note: To help automate the assignment of the following permissions, you can run the script provided in Creating a Least-Privileged User (LPU).
Resource Type View/Table/Function Name Required? Version
Instance pg_stat_bgwriter Yes 9.0+
Database pg_stat_database Yes 9.0+
Database pg_database Yes 9.0+
Database pg_stat_database_conflicts Yes 9.1+
Database pg_tablespace Yes 9.0+
Tablespace* pg_user* Yes 9.0+
Table pg_stat_user_tables No 9.0+
Table pg_statio_user_tables No 9.0+
Table pg_tables No 9.0+
Trigger pg_trigger No 9.1+
Index pg_stat_user_indexes No 9.0+
Index pg_statio_user_indexes No 9.0+
Function pg_stat_user_functions No 9.0+
Sequence pg_statio_all_sequences Yes 9.0+
Session pg_stat_activity Yes 9.2+
Query* pg_stat_statements* Yes 9.0+
Note: *NOTE: For security, an administrative-level monitoring user (i.e., “super user”) is required to view the SQL text or queryid of queries executed by other users. An “insufficient privileges” error will be returned in the Query text field if a read-only user is used. Tablespace data also requires an administrative-level monitoring user. However, using our defined LPU script will allow you to bypass assigning the "super user" attribute. See: Creating a Least-Privileged User (LPU).

Function Tracking: To track functions, ‘pg_stat_user_functions’ requires the log_statement_stats field to be modified (in postgresql.conf) to allow for any function tracking to happen.

Reference: http://www.postgresql.org/docs/current/static/runtime-config-statistics.html#GUC-TRACK-FUNCTIONS

Query Tracking: To track queries, these settings need to be added to the shared_preload_libraries settings:

shared_preload_libraries = ‘pg_stat_statements’

pg_stat_statements.max = 10000

pg_stat_statements.track = all

You also need to create the extension in the database. The extension is database bound and must be created for each database, even though it pulls data from each:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements

You can check if it is installed on the database by calling:

SELECT *

FROM pg_available_extensions

WHERE name = ‘pg_stat_statements’ AND installed_version IS NOT NULL

Creating a Least-Privileged User (LPU)

To help automate the creation of a least-privileged user (LPU) for your PostgreSQL instance, run the following script on the database you plan to monitor. If you're monitoring multiple databases, run the script on the postgres database.

CREATE SCHEMA bluemedora;
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
CREATE OR REPLACE FUNCTION bluemedora.pg_stat_statements() RETURNS SETOF pg_stat_statements AS
$$
SELECT * FROM public.pg_stat_statements;
$$ LANGUAGE sql VOLATILE SECURITY DEFINER;

CREATE OR REPLACE FUNCTION public.explain_this (
      l_query text,
      out explain json
    )
RETURNS SETOF json AS
$$
BEGIN
  RETURN QUERY EXECUTE 'explain (format json) ' || l_query;
END;
$$
LANGUAGE 'plpgsql'
VOLATILE
RETURNS NULL ON NULL INPUT
SECURITY DEFINER
COST 100 ROWS 1000;

CREATE USER lpu WITH PASSWORD 'tmppassword';
GRANT SELECT ON pg_database TO lpu;
GRANT SELECT ON pg_stat_bgwriter TO lpu;
GRANT SELECT ON pg_stat_database TO lpu;
GRANT SELECT ON pg_stat_user_indexes TO lpu;
GRANT SELECT ON pg_stat_user_tables TO lpu;
GRANT SELECT ON pg_statio_all_sequences TO lpu;
GRANT SELECT ON pg_statio_user_indexes TO lpu;
GRANT SELECT ON pg_statio_user_tables TO lpu;
GRANT SELECT ON pg_tables TO lpu;
GRANT SELECT ON pg_tablespace TO lpu;
GRANT SELECT ON pg_user TO lpu;
GRANT SELECT ON pg_stat_replication TO lpu;
GRANT SELECT ON pg_stat_database_conflicts TO lpu;
GRANT SELECT ON pg_trigger TO lpu;
GRANT SELECT ON pg_stat_activity TO lpu;
GRANT SELECT ON pg_stat_statements TO lpu;
GRANT USAGE ON SCHEMA bluemedora TO lpu;
GRANT EXECUTE ON FUNCTION public.explain_this(l_query text, out explain text) TO lpu;
Note: PostgreSQL databases previous to 9.2 may need to edit the script above.

Once complete, you can ensure the user has the EXPLAIN privilege by running:

SELECT exists( SELECT 1 FROM information_schema.role_routine_grants WHERE routine_name='explain_this' AND privilege_type='EXECUTE' AND grantee='explainer');

This query should return a single row with a single column of "exists" with a value of "true".

Note: If the user has permissions to read from your tables and views, they do not require this special EXPLAIN permission.