Automatic password refresh from connect backend

[sapslaj]

I'm working on trying to figure out if there's a way to have pgweb automatically refresh the password from the connect backend on some interval. It doesn't appear like there's a way to do that easily so I wanted to bring it up.

For context, at my company we use AWS RDS Aurora with IAM authentication. We run pgweb as a Kubernetes Deployment for each database we spin up. We made a sidecar that handles generating the sigv4 token and hooks up to pgweb's --connect-backend and that all works great for the most part.

The issue is that once that token expires (after one hour) pgweb doesn't attempt to get a new token automatically. Even setting the idle timeout to a low value like 5 minutes will just cause it to disconnect and not automatically reconnect. Simply refreshing the page doesn't seem to work either; you have to navigate to / which triggers a call to /connect/:resource to reconnect and get a new token from the connect backend. It's an easy workaround but the UX isn't great and we get quite a number of support requests from other teams about this.

I came up with a patch that sorta fixes the issue but it's a bit hacky.

diff --git a/pkg/api/middleware.go b/pkg/api/middleware.go
index 14b3c28..47dc629 100644
--- a/pkg/api/middleware.go
+++ b/pkg/api/middleware.go
@@ -40,7 +40,7 @@ func dbCheckMiddleware() gin.HandlerFunc {
 		// Determine the database connection handle for the session
 		conn := DbSessions.Get(sid)
 		if conn == nil {
-			badRequest(c, errNotConnected)
+			ConnectWithBackend(c)
 			return
 		}

I just wanted to start a discussion about potential solutions. I've got some bandwidth to submit PRs if that would be helpful.

[sosedoff]

Hi @sapslaj thanks for reaching out. I think you bring up a few very interesting points and use cases, and i'm sure other users of Pgweb may appreciate any development in this area. Based on your issue description:

When a new connection is made to the Connect Backend API, there should be a way to specify the response "freshness", or a resource TTL basically. With this metadata, Pgweb could potentially track which resources/sessions have expiry and try to automatically reconnect if needed.

Currently we're representing the Backend request/response pair with BackendRequest / BackendCredential structs, which do not have any TTL information on it. So i think the first step in this direction would be us adding TTL to the BackendCredential. Since TTL term is pretty generic and could mean a few things (ie session ttl, or response token ttl, or others), it would be a good idea to call it something like SessionTTL instead. We could also include some kind of a reconnect boolean flag to indicate that any session created from the credential could be refreshed.

A fair amount of refactor is needed on the Backend portion and also on the Client in order to better track sessions/connections established from the Backend feature. It would be a good time for me to look into it and clean things a bit since it has been a few years since i've made any changes to the Backend feature. Another issue with this is Pgweb does not have a "state" so if you're running multiple deployments of Pgweb, things may not work as expected.

Is there anything else you'd like to mention? Any other use cases and edge cases?

[sapslaj]

When a new connection is made to the Connect Backend API, there should be a way to specify the response "freshness", or a resource TTL basically. With this metadata, Pgweb could potentially track which resources/sessions have expiry and try to automatically reconnect if needed.

Currently we're representing the Backend request/response pair with BackendRequest / BackendCredential structs, which do not have any TTL information on it. So i think the first step in this direction would be us adding TTL to the BackendCredential. Since TTL term is pretty generic and could mean a few things (ie session ttl, or response token ttl, or others), it would be a good idea to call it something like SessionTTL instead. We could also include some kind of a reconnect boolean flag to indicate that any session created from the credential could be refreshed.

Good idea! I have a few questions though.

1. In a "traditional" sense TTL would mean something like "number of seconds until expiry" but I'm wondering if a specific timestamp would be more robust, that way pgweb wouldn't have to try to keep track of "seconds passed" or calculating the right timestamp and it could more lazily just check if time.Now() > cred.SessionExpiry { refresh() }. There are tradeoffs with that approach (like time.Parse() quirks) but it's an idea.
2. Would we want the Connect Backend to provide the TTL or have it be a CLI flag? Or both? It seems like having Connect Backend provide that makes the most sense. If it's a CLI flag then it has to be kept in sync with whatever the backend is doing which might cause some headaches. And having to consider both sounds like a bunch of unnecessary complexity on the pgweb side.

A fair amount of refactor is needed on the Backend portion and also on the Client in order to better track sessions/connections established from the Backend feature. It would be a good time for me to look into it and clean things a bit since it has been a few years since i've made any changes to the Backend feature. Another issue with this is Pgweb does not have a "state" so if you're running multiple deployments of Pgweb, things may not work as expected.

For what it's worth we deploy isolated single replica Deployments of pgweb that only ever talk to a single database so we probably wouldn't hit this particular issue but I'm sure others might. It does seem like there's some kind of in-memory "state" with SessionManager but it seems to be more-or-less just a connection pool manager and isn't too crazy. That's where I started trying to figure out if there was a proper way to implement this but haven't made a ton of progress yet.

Is there anything else you'd like to mention? Any other use cases and edge cases?

I can't think of anything else, at least specific to our use case. We aren't doing anything too crazy; IAM auth is relatively common in AWS-heavy environments (at least in my experience). Having pgweb refresh itself would definitely help the UX for this kind of environment with such short-lived tokens and makes infosec people happy 😄.

[sapslaj]

@sosedoff I put up a draft PR. It's kinda rough but it does work. Let me know what you think: #800

[sosedoff]

In a "traditional" sense TTL would mean something like "number of seconds until expiry" but I'm wondering if a specific timestamp would be more robust, that way pgweb wouldn't have to try to keep track of "seconds passed" or calculating the right timestamp and it could more lazily just check if time.Now() > cred.SessionExpiry { refresh() }. There are tradeoffs with that approach (like time.Parse() quirks) but it's an idea.

Yeah i think either a TTL in seconds or a specific expiration time would suffice here: either one or the other, not both.

Would we want the Connect Backend to provide the TTL or have it be a CLI flag? Or both? It seems like having Connect Backend provide that makes the most sense. If it's a CLI flag then it has to be kept in sync with whatever the backend is doing which might cause some headaches. And having to consider both sounds like a bunch of unnecessary complexity on the pgweb side.

Since Pgweb has no way of knowing the "real" expiry TTL, this should be something that the backend can provide. Plus, we can definitely keep this entire feature backwards compatible so there's no unwanted regressions.

I put up a draft PR. It's kinda rough but it does work. Let me know what you think: #800

Thanks, i will check it out. I don't have a lot of spare time these days so will try to get to it whenever i have a chance.