在sqlalchemy中管理用户权限

Question

我有一个sqlalchemy脚本，它创建并使用表示许多用户连接的许多引擎实例。所有引擎都配置为指向同一个postgres数据库。在

我有一个引擎，oSuperEngine可以做超级工作。我有另一个引擎“obobesengine”给鲍勃。在

现在我在做这样的事情：

sSQL = "GRANT ALL PRIVILEGES ON TABLE \"NICE_TABLE\" to bob;"
oSuperEngine.execute(sSQL)

sSQL = "insert into \"NICE_TABLE\" (foo) values (bar)"
oBobsEngine.execute(sSQL) # ERROR HERE

得到：

^{pr2}$

为什么会这样？在

在psql中\dp告诉我bob从未被授予权限。如果我用超级用户登录psql并手动授予bob权限，那么一切都会正常工作。在本例中，我使用的命令与通过sqlalchemy执行的命令完全相同。在

是不是有些东西冲得不对？sqlalchemy在尝试grant语句时是否出于某种原因喜欢静默失败？我该怎么做？在

很抱歉没有包含更多的代码，代码库有点复杂。事件的顺序已经通过日志向我确认。。。如果你需要更多的代码来理解我的问题，请告诉我。在

一些原木

2014-03-10 10:07:24,767 - common.sqlalchemy_tools - DEBUG - connection string = "postgresql+psycopg2://super:password@localhost/db_name"
2014-03-10 10:07:24,767 - sqlalchemy.engine.base.Engine - INFO - BEGIN;
2014-03-10 10:07:24,767 - sqlalchemy.engine.base.Engine - INFO - {}
2014-03-10 10:07:24,767 - sqlalchemy.engine.base.Engine - INFO - GRANT ALL PRIVILEGES ON TABLE "MY_TABLE" to bob;
2014-03-10 10:07:24,767 - sqlalchemy.engine.base.Engine - INFO - {}
2014-03-10 10:07:24,768 - sqlalchemy.engine.base.Engine - INFO - COMMIT;
2014-03-10 10:07:24,768 - sqlalchemy.engine.base.Engine - INFO - {}
 ...
2014-03-10 10:07:24,804 - common.sqlalchemy_tools - DEBUG - connection string = "postgresql+psycopg2://bob:password@localhost/db_name"
2014-03-10 10:07:24,814 - sqlalchemy.engine.base.Engine - INFO - BEGIN;
2014-03-10 10:07:24,815 - sqlalchemy.engine.base.Engine - INFO - {}
2014-03-10 10:07:24,827 - sqlalchemy.engine.base.Engine - INFO - insert into "MY_TABLE" (stuff) values (other stuff);
2014-03-10 10:07:24,827 - sqlalchemy.engine.base.Engine - INFO - {}
2014-03-10 10:07:24,828 - sqlalchemy.engine.base.Engine - INFO - ROLLBACK

第一组内容与oSuperEngine有关，第二组与bob的引擎有关。错误看起来像：

ProgrammingError: (ProgrammingError) permission denied for relation MY_TABLE
 'insert into "MY_TABLE" (stuff) values (stuff);' {}

Answer 1

如果执行上面的脚本，就会遇到事务隔离问题。其实我前段时间也有类似的问题。这里要记住的是，对于Bob的事务没有看到超级引擎所做的更改，因为事务仍然在运行，并且隔离不允许进行未提交的读取。Postgres有关于这个的extensive documentation，但本质是：在Postgres中不可能有未提交的阅读。在

显然，您的解决方案是先从超级引擎提交更改，然后在另一个事务中使用它。如果没有尝试过，但我猜您必须保持默认的Read committed隔离级别（因为更高的隔离级别不允许检测到事务（在本例中是bob）已启动后的更改）。在

因此，在执行Bobs查询之前：

oSuperEngine.execute("COMMIT")

然而，这带来了一个附带的问题，它有效地破坏了事务给您带来的可怕的东西：您不能轻松地回滚，因为更改已经提交。基本上你想要的是一个与保存点相反的东西：

虽然savepoint还没有存储对数据库的更改，但它确保回滚只会返回到特定的点，而不是一直返回。相反，您需要的是：将其存储在数据库中，但能够回滚并再次从中删除它。我不知道这样的事情，我高度怀疑它的存在，因为它会违反事务隔离的原则。在

我对这个问题的解决方案是编写自定义回滚例程，在异常时调用这些例程，以便可以手动撤消更改。这是一个相当大的工作，但从长远来看是高维护。最后，在我的例子中，我重新考虑了我的解决方案，放弃了多引擎方法（但这是一个你必须自己做的决定）。在

Answer 2

我也有同样的问题。通过这样做来修复：

sSQL = "GRANT ALL PRIVILEGES ON TABLE \"NICE_TABLE\" to bob;"
with oSuperEngine.begin() as conn:
    conn.execute(sSQL)

http://docs.sqlalchemy.org/en/latest/orm/session_basics.html#adding-additional-configuration-to-an-existing-sessionmaker