Re: Periodic authorization expiration checks using GoAway message

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(at)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(at)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 <hannuk(at)google(dot)com> 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 <postgres(at)jeltef(dot)nl>
> wrote:
> >
> > On Fri, Nov 28, 2025, 04:39 Ajit Awekar <ajitpostgres(at)gmail(dot)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/
>