Per user connection using SQLAlchemy ORM

[Torsten Irländer]

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

[Lele Gaifax]

Torsten Irländer <tor...@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 quello che ho pensato ieri
real: Emanuele Gaifas | comincerò ad aver paura di chi mi copia.
le...@metapensiero.it | -- Fortunato Depero, 1929.

[Torsten Irländer]

On Fri, Jul 25, 2014 at 09:44:32AM +0200, Lele Gaifax wrote:
> Torsten Irländer <tor...@irlaender.de>
> writes:

Hi Lele,

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?

Torsten

[Jonathan Vanasco]

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.  

[Lele Gaifax]

Torsten Irländer

<torsten....@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).

[Lele Gaifax]

Jonathan Vanasco <jona...@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.

[Brian Sutherland]

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

[Wichert Akkerman]

On 25 Jul 2014, at 09:44, Lele Gaifax <le...@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.

[Lele Gaifax]

Wichert Akkerman <wic...@wiggy.net>
writes:

> That code looks dangerous to me.

Yes, I took note about that, thank you! Luckily both events are unlikely
to happen, but are surely worth some more robust code.

I'll try to figure out how SA parses the DB URL, to see if/how the
trickiest of the problems (the one with the password containing
"@othermachine") is actually avoidable. Maybe I could completely rewrite
that code bypassing engine_from_config() and use something like

http://docs.sqlalchemy.org/en/rel_0_9/core/engines.html#custom-dbapi-args

instead.

thank you,
bye, lele.

[Torsten Irländer]

Am Freitag, 25. Juli 2014 08:55:29 UTC+2 schrieb Torsten Irländer:

Hi,

Does anyone have experience in this?

Thanks everyone for the answers and hints to be aware of. It helped me a lot!

Similar to Lele my application will not have too many users. So exceeding the max connection to the db is not very likely.
The main reason of doing this for me is to get rid of the saved password in the ini file. In a setup with a seperate application and db server I do not like the fact that the credetials for a full priviledged user to the database is saved as plaintext on the system where the application lives. It is not that much the point of delegating the permission system into the DB. It is nice to get the option to do this, but this comes with the drawback of loosing the portability of the DBMS and may increase the effort to keep the db permission and application permissions in sync.

So maybe it is enough to stick with one single user but provide the credentials once on application startup.

Torsten

[Laurence Rowe]

If you are only interested in using the database roles for authorization rather than authentication then you could switch role at the beginning of the transaction so you don't have to deal with custom connection pooling, something like:

set_local_role = sqlalchemy.sql.text("SET LOCAL ROLE :role_name;")

@sqlalchemy.event.listens_for(DBSession, 'after_begin')

def switch_to_user(session, sqla_txn, connection):

    request = pyramid.threadlocals.get_current_request()

    connection.execute(set_local_role, role_name=request.authenticated_userid)

Postgres docs: http://www.postgresql.org/docs/current/static/sql-set-role.html example: http://stackoverflow.com/a/19602050/199100

Laurence

[Torsten Irländer]

On Tue, Jul 29, 2014 at 03:59:05PM -0700, Laurence Rowe wrote:
> On Thursday, 24 July 2014 23:55:29 UTC-7, Torsten Irländer wrote:

Hi Laurence,

> > 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.
>

> If you are only interested in using the database roles for authorization
> rather than authentication then you could switch role at the beginning of
> the transaction so you don't have to deal with custom connection pooling,
> something like:
>
> set_local_role = sqlalchemy.sql.text("SET LOCAL ROLE :role_name;")
>
> @sqlalchemy.event.listens_for(DBSession, 'after_begin')
> def switch_to_user(session, sqla_txn, connection):
> request = pyramid.threadlocals.get_current_request()
> connection.execute(set_local_role,
> role_name=request.authenticated_userid)
>
> Postgres docs:
> http://www.postgresql.org/docs/current/static/sql-set-role.html
> example: http://stackoverflow.com/a/19602050/199100

Thanks your your input Laurnce. But as I wrote in another message its about
the opposite :) I am mainly interested in authentification and not authorization.
But maybe your tip becomes handy in the future for me :)

Torsten

--
Torsten Irländer | ++49-541-335 08 30 | http://www.intevation.de/
Intevation GmbH, 49074 Osnabrück, DE | AG Osnabrück, HR B 18998
Geschäftsführer: Frank Koormann, Bernhard Reiter, Dr. Jan-Oliver Wagner

[Jonathan Vanasco]

On Tuesday, July 29, 2014 6:59:05 PM UTC-4, Laurence Rowe wrote:

If you are only interested in using the database roles for authorization rather than authentication then you could switch role at the beginning of the transaction so you don't have to deal with custom connection pooling, something like:

set_local_role = sqlalchemy.sql.text("SET LOCAL ROLE :role_name;")

@sqlalchemy.event.listens_for(DBSession, 'after_begin')

def switch_to_user(session, sqla_txn, connection):

    request = pyramid.threadlocals.get_current_request()

    connection.execute(set_local_role, role_name=request.authenticated_userid)

Postgres docs: http://www.postgresql.org/docs/current/static/sql-set-role.html example: http://stackoverflow.com/a/19602050/199100

That's a brilliant approach.  Thanks for sharing!