Scenario
A monitoring system (Prometheus postgres_exporter, Datadog, or similar) has been connecting to PostgreSQL using a superuser account (postgres) for years. A security audit flags this as a critical vulnerability: if the monitoring system is compromised, the attacker has full database access. The DBA must create a minimal-privilege monitoring role that can read all statistics, execute health checks, and run pg_terminate_backend on runaway queries — without SUPERUSER.
How to Identify
Conditions:
pg_stat_activity shows usename = 'postgres' for monitoring connections- Monitoring DSN in application config contains
password=postgres_password
- The monitoring user has
rolsuper = true in pg_roles
- No dedicated monitoring role exists
- Monitoring user can
DROP TABLE, CREATE USER, ALTER SYSTEM — far beyond what monitoring needs
Analysis Steps
-- Find all connections and their role privileges:
SELECT a.pid, a.usename, r.rolsuper, r.rolinherit, r.rolcanlogin,
r.rolconnlimit, a.application_name, a.client_addr
FROM pg_stat_activity a
JOIN pg_roles r ON a.usename = r.rolname
ORDER BY r.rolsuper DESC, a.usename;
-- Check which roles have SUPERUSER:
SELECT rolname, rolsuper, rolcreaterole, rolcreatedb
FROM pg_roles
WHERE rolsuper = true OR rolcreaterole = true
ORDER BY rolname;
-- What can the monitoring user actually do?
-- (run as monitoring_user):
SELECT has_table_privilege('monitoring_user', 'pg_catalog.pg_class', 'SELECT') AS can_read_catalog;
SELECT has_function_privilege('monitoring_user', 'pg_stat_file(text)', 'EXECUTE');
Pitfalls
- SUPERUSER monitoring credentials are a critical security risk — they bypass all permission checks including RLS.
pg_monitor role (PG10+) grants visibility into pg_stat_activity, pg_stat_replication, pg_stat_bgwriter, etc. without SUPERUSER. Use it.pg_signal_backend allows pg_terminate_backend() and pg_cancel_backend() without SUPERUSER. Needed for monitoring that auto-kills long queries.- The
pg_monitor role does NOT grant: pg_read_all_data (table data), ALTER SYSTEM, CREATE USER. It is truly limited.
- Some monitoring tools (older Datadog agent versions) still require superuser for certain metrics. Check tool-specific docs for minimal privilege instructions.
SECURITY DEFINER helper functions can bridge the gap when a monitoring tool needs to call a privileged function without full SUPERUSER.
Resolution Approach
Create a dedicated monitoring role with pg_monitor + pg_signal_backend grants. Connect monitoring tools with this role. Revoke or rename the superuser account from monitoring configs. Optionally create SECURITY DEFINER wrapper functions for any privileged calls the monitoring tool requires.