managing user privileges in sqlalchemy

Question

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.

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

And getting:

ProgrammingError: (ProgrammingError) permission denied for relation NICE_TABLE

Why is this?

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.

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.

Some logs

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

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);' {}

Answer 1

I had the exact same problem. Fixed by doing like this:

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