VALID UNTIL password expiry versus client-certificate expiry for PostgreSQL role credentials

Direct answer

PostgreSQL does not provide a single configuration or SQL feature that makes VALID UNTIL and client-certificate expiry compose for one role. The two authentication paths are orthogonal: VALID UNTIL only blocks password-based logins (SCRAM‑SHA‑256, MD5), while certificate expiry is enforced by the TLS handshake and is unknown to PostgreSQL’s password verifier.

Can the two mechanisms act together?

No documented way exists to make them act together for a single role. If you need password expiry, you must rely on VALID UNTIL; if you need certificate-based expiry, you manage that through your PKI/TLS infrastructure. Choosing one mechanism per role—and documenting the choice—is the supported approach.

Restricting pg_hba.conf to honor VALID UNTIL

To prevent non‑password methods from bypassing the expiry check, pg_hba.conf must admit only password-auth methods (scram-sha-256, md5, or password). Example ordering:

# Password methods first; later rules must not allow cert/peer/trust
host    all             all             all               scram-sha-256
host    all             all             all               cert

Placing password rules before cert rules reduces the chance that a cert-authenticated session bypasses VALID UNTIL, but this restriction applies globally per database/user, not per‑role. Restricting to password‑only methods may lock out users who legitimately authenticate via client certificates for the same role.

Delegated recovery without superuser privileges

A role with an expired password cannot self‑reset because \password requires an already‑authenticated session. PostgreSQL does not expose certificate validity state in pg_authid or ALTER ROLE, so certificate renewal is an external PKI operation. If you grant CREATEROLE, a role can reset another role’s password, but only if it already has an authenticated session—and even then, the reset succeeds only if the target role’s VALID UNTIL has not already blocked the connection.

Delegated password reset without any authenticated session is not natively supported.

Safe verification steps

1. Create a test role with a past VALID UNTIL: CREATE ROLE test_role VALID UNTIL '2000-01-01';
2. Attempt password SCRAM login as that role; expect rejection if pg_hba.conf uses scram-sha-256.
3. Attempt client‑certificate login as that role with a valid cert; expect success if pg_hba.conf permits cert, because VALID UNTIL is ignored.
4. Check pg_hba.conf order and reload: sudo -u postgres psql -c 'SHOW hba_file;' && pg_ctl reload
5. Validate certificate dates: openssl x509 -in client.crt -noout -dates

Missing diagnostic detail: Which PostgreSQL version are you running, and does your deployment actually use client‑certificate authentication for the roles in question? The answer about pg_hba.conf restrictions and recovery options depends on the auth methods in active use.