在sqlalchemy中管理用户权限 [英] managing user privileges in sqlalchemy

问题描述

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

I have an sqlalchemy script that creates and uses many engine instances representing many user connections. All the engines are configured to point to the same postgres database.

我有一个引擎， oSuperEngine 可以做到超级的东西。我还有一个针对Bob的引擎'oBobsEngine`。

I have one engine, oSuperEngine that can do super stuff. I have another engine 'oBobsEngine` for Bob.

现在我正在执行以下操作：

Now I'm doing something like this:

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

得到：

ProgrammingError: (ProgrammingError) permission denied for relation NICE_TABLE

的权限被拒绝？为什么？

Why is this?

在psql \dp 中告诉我，从未授予bob权限。如果我以超级用户身份登录psql并手动授予bob权限，则一切正常。在这种情况下，我使用与通过sqlalchemy执行的命令完全相同的命令。

in psql \dp tells me that bob was never granted permissions. If I log into psql with a super user and grant bob his permissions manually then everything works fine. I use exactly the same commands in this case as I was executing via sqlalchemy.

是否有些东西无法正确刷新？ sqlalchemy是否出于某种原因喜欢尝试授予语句时无提示地失败？我该怎么做呢？

Is something not flushing correctly? Does sqlalchemy for some reason like to fail silently when attempting grant statements? How can I make this work?

很抱歉，由于不包含更多代码，因此代码库有些复杂。已经使用日志向我确认了事件顺序...请让我知道是否需要更多代码才能理解我的问题。

Apologies for not including more code, the code base is moderately convoluted. The order of events has been confirmed to me using logs... please let me know if you need more code in order to understand my problem.

某些日志

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 ，第二个是鲍勃的引擎。错误看起来像：

The first bunch of stuff happens with oSuperEngine, the second bunch with bob's engine. And the error looks like:

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

推荐答案

如果执行上述脚本，则会遇到事务隔离问题。实际上，我前段时间也遇到过类似的问题。这里要记住的事情是，去往Bob的事务看不到超级引擎所做的更改，因为事务仍在进行，并且隔离不允许进行未提交的读取。 Postgres对此有大量文档，但其实质是：在postgres中不可能进行未提交的读取。

If you execute the script above you run into a problem with transaction isolation. I actually had a similar issue some time ago. The thing to keep in mind here is that the transaction going for Bob does not see changes made by the Super engine because the transaction is still going and the isolation does not allow for an uncommited read. Postgres has extensive documentation about this but the essence is: An uncommited read is not possible in postgres.

显然，您的解决方案是先从Super引擎提交更改，然后 在其他事务中使用它。如果没有尝试过，但我的猜测是您必须保持默认的读取已提交隔离级别（因为更高的隔离度不允许检测更改，因为事务（在本例中为Bobs）已经开始）。

And your solution, obviously, is to commit your changes from the Super engine first, then work with it in the other transaction. If have not tried it but my guess is that you have to stay on the default Read committed isolation level (as higher isolation wouldn't allow to detect changes since a transaction - in this case Bobs - has started).

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

Thus, before executing Bobs query:

oSuperEngine.execute("COMMIT")

但是，这引起了一个副问题，有效地打破了交易带来的令人敬畏的事情您：因为更改已经提交，所以您不能轻易回滚。您基本上想要的是与保存点相反的地方：

However, this raises a side issue that effectively breaks the awesome thing transactions bring you: You cannot roll back easily since the changes are already commited. What you'd basically want here is the opposite of a savepoint:

鉴于保存点尚未将更改存储到数据库中，因此可以确保回滚仅返回到一个特定的观点，并非一路走来。您想要相反的做法：将其存储在数据库中，但可以回滚并再次从数据库中删除。我不知道这种事情，我非常怀疑它的存在，因为它会违反事务隔离的原则。

Whereas a savepoint does not store changes to the database yet, it ensures a rollback would only go back to a particular point, not all the way. You'd want the opposite: Store it in the database but be able to roll back and remove it from it again. I am not aware of such a thing and I highly doubt it exists since it would violate the principles of transaction isolation.

我对这个问题的解决方案是编写自定义回滚例程我会要求一个例外，以便可以手动撤消更改。但是，这需要大量工作，并且从长远来看将是高维护性的。最后，以我为例，我重新考虑了解决方案，放弃了多引擎方法（但这是您必须自己做的决定）。

My solution to this problem was to write custom rollback routines that I would call on an exception so that I could manually undo the changes. This is quite a bit of work though and is high-maintenance in the long run. In the end, in my case, I reconsidered my solution and dropped the multiple-engine approach (but that is a decision you have to make yourself).

这篇关于在sqlalchemy中管理用户权限的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！