GRANT weirdness.

[Koen Bok]

I want to grant permissions through sqlalchemy, but for some reason if
I let an engine execute the sql it does not work:

Code:

from sqlalchemy import *

engine = create_engine('postgres://localhost/Test')
engine.echo = True

engine.execute('CREATE USER testuser;')
engine.execute('GRANT SELECT ON TABLE testtable TO testuser;')

Result:

Test=# \z testtable
Access privileges for database "Test"
Schema | Name | Type | Access privileges
--------+---------------+-------+--------------------
public | checkout_info | table | {koen=arwdxt/koen}
(1 row)

And when I do the exact same sql in psql

Test=# CREATE USER testuser;
CREATE ROLE
Test=# GRANT SELECT ON TABLE testtable TO testuser;
GRANT
Test=# \z testtable
Access privileges for database "Test"
Schema | Name | Type | Access privileges
--------+---------------+-------+------------------------------------
public | Test | table | {koen=arwdxt/koen,testuser=r/koen}
(1 row)

I never had any problems doing any other sql stuff like creating
triggers etc with this. Anyone got a hint?

Thanks,

Koen

[Koen Bok]

And ofcourse the first result should be this in the above mail:

> Test=# \z testtable
> Access privileges for database "Test"
> Schema | Name | Type | Access privileges
> --------+---------------+-------+--------------------

> public | testtable | table | {koen=arwdxt/koen}
> (1 row)

[Michael Bayer]

On Apr 29, 2007, at 9:28 AM, Koen Bok wrote:

>
> I want to grant permissions through sqlalchemy, but for some reason if
> I let an engine execute the sql it does not work:
>
> Code:
>
> from sqlalchemy import *
>
> engine = create_engine('postgres://localhost/Test')
> engine.echo = True
>
> engine.execute('CREATE USER testuser;')
> engine.execute('GRANT SELECT ON TABLE testtable TO testuser;')

my only guess is that the GRANT needs a "COMMIT" issued afterwards
(which is a postgres weirdness in itself, that schema changes are
part of transactions). SA's autocommit logic is based on the
statement text containing INSERT, UPDATE, ALTER etc. but i dont think
GRANT is in there. so use an explicit connection/transaction for now.

[Koen Bok]

Yep that's it.

If anyone has the same just do this:

engine.execute('BEGIN;GRANT SELECT ON TABLE checkout_info TO
testuser;COMMIT;')

Ciao

Koen