Hi Dave Dave Page <dp...@pgadmin.org> schrieb am Dienstag, 5. März 2024 um 11:43 vorm.:
> Hi > > On Tue, 5 Mar 2024 at 08:43, Harry Lond <hl...@proton.me> wrote: > >> Hi, >> >> I start with the actual question: is there a way to configure pgAdmin not to >> open a new connection for each query? So to re-use the already opened >> connection that is used for the dashboard and use this one connection only? >> Maybe with a config parameter? >> >> Background: we are using pgAdmin in server mode in order to connect to AWS >> RDS clusters with IAM authentication enabled. >> >> In this scenario, the auth token created by 'aws rds generate-db-auth-token' >> is valid for 15 minutes only (no possibility to extend this). >> >> Setup works fine for the first 15 minutes. Connection can be established, >> dashboard works and queries can be executed. >> >> But while the dashboard and all query tool windows that have been opened >> prior to the token expiration remain useable, all new user interaction fails >> once the token expires. >> >> Opening a new query tool window asks for the password. Querying the first >> 100 rows of a table (when using 'view/edit data -> first 100 rows') fails >> without asking for a new password (shows PAM authentication failed for user >> ...). No way to recover from that, then reloading the whole pgAdmin page and >> re-connect to the database. >> >> We figured out that this is because of the underlying pgAdmin connection >> handling. All new interaction tries to open new connections, which is not >> possible because of the expired token. >> >> One might say this works like designed because the token/password is no >> longer valid. However, from an end user perspective pgAdmin is not really >> useable with AWS clusters that have IAM authentication enabled right now. >> >> That's why we had the idea to re-use the original connection that is used by >> the dashboard. Or is there maybe another solution for this? > > Unfortunately that's not really feasible. The problem is that some tools (in > particular the Query Tool) may change session variables - for example, you > might do "SET ROLE foo" or "SET search_path ...". If pgAdmin then uses that > connection for something else, it might not get the expected results. If it > resets the session, then your Query Tool instance might not get the results > you expect - and you might not even be aware of that - and that could easily > lead to inadvertent data loss. > > The problem is then worsened by transactions. Consider an ongoing transaction > in the Query Tool or the Edit Grid. Does a query executed in a different > Query Tool instance (say, "COMMIT" or "ROLLBACK") affect the other instance? > How would pgAdmin know what state is intentional and what isn't, or when to > wait for a transaction to end (or not) before doing something else such as > updating a dashboard? > > So, I understand the problem from the UX perspective, but I don't think it's > feasible for a tool like pgAdmin to handle it in a safe, predictable, and > intuitive way, if at all. The explanation makes perfect sense. So the only remaining solution right now would be to stop using the IAM authentication. Thanks for the quick reply!
