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,
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 <postgres@jeltef.nl> wrote:
>
> On Fri, Nov 28, 2025, 04:39 Ajit Awekar <ajitpostgres@gmail.com> 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/
