Re: RM2815: Relogin to pgAdmin from sqleditor/datadrid if session exprires

HI

On Mon, Jan 8, 2018 at 11:41 AM, Harshal Dhumal <
harshal(dot)dhumal(at)enterprisedb(dot)com> wrote:

> On Mon, Jan 8, 2018 at 4:34 PM, Dave Page <dpage(at)pgadmin(dot)org> wrote:
>
>> Hi
>>
>> On Fri, Jan 5, 2018 at 7:50 AM, Harshal Dhumal <
>> harshal(dot)dhumal(at)enterprisedb(dot)com> wrote:
>>
>>> Further details:
>>>
>>> 1. If session is expired and user performs any action from sqleditor
>>> that makes ajax call
>>> then in ajax error call back user can check and handle login related
>>> error using code snippet.
>>>
>>> if (pgAdmin.Browser.UserManagement.is_pga_login_required(xhr)) {
>>> return pgAdmin.Browser.UserManagement.pga_login();
>>> }
>>>
>>> Where is xhr is standard xhr or jqxhr object.
>>>
>>> 2. Similarly for connection lost (only maintenance db connection as we
>>> can recover or reconnect other
>>> connections if maintenance db connection is alive). It will attempt to
>>> create/reconnect connection without
>>> asking password (to handle passwordless connection, or saveed password
>>> or password from pgpass file)
>>> If connection to database still fails then it'll prompt for password.
>>>
>>> Code snippet:
>>> SqlEditorController.handle_connection_lost();
>>> once connection lost is detected one can call handle_connection_lost()
>>> to reconnect.
>>>
>>> 3. We maintain some additional data in session to maintain affinity
>>> between
>>> each sqleditor/datagrid instance to backend database connection. However
>>> if session expires and user
>>> re-loggins then we need to first restore affinity between sqleditor to
>>> backend database before we can start
>>> using query tool.
>>>
>>> Code snippet:
>>>
>>> if(is_new_transaction_required(xhr)) {
>>> SqlEditorController.init_transaction();
>>> }
>>>
>>> (note: I haven't looked at the code yet)
>>
>> How does this handle re-establishment of the connection mid-transaction,
>> or, if the user has modified any session variables?
>>
>> ServeManager and Connection Manager are written in a such way that if any
> connection is lost except maintenance db connection
> then we can re-connect or create new connection without prompting for
> database password and if maintenance db connection is lost
> then It prompts for password.
>

Right.

>
> Regarding session variables as long as flask session is not expired we
> uses same session variables. But in case of user logout (due to
> flask session expire) we create new transaction id and sets new session
> variables for that particular Sql editor /datagrid instance.
>

I mean DB session variables (and related things). For example, if the user
executed queries such as the following, then they absolutely need to know
if the session got reset:

CREATE TEMPORARY TABLE ....
SET ROLE ...
SET [various other options]

If the user has done any of those things (or similar things that I haven't
thought of), then we cannot just blindly reset the database connection.

--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company