[horde] SQL Session Handler - Update...

[John C. Amodeo]

It seems the Postgres logs are throwing the following error when the 
user's session expires and they try to renew their connection:

postgres[18698]: [3] ERROR:  Cannot insert a duplicate key into unique 
index horde_sessionhandler_pkey

I think this has something to do with the "double login" problem I 
mention below...

If anyone has any insight, I'd really be happy to hear about it.

TIA, -John

John C. Amodeo wrote:

> Greetings,
>
> I've been working on updating the code for the 
> lib/SessionHandler/pgsql.php module, and I'm running into a glitch 
> somewhere in the system that I can't seem to get past.  I was hoping 
> one of the developers (who knows how session management works better 
> than I do) can assist me to find a solution to the problem.
>
> Background:
> It seems the pgsql.php file included in Horde 2.2.5 used php functions 
> that are obsolete in newer versions of php and the code needs to be 
> updated.  In addition, session_data is stored in a 'text' type field, 
> which seems to be problematic and causes strange behavior to occur 
> while using IMP/Turba/Mnemo, etc...
>
> Goal:
>
> 1) Update all PG functions to comply with changes in PHP standards
> 2) Change storage method to make use of the Postgres "BYTEA" data type 
> to eliminate problems with session management when using the "TEXT" 
> type field.
> 3) Fix a few problems with the code.
> 4) Supply a patch to the Horde list
>
> The new Postgres database looks like this:
>
>        Column        |         Type          | Modifiers
> ----------------------+-----------------------+-----------
> session_id           | character varying(32) | not null
> session_lastmodified | integer               | not null
> session_data         | bytea                 |
> Indexes: horde_sessionhandler_pkey primary key btree (session_id)
>
> Here's a partial list of deprecated PHP functions that were updated:
>
> Old Function --> New Function
> pg_exec --> pg_query
> pg_numrows --> pg_num_rows
> pg_freeresult --> pg_free_result
> etc...
>
> Results:
>
> We've added a function to the code, using "pg_escape_bytea" that 
> formats the session data for storage into the SQL server.  I've tested 
> the new code for about 2 weeks now, and everything works beautifully 
> *except* for one problem, which I have found exists even with the old 
> code.
>
> Basically, under a certain condition, the old Session ID fails to get 
> deleted from the SQL table.  The end result to the user is he/she must 
> log in twice *if* his/her session expires while using web mail.  With 
> more detail, this is how you would reproduce the error:
>
> 1) User logs into web mail.  Everything works fine.  Session 
> information is properly stored in the SQL tables.
> 2) If there is a significant amount of time where the user does 
> nothing, the session expires.
> 3) When the user comes back to the system and tries to use any Horde 
> application, the user is brought to the log in page with a message 
> that the "Session Has Expired, bla bla bla"
> 4) The user must then enter credentials and log back into the system.
> 5) Login is successful (according to Horde log and imap server log) 
> but the session is still expired.  The Horde sessionhandler throws 
> errors to the log "Failed to delete session etc..."
> 6) User is brought back to the login page, enters credentials again, 
> and this time the SessionHandler successfully deletes the expired 
> Session ID, creates a new one, and the user can successfully use Horde 
> applications.
>
> This consistently happens *every* time.  (This is not a sporadic 
> problem...)  I know this problem exists with the 2.2.5 Postgres 
> module, but I looked at the code for MySQL and the logic is the same, 
> so it may exist there as well...
>
> I had thought for a while that there may be some error in the query 
> that gets sent to the SQL server, but if I copy and paste the string 
> from the error logs directly into the SQL server buffer, the command 
> executes correctly and deletes the Session ID.
>
> I've had 3 sets of eyes look at this (2 of whom are really good 
> database programmers) and we cannot figure out where the problem is...
>
> I was thinking the problem might lie in the logic sequence of checking 
> for expired sessions, logging into the system, etc., but I don't know 
> enough about the tie in between the session handler, imp/login.php, or 
> lib/Horde.php to trace what's going on...
>
> Nonetheless, once I get this little bug fixed (or worked around) we'll 
> have an updated session handler for Postgres that works significantly 
> better than the current one...
>
> So, if anyone has any ideas, I'd love to know what you're thinking...
>
> Thanks in advance.
> -John