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:
-
Create the login role with a strong password:
CREATE ROLE workbench_monitor LOGIN PASSWORD 'use-a-strong-password'; -
Grant the
pg_monitorpredefined role, which conferspg_read_all_settings,pg_read_all_stats, andpg_stat_scan_tables:GRANT pg_monitor TO workbench_monitor; -
Grant
CONNECTon each database that the collector should probe, repeating the statement for every database:GRANT CONNECT ON DATABASE myapp TO workbench_monitor; -
If you want the metrics that the optional extensions provide, enable the extensions as described in Enabling Optional Extensions.
-
Add a
pg_hba.confentry 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_infoprobe callscurrent_setting('data_directory'), which raises a hard error withoutpg_read_all_settingsand so fails the whole probe. - Superuser-restricted settings are omitted from
pg_settingsentirely withoutpg_read_all_settings, rather than appearing with a NULLsetting; the view itself is readable by PUBLIC, so the rows simply are not returned. Thepg_settingsprobe runs a plainSELECT * 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 NULLsettingvalues in either case. - The
querycolumn and other cross-session columns ofpg_stat_activityare masked withoutpg_read_all_stats. - The
pg_stat_wal_receiverview is defined with aWHERE pid IS NOT NULLclause over a privileged function, so an unprivileged role sees zero rows rather than nulls; this affects both thepg_stat_replicationprobe and standby detection in thepg_node_roleprobe. - The wal sender columns of
pg_stat_replicationare masked withoutpg_read_all_stats. - Per-backend detail in
pg_stat_sslandpg_stat_gssapiis masked withoutpg_read_all_stats. - The
querytext of other users' entries inpg_stat_statementsreads as<insufficient privilege>withoutpg_read_all_stats. - The
pg_database_size()function requires eitherCONNECTon the target database orpg_read_all_stats, and thepg_databaseprobe sizes every row including templates, sopg_read_all_statsis 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_rulesandpg_ident_file_mappingsviews are revoked from PUBLIC and granted to no predefined role, so both probes return no data. Thepg_monitorrole does not cover them. - The
subconninfocolumn ofpg_subscriptionis superuser-only, although SELECT on every other column is granted to PUBLIC. Thepg_node_roleprobe 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_statsand 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
CONNECTon every non-template database wheredatallowconnis 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
datconnlimitof each database, the server'smax_connectionssetting, and the collector's ownpool.max_connections_per_serveroption.
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 thepg_stat_statementsprobe 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 thepg_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.