PostgreSQL scram_iterations functions as per-password property rather than server setting

A technical deep dive into PostgreSQL's SCRAM-SHA-256 authentication mechanism reveals that the scram_iterations parameter behaves differently than standard Grand Unified Configuration (GUC) settings. While it appears to be a server-wide configuration, the iteration count is actually written into the individual password string stored in `pg_authid.rolpassword at the moment the password is set.

This architecture means that updating the scram_iterations value and reloading the server has no impact on existing user passwords. To increase the security of a specific role, the password must be explicitly reset to incorporate the new iteration count. This distinction is critical for database administrators managing security hardening or performance tuning for high-frequency connection environments.

Technical behavior of SCRAM iterations

• Storage Mechanism: The iteration count is embedded in the stored password string in the format SCRAM-SHA-256$count:<salt>$<StoredKey>:<ServerKey>, ensuring the count persists through pg_dumpall restores and pg_upgrade operations.
• Client-Side CPU Load: The iteration count represents a PBKDF2 workload performed by the client; higher counts increase the time required for the client to derive keys during every new connection.
• Server-Side Impact: Under standard SCRAM-SHA-256 authentication, the server performs minimal work during login, effectively offloading the computational cost of the iterations to the connecting client.
• Version Compatibility: Introduced in PostgreSQL 16, the parameter is marked as GUC_REPORT, allowing libpq to learn the session's current value, though older libpq versions will ignore the setting and default to 4096 iterations.
• Security Trade-offs: Higher iteration counts protect against offline brute-force attacks if the pg_authid table is leaked, but provide no additional security for long, random application passwords while increasing connection latency.

Why it matters

Database administrators must realize that changing this setting is not a global security upgrade for existing users; it requires a manual password rotation policy. For high-scale applications using connection patterns without a pooler, high iteration counts can significantly increase connection latency and client-side CPU usage.