Hi Helio, I've tried something similar however, I believe what you're suggesting only works on a single table if I under stand correctly? for example: GRANT SELECT, INSERT, UPDATE, DELETE ON migration TO live_application; fails with the error: ERROR: relation "migration" does not exist SQL state: 42P01 On 11/03/2016, Helio Campos Mello de Andrade <helio.campos@xxxxxxxxx> wrote: > Hi Rob, > > - Does this helps? > > THE RE_MIGRATION_ROLE > ===================== > > # Creating the role > ## Create the role with your permissions > 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; > > ## Create the user > CREATE USER live_app_user1 ....; > GRANT live_application TO live_app_user1; > > Regards, > > -- > Helio Campos Mello de Andrade > > http://training.linuxfoundation.org/certification/verify-linux-certifications > (ID: LFCS-1500-0312-0100, Last name: Andrade) > http://www.expertrating.com/reports/transcript.aspx?transcriptid=1608144 > http://www.expertrating.com/reports/transcript.aspx?transcriptid=2962390 > > 2016-03-11 8:18 GMT-03:00 Rob Emery <re-pgsql@xxxxxxxxxxxxxxx>: > >> 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 >> >> <https://www.codeweavers.net> >> >> >> <http://t.sidekickopen42.com/e1t/c/5/f18dQhb0S7lC8dDMPbW2n0x6l2B9nMJN7t5XX4QBhKzW64Js984WrKTYVd0r_-56dzZCf1d8gHj02?t=http%3A%2F%2Fus6.campaign-archive1.com%2F%3Fu%3Ddb934935d4174e46e9063c8c4%26id%3De34a33f79a&si=4882959321006080&pi=04b7a1ae-3680-4ca6-d2bb-74bc3d2d6fc0> >> Codeweavers >> >> March >> Newsletter >> <http://t.sidekickopen42.com/e1t/c/5/f18dQhb0S7lC8dDMPbW2n0x6l2B9nMJN7t5XX4QBhKzW64Js984WrKTYVd0r_-56dzZCf1d8gHj02?t=http%3A%2F%2Fus6.campaign-archive2.com%2F%3Fu%3Ddb934935d4174e46e9063c8c4%26id%3D8939a8a212&si=4882959321006080&pi=04b7a1ae-3680-4ca6-d2bb-74bc3d2d6fc0> >> >> <http://t.sidekickopen42.com/e1t/c/5/f18dQhb0S7lC8dDMPbW2n0x6l2B9nMJN7t5XX4QBhKzW64Js984WrKTYVd0r_-56dzZCf1d8gHj02?t=http%3A%2F%2Fus6.campaign-archive2.com%2F%3Fu%3Ddb934935d4174e46e9063c8c4%26id%3D8939a8a212&si=4882959321006080&pi=04b7a1ae-3680-4ca6-d2bb-74bc3d2d6fc0> >> l >> >> *Codeweavers' Digital Marketing Conference >> <http://t.sidekickopen42.com/e1t/c/5/f18dQhb0S7lC8dDMPbW2n0x6l2B9nMJN7t5XX4QBhKzW64Js984WrKTYVd0r_-56dzZCf1d8gHj02?t=https%3A%2F%2Fcodeweavers.net%2Fcodeweavers-digital-marketing-conference&si=4882959321006080&pi=04b7a1ae-3680-4ca6-d2bb-74bc3d2d6fc0>* >> >> The launch of the stats that will help you sell more cars >> <http://t.sidekickopen42.com/e1t/c/5/f18dQhb0S7lC8dDMPbW2n0x6l2B9nMJN7t5XX4QBhKzW64Js984WrKTYVd0r_-56dzZCf1d8gHj02?t=https%3A%2F%2Fcodeweavers.net%2Fcompany-blog%2Fthe-launch-of-the-stats-that-will-help-you-sell-more-cars&si=4882959321006080&pi=04b7a1ae-3680-4ca6-d2bb-74bc3d2d6fc0> >> >> *Phone:* 0800 021 0888 * Email: *contactus@xxxxxxxxxxxxxxx >> *Codeweavers Ltd* | Barn 4 | Dunston Business Village | Dunston | ST18 >> 9AB >> Registered in England and Wales No. 04092394 | VAT registration no. 974 >> 9705 63 >> >> <https://www.linkedin.com/company/codeweavers-limited> >> <https://vimeo.com/codeweaversltd> >> <https://plus.google.com/b/105942302039373248738/+CodeweaversNet/posts> >> <https://twitter.com/CodeweaversTeam?lang=en-gb> >> > -- Robert Emery Infrastructure Director E: robertemery@xxxxxxxxxxxxxxx | T: 01785 711633 | W: www.codeweavers.net -- <https://www.codeweavers.net> <http://t.sidekickopen42.com/e1t/c/5/f18dQhb0S7lC8dDMPbW2n0x6l2B9nMJN7t5XX4QBhKzW64Js984WrKTYVd0r_-56dzZCf1d8gHj02?t=http%3A%2F%2Fus6.campaign-archive1.com%2F%3Fu%3Ddb934935d4174e46e9063c8c4%26id%3De34a33f79a&si=4882959321006080&pi=04b7a1ae-3680-4ca6-d2bb-74bc3d2d6fc0> Codeweavers March Newsletter <http://t.sidekickopen42.com/e1t/c/5/f18dQhb0S7lC8dDMPbW2n0x6l2B9nMJN7t5XX4QBhKzW64Js984WrKTYVd0r_-56dzZCf1d8gHj02?t=http%3A%2F%2Fus6.campaign-archive2.com%2F%3Fu%3Ddb934935d4174e46e9063c8c4%26id%3D8939a8a212&si=4882959321006080&pi=04b7a1ae-3680-4ca6-d2bb-74bc3d2d6fc0> <http://t.sidekickopen42.com/e1t/c/5/f18dQhb0S7lC8dDMPbW2n0x6l2B9nMJN7t5XX4QBhKzW64Js984WrKTYVd0r_-56dzZCf1d8gHj02?t=http%3A%2F%2Fus6.campaign-archive2.com%2F%3Fu%3Ddb934935d4174e46e9063c8c4%26id%3D8939a8a212&si=4882959321006080&pi=04b7a1ae-3680-4ca6-d2bb-74bc3d2d6fc0> l *Codeweavers' Digital Marketing Conference <http://t.sidekickopen42.com/e1t/c/5/f18dQhb0S7lC8dDMPbW2n0x6l2B9nMJN7t5XX4QBhKzW64Js984WrKTYVd0r_-56dzZCf1d8gHj02?t=https%3A%2F%2Fcodeweavers.net%2Fcodeweavers-digital-marketing-conference&si=4882959321006080&pi=04b7a1ae-3680-4ca6-d2bb-74bc3d2d6fc0>* The launch of the stats that will help you sell more cars <http://t.sidekickopen42.com/e1t/c/5/f18dQhb0S7lC8dDMPbW2n0x6l2B9nMJN7t5XX4QBhKzW64Js984WrKTYVd0r_-56dzZCf1d8gHj02?t=https%3A%2F%2Fcodeweavers.net%2Fcompany-blog%2Fthe-launch-of-the-stats-that-will-help-you-sell-more-cars&si=4882959321006080&pi=04b7a1ae-3680-4ca6-d2bb-74bc3d2d6fc0> *Phone:* 0800 021 0888 * Email: *contactus@xxxxxxxxxxxxxxx *Codeweavers Ltd* | Barn 4 | Dunston Business Village | Dunston | ST18 9AB Registered in England and Wales No. 04092394 | VAT registration no. 974 9705 63 <https://www.linkedin.com/company/codeweavers-limited> <https://vimeo.com/codeweaversltd> <https://plus.google.com/b/105942302039373248738/+CodeweaversNet/posts> <https://twitter.com/CodeweaversTeam?lang=en-gb> -- Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
