Question
Native VALID UNTIL vs External IdP for Credential Expiration
Nia Finch
0 reputation · 15 Sept 2023, 04:28 UTC
93K views
Loading…
0 reputation · 15 Sept 2023, 04:28 UTC
Implementing a least-privilege security model in PostgreSQL often requires strict control over credential lifespans to mitigate the risk of stale accounts. There are two primary documented paths for enforcing expiration: using the native VALID UNTIL attribute within CREATE ROLE or delegating authentication to an external Identity Provider (IdP) via GSSAPI or LDAP.
The native VALID UNTIL clause provides database-level autonomy and requires no external infrastructure, but it lacks built-in notification systems or grace periods for users. Conversely, external IdP integration centralizes governance and rotation policies but introduces a dependency on network availability and directory service configuration.
A critical consideration is that the VALID UNTIL attribute specifically targets password-based authentication and may be bypassed depending on the pg_hba.conf configuration (e.g., using trust or certificate-based methods).
VALID UNTIL attribute offer sufficient granularity for environments requiring automated credential rotation without external orchestration?31775 reputation · 15 Sept 2023, 15:00 UTC
For environments that mix authentication methods (password, GSSAPI, LDAP, certificates), an external Identity Provider gives more reliable enforcement of least‑privilege credential expiration because the native VALID UNTIL attribute only applies to password‑based logins and can be bypassed by other pg_hba.conf methods.
If your deployment uses password authentication exclusively, VALID UNTIL can support automated rotation without external orchestration, but you must manage the rotation yourself (e.g., a cron job that updates the role’s expiration date).
postgresqlVALID UNTIL column is checked only when the authentication method relies on a password (md5, scram‑sha‑256, etc.).When multiple auth methods are present, a role may log in via a method that ignores VALID UNTIL (e.g., trust or certificate). In that case the expiration attribute offers no protection, so relying on it alone creates a gap. An IdP centralizes the policy and enforces it regardless of how the user authenticates, provided the IdP is consulted for each request.
pg_hba.conf and list the authentication methods used for each host/user combination.VALID UNTIL; implement a scheduled task that updates the role’s expiration date as part of your rotation process.VALID UNTIL with IdP checks to close the gap.To confirm whether VALID UNTIL alone is sufficient, please share the proportion of authentication lines that are password‑based versus other methods in your pg_hba.conf. This detail changes the recommendation.
Use comments to ask for clarification. Post a solution as an answer.
31,775 reputation · 15 Sept 2023, 09:16 UTC
When pg_hba.conf employs trust authentication, the role's VALID UNTIL attribute is completely ignored; connections succeed regardless of the expiration date. This circumvention risk means least-privilege policies relying solely on VALID UNTIL can be bypassed if any user path uses a non-password method. A practical verification step is to query pg_authid.rolvaliduntil and connect as the role via each configured authentication method to observe whether the server rejects the session after the stated date.