使用 sqlalchemy 如何在每个请求的基础上动态绑定到数据库引擎 [英] With sqlalchemy how to dynamically bind to database engine on a per-request basis

问题描述

我有一个基于 Pylons 的 Web 应用程序，它通过 Sqlalchemy (v0.5) 连接到 Postgres 数据库.为了安全起见，我没有使用一般的 Postgres 用户(例如webapp")，而不是遵循简单 Web 应用程序的典型模式(如几乎所有教程中所见)，而是要求用户输入他们自己的 Postgres 用户 ID 和密码，并使用它来建立连接.这意味着我们可以获得 Postgres 安全性的全部好处.

更复杂的是，有两个独立的数据库要连接.尽管它们目前在同一个 Postgres 集群中，但它们需要能够在以后移动到不同的主机.

我们正在使用 sqlalchemy 的 declarative 包，尽管我看不出这与此事有任何关系.

大多数 sqlalchemy 示例都展示了一些简单的方法，例如在应用程序启动时使用通用数据库用户 ID 和密码设置一次元数据，该用户 ID 和密码通过 Web 应用程序使用.这通常通过 Metadata.bind = create_engine() 完成，有时甚至在数据库模型文件中的模块级别.

我的问题是，我们如何推迟建立连接，直到用户登录，然后(当然)为每个后续请求重新使用这些连接，或使用相同的凭据重新建立它们.

我们有这项工作 - 我们认为 - 但我不仅不确定它的安全性，而且我还认为它在这种情况下看起来非常重要.

在 BaseController 的 __call__ 方法中，我们从 web session 中检索用户 ID 和密码，为每个数据库调用一次 sqlalchemy create_engine()，然后调用重复调用 Session.bind_mapper() 的例程，即使任何给定的请求通常只引用一个或两个表，对于可能在这些连接中的每一个上引用的每个表，一次.它看起来像这样:

# 在 BaseController 类的 lib/base.py 中def __call __(自我，环境，start_response):# 注意:网络会话包含 {'username': XXX, 'password': YYY}url1 = 'postgres://%(username)s:%(password)s@server1/finance'% 会话url2 = 'postgres://%(username)s:%(password)s@server2/staff' % 会话财务 = create_engine(url1)工作人员 = create_engine(url2)db_configure(staff, Finance) # 见下文... 等等# 在另一个文件中会话 = scoped_session(sessionmaker())def db_configure(员工，财务):s = 会话()从 db.finance 导入员工、客户、发票对于 c 在 [员工，顾客，发票，]:s.bind_mapper(c, 财务)从 db.staff 导入项目，小时对于 c 在 [项目，小时，]:s.bind_mapper(c, 工作人员)s.close() # 防止会话之间的连接泄漏?

所以 create_engine() 调用发生在每个请求上......我可以看到它是需要的，连接池可能会缓存它们并明智地做事.

但是在 每个 请求上为 每个 表调用一次 Session.bind_mapper() 吗?似乎必须有更好的方法.

显然，由于对强大安全性的渴望是这一切的基础，我们不希望为高安全性用户建立的连接有任何机会被低安全性用户在以后的请求中无意中使用.

解决方案

将全局对象(映射器、元数据)绑定到用户特定的连接并不是一个好方法.以及使用作用域会话.我建议为每个请求创建新会话并将其配置为使用特定于用户的连接.以下示例假定您为每个数据库使用单独的元数据对象:

binds = {}Finance_engine = create_engine(url1)binds.update(dict.fromkeys(finance_metadata.sorted_tables，finance_engine))# 使用到联合表的映射时需要以下行(例如# 在联合表继承中)由于 SQLAlchemy 0.5.4 中的错误(或错误功能).# 这个问题可能会在新版本中修复.binds.update(dict.fromkeys([Employee, Customer, Invoice], Finance_engine))Staff_engine = create_engine(url2)binds.update(dict.fromkeys(staff_metadata.sorted_tables，staff_engine))# 见上面的评论.binds.update(dict.fromkeys([Project, Hour], staff_engine))session = sessionmaker(binds=binds)()

I have a Pylons-based web application which connects via Sqlalchemy (v0.5) to a Postgres database. For security, rather than follow the typical pattern of simple web apps (as seen in just about all tutorials), I'm not using a generic Postgres user (e.g. "webapp") but am requiring that users enter their own Postgres userid and password, and am using that to establish the connection. That means we get the full benefit of Postgres security.

Complicating things still further, there are two separate databases to connect to. Although they're currently in the same Postgres cluster, they need to be able to move to separate hosts at a later date.

We're using sqlalchemy's declarative package, though I can't see that this has any bearing on the matter.

Most examples of sqlalchemy show trivial approaches such as setting up the Metadata once, at application startup, with a generic database userid and password, which is used through the web application. This is usually done with Metadata.bind = create_engine(), sometimes even at module-level in the database model files.

My question is, how can we defer establishing the connections until the user has logged in, and then (of course) re-use those connections, or re-establish them using the same credentials, for each subsequent request.

We have this working -- we think -- but I'm not only not certain of the safety of it, I also think it looks incredibly heavy-weight for the situation.

Inside the __call__ method of the BaseController we retrieve the userid and password from the web session, call sqlalchemy create_engine() once for each database, then call a routine which calls Session.bind_mapper() repeatedly, once for each table that may be referenced on each of those connections, even though any given request usually references only one or two tables. It looks something like this:

# in lib/base.py on the BaseController class
def __call__(self, environ, start_response):

    # note: web session contains {'username': XXX, 'password': YYY}
    url1 = 'postgres://%(username)s:%(password)s@server1/finance' % session
    url2 = 'postgres://%(username)s:%(password)s@server2/staff' % session

    finance = create_engine(url1)
    staff = create_engine(url2)
    db_configure(staff, finance)  # see below
    ... etc

# in another file

Session = scoped_session(sessionmaker())

def db_configure(staff, finance):
    s = Session()

    from db.finance import Employee, Customer, Invoice
    for c in [
        Employee,
        Customer,
        Invoice,
        ]:
        s.bind_mapper(c, finance)

    from db.staff import Project, Hour
    for c in [
        Project,
        Hour,
        ]:
        s.bind_mapper(c, staff)

    s.close()  # prevents leaking connections between sessions?

So the create_engine() calls occur on every request... I can see that being needed, and the Connection Pool probably caches them and does things sensibly.

But calling Session.bind_mapper() once for each table, on every request? Seems like there has to be a better way.

Obviously, since a desire for strong security underlies all this, we don't want any chance that a connection established for a high-security user will inadvertently be used in a later request by a low-security user.

解决方案

Binding global objects (mappers, metadata) to user-specific connection is not good way. As well as using scoped session. I suggest to create new session for each request and configure it to use user-specific connections. The following sample assumes that you use separate metadata objects for each database:

binds = {}

finance_engine = create_engine(url1)
binds.update(dict.fromkeys(finance_metadata.sorted_tables, finance_engine))
# The following line is required when mappings to joint tables are used (e.g.
# in joint table inheritance) due to bug (or misfeature) in SQLAlchemy 0.5.4.
# This issue might be fixed in newer versions.
binds.update(dict.fromkeys([Employee, Customer, Invoice], finance_engine))

staff_engine = create_engine(url2)
binds.update(dict.fromkeys(staff_metadata.sorted_tables, staff_engine))
# See comment above.
binds.update(dict.fromkeys([Project, Hour], staff_engine))

session = sessionmaker(binds=binds)()

这篇关于使用 sqlalchemy 如何在每个请求的基础上动态绑定到数据库引擎的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！