Thread

1. Periodic authorization expiration checks using GoAway message

   Ajit Awekar <ajitpostgres@gmail.com> — 2025-11-28T11:39:04Z

   This patch introduces a mechanism to address the security issue of stale,
   authorized connections persisting beyond their validity period. .
   Currently, once a session is established, postgres does not automatically
   re-validate credentials. If a password expires (rolvaliduntil)  the session
   remains active indefinitely. Same applies to centralized authentication
   systems (like Kerberos or OAuth).
   
   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.
   
   The Solution: To handle this authorization gap gracefully, this patch
   leverages the pending GoAway protocol message to notify clients.
   
   Please find below summary of the solution
   
   New GUC: auth_expiration_check_interval (integer, minutes). Controls the
   frequency of checking a session's authorization status. Setting it to 0
   (default) disables the check.
   
   Periodic Idle Check: When a backend process is idle (waiting for the next
   command) and the timeout is reached, the server calls a placeholder
   function check_external_auth_status_expired().
   
   Graceful Disconnect: If authorization is revoked/expired, the server sends
   the GoAway message.  This allows the client to finish any current
   processing and reconnect cleanly.
   
   Thanks & Best Regards,
   Ajit Awekar
   

2. Re: Periodic authorization expiration checks using GoAway message

   Jelte Fennema-Nio <postgres@jeltef.nl> — 2025-11-28T17:18:57Z

   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/
   
   >
   

3. Re: Periodic authorization expiration checks using GoAway message

   Hannu Krosing <hannuk@google.com> — 2025-11-28T17:52:20Z

   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/
   
   
   
   

4. Re: Periodic authorization expiration checks using GoAway message

   Ajit Awekar <ajitpostgres@gmail.com> — 2025-12-09T13:10:22Z

   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 <hannuk@google.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@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/
   >
   

5. Re: Periodic authorization expiration checks using GoAway message

   Jacob Champion <jacob.champion@enterprisedb.com> — 2025-12-10T20:02:23Z

   (To call it out explicitly: I work with Ajit, and I asked him to take
   a look at GoAway, and I'm particularly interested in the
   "reauthenticate or else" case. Let me know if any of that is
   problematic -- or if anyone's worried that it will become so -- so I
   can course-correct sooner rather than later.)
   
   On Fri, Nov 28, 2025 at 9:52 AM Hannu Krosing <hannuk@google.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.
   
   I agree. I like the idea of the server coordinating (and then
   enforcing) connection lifetime and cross-connection handoffs with the
   client, but like Jelte said, the current GoAway proposal isn't really
   built for that.
   
   Is there enough interest in the more general problem for us to try
   combining the use cases? Or should they remain separate?
   
   Thanks,
   --Jacob
   
   
   
   

6. Re: Periodic authorization expiration checks using GoAway message

   Jelte Fennema-Nio <postgres@jeltef.nl> — 2025-12-10T21:20:46Z

   On Wed, 10 Dec 2025 at 21:02, Jacob Champion
   <jacob.champion@enterprisedb.com> wrote:
   >
   > (To call it out explicitly: I work with Ajit, and I asked him to take
   > a look at GoAway, and I'm particularly interested in the
   > "reauthenticate or else" case. Let me know if any of that is
   > problematic -- or if anyone's worried that it will become so -- so I
   > can course-correct sooner rather than later.)
   
   I think password rollover without downtime requires more thought than
   discussed in this thread so far. Currently the simplest way (that I
   know of) to rollover passwords without downtime is to have two users
   that you can switch between, and one has been configured with:
   ALTER USER b SET ROLE = a;
   
   So both effectively log in as a.
   
   Reading between the lines, I guess you're looking at this from the
   OAuth lens. Not "normal" passwords.
   
   > On Fri, Nov 28, 2025 at 9:52 AM Hannu Krosing <hannuk@google.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.
   >
   > I agree. I like the idea of the server coordinating (and then
   > enforcing) connection lifetime and cross-connection handoffs with the
   > client, but like Jelte said, the current GoAway proposal isn't really
   > built for that.
   
   If you want to re-authenticate over the existing connection (and
   keeping your session etc), then I think that's a very different thing
   than what I intended the GoAway message to be used for.
   
   > Is there enough interest in the more general problem for us to try
   > combining the use cases? Or should they remain separate?
   
   I'm not sure what you mean with "combine the use cases". If you think
   the GoAway protocol message definition could be extended slightly to
   better serve this use case somehow. For instance if you think we
   should have the GoAway message include an (optional) number of
   seconds, so a client could say to the user: "Disconnect within 6
   minutes". (just an example, not necessarily something I think is a
   good idea)
   
   If you mean combining by introducing a single shared protocol message
   for both the "re-authenticate on existing session" and "please
   reconnect asap", then I'd say: No, let's keep them separate.
   
   I think for "re-authenticate now on existing connection" it'd be much
   more natural for the server to simply send a new authentication
   request message, and expect the client to respond to that. The auth
   flow based on these messages is already implemented by each client,
   they'd only need to change it so that it could be called into at any
   moment (or maybe certain defined moments like in between queries).
   
   
   
   

7. Re: Periodic authorization expiration checks using GoAway message

   Ajit Awekar <ajitpostgres@gmail.com> — 2025-12-11T10:52:33Z

   Thanks a lot Jacob and Jelte for your valuable insights.
   
   I agree that the seamless re-authentication model (re-authentication over
   the active connection) is suited only for external centralized
   authentication methods like OAuth2 and LDAP.
   
   I have below questions
   
      - Does the client  need to pause current operations, execute a
      simplified re-authentication sequence (triggered by the server's
      Authentication Request), and then transparently resume the session upon
      success?
      - How frequently should the authorization expiration check occur in the
      backend, Would the frequency be tied to a new session GUC (e.g.,
      authorization_check_interval), allowing administrators to configure it?
      - What should the behavior be for older version clients (backward
      compatibility)  that do not understand this new server-initiated
      reauthentication message? In this case is the safest approach for the
      server to terminate the connection?
   
   
   Thanks & Best Regards,
   Ajit
   
   On Thu, 11 Dec 2025 at 02:50, Jelte Fennema-Nio <postgres@jeltef.nl> wrote:
   
   > On Wed, 10 Dec 2025 at 21:02, Jacob Champion
   > <jacob.champion@enterprisedb.com> wrote:
   > >
   > > (To call it out explicitly: I work with Ajit, and I asked him to take
   > > a look at GoAway, and I'm particularly interested in the
   > > "reauthenticate or else" case. Let me know if any of that is
   > > problematic -- or if anyone's worried that it will become so -- so I
   > > can course-correct sooner rather than later.)
   >
   > I think password rollover without downtime requires more thought than
   > discussed in this thread so far. Currently the simplest way (that I
   > know of) to rollover passwords without downtime is to have two users
   > that you can switch between, and one has been configured with:
   > ALTER USER b SET ROLE = a;
   >
   > So both effectively log in as a.
   >
   > Reading between the lines, I guess you're looking at this from the
   > OAuth lens. Not "normal" passwords.
   >
   > > On Fri, Nov 28, 2025 at 9:52 AM Hannu Krosing <hannuk@google.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.
   > >
   > > I agree. I like the idea of the server coordinating (and then
   > > enforcing) connection lifetime and cross-connection handoffs with the
   > > client, but like Jelte said, the current GoAway proposal isn't really
   > > built for that.
   >
   > If you want to re-authenticate over the existing connection (and
   > keeping your session etc), then I think that's a very different thing
   > than what I intended the GoAway message to be used for.
   >
   > > Is there enough interest in the more general problem for us to try
   > > combining the use cases? Or should they remain separate?
   >
   > I'm not sure what you mean with "combine the use cases". If you think
   > the GoAway protocol message definition could be extended slightly to
   > better serve this use case somehow. For instance if you think we
   > should have the GoAway message include an (optional) number of
   > seconds, so a client could say to the user: "Disconnect within 6
   > minutes". (just an example, not necessarily something I think is a
   > good idea)
   >
   > If you mean combining by introducing a single shared protocol message
   > for both the "re-authenticate on existing session" and "please
   > reconnect asap", then I'd say: No, let's keep them separate.
   >
   > I think for "re-authenticate now on existing connection" it'd be much
   > more natural for the server to simply send a new authentication
   > request message, and expect the client to respond to that. The auth
   > flow based on these messages is already implemented by each client,
   > they'd only need to change it so that it could be called into at any
   > moment (or maybe certain defined moments like in between queries).
   >
   

8. Re: Periodic authorization expiration checks using GoAway message

   Jacob Champion <jacob.champion@enterprisedb.com> — 2025-12-12T00:09:51Z

   On Wed, Dec 10, 2025 at 1:21 PM Jelte Fennema-Nio <postgres@jeltef.nl> wrote:
   > I think password rollover without downtime requires more thought than
   > discussed in this thread so far.
   
   Sure. See also
   
       https://postgr.es/m/CAGB%2BVh5SQQorNDEKP%2B0G%3DsmxHRhbhs%2BVkmQWD5Vh98fmn8X4dg%40mail.gmail.com
   
   > Reading between the lines, I guess you're looking at this from the
   > OAuth lens.
   
   Yes. Or Kerberos.
   
   > Not "normal" passwords.
   
   I could see a case for kicking connections after a SCRAM password
   change, if they're not able to reauthenticate in X interval. But I
   wouldn't make it my top priority, necessarily.
   
   > > Is there enough interest in the more general problem for us to try
   > > combining the use cases? Or should they remain separate?
   >
   > I'm not sure what you mean with "combine the use cases". If you think
   > the GoAway protocol message definition could be extended slightly to
   > better serve this use case somehow.
   
   Just that the two cases of "please consider reconnecting due to a
   topology change" and "you didn't reauthenticate in time, so now you
   _have_ to reconnect, bye" seem like they might be related at the
   protocol level, since some types of topology changes might warrant a
   harsher approach, and some types of authentication might do well with
   a gentler one.
   
   > If you mean combining by introducing a single shared protocol message
   > for both the "re-authenticate on existing session" and "please
   > reconnect asap", then I'd say: No, let's keep them separate.
   
   Agreed; I did not mean that.
   
   > I think for "re-authenticate now on existing connection" it'd be much
   > more natural for the server to simply send a new authentication
   > request message, and expect the client to respond to that. The auth
   > flow based on these messages is already implemented by each client,
   > they'd only need to change it so that it could be called into at any
   > moment (or maybe certain defined moments like in between queries).
   
   I think that's probably the hard part. "in between" is not
   particularly well-defined, especially once you add in some async
   pipelining, right? Contrast with HTTP/3's GOAWAY, especially its
   graceful shutdown flow.
   
   --Jacob
   
   
   
   

9. Re: Periodic authorization expiration checks using GoAway message

   Jacob Champion <jacob.champion@enterprisedb.com> — 2025-12-12T00:13:47Z

   On Thu, Dec 11, 2025 at 2:52 AM Ajit Awekar <ajitpostgres@gmail.com> wrote:
   > I agree that the seamless re-authentication model (re-authentication over the active connection) is suited only for external centralized authentication methods like OAuth2 and LDAP.
   
   Well, see my response to Jelte above. But I think it's certainly
   easier to pitch the usefulness of the feature for external methods.
   
   > Does the client  need to pause current operations, execute a simplified re-authentication sequence (triggered by the server's Authentication Request), and then transparently resume the session upon success?
   
   I think it would have to. But we don't number our conversations like
   the other protocols with GOAWAY do, so it's not immediately clear to
   me how we would do it.
   
   The reauthentication sequence isn't guaranteed to be silent, either.
   Imagine that you were typing a SQL command and psql popped up a
   password prompt right in the middle; that's not a good user
   experience.
   
   > How frequently should the authorization expiration check occur in the backend, Would the frequency be tied to a new session GUC (e.g., authorization_check_interval), allowing administrators to configure it?
   
   I don't think there's one answer, so it'd probably have to be
   configurable. Offline tokens and Kerberos tickets might have a known
   timestamp for expiration, so you could just do a cheap timestamp
   comparison for every single request. Online checks (to allow
   revocation) would need more thought by the DBA; there's a
   performance-staleness tradeoff there.
   
   > What should the behavior be for older version clients (backward compatibility)  that do not understand this new server-initiated reauthentication message? In this case is the safest approach for the server to terminate the connection?
   
   "Safe" is decided by the DBA, I think. Turning this on might imply
   that you care more about security than the cost of cleaning up after a
   client that got kicked off halfway through an important transaction...
   or it might not.
   
   --Jacob
   
   
   
   

10. Re: Periodic authorization expiration checks using GoAway message

    Jelte Fennema-Nio <postgres@jeltef.nl> — 2025-12-12T00:36:08Z

    On Fri, 12 Dec 2025 at 01:10, Jacob Champion
    <jacob.champion@enterprisedb.com> wrote:
    >     https://postgr.es/m/CAGB%2BVh5SQQorNDEKP%2B0G%3DsmxHRhbhs%2BVkmQWD5Vh98fmn8X4dg%40mail.gmail.com
    
    Thanks, I hadn't seen that one before.
    
    > "you didn't reauthenticate in time, so now you
    > _have_ to reconnect, bye"
    
    I might be missing something but I feel like we currently do this in
    various other places using:
    FATAL: <message explaining reason>
    <connection close>
    
    > I think that's probably the hard part. "in between" is not
    > particularly well-defined, especially once you add in some async
    > pipelining, right?
    
    Yeah, pipelining is annoying for these kind of things. But looking
    it's not so bad. What if you define the flow as:
    1. server sends 'R' message when it realizes it wants a
    re-authentication (allowed whenever, just like NoticeResponse)
    2. client can continue to send whatever and server will respond accordingly
    3a. client starts authentication flow by sending a 'p' message
    3b. client did not complete auth flow within timeout of the server, so
    server sends FATAL + closes connection.
    
    
    
    

11. Re: Periodic authorization expiration checks using GoAway message

    Zsolt Parragi <zsolt.parragi@percona.com> — 2025-12-12T11:49:23Z

    Would client side revalidation allow re-authentication while a
    long-running query is in progress? Or would it kick out a connection
    because it can't reauthenticate after some grace period? A strict OIDC
    setup might use 5 or 10 minute access tokens, where this is a
    realistic issue.
    
    > Online checks (to allow revocation) would need more thought by the DBA; there's a performance-staleness tradeoff there.
    
    Are revocation checks really related to GoAway? Even with offline OIDC
    tokens, we can implement periodic server side checks to see if a long
    lived token is still alive using an introspection endpoint.
    
    I think this should be already possible with current validators, by
    closing the connection if we find out that a token was revoked -
    trying to implement this is on my TODO list. Should we really handle
    this through GoAway, and allow a graceful period? If a token was
    revoked, there's usually a good reason for that.
    
    
    
    

12. Re: Periodic authorization expiration checks using GoAway message

    Jacob Champion <jacob.champion@enterprisedb.com> — 2025-12-15T17:17:04Z

    On Thu, Dec 11, 2025 at 4:36 PM Jelte Fennema-Nio <postgres@jeltef.nl> wrote:
    > > "you didn't reauthenticate in time, so now you
    > > _have_ to reconnect, bye"
    >
    > I might be missing something but I feel like we currently do this in
    > various other places using:
    > FATAL: <message explaining reason>
    > <connection close>
    
    Part of the appeal to me is the ability for the client to decide to
    transparently reconnect if possible, rather than surfacing an error to
    the user. But if there's no other metadata to be used by the client
    during that, then I suppose we could create a new 08Pxx SQLSTATE to
    mean that. That does cover the backwards compatibility angle pretty
    well.
    
    > Yeah, pipelining is annoying for these kind of things. But looking
    > it's not so bad. What if you define the flow as:
    > 1. server sends 'R' message when it realizes it wants a
    > re-authentication (allowed whenever, just like NoticeResponse)
    > 2. client can continue to send whatever and server will respond accordingly
    > 3a. client starts authentication flow by sending a 'p' message
    > 3b. client did not complete auth flow within timeout of the server, so
    > server sends FATAL + closes connection.
    
    I think a timeout-based flow (though simple!) might be hard to
    maintain for a DBA. It's basically a race condition that they would
    have to tune. (I'm not saying I'm opposed to a timeout here; just that
    the timeout should be a backstop for a misbehaved client, rather than
    the thing the conversation is based on. The client should know what
    it's allowed to finish up before reauthenticating.)
    
    --Jacob
    
    
    
    

13. Re: Periodic authorization expiration checks using GoAway message

    Jelte Fennema-Nio <postgres@jeltef.nl> — 2025-12-15T17:27:47Z

    On Mon, 15 Dec 2025 at 18:17, Jacob Champion
    <jacob.champion@enterprisedb.com> wrote:
    > Part of the appeal to me is the ability for the client to decide to
    > transparently reconnect if possible, rather than surfacing an error to
    > the user. But if there's no other metadata to be used by the client
    > during that, then I suppose we could create a new 08Pxx SQLSTATE to
    > mean that. That does cover the backwards compatibility angle pretty
    > well.
    
    Seems reasonable to use dedicated error code for that. My
    understanding (see below) would be that getting this error would be
    the exception, because in the happy path the client would
    re-authenticate on the existing connection.
    
    > > Yeah, pipelining is annoying for these kind of things. But looking
    > > it's not so bad. What if you define the flow as:
    > > 1. server sends 'R' message when it realizes it wants a
    > > re-authentication (allowed whenever, just like NoticeResponse)
    > > 2. client can continue to send whatever and server will respond accordingly
    > > 3a. client starts authentication flow by sending a 'p' message
    > > 3b. client did not complete auth flow within timeout of the server, so
    > > server sends FATAL + closes connection.
    >
    > I think a timeout-based flow (though simple!) might be hard to
    > maintain for a DBA. It's basically a race condition that they would
    > have to tune. (I'm not saying I'm opposed to a timeout here; just that
    > the timeout should be a backstop for a misbehaved client, rather than
    > the thing the conversation is based on. The client should know what
    > it's allowed to finish up before reauthenticating.)
    
    To clarify: I meant the timeout as a backstop in this flow. Once the
    client receives a 'R' message it should be re-authenticating ASAP. But
    if it still had some messages in flight, the server can still choose
    to process them during a grace period.
    
    
    
    

14. Re: Periodic authorization expiration checks using GoAway message

    Jacob Champion <jacob.champion@enterprisedb.com> — 2025-12-15T17:27:59Z

    On Fri, Dec 12, 2025 at 3:49 AM Zsolt Parragi <zsolt.parragi@percona.com> wrote:
    > Would client side revalidation allow re-authentication while a
    > long-running query is in progress?
    
    I think this is related to the async concern and the "what is a client
    allowed to do before reauthentication" question.
    
    > > Online checks (to allow revocation) would need more thought by the DBA; there's a performance-staleness tradeoff there.
    >
    > Are revocation checks really related to GoAway? Even with offline OIDC
    > tokens, we can implement periodic server side checks to see if a long
    > lived token is still alive using an introspection endpoint.
    
    I view it as related (potentially) to the continuity of the connection
    handoff, if the client doesn't reauthenticate. The revocation itself
    doesn't have much to do with GoAway.
    
    > I think this should be already possible with current validators, by
    > closing the connection if we find out that a token was revoked -
    > trying to implement this is on my TODO list.
    
    (I don't see this as a message to be used during initial authentication.)
    
    > Should we really handle
    > this through GoAway, and allow a graceful period? If a token was
    > revoked, there's usually a good reason for that.
    
    What to do with a token that's revoked while a
    connection/query/transaction is in progress is a big design decision,
    I think. But I could see a case for defaulting to graceful
    reauthentication even in the case of OAuth revocation.
    
    If you're worried about a single bearer token, you can revoke it
    (clients can still refresh them and keep going). If you're worried
    about a refresh token, you can revoke it (clients might ask users for
    another one and keep going). And if you're worried about a client, you
    can revoke its ability to access the required scopes, and then you've
    locked it out. The protocol doesn't necessarily need to care that the
    graceful reauthentication is doomed to failure.
    
    --Jacob
    
    
    
    

15. Re: Periodic authorization expiration checks using GoAway message

    Jacob Champion <jacob.champion@enterprisedb.com> — 2025-12-15T17:31:33Z

    On Mon, Dec 15, 2025 at 9:28 AM Jelte Fennema-Nio <postgres@jeltef.nl> wrote:
    > To clarify: I meant the timeout as a backstop in this flow. Once the
    > client receives a 'R' message it should be re-authenticating ASAP. But
    > if it still had some messages in flight, the server can still choose
    > to process them during a grace period.
    
    But it seems iffy to change authentication metadata associated with
    the connection halfway through a transaction, no? Am I missing
    something that makes that architecturally safe?
    
    --Jacob
    
    
    
    

16. Re: Periodic authorization expiration checks using GoAway message

    Jelte Fennema-Nio <postgres@jeltef.nl> — 2025-12-15T21:50:04Z

    On Mon, 15 Dec 2025 at 18:31, Jacob Champion
    <jacob.champion@enterprisedb.com> wrote:
    > But it seems iffy to change authentication metadata associated with
    > the connection halfway through a transaction, no? Am I missing
    > something that makes that architecturally safe?
    
    It felt a bit iffy to me too initially, but then I started looking at
    it from the other direction: i.e. what am I missing that actually
    makes this architecturally unsafe? And I cannot think of anything.
    
    I see two possible things happening when re-authenticating mid-transaction:
    1. User re-authenticates correctly, the transaction can continue as it
    would normally
    2. User re-authenticates incorrectly, connection is closed and
    transaction is aborted
    
    Both of those situations seem totally reasonable to me. What metadata
    are you worried about changing mid transaction that could mess stuff
    up? The primary one I can imagine is the username, but in my proposed
    implementation of the feature that one would have to stay the same
    anyway: The authentication related messages ('R' and 'p') don't
    contain username, that's part of the StartupMessage.
    
    
    
    

17. Re: Periodic authorization expiration checks using GoAway message

    Zsolt Parragi <zsolt.parragi@percona.com> — 2025-12-16T08:05:17Z

    > I think this is related to the async concern and the "what is a client
    > allowed to do before reauthentication" question.
    
    Yes, but my point was that I think the different situations (normally
    expired token, we most likely still have a valid refresh token vs
    token that was for some reason revoked before its normal expiration
    date) are different and require different handling, I don't think we
    should treat them the same way. GoAway seems to be more useful for the
    first one.
    
    > (I don't see this as a message to be used during initial authentication.)
    
    What I meant that we can already implement a background process that
    watches active (oauth) connections, and either:
    
    * Revalidates tokens periodically using introspection APIs
    * Implements the Back-Channel logout[1] supported by several identity providers
    
    And if the checks fail (a token is invalidated), we immediately close
    the connection that uses the token.
    
    This situation can happen either when:
    
    a. The user presses the "logout everywhere" button
    b. The users permissions change
    c. The user is deactivated (e.g. employee termination)
    d. A security check invalidates the user's session
    
    From these four, I think graceful logout/continuing the current query
    is only an option for (a), maybe (b), for (c) and (d) we should log
    out the user from everywhere as soon as possible.
    
    And even for the normal "logout" button, I can see graceful logout as
    a potential option, but I'm not sure how many people would want it
    instead of the normal "log out instantly" behavior. I think most
    people would expect it to work exactly like a normal logout, like
    closing psql.
    
    For (a) and (d) keeping the connection alive, but inactive, and
    letting the user log in again without losing the session could be an
    option, and GoAway could be useful there if I understand correctly.
    For (b) and (c) there's no way for the session to continue. But
    because of (d) I think it is important to terminate the query
    immediately (or at least: prevent committing, but that seems more
    difficult to do), I don't think anybody would want graceful
    termination in case of a security incident.
    
    On the other hand, if the access token expires, and we have to
    automatically/manually get a new one, I imagine most people would
    want/expect graceful behavior.
    
    [1] https://openid.net/specs/openid-connect-backchannel-1_0.html
    
    
    
    

18. Re: Periodic authorization expiration checks using GoAway message

    Ajit Awekar <ajitpostgres@gmail.com> — 2025-12-16T09:15:35Z

    >What I meant that we can already implement a background process that
    >watches active (oauth) connections, and either:
    
    >Revalidates tokens periodically using introspection APIs
    
    +1 as this will  offload validation logic to a dedicated background
    process.
    
    Thanks & Best Regards,
    Ajit
    
    On Tue, 16 Dec 2025 at 13:35, Zsolt Parragi <zsolt.parragi@percona.com>
    wrote:
    
    > > I think this is related to the async concern and the "what is a client
    > > allowed to do before reauthentication" question.
    >
    > Yes, but my point was that I think the different situations (normally
    > expired token, we most likely still have a valid refresh token vs
    > token that was for some reason revoked before its normal expiration
    > date) are different and require different handling, I don't think we
    > should treat them the same way. GoAway seems to be more useful for the
    > first one.
    >
    > > (I don't see this as a message to be used during initial authentication.)
    >
    > What I meant that we can already implement a background process that
    > watches active (oauth) connections, and either:
    >
    > * Revalidates tokens periodically using introspection APIs
    > * Implements the Back-Channel logout[1] supported by several identity
    > providers
    >
    > And if the checks fail (a token is invalidated), we immediately close
    > the connection that uses the token.
    >
    > This situation can happen either when:
    >
    > a. The user presses the "logout everywhere" button
    > b. The users permissions change
    > c. The user is deactivated (e.g. employee termination)
    > d. A security check invalidates the user's session
    >
    > From these four, I think graceful logout/continuing the current query
    > is only an option for (a), maybe (b), for (c) and (d) we should log
    > out the user from everywhere as soon as possible.
    >
    > And even for the normal "logout" button, I can see graceful logout as
    > a potential option, but I'm not sure how many people would want it
    > instead of the normal "log out instantly" behavior. I think most
    > people would expect it to work exactly like a normal logout, like
    > closing psql.
    >
    > For (a) and (d) keeping the connection alive, but inactive, and
    > letting the user log in again without losing the session could be an
    > option, and GoAway could be useful there if I understand correctly.
    > For (b) and (c) there's no way for the session to continue. But
    > because of (d) I think it is important to terminate the query
    > immediately (or at least: prevent committing, but that seems more
    > difficult to do), I don't think anybody would want graceful
    > termination in case of a security incident.
    >
    > On the other hand, if the access token expires, and we have to
    > automatically/manually get a new one, I imagine most people would
    > want/expect graceful behavior.
    >
    > [1] https://openid.net/specs/openid-connect-backchannel-1_0.html
    >
    

19. Re: Periodic authorization expiration checks using GoAway message

    Jacob Champion <jacob.champion@enterprisedb.com> — 2025-12-16T19:53:55Z

    On Mon, Dec 15, 2025 at 1:50 PM Jelte Fennema-Nio <postgres@jeltef.nl> wrote:
    > What metadata
    > are you worried about changing mid transaction that could mess stuff
    > up? The primary one I can imagine is the username
    
    The HBA inputs can't be allowed to change; that's not what I'm worried about.
    
    The system_user might change. Cached SCRAM keys can change. Every
    certificate-derived piece of metadata in sslinfo could change.
    MyProcPort->peer and MyProcPort->gss might be wholly reassigned. And
    any user-metadata functions provided by OAuth validator modules would
    need to carefully consider their volatility guarantees at minimum.
    There's probably more.
    
    --Jacob
    
    
    
    

20. Re: Periodic authorization expiration checks using GoAway message

    Jacob Champion <jacob.champion@enterprisedb.com> — 2025-12-16T20:19:51Z

    On Tue, Dec 16, 2025 at 12:05 AM Zsolt Parragi
    <zsolt.parragi@percona.com> wrote:
    > a. The user presses the "logout everywhere" button
    > b. The users permissions change
    > c. The user is deactivated (e.g. employee termination)
    > d. A security check invalidates the user's session
    >
    > From these four, I think graceful logout/continuing the current query
    > is only an option for (a), maybe (b), for (c) and (d) we should log
    > out the user from everywhere as soon as possible.
    
    To me that seems like a matter of policy and not protocol. (As long as
    we come to some agreement on the semantics of what a client is and is
    not allowed to do before reauthenticating.)
    
    Said another way: it seems very useful to let a DBA choose between
    graceful reauthentication and hard connection loss for different
    situations. But I don't think those decisions should be assumed in the
    protocol design or hardcoded in our server. Even for case (d), a DBA
    might choose to bound clients via transaction_timeout for a particular
    application; since we've never had this feature before, I don't want
    to make proclamations about how people are going to want to deploy it.
    
    --Jacob
    
    
    
    

21. Re: Periodic authorization expiration checks using GoAway message

    Jacob Champion <jacob.champion@enterprisedb.com> — 2025-12-16T20:22:11Z

    On Tue, Dec 16, 2025 at 1:15 AM Ajit Awekar <ajitpostgres@gmail.com> wrote:
    > >What I meant that we can already implement a background process that
    > >watches active (oauth) connections, and either:
    >
    > >Revalidates tokens periodically using introspection APIs
    >
    > +1 as this will  offload validation logic to a dedicated background process.
    
    Is the hope that batching validation will make things more efficient,
    or is there another goal to using a background process? You still have
    to communicate back to each backend.
    
    --Jacob
    
    
    
    

22. Re: Periodic authorization expiration checks using GoAway message

    Ajit Awekar <ajitpostgres@gmail.com> — 2025-12-17T10:06:28Z

    Background process will help us to gain performance so as it will be
    seamless to users and user queries will not be impacted.
    
    Thanks & Best Regards,
    Ajit
    
    On Wed, 17 Dec 2025 at 01:52, Jacob Champion <
    jacob.champion@enterprisedb.com> wrote:
    
    > On Tue, Dec 16, 2025 at 1:15 AM Ajit Awekar <ajitpostgres@gmail.com>
    > wrote:
    > > >What I meant that we can already implement a background process that
    > > >watches active (oauth) connections, and either:
    > >
    > > >Revalidates tokens periodically using introspection APIs
    > >
    > > +1 as this will  offload validation logic to a dedicated background
    > process.
    >
    > Is the hope that batching validation will make things more efficient,
    > or is there another goal to using a background process? You still have
    > to communicate back to each backend.
    >
    > --Jacob
    >
    

23. Re: Periodic authorization expiration checks using GoAway message

    Zsolt Parragi <zsolt.parragi@percona.com> — 2025-12-17T10:44:25Z

    > To me that seems like a matter of policy and not protocol. (As long as
    > we come to some agreement on the semantics of what a client is and is
    > not allowed to do before reauthenticating.)
    
    It's great if this is configurable, as long as DBAs can choose both
    the immediate and graceful options.
    
    > Is the hope that batching validation will make things more efficient,
    > or is there another goal to using a background process? You still have
    > to communicate back to each backend.
    
    * I think I can implement it with background threads / immediate
    disconnection currently for PG18 in our validator (this of course
    might improve with PG19 and later)
    * Can we implement the immediate disconnection without a background
    process? I would again use the long running query example. If a query
    is running for 5-10-... minutes, is there another way to disconnect
    the connection before the query completes?
    * Some identity providers support pushing revocation data instead of
    pulling, and this usually works over HTTP. Which means that it needs a
    background process running a mini HTTP server (as part of an oauth
    validator).
    
    
    
    

24. Re: Periodic authorization expiration checks using GoAway message

    Bruce Momjian <bruce@momjian.us> — 2025-12-23T21:59:21Z

    On Wed, Dec 10, 2025 at 10:20:46PM +0100, Jelte Fennema-Nio wrote:
    > On Wed, 10 Dec 2025 at 21:02, Jacob Champion
    > <jacob.champion@enterprisedb.com> wrote:
    > >
    > > (To call it out explicitly: I work with Ajit, and I asked him to take
    > > a look at GoAway, and I'm particularly interested in the
    > > "reauthenticate or else" case. Let me know if any of that is
    > > problematic -- or if anyone's worried that it will become so -- so I
    > > can course-correct sooner rather than later.)
    > 
    > I think password rollover without downtime requires more thought than
    > discussed in this thread so far. Currently the simplest way (that I
    > know of) to rollover passwords without downtime is to have two users
    > that you can switch between, and one has been configured with:
    > ALTER USER b SET ROLE = a;
    > 
    > So both effectively log in as a.
    
    I have often thought we should allow two passwords for each user for
    such password rotation purposes.
    
    -- 
      Bruce Momjian  <bruce@momjian.us>        https://momjian.us
      EDB                                      https://enterprisedb.com
    
      Do not let urgent matters crowd out time for investment in the future.