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.