Manage user privileges in sqlalchemy

Question

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

I have one engine oSuperEngine, which can do super. I have another oBobsEngine engine for Bob.

Now I am 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

And having received:

ProgrammingError: (ProgrammingError) permission denied for relation NICE_TABLE

Why is this?

in psql \dptells me that bob has never been granted permissions. If I log into psql with superuser and grant bob my permissions manually, then everything works fine. I use exactly the same commands in this case as I did through sqlalchemy.

- ? sqlalchemy - ? ?

, , . , ..., , , , .

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

script , . . , , , , Super, , . Postgres , , postgres .

, , , Super engine, , . , , ( - - ).

, Bobs:

oSuperEngine.execute("COMMIT")

, , : , . , , :

, , , . : , . , , , .

, , , . , . , , ( , ).

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