Creating a Monitoring User

Why a dedicated user?

We recommend creating a dedicated PostgreSQL user for dbaBrain. This provides:

• -Least-privilege access - only monitoring permissions, no data access
• -Audit trail - easy to identify dbaBrain queries in pg_stat_activity
• -Security - separate credentials that can be rotated independently
• -Compliance - clear separation of monitoring from application access

Standard Setup (PostgreSQL 10+)

sql
-- Create the user
CREATE USER dbabrain_monitor WITH PASSWORD 'generate-a-secure-password-here';

-- Grant the pg_monitor role (read-only access to stats)
GRANT pg_monitor TO dbabrain_monitor;

-- Grant schema access
GRANT USAGE ON SCHEMA public TO dbabrain_monitor;

-- For multiple schemas
GRANT USAGE ON SCHEMA schema1, schema2 TO dbabrain_monitor;

Enable pg_stat_statements

sql
-- Add to postgresql.conf (requires restart)
-- shared_preload_libraries = 'pg_stat_statements'

-- Or for cloud managed databases, enable via parameter group

-- Then create the extension
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Verify it works
SELECT count(*) FROM pg_stat_statements;

Enable I/O Timing

sql
-- Enable disk I/O timing (no restart required)
ALTER SYSTEM SET track_io_timing = 'on';
SELECT pg_reload_conf();

-- Verify
SHOW track_io_timing;

Verify Permissions

Connect as the monitoring user and verify access:

sql
-- Should return rows
SELECT count(*) FROM pg_stat_activity;
SELECT count(*) FROM pg_stat_user_tables;
SELECT count(*) FROM pg_stat_statements;

-- Should return server settings
SELECT name, setting FROM pg_settings LIMIT 5;

-- Should return replication info (if applicable)
SELECT * FROM pg_stat_replication;