Hello all, Following the discussion regarding how to enforce rolvaliduntil for users within an active session, I have implemented a solution that uses the pg_authid SysCache listener mechanism as suggested. Please find the attached patch for review.
Below is use case details for same User3 started session edb@localhost:~$ psql -U user3 -d postgres psql (19devel) Type "help" for help. postgres=> \d Did not find any relations. postgres=> \d *<= prior to this command, password was expired in another session by super user as shown below and it reflected immediately in active session (prior active session was not impacted)* FATAL: Connection expired due to internal password policy enforcement DETAIL: User's password expired at 2025-11-02 16:59:37.462644+05:30. HINT: Reconnect with a renewed password or obtain new authorization. Before executing second \d command below super user session expired the password of user3 as below edb@localhost:~/Downloads/pg/postgres$ psql -d postgres psql (19devel) Type "help" for help. postgres=# ALTER USER user3 VALID UNTIL '2025-11-02 16:59:37.462644+05:30'; ALTER ROLE Thanks, Ajit Awekar On Fri, 28 Nov 2025 at 23:22, Hannu Krosing <[email protected]> wrote: > Also have not looked at the patch, but we should also make sure that > there is not just be GoAway, but also a way to re-authenticate or > "extend lease" or whatever the terminology is for a specific > authentication method. > > So maybe the message should be ReAuthentiocateOrElse" ? > > On Fri, Nov 28, 2025 at 6:19 PM Jelte Fennema-Nio <[email protected]> > wrote: > > > > On Fri, Nov 28, 2025, 04:39 Ajit Awekar <[email protected]> wrote: > >> > >> This patch depends on the "GoAway" protocol message proposal currently > under review here: > https://www.postgresql.org/message-id/DDPQ1RV5FE9U.I2WW34NGRD8Z%40jeltef.nl > Please apply this patch on top of the GoAway patch. > > > > > > A review of the GoAway patch from you would definitely be appreciated > (even if there's no actionable feedback like: "this looks good and I > managed use it for my own patch successfully") > > > >> The Solution: To handle this authorization gap gracefully, this patch > leverages the pending GoAway protocol message to notify clients. > > > > > > I didn't look at the patch (I'm on my phone). But my first thought is > that only relying on the proposed version of GoAway is insufficient for > anything related to security. The GoAway message is both best effort, and > only supported with newer protocol versions. So while I think it's a good > usecase for GoAway, I think there *also* needs to be a hard timeout at > which point the connection gets forcefully terminated if it's using old > credentials. > > > > Regarding the configurable interval that you describe for checking auth > changes, I think it might be better to register a SysCache update receiver > instead (or just poll the SysCache value > > > > Finally, can you register this patch on the commitfest? > https://commitfest.postgresql.org/ >
password_expire.patch
Description: Binary data
