How can I set a different owner for all that alembic creates?

Question

So we have a restricted user that should update, insert and delete on the tables and one who may create, alter and drop tables.

We use alembic to migrate the database so of course the second user has to run the migration but then the first user has no rights to use the tables.

Of course I could run some postgres specific code to individually change the owner on whatever alembic creates but that can't be right. How am I supposed to solve this? Or is this a postgres issue? I don't see how I can grant user1 stuff on non-existing tables of one database.

Answer 1

I know it's an old question but I met the same issue and found two nice solutions for that:

1. the quick and easy - add a general grant command to the upgrade template (script.py.mako): mine looks hence:

def upgrade():
    ${upgrades if upgrades else "pass"}
    sql = "grant all on all tables in schema public to simpleuser"
    op.execute(sql)

2. if you're not happy with a too-general grant statement, you can try to alter the create_table directive in alembic to add a specific grant command after the table creation. I found this attitude too unnecessarily too complicated but if this your desired direction please read about alembic api here

P.S. when granting access to tables, never forget their sequences if they have serials