I'm in the process of migrating a Pylons app to Pyramid. It uses a
SQLAlchemy/MySQL back end for sessions (the session DB is separate from the
main app DB).

I swapped out Beaker's SessionMiddleware with a pyramid_beaker session
factory. This seems like a straightforward transformation, and under the
hood pyramid_beaker looks quite similar to SessionMiddleware (i.e., the way
the session object is initialized per request is very similar).

The problem I'm having is this: session DB connections aren't being closed,
which causes the app to hang. If I switch to a file based back end,
everything works fine.

Most likely, I'm overlooking something that should be obvious, but has
anyone run into similar issues when moving from SessionMiddleware to
pyramid_beaker?

I'm staring at the code for both pyramid_beaker and the SessionMiddleware,
and in both cases I do not see any code to close the session. The behavior
looks to be consistent between the two to me. Neither library appears to do
anything unless the session object was accessed, and in both cases they
simply call session.persist() on a SessionObject.

https://github.com/bbangert/beaker/blob/master/beaker/middleware.py#L136
https://github.com/Pylons/pyramid_beaker/blob/master/pyramid_beaker/_...

On Wed, Feb 6, 2013 at 3:33 PM, Wyatt Baldwin
<wyatt.lee.bald...@gmail.com>wrote:

what are you using to manage sql connections in your app ?

i dropped pyramid's transaction management and went with my own
solution - which registers a db cleanup routine via a tween.
depending on how your connections are managed, you might be missing a
cleanup.

please post the fix when you figure it out.  i'm considering a similar
scheme, but haven't gotten to this yet.  our production sessions are
on Memcached now, and i use a secure cookie to autologin if it's
lost... but I want to consider moving to mysql based sessions, which
can sit on their own server ( which is entirely separate from our
postgresql data store ).

I've partially figured it out, and it looks like it's an issue with my
configuration, not pyramid_beaker.

The way I had things set up, *every* request (and, in particular, static
requests) was accessing the `request.session` property. I've changed things
so this isn't the case, but what I can't figure out is why that would
really matter. I mean, you should avoid accessing the session if you're not
using it, but merely touching the session property shouldn't cause a DB
connection, and even it does, the connection should still be closed.

Basically, my current "solution" is simply to not access the session as
much, which isn't very robust.

One thought on what may be causing this is that some of these requests are
relatively long-running. Increasing the pool size for the session engine
may help...

I'll post more later.

Most session implementations (including beaker) have a timeout setting to
decide how long the session data should remain valid (server side,
independent of the cookie expire settings). When you access request.session
it's going to load the session from storage and update the last accessed
time as a way of keeping the session alive. If it didn't work this way
you'd run into scenarios where a user is live and active on your site but
keeps getting timed out based on when their session first started.

Though I can't explain why the connections are staying open.

On Thu, Feb 7, 2013 at 9:44 AM, Wyatt Baldwin
<wyatt.lee.bald...@gmail.com>wrote:

Forgot to mention that I'm using a slightly modified version of Beaker's
ext:database back end, but when I switch to the stock ext:database back
end, the issue is still present.

hm, i'm looking at the ext source ( https://github.com/bbangert/beaker/blob/master/beaker/ext/database.py
) and the files mike noted as well.

i likely missed something, but I don't see anything in the
ext.backend or pyramid_beaker that handles closing connections, and it
looks like very rudimentary connection/pooling management

just a couple ideas , likely bad ones :

how many requests /connections do you need to re-create this ?  could
you try using a query log that shows the connection id ?  that helps
me bugfix most sql and framework issues

could you try a dirty hack, save the session connection to a request
(or other) attribute, and then use a subscriber to manually close
it ?

is there anything in the connection string that is overlapping with
your app ?  any chance sqlalchemy is keeping the app open because it
thinks it's the session ?

On Wed, Feb 6, 2013 at 1:33 PM, Wyatt Baldwin
<wyatt.lee.bald...@gmail.com>wrote:

Good to know that database sessions work. I've never done them because the
Beaker documentation was so incomplete on what the settings should be and I
didn't want to guess. Can you post your settings?

I use file-based sessions because they're the default although they're kind
of cumbersome. Except one case where I used memory sessions because it was
a desktop app that wasn't supposed to write to the disk. When I asked Ben
about which session backend to use at one point, he said everybody's moving
to Memcached. I never did because the data disappears if you reboot the
server and users get annoyed if their session gets dropped in the middle or
they have to log in again, especially if they've just filled out a long
form.

FWIW - to get around that , I use an autologin routine...

1. I set an autologin cookie for anywhere from 1-30 days ( 1 if I
decided to set it, 30 if the user wants to be remembered )
2. If I catch a user who is logged out, I check for the autologin
cookie.  If that works, I redirect them to the autologin url, process
the cookie, and then redirect back to the resource.  ( this could
probably all happen within a single page, but this was fast )
3. To write the autologin cookies, i wrote this library --
https://github.com/jvanasco/insecure_but_secure_enough/blob/master/in...

with this library, i can create an encrypted and/or signed cookie that
is relatively secure.

the basic premise is this:
- the cookie has an encrypted value , and an unencrypted timestamp +
digest
- before doing any expensive decryption, the server can use the
digest / timestamp to decide if it's worth unencrypting ( too old,
invalid , etc )
- the encryption / decryption is handled by a provider, which has
hooks for time-based lookups.

this way you can have your encryption factory change daily, weekly,
monthly, etc.  it's not secure enough for sensitive data, but by the
time 99.999% of people would have broken something, you can be on a
new set of encryption keys.  you can also use this to create a payload
for URL based autologins for emails.

As a rule of thumb, i also note in every session what sort of Login
occured -- a form, autologin, facebook connect, etc.  I always require
a new form login if someone wants to access account settings.

On Feb 8, 12:33 pm, Michael Merickel <mmeri...@gmail.com> wrote:

i think its best to be upfront with the shortcomings of technology in
general!  if you spin up a few AWS instances, you can beat most small
encryptions.

I originally used that , and then looked at some other libraries that
offered signed and encrypted cookies because I didn't like signed
cookies for autologin.

my problems were:
- signed only assets that, within a reasonable amount of probability,
the payload originated on your server
- signed gives the user the serialized payload ( unless you use a
callback/function to encrypt/decrypt ).  i don't necessarily want the
consumer to know what is in the payload.
- there was a lack of mechanism for rotating the signing factory. ie,
have a table of keys that constantly change.

to deter exploitation attempts, most large scale web services / API
providers have a fairly quick expiry on how long the singing can last
and they rotate keys quickly.

so i pieced together a bunch of code from Pyramid's signed_serialize,
Facebook's API pattern, and a few other python libraries to make
something a bit more general-purpose and more-secure.