CREATE ROLE re_migration WITH optional_permissions;
## Grant role ALL permissions in the database migration GRANT ALL ON migration TO re_migration;
## Make everyone in this role to automaticaly have all the role's privileges ALTER ROLE re_migration INHERIT;
## Create the user
CREATE USER migration_user1 ....;
GRANT re_migration TO migration_user1;
THE LIVE_APPLICATION_ROLE
=========================
# Creating the role
## Create the role with your permissions CREATE ROLE live_application WITH optional_permissions;
## Grant role SELECT, INSERT, UPDATE, DELETE permissions in the database migration GRANT SELECT, INSERT, UPDATE, DELETE ON migration TO live_application;
## Make everyone in this role to automaticaly have all the role's privileges ALTER ROLE live_application INHERIT;
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.
Phone:0800 021 0888 Email: contactus@codeweavers.net Codeweavers Ltd | Barn 4 | Dunston Business Village | Dunston | ST18 9AB Registered in England and Wales No. 04092394 | VAT registration no. 974 9705 63