SQL Alchemy and multi-device multi-session

[davidcer]

I am using on my project SQL Alchemy with Session Maker. Installed Flask-Security-Too as a bootsstrap for user authentication.
But what I detected is that when I do multi-device CRUD (i.e. I have a user on my desktop). Log-in with the user on my cell an update a user date), it gives an error:

sqlalchemy.exc.InvalidRequestError: Object '<User at 0x7f8dc23c7670>' is already attached to session '10' (this is '1')

So, I think that in some way this happens because Flask Security Too doesn't follows strictly Session Guideliness (none session is close after a query). Do I have a correct diagnostic? I have the idea that adding session close woudl be a solution and I am going to go in that direction.

[jwag956]

FS requires the provided datastore manage sessions - I.e. pushing/creating on request entry and popping/closing on request pop.
FS ships with 4 pre-defined/supported datastores - for example the SQLAlchemyUserDatastore uses Flask-SQLAlchemy that manages pushing and popping sessions (using SQLAlchemy scoped_session).

I am not familiar with 'Session Maker' - but you might have to define your own DataStore (possibly similar to SQLAlchemySessionUserDatastore)

[davidcer]

Session Maker is a feature of SQLAlchemy to create sessions. I uderstand that is the factory of the scopped_session you are referring to:
https://docs.sqlalchemy.org/en/20/orm/contextual.html

My doubt (I spent the weekend reviewing the code) is if FS efectivly closes the session after a request or remains open. May be is needed for lazzy loading.

By the error I suppose that some request my keep the connection open (and for SQLAchemy is a problem since this migth happen):

sqlalchemy.exc.InvalidRequestError: Object '<User at 0x7f8dc23c7670>' is already attached to session '10' (this is '1')

ps. After using FS I noted a notorious increment on postgres connections that remains open.

[jwag956]

So are you using SQLAlchemySessionUserDatastore as your FS datastore? and can you share your configuration code?

[davidcer]

yes! for sure. thanks:

DB Configuration:
`from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.orm import scoped_session
from sqlalchemy.ext.declarative import declarative_base

import os

SQLALCHEMY_DATABASE_URI= os.environ.get('SQLALCHEMY_DATABASE_URI')

engine = create_engine(SQLALCHEMY_DATABASE_URI, pool_size=14)

SessionFactory = scoped_session(sessionmaker(bind=engine))

query = SessionFactory.query_property()

session = SessionFactory()

Base = declarative_base()

Base.query = query`

FS:
`app = Flask(name)

babel = Babel(app, default_locale='es')

app.config['SECRET_KEY'] = 'super secret key'
app.config['DEBUG'] = True
app.config['TEMPLATES_AUTO_RELOAD'] = True
app.config['SEND_FILE_MAX_AGE_DEFAULT'] = 0
app.config['SECURITY_DEFAULT_REMEMBER_ME'] = True
app.config['SECURITY_FRESHNESS'] = datetime.timedelta(hours=24)
app.config['SECURITY_FRESHNESS_GRACE_PERIOD'] = datetime.timedelta(hours=1)
app.config['SECURITY_PASSWORD_HASH'] = "pbkdf2_sha256"
app.config['SECURITY_PASSWORD_SALT'] = app.config['SECRET_KEY']
app.config['SECURITY_REGISTERABLE'] = True
app.config['RETURN_GENERIC_RESPONSES'] = True
app.config['SECURITY_CONFIRMABLE'] = True
app.config['SECURITY_SEND_REGISTER_EMAIL'] = True
app.config['SECURITY_POST_REGISTER_VIEW'] = "/profile"
app.config['SECURITY_RECOVERABLE'] = True
app.config['SECURITY_RESET_URL'] = "/restablecer_contrasena"
app.config['EMAIL_SENDER'] = 'xxx'
app.config['EMAIL_SUBJECT_REGISTER'] = 'xxx'
app.config['MAIL_SERVER'] = 'smtp.mailgun.org'
app.config['MAIL_PORT'] = 587
app.config['MAIL_USE_TLS'] = True
app.config['MAIL_DEFAULT_SENDER'] = 'xxx'
app.config['MAIL_USERNAME'] = 'xxx'
app.config['MAIL_PASSWORD'] = 'xxx'

from flask_security import RegisterForm
from wtforms import StringField, EmailField, validators
from wtforms.validators import DataRequired

user_datastore = SQLAlchemySessionUserDatastore(session, User, Role)
app.security = Security(app , user_datastore )`

Flask Application Instantiation:

python3 /root/xxx/venv/bin/gunicorn myapp:app -k gevent -w 5 --reload -p 8000

[davidcer]

@jwag956 just a question about something we have been talking about. would this operator on flask app create any conflict with flask-principal?

@app.after_request def after_request_func(response): try: session.close() except: logger.warning('problems when closing the session') return response

Thee idea is to assure that all sessions of instances of SessionFactory are properly closed.

[jwag956]

Sorry I haven't had much time to think about this. I did try to write a unit test that exposed this same issue but so far no luck.
However, here's my initial take on this (further discussion always encouraged).
FS has support for 4 datastores (really ORMs) and how they manage connections/sessions differs. In general though, FS really shouldn't manage sessions/connections since it doesn't really know anything about the application and how the application wants/needs to manage its DB.

For Flask-SQLAlchemy (SQLAlchemyUserDatastore) - it handles the session completely.
For SQLALchemySessionUserDatastore the application provides the session and it seems reasonable that the application be responsible for managing it.

Note that Flask-SQLAlchemy registers @app.teardown_appcontext(self._teardown_session) which manages the session - you might try the same thing.