Clear ownership for ongoing database work.

Inspects active replication client slots, write lag bytes, and transaction age to prevent standby desynchronization and wraparound.

-- 1. Inspect physical and logical replication slot lag
SELECT slot_name, plugin, active,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal_bytes
FROM pg_replication_slots;

-- 2. Check transaction age toward autovacuum wraparound threshold
SELECT datname, age(datfrozenxid),
       current_setting(autovacuum_freeze_max_age)::int - age(datfrozenxid) AS tx_until_wraparound
FROM pg_database
ORDER BY age(datfrozenxid) DESC LIMIT 5;

Identifies all database roles holding superuser privileges, bypassrls attributes, or direct login access without password rotation.

-- 1. Identify roles with elevated administrative or superuser privileges
SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolconnlimit
FROM pg_roles
WHERE rolsuper = true OR rolcreaterole = true
ORDER BY rolname;

-- 2. Verify connection encryption status of active client sessions
SELECT pid, usename, client_addr, ssl, version, cipher
FROM pg_stat_ssl
JOIN pg_stat_activity ON pg_stat_ssl.pid = pg_stat_activity.pid
WHERE client_addr IS NOT NULL LIMIT 10;