[pylons-discuss] Per user connection using SQLAlchemy ORM
Hi, I am not sure if this topic should better be in the sqlalchemy group but as it is directly related to the way pyramid web applications are driven often I ended up posting here. I have a webapplication using sqlalchemy and ORM with one single connection (or better a connection pool) to the database. The credentials to connect to the database are set in the ini file. The session is build and the application uses it to talk to the database. Nothing unusual yet. I think this is how many web application setup the db connection. My goal is to replace this central db connection (with credentials held as plaintext in the ini file) with a per request connection with credetials provided by the user during the login process in the webapplication. I know that this comes with performance issues as the connection will be established and closed on every request. Example: Bob calls the login page of the webapplication and provides his username and password. This username and password will be used to initiate a connection for this user. If a connection can be established the username and password can be saved in the session and reused for later connections again. I think/hope basically this should be possible in some way. But how? At least I know that i can rebind the connection of the sqlalchemy session. So i can initiate a new connection for the current request and bind it to the session. But I expect problems here with concurrent requests. So maybe having a session per user might be worth looking closer at. Does anyone have experience in this? Torsten -- You received this message because you are subscribed to the Google Groups pylons-discuss group. To unsubscribe from this group and stop receiving emails from it, send an email to pylons-discuss+unsubscr...@googlegroups.com. To post to this group, send email to pylons-discuss@googlegroups.com. Visit this group at http://groups.google.com/group/pylons-discuss. For more options, visit https://groups.google.com/d/optout.
[pylons-discuss] Re: Per user connection using SQLAlchemy ORM
Torsten Irländer tors...@irlaender.de
writes:
My goal is to replace this central db connection (with credentials held as
plaintext in the ini file) with a per request connection with credetials
provided by the user during the login process in the webapplication. I know
that this comes with performance issues as the connection will be
established and closed on every request.
...
Does anyone have experience in this?
Yes, I used this approach in a couple of web applications, basically
creating a registry where, keyed on ``request.session.id``, I stored a
SA session maker bound to an engine configured explicitly for the the
requesting user.
The sqlalchemy.url in the configuration file is something like
sqlalchemy.url = postgresql://username:password@localhost/database
and this is the content of models/session.py:
import logging
from threading import Lock
from pyramid.httpexceptions import HTTPUnauthorized
from sqlalchemy import engine_from_config
from sqlalchemy.orm import scoped_session, sessionmaker
from zope.sqlalchemy import ZopeTransactionExtension
logger = logging.getLogger(__name__)
lock = Lock()
sessions_registry = {}
def create_session(request=None, username=None, password='', settings=None):
Create a new SQLAlchemy session for a user.
:param request: either ``None`` or the Pyramid request object
:param username: either ``None`` or the name of authenticating user
:param password: when authenticating, the password of the user
:param settings: either None or the Pyramid application settings
This is usually called with just the first argument, the current
`request`.
The main exception is at user login time, when the `username` and
its `password` come from the authentication panel: in this case, a
new SQLAlchemy session factory is created for the given user,
using the configuration stored in `settings`.
if request is not None:
if user_name in request.session:
username = request.session['user_name']
password = request.session['user_password']
config = request.registry.settings
if request.registry.__name__ == 'testing':
session_id = ('cli', username)
else:
session_id = request.session.id
else:
config = settings
session_id = ('cli', username)
lock.acquire()
try:
maker = sessions_registry.get(session_id)
if maker is None:
if username in sessions_registry:
# Remove old session
logger.debug(u'Closing old session for user %s', username)
old_session_id = sessions_registry[username]
del sessions_registry[username]
old_session_maker = sessions_registry[old_session_id]
old_session_maker.close()
old_session_maker.bind.dispose()
del sessions_registry[old_session_id]
url = config['sqlalchemy.url']
if username is None:
url = url.replace(u'username:password@', u'')
else:
url = url.replace(u'username', username)
url = url.replace(u'password', password)
logger.debug(u'Creating new SQLAlchemy DB engine for user %s',
username or u'ADMIN')
# Open a connection, to verify credentials
engine = engine_from_config(config, 'sqlalchemy.',
url=url.encode('utf-8'))
try:
engine.connect().close()
except Exception, e:
logger.warning(u'User %s cannot login on %s: %s',
username or u'ADMIN',
config['sqlalchemy.url'],
str(e).split('\n')[0])
raise HTTPUnauthorized()
else:
logger.info(u'User %s successfully connected on %s',
username or u'ADMIN', config['sqlalchemy.url'])
sm = sessionmaker(autoflush=False, autocommit=False,
bind=engine,
extension=ZopeTransactionExtension())
maker = scoped_session(sm)
sessions_registry[session_id] = maker
sessions_registry[username] = session_id
finally:
lock.release()
return maker()
Then everything, from initializedb to all the views, use this function
to get a SA session.
Maybe there are better ways to accomplish the task, but the above worked
great for me.
Hope this helps,
ciao, lele.
--
nickname: Lele Gaifax | Quando vivrò di
Re: [pylons-discuss] Re: Per user connection using SQLAlchemy ORM
On Fri, Jul 25, 2014 at 09:44:32AM +0200, Lele Gaifax wrote:
Torsten Irländer tors...@irlaender.de
writes:
Hi Lele,
My goal is to replace this central db connection (with credentials held as
plaintext in the ini file) with a per request connection with credetials
provided by the user during the login process in the webapplication. I know
that this comes with performance issues as the connection will be
established and closed on every request.
...
Does anyone have experience in this?
Yes, I used this approach in a couple of web applications, basically
creating a registry where, keyed on ``request.session.id``, I stored a
SA session maker bound to an engine configured explicitly for the the
requesting user.
The sqlalchemy.url in the configuration file is something like
sqlalchemy.url = postgresql://username:password@localhost/database
and this is the content of models/session.py:
import logging
from threading import Lock
from pyramid.httpexceptions import HTTPUnauthorized
from sqlalchemy import engine_from_config
from sqlalchemy.orm import scoped_session, sessionmaker
from zope.sqlalchemy import ZopeTransactionExtension
logger = logging.getLogger(__name__)
lock = Lock()
sessions_registry = {}
def create_session(request=None, username=None, password='',
settings=None):
Create a new SQLAlchemy session for a user.
:param request: either ``None`` or the Pyramid request object
:param username: either ``None`` or the name of authenticating user
:param password: when authenticating, the password of the user
:param settings: either None or the Pyramid application settings
This is usually called with just the first argument, the current
`request`.
The main exception is at user login time, when the `username` and
its `password` come from the authentication panel: in this case, a
new SQLAlchemy session factory is created for the given user,
using the configuration stored in `settings`.
if request is not None:
if user_name in request.session:
username = request.session['user_name']
password = request.session['user_password']
config = request.registry.settings
if request.registry.__name__ == 'testing':
session_id = ('cli', username)
else:
session_id = request.session.id
else:
config = settings
session_id = ('cli', username)
lock.acquire()
try:
maker = sessions_registry.get(session_id)
if maker is None:
if username in sessions_registry:
# Remove old session
logger.debug(u'Closing old session for user %s',
username)
old_session_id = sessions_registry[username]
del sessions_registry[username]
old_session_maker = sessions_registry[old_session_id]
old_session_maker.close()
old_session_maker.bind.dispose()
del sessions_registry[old_session_id]
url = config['sqlalchemy.url']
if username is None:
url = url.replace(u'username:password@', u'')
else:
url = url.replace(u'username', username)
url = url.replace(u'password', password)
logger.debug(u'Creating new SQLAlchemy DB engine for user
%s',
username or u'ADMIN')
# Open a connection, to verify credentials
engine = engine_from_config(config, 'sqlalchemy.',
url=url.encode('utf-8'))
try:
engine.connect().close()
except Exception, e:
logger.warning(u'User %s cannot login on %s: %s',
username or u'ADMIN',
config['sqlalchemy.url'],
str(e).split('\n')[0])
raise HTTPUnauthorized()
else:
logger.info(u'User %s successfully connected on %s',
username or u'ADMIN',
config['sqlalchemy.url'])
sm = sessionmaker(autoflush=False, autocommit=False,
bind=engine,
extension=ZopeTransactionExtension())
maker = scoped_session(sm)
sessions_registry[session_id] = maker
sessions_registry[username] = session_id
finally:
lock.release()
return maker()
Then everything, from initializedb to all the views, use
Re: [pylons-discuss] Re: Per user connection using SQLAlchemy ORM
I have a bit of non-pyramid/sqlalchemy insight / warnings for this: with this design pattern, you're going to need to pay close attention to the database server setup, and (probably) either disable connection pooling or create some sort of management for it. depending on usage, you have the potential to run close to tying up the max_connections. also - is there any reason why you're entirely replacing the database connection? you can easily have multiple database connections / sessions. keep all of your application logic (and auth) on one session; keep all the user/client stuff on a second. just address/use sessions uniquely. -- You received this message because you are subscribed to the Google Groups pylons-discuss group. To unsubscribe from this group and stop receiving emails from it, send an email to pylons-discuss+unsubscr...@googlegroups.com. To post to this group, send email to pylons-discuss@googlegroups.com. Visit this group at http://groups.google.com/group/pylons-discuss. For more options, visit https://groups.google.com/d/optout.
[pylons-discuss] Re: Per user connection using SQLAlchemy ORM
Torsten Irländer torsten.irlaen...@intevation.de writes: It does! Thanks you very much for the input. Did you encounter any drawbacks in contrast to the usual way to do establish db connections? E.g is it really an issue with the performance? No, never noticed at least. The kind of apps I used it for is an intranet ExtJS client, with say dozens of contemporary users, with long lived sessions (tipically all the day). ciao, lele. -- nickname: Lele Gaifax | Quando vivrò di quello che ho pensato ieri real: Emanuele Gaifas | comincerò ad aver paura di chi mi copia. l...@metapensiero.it | -- Fortunato Depero, 1929. -- You received this message because you are subscribed to the Google Groups pylons-discuss group. To unsubscribe from this group and stop receiving emails from it, send an email to pylons-discuss+unsubscr...@googlegroups.com. To post to this group, send email to pylons-discuss@googlegroups.com. Visit this group at http://groups.google.com/group/pylons-discuss. For more options, visit https://groups.google.com/d/optout.
[pylons-discuss] Re: Per user connection using SQLAlchemy ORM
Jonathan Vanasco jonat...@findmeon.com writes: I have a bit of non-pyramid/sqlalchemy insight / warnings for this: with this design pattern, you're going to need to pay close attention to the database server setup, and (probably) either disable connection pooling or create some sort of management for it. depending on usage, you have the potential to run close to tying up the max_connections. Thank you for the head up, but I never got close to that limit: as said in my other message, the kind of app was an intranet ExtJS web client, for a small to medium sized company. also - is there any reason why you're entirely replacing the database connection? you can easily have multiple database connections / sessions. keep all of your application logic (and auth) on one session; keep all the user/client stuff on a second. just address/use sessions uniquely. The apps served sensitive data, so I wanted to be absolutely sure that any single user could only see what the his profile allowed, and I built very strict visibility rules within the database, both horizontal (ie which tables) and vertical (ie which records). Also, in this way the database itself knows the exact user, and every change was logged with the account that caused it. This dramatically simplified the Pyramid bridge, no ACLs, no chance of introducing security bugs due to careless filtered queries, no way of bypassing the visibility rules, and so on. ciao, lele. -- nickname: Lele Gaifax | Quando vivrò di quello che ho pensato ieri real: Emanuele Gaifas | comincerò ad aver paura di chi mi copia. l...@metapensiero.it | -- Fortunato Depero, 1929. -- You received this message because you are subscribed to the Google Groups pylons-discuss group. To unsubscribe from this group and stop receiving emails from it, send an email to pylons-discuss+unsubscr...@googlegroups.com. To post to this group, send email to pylons-discuss@googlegroups.com. Visit this group at http://groups.google.com/group/pylons-discuss. For more options, visit https://groups.google.com/d/optout.
Re: [pylons-discuss] Per user connection using SQLAlchemy ORM
On Thu, Jul 24, 2014 at 11:55:29PM -0700, Torsten Irländer wrote: I think/hope basically this should be possible in some way. But how? At least I know that i can rebind the connection of the sqlalchemy session. So i can initiate a new connection for the current request and bind it to the session. But I expect problems here with concurrent requests. So maybe having a session per user might be worth looking closer at. Does anyone have experience in this? I've had great success just instantiating a sqlalchemy.orm.Session object directly in a reify'd property on the request. i.e. Not using a sessionmaker or scoped session at all. -- Brian Sutherland -- You received this message because you are subscribed to the Google Groups pylons-discuss group. To unsubscribe from this group and stop receiving emails from it, send an email to pylons-discuss+unsubscr...@googlegroups.com. To post to this group, send email to pylons-discuss@googlegroups.com. Visit this group at http://groups.google.com/group/pylons-discuss. For more options, visit https://groups.google.com/d/optout.
Re: [pylons-discuss] Per user connection using SQLAlchemy ORM
On 25 Jul 2014, at 09:44, Lele Gaifax l...@metapensiero.it wrote: url = config['sqlalchemy.url'] if username is None: url = url.replace(u'username:password@', u'') else: url = url.replace(u'username', username) url = url.replace(u'password', password) That code looks dangerous to me. If someone picks “password” as username the results will be interesting. Worse things will happen if you other characters. For example what my password is “password@192.168.0.100/“ – will that make the system try to connect to a PostgreSQL server at 192.168.0.100? Wichert. -- You received this message because you are subscribed to the Google Groups pylons-discuss group. To unsubscribe from this group and stop receiving emails from it, send an email to pylons-discuss+unsubscr...@googlegroups.com. To post to this group, send email to pylons-discuss@googlegroups.com. Visit this group at http://groups.google.com/group/pylons-discuss. For more options, visit https://groups.google.com/d/optout.
