python - With sqlalchemy how to dynamically bind to database engine on a per-request basis -
I have a Pillons-based web application that connects to a postgraze database (v0.5). Instead of following the simple pattern of simple web app, for safety, (as seen only in all tutorials), I'm not using a generic postgrazy user (like "webpap"), but I want it That the user is using his own postgraduate user id and password, and using that connection. This means that we get the full benefit of postgrace security.
To make things still complicated, there are two separate databases to connect to it, although they are currently in the same postgres cluster, they should be able to move different hosts at a later date.
We are using the package of Sclaimimi, although I do not know whether this is any effect on the case.
Most examples of sclechemy are used with a normal database user with common applications and once set metadata with password, such as through a web application. It usually creates metadata.bind = (), sometimes even on the module-level in database model files.
My question is that we can postpone the establishment of the connection until the user is logged in, and then (of course) reuse those connections, or for each subsequent request, Restore using same credentials.
We do this work - we think - but I'm definitely not sure about it, I also feel that it looks incredibly heavy weight for the situation.
Within the BaseController's __Call __ method we retrieve the useraddress and password. Web Session, call sclechemy_egin for each database, then call a regular Call, which calls for a session. Bund_mapper () Repeatedly, once for each table that can be , is referred to at each of those connections, even if any given request usually refers to only one or two tables This looks like this: BaseController class def __call __ (in self, environment, start_response) in lib / base.py in
#: # Note: {'Username' in the Web session: XXX, 'password': YYY} url1 = 'postgres: //% (username) s:% (password) s @ server1 / finance'% session url2 = 'postgres: //% (username) s:% (password) S @ Server2 / staff '% finance finance = Create_engine (url1) employee = create_engine (url2) db_configure (employee, finance) # see below ... etc # Sessions in another file = scoped_session (sessionmaker ()) def db_configure (employee, finance): S = session () Db.finance Import Employee, Client, [Employee, Client, Invoice, In] Invoice for: db.staff Import Project to s.bind_mapper (c, finance), [project, hour,]: s.bind_mapper (c, Staff) s.close () prevents the interaction between # sessions? Then there are create_engine () calls on every request ... I can see that the need is happening, and the connection pool probably caches them and does something wisely < Em
but call.bind_mapper () to call ? It seems that should be in better way.
Obviously, because strong desire for strong security is under all of this, we should not have any chance that a connection to the high security user will be used unknowingly by a low-security user later in the request. .
Compulsive global objects (Mappers, Metadata) for the user-specified connection is not a good way, as well as scope Using the session. I recommend creating a new session for each request and configuring it to use a user-specific connection. The following sample assumes that you use different metadata objects for each database:
binds = {} finance_engine = create_engine (url1) binds.update (dict.fromkeys (finance_metadata. Due to bugs (or misconceptions) in # SQLAlchemy 0.5.4, the following line is required when the mapping of the combined tables is used (Example # The Joint Table Inherited). # This problem may be fixed in new versions. Binds.update (dict.fromkeys ([employee, customer, invoice], finance_ignin) employee_join = making_image (url2) binds.update (dict.fromkeys (staff_metadata.sorted_tables, staff_engine)) # See the comment above. Binds.update (dict. Fromkeys ([project, hour], staff_agine) session = session maker (bind = binds) ()
Comments
Post a Comment