pg_hba.conf Reload Not Effective for Existing Connections

Scenario

A developer’s credentials were compromised. The security team immediately updates pg_hba.conf to reject that user and calls SELECT pg_reload_conf(). They verify the rule is loaded via pg_hba_file_rules. But the developer’s existing sessions remain active and continue running queries — pg_stat_activity still shows them connected. The reload only affects new connection attempts, not already-established connections.

How to Identify

Conditions:

• pg_hba_file_rules shows the new restrictive rule loaded
• pg_stat_activity still shows sessions for the revoked user
• pg_reload_conf() returned true but sessions persist
• No error was returned — reload succeeded, but existing connections are unaffected

Analysis Steps

-- Verify the new HBA rule is loaded
SELECT line_num, type, database, user_name, address, auth_method
FROM pg_hba_file_rules
WHERE 'compromised_user' = ANY(user_name)
   OR 'all' = ANY(user_name)
ORDER BY line_num;

-- Check if compromised user still has active sessions
SELECT pid, usename, client_addr, state, backend_start,
       now() - backend_start AS session_age,
       query
FROM pg_stat_activity
WHERE usename = 'compromised_user'
ORDER BY backend_start;
-- Sessions here = still active despite HBA rule change

-- Check if the user's role is still enabled (separate from HBA):
SELECT rolname, rolcanlogin FROM pg_roles WHERE rolname = 'compromised_user';
-- rolcanlogin=true = role still able to log in (HBA change alone isn't enough)

-- Check locks held by compromised sessions:
SELECT l.pid, l.relation::regclass, l.mode, l.granted
FROM pg_locks l
WHERE l.pid IN (
    SELECT pid FROM pg_stat_activity WHERE usename = 'compromised_user'
);

Pitfalls

• pg_reload_conf() (or kill -HUP) applies to new connections only. Already-established connections are not re-authenticated.
• Even ALTER ROLE compromised_user NOLOGIN does not terminate existing sessions — it only prevents new logins.
• The only way to immediately cut off an existing session is pg_terminate_backend(pid).
• pg_cancel_backend(pid) cancels the current query but leaves the connection open — not sufficient for security incidents.
• A compromised role may have open transactions holding locks that block other operations. After terminating sessions, check for leftover lock/transaction state.

Resolution Approach

After updating pg_hba.conf and reloading: immediately run pg_terminate_backend() for all sessions of the compromised user. Also run ALTER ROLE ... NOLOGIN to prevent reconnection even if HBA rules are wrong. This is a two-step process: reload handles future connections, terminate handles existing ones.