Prepare PostgreSQL
Network Access and Monitoring Role
Allow connections from SA Data Collector to each PostgreSQL instance. Create a dedicated role without superuser privileges:
CREATE ROLE sm_monitor LOGIN PASSWORD '<пароль>';
GRANT pg_monitor TO sm_monitor;
Verify that the role can query pg_stat_activity on each node. Do not use an application owner account or a superuser account.
Tier 1 Extensions
To collect query statistics, add pg_stat_statements to shared_preload_libraries, restart PostgreSQL, and create the extension in the monitored database:
CREATE EXTENSION pg_stat_statements;
For bloat analysis, create pgstattuple. For wait profiling, install the third-party pg_wait_sampling extension. Setting track_io_timing = on and compute_query_id = on or auto increases the range of available signals.
Logs and pgaudit
Logging and auditing are supported for PostgreSQL 15-17. Configure:
logging_collector = on
log_destination = 'jsonlog'
For auditing, use the ddl, role profile and object auditing through a marker role:
CREATE ROLE sm_auditor NOLOGIN;
GRANT SELECT, INSERT, UPDATE, DELETE ON <объект> TO sm_auditor;
pgaudit.role = 'sm_auditor'
pgaudit.log = 'ddl, role'
pgaudit.log_parameter = off
pgaudit.log_catalog = off
pgaudit.log_relation = on
pgaudit.log_statement_once = on
Keep pgaudit.log_parameter disabled. Otherwise, query parameter values and application table contents may appear in the log.
If Patroni manages the cluster, change PostgreSQL settings through DCS, not only in postgresql.conf. After making changes, compare the Patroni configuration with the actual pg_settings values on each node.
TLS
Secure four segments of the data path:
| Segment | Requirement |
|---|---|
Smart Monitor Data Collector - PostgreSQL | ssl = on, a node certificate, and sslmode=verify-full |
Smart Monitor Data Collector - Patroni REST | HTTPS and root certificate validation |
Smart Monitor Data Collector - PgBouncer | client_tls_sslmode = require |
Smart Beat - Smart Monitor Data Collector | TLS on the beats input and certificate validation by the agent |
The PostgreSQL certificate must include the connection address in the Subject Alternative Name. Use the installation's certificate authority.
Switching Patroni REST to HTTPS affects load balancers, patronictl, and external monitoring systems. Update their configurations during the same maintenance window. Otherwise, state reads may continue to work while switchover operations or health checks fail.