Skip to content

Preparing Monitored Servers

Every monitored connection stores the credentials of a PostgreSQL role that the Workbench uses to log in to the monitored instance. This page describes the privileges that role needs, recommends a least-privilege configuration, and explains the gaps that remain when the role is not a superuser.

The collector is strictly read-only on monitored instances. The probes read system catalogues, statistics views, and a small number of information functions; the collector never resets statistics, never terminates or cancels backends, and never reloads the server configuration. The collector writes everything it collects to the datastore, which uses its own separate credentials described in Configuring the Collector.

Creating a Monitoring Role

Before you add a server to the Workbench, create a dedicated PostgreSQL login role on the server for the collector to use. The role needs the pg_monitor predefined role and CONNECT on each database you want to monitor. Connect to each monitored server as a superuser and complete the following steps:

  1. Create the login role with a strong password:

    CREATE ROLE workbench_monitor LOGIN PASSWORD 'use-a-strong-password';
    
  2. Grant the pg_monitor predefined role, which confers pg_read_all_settings, pg_read_all_stats, and pg_stat_scan_tables:

    GRANT pg_monitor TO workbench_monitor;
    
  3. Grant CONNECT on each database that the collector should probe, repeating the statement for every database:

    GRANT CONNECT ON DATABASE myapp TO workbench_monitor;
    
  4. If you want the metrics that the optional extensions provide, enable the extensions as described in Enabling Optional Extensions.

  5. Add a pg_hba.conf entry that permits the new role to connect from the host running the collector, then reload the server configuration.

When you add the server in the Workbench, enter this role's name and password in the server definition, as described in Using the Workbench. The alerter needs no privileges on monitored servers, because the alerter reads only the datastore.

Understanding the pg_monitor Role

The pg_monitor predefined role grants the pg_read_all_settings, pg_read_all_stats, and pg_stat_scan_tables roles, which together let a role read server settings and statistics without superuser access. The following sections describe why the monitoring role needs pg_monitor, which privileges the collector does not need, and the gaps that remain without superuser access.

Why the Monitoring Role Needs pg_monitor

Several probes fail or silently lose data without pg_monitor, so treat the role as a requirement rather than a refinement. The most important dependencies include:

  • The pg_server_info probe calls current_setting('data_directory'), which raises a hard error without pg_read_all_settings and so fails the whole probe.
  • Superuser-restricted settings are omitted from pg_settings entirely without pg_read_all_settings, rather than appearing with a NULL setting; the view itself is readable by PUBLIC, so the rows simply are not returned. The pg_settings probe runs a plain SELECT * FROM pg_settings, so an unprivileged monitoring role silently collects a smaller set of settings with nothing to indicate the shortfall. Measured on PostgreSQL 18: 380 rows without the privilege against 403 with it, and no NULL setting values in either case.
  • The query column and other cross-session columns of pg_stat_activity are masked without pg_read_all_stats.
  • The pg_stat_wal_receiver view is defined with a WHERE pid IS NOT NULL clause over a privileged function, so an unprivileged role sees zero rows rather than nulls; this affects both the pg_stat_replication probe and standby detection in the pg_node_role probe.
  • The wal sender columns of pg_stat_replication are masked without pg_read_all_stats.
  • Per-backend detail in pg_stat_ssl and pg_stat_gssapi is masked without pg_read_all_stats.
  • The query text of other users' entries in pg_stat_statements reads as <insufficient privilege> without pg_read_all_stats.
  • The pg_database_size() function requires either CONNECT on the target database or pg_read_all_stats, and the pg_database probe sizes every row including templates, so pg_read_all_stats is the reliable answer.

The third role that pg_monitor confers, pg_stat_scan_tables, is not currently exercised by any probe, because the collector uses neither pgstattuple nor pg_buffercache. The role simply comes along with pg_monitor, so do not go looking for the feature that needs it.

Privileges the Collector Does Not Need

Two functions that look privileged need no grant at all. The pg_server_info probe calls pg_control_system() and the pg_node_role probe calls pg_control_checkpoint(); PostgreSQL leaves execution of both available to PUBLIC. Adding explicit grants for either function achieves nothing.

The collector also needs no access to the data in your tables. Table and index statistics come from catalogue views combined with pg_table_size() and pg_relation_size(), which report on relation storage rather than reading rows.

Gaps That Remain Without Superuser Access

A pg_monitor role leaves a small number of gaps, each of which degrades quietly rather than raising an error. Review the following list and decide whether the affected data matters in your environment.

  • The pg_hba_file_rules and pg_ident_file_mappings views are revoked from PUBLIC and granted to no predefined role, so both probes return no data. The pg_monitor role does not cover them.
  • The subconninfo column of pg_subscription is superuser-only, although SELECT on every other column is granted to PUBLIC. The pg_node_role probe reads that column to derive the publisher host and port, and degrades silently without access.
  • A database that the role cannot connect to is skipped, as described in Granting CONNECT on Each Database.
  • The system_stats and Spock probes return no data, and the collector records them as unavailable, when the corresponding extension is absent from the monitored server.

To close the authentication file gaps without granting superuser, grant both the view and the underlying function of the same name. The function grant is easy to overlook, and the view alone is not enough.

In the following example, the statements grant access to both authentication file views and their functions:

GRANT SELECT ON pg_hba_file_rules TO workbench_monitor;
GRANT EXECUTE ON FUNCTION pg_hba_file_rules() TO workbench_monitor;
GRANT SELECT ON pg_ident_file_mappings TO workbench_monitor;
GRANT EXECUTE ON FUNCTION pg_ident_file_mappings()
    TO workbench_monitor;

A superuser monitoring role covers every probe with no gaps at all, and that is the trade-off to weigh: complete coverage against least privilege. The shipped demonstration configuration at ai-dba-server.yaml monitors as the postgres superuser for the sake of simplicity, which suits a walkthrough rather than a production estate.

Reviewing the Privileges Each Probe Needs

The pg_monitor role covers most of the collection; a few probes need additional privileges, and a few need none. The Probe Reference lists the source views and columns that each probe reads. The following table shows the privilege each group of probes requires:

Probe or feature Required privilege
pg_server_info pg_read_all_settings, for data_directory
pg_settings pg_read_all_settings, or restricted rows are omitted
pg_stat_activity pg_read_all_stats, or other sessions are masked
pg_stat_replication pg_read_all_stats, for sender and receiver rows
pg_node_role pg_read_all_stats, plus superuser for subconninfo
pg_stat_connection_security pg_read_all_stats, for backend detail
pg_stat_statements pg_read_all_stats, or query text is hidden
pg_database pg_read_all_stats, or CONNECT on all databases
pg_hba_file_rules Grants on the view and function, or superuser
pg_ident_file_mappings Grants on the view and function, or superuser
The ten database-scoped probes CONNECT on each database probed
spock_exception_log, spock_resolutions USAGE and SELECT in spock
The ten pg_sys_* probes The system_stats extension, no grants
Remaining server-scoped probes No privilege beyond login

Extending Monitoring Coverage

The pg_monitor role grants read access to the statistics views and server settings that most probes query. The role does not grant CONNECT on individual databases, create the extensions that some probes read, enable compute_query_id, or grant access to the tables in the spock schema. The following sections describe each of those additional grants and settings.

Granting CONNECT on Each Database

Most probes run once for each monitored server, but ten database-scoped probes run separately in every database on the server, so the monitoring role needs CONNECT on each database that you want those probes to cover. The following table lists the database-scoped probes:

Probe Collects
pg_stat_database Database-wide activity, transaction, and I/O statistics.
pg_stat_database_conflicts Queries cancelled by recovery conflicts on a standby.
pg_stat_all_tables Table access, tuple, and vacuum statistics.
pg_stat_all_indexes Index scan statistics.
pg_statio_all_sequences Sequence I/O statistics.
pg_stat_user_functions User-defined function call statistics.
pg_extension The installed extensions and their versions.
pg_stat_statements Query execution statistics from the pg_stat_statements extension.
spock_exception_log Spock replication exceptions.
spock_resolutions Spock conflict resolutions.

The collector probes every database on the server that accepts connections and is not a template. The following query returns the same list of databases, so you can run it on a monitored server to see which databases the monitoring role needs CONNECT on:

SELECT datname
FROM pg_database
WHERE datallowconn = TRUE
  AND NOT datistemplate;

The collector then opens a separate connection to each database in that list, reusing the same stored credentials. This has three practical consequences:

  • The monitoring role needs CONNECT on every non-template database where datallowconn is true.
  • Each optional extension must be created separately in every database from which you want the extension's metrics.
  • The instance needs enough connection headroom for one collector connection per database, bounded by the datconnlimit of each database, the server's max_connections setting, and the collector's own pool.max_connections_per_server option.

The collector lists only the databases on which the role holds the CONNECT privilege, so a database without a CONNECT grant is left out without any log entry; collection continues for the other databases. A database that the collector lists but the server refuses is skipped with a logged error rather than a fatal one. A missing CONNECT grant is the most common misconfiguration and leaves no trace in the collector log, so after you add a monitored connection, confirm that metrics arrive for every database you expect.

Enabling Optional Extensions

Two optional extensions add metrics that PostgreSQL does not provide on its own. An extension is a per-database object, so create each extension in the databases where its probes run:

  • create pg_stat_statements, which adds query statistics, in every database that you want the collector to probe, because the pg_stat_statements probe runs in each database.
  • create system_stats, which adds operating system metrics such as CPU, memory, and disk usage, in the maintenance database named in the server definition, because the pg_sys_* probes run once for each server in that database.

The following table describes the optional extensions and the probes that depend on them:

Extension Probes Additional requirement
pg_stat_statements pg_stat_statements List pg_stat_statements in shared_preload_libraries and restart the server.
system_stats The ten pg_sys_* probes None.

In the following example, the statements create both extensions in the current database:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
CREATE EXTENSION IF NOT EXISTS system_stats;

The probes that rely on these extensions check for the relevant objects before running, and return no data rather than raising an error when an extension is absent. You can therefore leave both extensions out without disturbing the rest of the collection. The collector records such a probe as unavailable with the reason that the extension is not installed; a probe whose extension is installed but has nothing to report is recorded as available.

Enabling Query Identifiers

The optional compute_query_id setting improves what the Workbench can report without affecting whether collection succeeds. Turn on compute_query_id so that PostgreSQL publishes a query identifier in pg_stat_activity; first confirm that no extension in shared_preload_libraries computes query identifiers of its own, because PostgreSQL requires the setting to stay off when one does. The default value, auto, already computes identifiers when pg_stat_statements is loaded. The collector records that identifier with every activity sample, and the server uses it to name the client last seen running each statement in the Top Queries panel and on the query detail view. The attribution reads "Not observed" when no activity sample carries the statement's query identifier, which is always the case on a server that computes no query identifiers.

In the following example, the statement enables the setting for the whole instance; a reload of the configuration applies the change:

ALTER SYSTEM SET compute_query_id = 'on';
SELECT pg_reload_conf();

The auto default enables the identifier only when an extension such as pg_stat_statements asks for one, so an instance that already runs pg_stat_statements needs no change here.

Granting Access for Spock Metrics

Monitored instances that participate in a Spock cluster expose replication metadata in the spock schema. The collector reads five tables in that schema and calls no Spock functions at all.

In the following example, the statements grant the access the collector needs for Spock metrics:

GRANT USAGE ON SCHEMA spock TO workbench_monitor;
GRANT SELECT ON spock.exception_log TO workbench_monitor;
GRANT SELECT ON spock.resolutions TO workbench_monitor;
GRANT SELECT ON spock.local_node TO workbench_monitor;
GRANT SELECT ON spock.node TO workbench_monitor;
GRANT SELECT ON spock.subscription TO workbench_monitor;

The spock.exception_log and spock.resolutions tables are database-scoped, so grant access in every database that hosts Spock replication. Instances with no Spock installation need none of these grants.

Understanding AI and MCP Access to User Data

The MCP server connects to monitored instances with the same stored credentials that the collector uses, and several of its tools do read user data. The following table describes the tools that touch table data:

Tool Access to user data
query_database Executes read-only SQL supplied by the user, refusing write statements
execute_explain Runs EXPLAIN ANALYZE, which executes the query
test_query Runs EXPLAIN only, without executing the query
count_rows Runs SELECT COUNT(*) against a table
get_schema_info Reads schema metadata for a database
similarity_search Reads vector data from a table

If you want these features to work, the monitoring role additionally needs USAGE on the relevant user schemas and SELECT on the tables you are willing to expose. The collector alone needs neither.

Grant this access deliberately, because the read_write setting on a Workbench connection is an application-level gate rather than a database one. The privileges of the PostgreSQL role are the real backstop: a read_write grant in the Workbench achieves nothing unless the underlying role also holds write privileges, and granting the role write privileges widens what the AI features can do regardless of the Workbench setting. Treat the two as one security decision.