Separate DDL and SQL users - Mailing list pgsql-admin

Hello All,

So we're looking at automating our migrations against PG for the developers so that it's simple enough for them and no maintenance for me. I'm struggling to find a role/permissions structure that works; we've come from SQL Server so we're used to having DBRoles.

So I want the re_migration role to be able to create tables, sequences etc and grant to other users etc; yet I want the live_application role to be able to select,insert,update,delete.

It seems that the only real solution here is to have the db owned by re_migration, then in every migration GRANT SELECT,INSERT,UPDATE,DELETE to the live_application role?

Previously I've always set the owner on tables etc to 'postgres' so that it's not bound to any special user.

Any suggestions?

Thanks,
Rob