PostgreSQL Privileged Roles and Insecure pg_hba Rules
Lists superusers and high-privilege roles plus 'trust' or weak authentication rules. A quick start for security audits and hardening.
SELECT rolname, rolsuper, rolcreaterole, rolcreatedb, rolreplication, rolbypassrls, rolcanlogin, rolvaliduntil
FROM pg_roles
WHERE rolsuper OR rolcreaterole OR rolreplication OR rolbypassrls
ORDER BY rolsuper DESC, rolname;
SELECT line_number, type, database, user_name, address, auth_method
FROM pg_hba_file_rules
WHERE auth_method IN ('trust','password','md5')
ORDER BY line_number;Review `trust` or overly broad CIDR rules (`0.0.0.0/0`) in `pg_hba.conf` and restrict superuser privileges.
- This script is designed for read-only diagnostics; it does not alter schema or modify data directly.
- Prior to executing on production systems under high load, verify the target database version (Min. Version: 10).
- Exercise caution with mutating actions (e.g., terminating sessions or dropping indexes) referenced in the notes section.
Other POSTGRESQL Diagnostic Scripts
View All (100)Find Long-Running Queries and Idle-in-Transaction Sessions in PostgreSQL
Lists queries running longer than a minute, their wait events, and idle-in-transaction sessions that block vacuum. The first script to run during a performance incident.
PostgreSQL Lock Chain: Who Is Blocking Whom?
Uses pg_blocking_pids to pair blocked and blocking sessions with their queries and wait times. Shows the root cause of a lock chain in seconds.
PostgreSQL Dead Tuples, Bloat and Autovacuum Health Check
Shows dead tuple ratio and last vacuum/analyze times per table. Helps you find tables where autovacuum settings are not keeping up.
Find Unused Indexes in PostgreSQL
Lists never-scanned indexes that are neither unique nor primary keys, with their sizes. Surfaces cleanup candidates to cut write overhead and disk usage.